Проектирование таблицы временных рядов в PostgreSQL только для добавления данных

Цель

Создать таблицу, которая принимает постоянный поток строк с временными метками, быстро отвечает на запрос «ряд X между t1 и t2» и дёшево удаляет старые данные без разрастания таблицы.

Предварительные условия

Известный срок хранения, основные формы запросов — один ряд в интервале или агрегаты по интервалам, — оценка числа строк в сутки и тип времени: timestamptz, документированное сокращение timestamp with time zone, хранит абсолютный момент и исключает неоднозначность переходов на летнее время.

Порядок действий

1. Определите строку: series_id — внешний ключ к таблице метаданных, ts timestamptz NOT NULL, столбцы измерений с NOT NULL, если отсутствие значения невозможно, и без суррогатного ID, если на отдельные строки нет ссылок. Первичный ключ (series_id, ts) обслуживает и основной запрос; документация требует, чтобы первичный ключ или ограничение уникальности секционированной таблицы включали все столбцы ключа секционирования.
2. Создайте таблицу с PARTITION BY RANGE (ts) и одной секцией на день, неделю или месяц. Выберите размер так, чтобы типичный интервал запроса охватывал немного секций, а срок хранения составлял их целое число. Документация предупреждает: слишком много секций увеличивает время планирования и потребление памяти.
3. Заблаговременно создавайте будущие секции заданием по расписанию или инструментом вроде pg_partman. Вставка в отсутствующий диапазон завершится ошибкой; секция DEFAULT незаметно примет такие строки. Документация отмечает, что при последующем присоединении новой секции выполняется сканирование секции по умолчанию под блокировкой ACCESS EXCLUSIVE, если ограничение CHECK не исключает новый диапазон.
4. Создавайте индексы под форму запроса: первичный ключ B-tree для диапазонов одного ряда; индекс BRIN по ts для временных сканирований всей таблицы. Документация по типам индексов описывает BRIN как сводки по диапазонам физических блоков; они эффективны, когда значения коррелируют с физическим положением, как в данных, которые только добавляются.
5. Обеспечьте срок хранения через ALTER TABLE ... DETACH PARTITION ... CONCURRENTLY, при необходимости архивируйте данные, затем выполняйте DROP TABLE. Документация по секционированию указывает, что это намного быстрее массового DELETE и не создаёт связанных с ним затрат на VACUUM.
6. Добавьте таблицу сводных данных — почасовых или суточных агрегатов, — заполняемую заданием, если панели мониторинга запрашивают длинные интервалы.
7. Загружайте данные пакетами через COPY или многострочный INSERT, упорядочивая по времени, чтобы диапазоны BRIN оставались узкими.

Ожидаемый результат

Запросы с условием по времени затрагивают лишь секции нужного диапазона благодаря отсечению секций; вставки попадают в новейшую секцию; старые данные исчезают посредством операции над метаданными, а удаление не вызывает разрастания.

Ограничения и основания метода

Отсечение требует условия по ключу секционирования; запрос по ряду без временной границы читает индекс каждой секции. Обновления и поступление данных не по порядку ослабляют корреляцию BRIN. Подход следует указанной документации; значения пропускной способности или объёма не заявлены.