От материализованных представлений к непрерывным агрегатам: улучшение PostgreSQL с помощью аналитики в реальном времени

Предоставление пользователям доступа к аналитике данных в режиме реального времени является ключевой возможностью многих современных приложений. Представьте, что вы используете свою любимую платформу SaaS — скорее всего, это интуитивно понятная панель управления, представляющая данные в реальном времени и историческую информацию. Вероятно, вы сможете взаимодействовать с платформой, создавая индивидуальные отчеты, изучая подробные показатели и визуализируя тенденции, охватывающие недели или месяцы.

Вы, конечно, не хотели бы, чтобы эта платформа была медленной для пользователя. Это означает, что база данных, лежащая в основе этих продуктов, должна быстро выполнять запросы к большим объемам данных, включая сложные аналитические запросы.

Пока PostgreSQL сегодня является самой любимой базой данных среди разработчиков. , он не известен своей скоростью запроса больших объемов данных. Но не волнуйтесь: в наборе инструментов Postgres всегда есть инструмент. Один из лучших — материализованные представления.

Что такое материализованные представления PostgreSQL?

Материализованные представления PostgreSQL, основанные на методе материализации, предварительно вычисляют часто выполняемые запросы и сохраняют результаты в виде таблицы. В отличие от стандартных представлений PostgreSQL, которые запускают базовый запрос каждый раз при обращении к представлению, материализованные представления сохраняют результат исходного запроса в базе данных. Самое замечательное в этом то, что вашей базе данных не придется выполнять запрос каждый раз, когда вы его запускаете: результаты уже доступны на диске — вы получите ответ на свой запрос гораздо быстрее.

Это отличный способ оптимизировать ответы на запросы, требующие больших ресурсов для вычислений. Например, запросы, которые могут включать обработку больших объемов данных, агрегаты или множественные соединения.

Работать с материализованными представлениями очень просто. Чтобы создать представление, вы должны использовать оператор CREATE MATERIALIZED VIEW и выбранный вами запрос.

После создания материализованного представления вы можете запросить его как обычную таблицу PostgreSQL :

 CREATE MATERIALIZED VIEW customer_orders AS SELECT customer_id, COUNT(*) as total_orders FROM orders GROUP BY customer_id;

 -- Query the materialized view SELECT * FROM customer_orders;

Это материализованное представление быстро устареет, пока вы его не обновите: даже если вы добавляете новые данные в базовую таблицу (или обновляете или удаляете данные), материализованное представление не включает эти изменения автоматически; это снимок на момент создания. Чтобы обновить материализованное представление, нужно запустить REFRESH MATERIALIZED VIEW .

 REFRESH MATERIALIZED VIEW customer_orders;

Этот последний момент (то, как обрабатываются обновления) является ахиллесовой пятой материализованных представлений, о чем мы поговорим в следующем разделе.

А как насчет аналитики в реальном времени? Ограничения материализованных представлений PostgreSQL

Как мы уже говорили, материализованные представления PostgreSQL — мощный инструмент для ускорения часто выполняемых запросов, особенно если эти запросы обрабатывают большие объемы данных. Но у материализованных представлений есть один далеко не идеальный аспект: чтобы поддерживать актуальность материализованных представлений, их необходимо обновлять.

Эта единственная проблема создает три важных ограничения:

Обновления неэффективны и требуют больших вычислительных затрат.

При обновлении материализованного представления запрос пересчитывается для всего набора данных. На самом деле, когда вы запускаете обновление, старые материализованные данные удаляются, а затем заменяются новыми, повторно материализованными данными. Реализация дополнительные обновления (когда обновляются только измененные данные) сделает процесс агрегирования намного более эффективным, но его сложно правильно реализовать в рамках согласованной реляционной базы данных. Иногда возможны обходные пути с помощью дополнительных сценариев, но они далеко не просты, особенно для сложных запросов или в случае поступления запоздалых данных.

Обновления не запускаются автоматически

Как упоминалось ранее, материализованные представления не будут автоматически включать последние данные. Их необходимо обновить, выполнив команду REFRESH MATERIALIZED VIEW . Запуск обновления вручную в производственных условиях невозможен: гораздо более реалистичной была бы автоматизация обновления.

К сожалению, материализованные представления не имеют встроенной функции автоматического обновления, поэтому для создания расписания автоматического обновления материализованных представлений в PostgreSQL требуется какой-либо планировщик. Это можно выполнить в базе данных с помощью расширения или вне базы данных с помощью планировщика, такого как cron. Однако это удалось, поскольку обновления стоят дорого и занимают много времени. Очень легко оказаться в ситуации, когда вы не можете обновить представление достаточно быстро.

Материализованные представления не отображают актуальные результаты.

Следствием статической природы материализованных представлений является то, что при запросе они пропускают данные, добавленные или измененные с момента последнего обновления (даже если это обновление происходит по расписанию). Если ваше окно планирования установлено на час, то ваш совокупный показатель будет составлять до часа плюс фактическое время для устаревания обновления. Но сегодня многие приложения подразумевают постоянный поток принимаемых данных, и часто этим приложениям приходится предлагать своим пользователям актуальные результаты, чтобы гарантировать, что они получают точную информацию при запросе представления.

