Как загрузить в PostgreSQL 500 млн записей и работать с ними в R

Я заметил, что истории из моей жизни пользуются большим успехом, чем скучные новости о Linux. Поэтому рассказываю, чем я сегодня занимался.

[TOC Краткое содержание]

Как разбогатеть на покупке и продаже акций

Буржуинский заказчик хочет стать олигархом. Разбогатеть планируется на спекуляциях акциями, так как буржуи вообще не любят работать руками. Их бы к нам в Россию-матушку! Быстро бы на заводе перевоспитали! Но как понять, какие акции покупать, а какие продавать? Для этого, считает заказчик, нужно запихнуть все котировки акций за всё время в базу данных и затем шаманить над ней с помощью R, искать скрытые взаимосвязи. Я считаю такой взгляд несколько наивным, но переубеждать иностранного гражданина не стал, по понятным причинам. К тому же, сам таким был:

• Прогнозирование биржевых котировок в R
• Как Linux и R помогли мне заработать на Forex
• Бежать ли в обменник: используем силу Linux и R для прогнозирования курса доллара
• Искусственный интеллект предсказал курс доллара. Валютчики в шоке!

Короче, еле унес ноги. Но последние трусы пришлось продать. Но сейчас не об этом, хотя тема про мои трусы не менее важна для читателя Либератума.

PostgreSQL — сила, Mongo — могила

Использовать решили PostgreSQL. MySQL сдохнет на первой сотне миллионов записей, да еще отсутствие многих нужных фич делает ее непригодной для более-менее серьезного использования. Всякие хипстерские штуки типа MongoDB тоже всерьез не рассматривали. Шлак типа Монги приходит и уходит, а классика Postgres вечна.

PostgreSQL скушал 500M записей и подавился

Исходные данные в CSV. И их много — около 50 Гб. Это, разумеется, только первая часть. Так, для тестирования. Будет на порядок, а то и на два, больше. Я написал на коленке скрипт на Ruby, который парсит CSV, чистит данные и проверяет их. А потом распихивает по таблицам. Их две. В первой сами данные хранятся, во второй хранятся метаданные: символ, на какой бирже торговались, резолюция и т.п. Через 300M записей Postgres торжественно умер. Формально поциент оставался немного живым, но самые простые запросы выполнялись по 10-20 минут. Пришлось усыпить.

Обновление PostgreSQL до 10.6

Я снес PostgreSQL 9.6 из стандартной поставки Ubuntu 16.04 и поставил десяточку 1 из официального репозитория. Говорят, в 10 запилили крутое партицирование. Как оказалось, не такое уж оно и крутое. Синтаксис стал действительно намного более удобным и приятным, а вот производительность не радует. Снова пришлось усыпить бедолагу, чтобы не мучился.

pg_pathman — гениальное изобретение талантливых российских погромистов

Решил попробовать расширение pg_pathman, так как недавно случайно посмотрел про него мультик на Ютюбе. Скачал исходники, скомпилировал, приделал к PgSQL и запустил. У меня нет слов! Пользоваться pg_pathman еще проще, чем нативным партицированием. Скорость увеличилась на 2 порядка. Загрузил полмиллиарда записей, а поциент по-прежнему бодр и весел, в отличном расположении духа. Партицировал на тысячу секций по символу акции. Родительская таблица прозрачно распилилась на дочерние и равномерно2 распределилась. Пока данные лежат на одном SSD, но я так понимаю, что потом их можно будет разнести по разным дискам. Кстати, думали о партицировании по времени, так как некоторые старые данные используются реже, чем актуальные. Можно разделить и так, а потом положить старые данные на HDD, новые на SSD? а совсем новые запихнуть в ОЗУ.

Вперед, к новым горизонтам!

Раньше мысль запихнуть в базу даже 100 млн записей пугала. Теперь я вижу, что партицирование и прекрасный Postgres открывают новые горизонты. Теперь я мечтаю побыстрее запихнуть в базу 100 пиздилюонов записей. Я уверен, у меня всё получится.

Россия встает с колен

Спасибо разработчикам pg_pathman! Вдвойне приятно, что это ребята из России. Я так понимаю, что когда Оракл стали гнать из России сцаной метлой, в России открылась фирма Postgres Professional, которая рождена, чтоб сказку сделать былью допилить Postgres до уровня Oracle. Пока получается очень даже неплохо. В то время, как бездуховные буржуины думают как наспекулировать акциями, талантливые русские погромисты выводят оупенсорсный продукт на высочайший уровень.

• 1. 10.6, есть еще 11.
• 2. Почти.