PostgreSQL — как архивировать старые записи в большой таблице?

Question

[Михаил Юрьевич]

Есть таблица:

• 100 млн записей
  
• каждый день прирост 1-2 млн записей
  
• горячие данные - за последний месяц
  

На данный момент база крутится на SSD дисках, ищем вариант оптимального архивирования старых записей, пока вырисовывается такое решение:

• поднимаем второй сервер на больших HDD дисках
  
• по крону складываем старые записи
  
• меняем код приложения - добавляем запросы к 2ум базам на основе необходимой даты
  

Больше всего смущает последний пункт, существует ли прозрачное решение отправки 2ух запросов к разным базам и автоматическое слияния результата?
Может есть более правильный подход к данной проблеме?
Что делать когда данных станет больше и понадобиться уже несколько серверов для архивации?

Comments

[sim3x]

данные из таблицы слабо связаны (не связаны) в бд?
Почему появилось желание заняться такими вещами?

[Михаил Юрьевич]

sim3x: данные не связаны, желание - держать горячие данные в быстрой бд(ssd) и остальные(более старые, к ним такие же запросы, только реже) в архивной, при этом максимально прозрачно для приложения

[sim3x]

Forbidden: те начались проседания по скорости?

*замечание: "быстрая база" только тогда, когда она в памяти лежит полностью, чуть медленне, когда все индексы лежат в памяти
**если под ссд не понимается массив, включенный через pci, с ddr5 кешем на борту

[Михаил Юрьевич]

sim3x: проседаний нет, но в обозримом будущем вылезем за размеры SSD

Answer 1

[Melkij]

Как разделить таблицу, горячие данные оставить на SSD, холодные - на HDD. Для этого во-первых партицирование для разделения таблицы на две. https://habrahabr.ru/post/273933/ (как обычно, внимание на комменты и pg_partman)
Затем, до миграции данных (или сразу при создании партиций), перенос архивных в другой tablespace www.postgresql.org/docs/current/static/sql-createt... stackoverflow.com/a/11228536 на HDD.
Затем миграция данных на партиции.
Вообще-то, это уже может быть вполне достаточно. 1-2млн строк * 365 дней это не запредельно много. Хотя не указан характер данных.

Прозрачный для приложения перенос таблиц на другую железку - FDW, foreign data wrapper. Чем актуальнее postgresql - тем лучше. Пилится штука весьма активно по части оптимального распределения запроса. Дружит ли уже с партицированием - честно, не в курсе.

Прозрачно отправить запрос на две базы и склеить - элементарно view с union all из локальной таблицы и FDW. Только это неинтересный вариант, зачем для запроса на горячие данные дёргать холодную часть базы?

Вдобавок, можете посмотреть в сторону postgresql-xl, greenplum. Первый года полтора назад был не вполне production-ready, сейчас не знаю, второй используется даже в банковской сфере, но как мне помнится катастрофически не годится для OLTP, только OLAP нагрузка.

Comments

[Михаил Юрьевич]

Спасибо за такой развернутый ответ, многое стало на свои места