Жаль, что материализованные взгляды ограничены этими ограничениями. Если вы создаете платформу SaaS на основе живого набора данных, в который часто поступают новые данные, следует ли полностью отказаться от материализованных представлений?

Ответ - нет. В Timescale мы создали решение, которое эффективно улучшает материализованные представления, делая их более подходящими для современных приложений: непрерывные агрегаты.

Встречайте непрерывные агрегаты: материализованные представления с автоматическим обновлением для аналитики в реальном времени

Представьте себе мир, в котором материализованные представления — это не просто статические снимки, а динамически и эффективно обновляемые. Вы получите доступ к желаемому повышению производительности запросов, не беспокоясь ни о чем другом. Ну, вроде бы мы описали непрерывные агрегаты Timescale.

Непрерывные агрегаты (доступные для всех баз данных PostgreSQL через расширение TimescaleDB и в AWS через платформу Timescale) представляют собой материализованные представления, дополненные эффективными возможностями автоматического обновления и элементом реального времени. Они выглядят и работают почти так же, как материализованные представления, но позволяют следующее:

• Автоматические обновления с помощью политики обновления
• Более эффективный процесс обновления: при запуске обновления затрагиваются только те данные, которые изменились с момента последнего обновления.
• Актуальные результаты, расширяющие возможности использования материализованных представлений (например, аналитика в реальном времени, интерактивные информационные панели, отчеты и т. д.).

Автоматическое и ресурсоэффективное обновление

Создание непрерывного агрегата очень похоже на создание материализованного представления (и его также можно запрашивать как обычную таблицу PostgreSQL):

 CREATE MATERIALIZED VIEW hourly_sales WITH (timescaledb.continuous) AS SELECT time_bucket(INTERVAL '1 hour', sale_time) as hour, product_id, SUM(units_sold) as total_units_sold FROM sales_data GROUP BY hour, product_id;

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

В приведенном ниже примере устанавливается политика обновления для непрерывного обновления агрегата каждые 30 минут. Параметр end_offset определяет временной диапазон обновляемых данных, а schedule_interval задает частоту обновления непрерывного агрегата:

 -- Setting up a refresh policy SELECT add_continuous_aggregate_policy('hourly_sales', end_offset => INTERVAL '1 minute', schedule_interval => INTERVAL '30 minutes');

Когда эта политика обновления вступит в силу, процесс станет намного более эффективным, чем если бы мы использовали простое материализованное представление. В отличие от выполнения REFRESH MATERIALIZED VIEW , при обновлении непрерывного агрегата Timescale не удаляет все старые данные и не пересчитывает агрегат по ним: механизм просто запускает запрос к самому последнему периоду обновления (например, 30 минут) и добавляет его. к материализации.

Аналогично, UPDATE и DELETE , выполненные в течение этого последнего периода, идентифицируются, пересчитывая фрагмент (раздел), который их включает. (Непрерывные агрегаты, построенные на основе Timescale гипертаблицы , которые представляют собой автоматически секционированные таблицы PostgreSQL. Это огромное преимущество, позволяющее движку пересчитывать только определенные разделы, а не всю таблицу в случае изменения данных.)

Отображение актуальных результатов для аналитики в реальном времени

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

Чтобы обеспечить эту функциональность, мы добавили функция агрегирования в реальном времени к непрерывным агрегатам. Когда включена агрегация в реальном времени и вы запросите непрерывный агрегат, результат, который вы увидите, будет состоять из двух частей:

• Материализованные данные в базовом материализованном представлении, которое было обновлено при последнем обновлении.
• Самые последние, еще не материализованные необработанные данные, которые все еще находятся исключительно в вашей базовой таблице (или, если быть точным, в гипертаблице).

Эта функция преобразует материализованные представления из статических снимков в динамические объекты, гарантируя, что хранимые данные являются не просто историческим отражением, а актуальным представлением базовых наборов данных.

Использование непрерывных агрегатов: пример

Даже если все это звучит хорошо, (надеюсь) это будет лучше сочетаться с примером.

Представьте себе платформу, используемую транспортными агентствами и компаниями по совместному использованию поездок. Эта платформа содержит информационную панель, на которой компании могут видеть обзор состояния своего автопарка, включая таблицу с последним статусом ключевых показателей и две визуализации, показывающие, как показатели работают в тот конкретный день и в контексте недели.

Для работы этого приложения нам сначала понадобится гипертаблица, в которую постоянно вставляются данные о поездках. Гипертаблица может выглядеть примерно так:

 CREATE TABLE rides ( ride_id SERIAL PRIMARY KEY, vehicle_id INT, start_time TIMESTAMPTZ NOT NULL, end_time TIMESTAMPTZ NOT NULL, distance FLOAT NOT NULL, price_paid FLOAT NOT NULL ); SELECT create_hypertable('rides', 'start_time');

