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

Фон:

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

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

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

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

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

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

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

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

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

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

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

Ответы

Ответ 1

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

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

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

Ответ 2

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

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

Ответ 3

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