PostgreSQL в хранилище данных: лучший подход для ETL / извлечения данных почти в реальном времени

Question

Фон:

У меня есть база данных PostgreSQL (v8.3), которая сильно оптимизирована для OLTP.

Мне нужно извлекать из него данные на основе полу-реального времени (кто-то обязательно спросит, что означает полу-реальное время, и ответ будет настолько часто, насколько это возможно, но я буду прагматичен, так как тест позволяет сказать, что мы надеемся на каждые 15 минут) и передать его в хранилище данных.

Сколько данных? В пиковые моменты времени мы говорим о 80-100 тыс. Строк в минуту, попадающих на сторону OLTP, в непиковое время это значительно снизится до 15-20 тыс. Наиболее часто обновляемые строки имеют размер ~64 байта в каждой, но существуют различные таблицы и т. Д., Поэтому данные довольно разнообразны и могут варьироваться до 4000 байтов в строке. OLTP активен 24x5,5.

Лучшее решение?

Из того, что я могу собрать, наиболее практичное решение состоит в следующем:

• Создайте TRIGGER для записи всех действий DML во вращающийся файл журнала CSV
• Выполните все необходимые преобразования
• Используйте встроенный инструмент для передачи данных DW, чтобы эффективно перекачать преобразованный CSV в DW

Почему этот подход?

• Триггеры позволяют выбирать целевые таблицы, а не быть общесистемными + вывод настраивается (т.е. в CSV) и относительно прост в написании и развертывании. SLONY использует аналогичный подход и накладные расходы приемлемы
• CSV легко и быстро трансформируется
• Легко качать CSV в DW

Альтернативы рассматриваются....

• Использование собственного ведения журнала ( http://www.postgresql.org/docs/8.3/static/runtime-config-logging.html). Проблема в том, что это выглядело очень многословно относительно того, что мне было нужно, и было немного сложнее разобрать и преобразовать. Однако это может быть быстрее, так как я полагаю, что по сравнению с TRIGGER накладных расходов меньше. Конечно, это облегчит работу администратора, так как оно распространяется на всю систему, но опять же, мне не нужны некоторые таблицы (некоторые используются для постоянного хранения сообщений JMS, которые я не хочу регистрировать)
• Запрос данных напрямую через инструмент ETL, такой как Talend, и закачка их в DW ... проблема заключается в том, что для поддержки этой схемы OLTP потребуется настроить, и это имеет много негативных побочных эффектов.
• Использование измененного / взломанного SLONY - SLONY хорошо регистрирует и переносит изменения на подчиненное устройство, поэтому концептуальная основа существует, но предлагаемое решение кажется проще и чище
• Используя WAL

Кто-нибудь делал это раньше? Хотите поделиться своими мыслями?

Answer 1

Предполагая, что ваши интересующие таблицы имеют (или могут быть дополнены) уникальный индексированный последовательный ключ, вы получите гораздо более выгодную ценность от простой выдачи SELECT ... FROM table ... WHERE key > :last_max_key с выводом в файл, где last_max_key является последним значением ключа из последнего извлечения (0 при первом извлечении.) Этот инкрементный, отделенный подход избегает введения задержки триггера в пути ввода данных (будь то пользовательские триггеры или измененный Slony), и в зависимости от вашей настройки может масштабироваться лучше с числом Процессоры и т. Д. (Тем не менее, если вы также должны отслеживать UPDATE с, и последовательный ключ был добавлен вами, а затем ваш UPDATE заявления должны SET ключевой столбец для NULL поэтому он получает новое значение и выбирается при следующем извлечении. Вы не сможете отслеживать DELETE без триггера.) Это то, что вы имели в виду, когда упомянули Talend?

Я не буду использовать средства ведения журнала, если вы не можете реализовать решение выше; Ведение журнала, скорее всего, включает в себя накладные расходы блокировки, чтобы гарантировать, что строки журнала записываются последовательно и не перекрываются / не перезаписывают друг друга, когда в журнал записывается несколько бэкэндов (проверьте исходный код Postgres.) Затраты на блокировку могут быть не катастрофическими, но вы можете обойтись без них, если Вы можете использовать инкрементный SELECT альтернатива. Более того, регистрация операторов заглушит любые полезные сообщения WARNING или ERROR, и сам синтаксический анализ не будет мгновенным.

Если вы не хотите анализировать WAL (включая отслеживание состояния транзакции и готовность переписывать код каждый раз, когда вы обновляете Postgres), я также не обязательно буду использовать WAL - то есть, если у вас нет дополнительного оборудования, в этом случае вы может отправлять WAL на другую машину для извлечения (на второй машине вы можете использовать бесстыдные триггеры - или даже запись операторов - поскольку все, что там происходит, не влияет INSERT / UPDATE / DELETE производительность на основном компьютере.) Обратите внимание, что с точки зрения производительности (на основном компьютере), если вы не можете записывать журналы в SAN, вы получите сопоставимый удар по производительности (в основном с перегрузкой кеша файловой системы) от доставки WAL. на другой машине, как от запуска инкрементной SELECT,

Answer 2

Если вы можете думать о "таблице контрольных сумм", которая содержит только идентификаторы и "контрольные суммы", вы можете не только быстро выбрать новые записи, но также и измененные и удаленные записи.

контрольная сумма может быть функцией контрольной суммы crc32, которая вам нравится.

Answer 3

Новое предложение ON CONFLICT в PostgreSQL изменило способ, которым я делаю много обновлений. Я извлекаю новые данные (основанные на row_update_timestamp) во временную таблицу, а затем в одном операторе SQL INSERT в целевую таблицу с помощью ON CONFLICT UPDATE. Если ваша целевая таблица секционирована, то вам нужно прыгнуть через пару обручей (то есть напрямую попасть в таблицу разделов). ETL может произойти при загрузке таблицы Temp (скорее всего) или в ON ON CONFLICT SQL (если тривиально). По сравнению с другими системами "UPSERT" (обновление, вставка, если ноль строк и т. Д.), Это показывает значительное улучшение скорости. В нашей конкретной среде DW нам не нужно / не нужно размещать DELETE. Посмотрите документы ON CONFLICT - это дает Oracle MERGE возможность заработать деньги!

Answer 4

Мой взгляд на эту тему на сегодняшний день 2023 года...

Вариант 1 (пакетный подход):

• Промежуточное извлечение с использованием целого числа или отметки времени для сохранения максимального количества строк, передаваемых на таблицу на каждой итерации. Мы всегда можем использовать оператор ONCONFLICT, чтобы избежать неожиданного нарушения ключа из-за неожиданного сбоя итерации. Таким образом невозможно отслеживать удаление строк, но мы можем использовать флаг удаления в качестве столбца для фильтрации в хранилище данных.
• DataWarehouse использует вычисляемые таблицы, создавая хранимые процедуры для выполнения сложных соединений/вычислений и вставки результатов в новые предварительно рассчитанные таблицы.

-Option2 (конвейерный подход)

• Промежуточное использование логической репликации для извлечения в реальном времени. Логическая репликация может фиксировать и реплицировать изменения в той же последовательности, в которой они произошли, поэтому целевая база данных всегда будет согласованной. Таким образом можно также отслеживать удаления.
• DataWarehouse использует сочетание добавочных материализованных представлений для предварительно рассчитанных облегченных соединений/вычислений в реальном времени и вычисляемых таблиц, использующих хранимые процедуры для более тяжелых соединений/вычислений, поскольку IVM в настоящее время не поддерживает соединения и все типы агрегаций.