Гипертаблицы очень быстры и масштабируемы — эта таблица останется производительной, даже если в ней миллиарды строк.

Чтобы обеспечить таблицу оперативным обзором, мы будем использовать непрерывный агрегат для группировки данных по 30 минутам. Это позволит сделать процесс быстрым и отзывчивым:

 -- Create continuous aggregate for live overview CREATE MATERIALIZED VIEW live_dashboard WITH (timescaledb.continuous, timescaledb.materialized_only=false)) AS SELECT vehicle_id, time_bucket(INTERVAL '30 minute', start_time) as minute, COUNT(ride_id) as number_of_rides, AVG(price_paid) as average_price FROM rides GROUP BY vehicle_id, minute;

 -- Set up a refresh policy SELECT add_continuous_aggregate_policy('live_dashboard', end_offset => INTERVAL '10 minutes', schedule_interval => INTERVAL '15 minute');

В предыдущем коде параметр end_offset гарантирует, что агрегат не будет немедленно пытаться обновить самые последние данные, предоставляя некоторое буферное время для компенсации любых задержек в поступлении данных. Установка значения end_offset равным 10 minutes означает, что агрегат будет обновлять данные, возраст которых не менее 10 минут, гарантируя, что обновления не будут пропущены из-за незначительных задержек в притоке данных. В реальном случае вы должны скорректировать это значение на основе средней задержки, которую вы наблюдаете в своем конвейере данных.

Чтобы обеспечить визуализацию, предлагающую ежедневный обзор, мы создадим второй непрерывный агрегат. На этой диаграмме данные отображаются по часам, поэтому нам не нужна поминутная детализация, как в предыдущем случае:

 -- Create continuous aggregate for daily overview CREATE MATERIALIZED VIEW hourly_metrics WITH (timescaledb.continuous, timescaledb.materialized_only=false) AS SELECT vehicle_id, time_bucket(INTERVAL '1 hour', start_time) as hour, COUNT(ride_id) as number_of_rides, SUM(price_paid) as total_revenue FROM rides WHERE start_time > NOW() - INTERVAL '1 day' GROUP BY vehicle_id, hour;

 -- Define refresh policy SELECT add_continuous_aggregate_policy('hourly_metrics', end_offset => INTERVAL '10 minutes', schedule_interval => INTERVAL `1 hour`);

Наконец, чтобы диаграмма отображала неделю, мы создадим еще один непрерывный агрегат, на этот раз агрегируя данные по дням:

 -- Create continuous aggregate to power chart with weekly overview CREATE MATERIALIZED VIEW daily_metrics WITH (timescaledb.continuous, timescaledb.materialized_only=false) AS SELECT vehicle_id, time_bucket(INTERVAL '1 day', start_time) as day, COUNT(ride_id) as number_of_rides, SUM(price_paid) as total_revenue FROM rides WHERE start_time > NOW() - INTERVAL '1 week' GROUP BY vehicle_id, day;

 -- Define refresh policy SELECT add_continuous_aggregate_policy('daily_metrics', end_offset => INTERVAL '10 minutes', schedule_interval => INTERVAL '1 day);

PS Чтобы сделать определение непрерывных агрегатов еще более эффективным, В шкале времени представлены иерархические непрерывные агрегаты. в TimescaleDB 2.9. Познакомившись с непрерывными агрегатами, вы можете начать создавать их поверх других непрерывных агрегатов — например, в предыдущем примере вы также можете определить почасовые агрегаты поверх поминутного агрегата.

Заключение

Даже если PostgreSQL изначально не создавался для приложений, которым необходимо обрабатывать большие наборы данных в реальном времени, угадайте, что — эти типы рабочих нагрузок теперь повсюду. Однако в PostgreSQL есть функции, которые помогают справиться с этой задачей. Материализованные представления являются одними из самых мощных, поскольку они позволяют предварительно вычислять результаты запроса и сохранять их на диске для быстрого поиска.

Однако материализованные представления имеют три важных ограничения. Во-первых, запуск обновлений очень неэффективен в вычислительном отношении. Во-вторых, даже настройка этих автоматических обновлений не является гладким процессом. В-третьих, материализованные представления не отображают актуальные результаты, поскольку исключают данные, которые были добавлены или изменены с момента последнего обновления.

Эти ограничения делают материализованные представления непрактичным решением для многих современных приложений. Чтобы решить эту проблему, мы построили непрерывные агрегаты. Это материализованные представления PostgreSQL, в которых вы можете легко определить политику обновления, чтобы обновления происходили автоматически. Эти обновления также являются инкрементными и, следовательно, гораздо более эффективными. Наконец, непрерывные агрегаты позволяют объединять материализованные данные с необработанными данными, добавленными и измененными с момента последнего обновления, гарантируя, что вы получите только актуальные результаты.

Если вы используете PostgreSQL на своем оборудовании, вы можете получить доступ к непрерывным агрегатам, выполнив установка расширения TimescaleDB . Если вы используете AWS, обратите внимание на платформу Timescale. . Первые 30 дней бесплатно.

Авторы сценария Карлота Сото и Мэт Арье.