Средний

4 мин

10K

Если вы работаете с временными рядами в PostgreSQL, скорее всего сталкивались с необходимостью в выгрузке данных в Python, а потом как-то возвращали результат обратно. Это работает, но неудобно и медленно. Я написала расширение pg_stl, которое позволяет делать всё это прямо внутри базы — на нативном C, без экспорта данных наружу.

В этой статье расскажу, что это такое, как работает и почему это быстрее, чем подход с Python.

Зачем это нужно

Стандартный подход к анализу временных рядов в PostgreSQL выглядит примерно так:

• Делаем SELECT и выгружаем данные из базы

• Отправляем их в Python или R

• Считаем нужные метрики

• Возвращаем результаты обратно

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

Расширение pg_stl решает эту проблему: всё вычисляется внутри базы, данные никуда не уходят, а C-код работает напрямую с памятью PostgreSQL через его внутреннее API.

Что реализовано

В расширении три алгоритма:

• ACF (автокорреляционная функция) — показывает корреляцию ряда с самим собой на разных лагах. Помогает найти сезонность и наличие тренда.

• PACF (частная автокорреляционная функция) — в отличие от ACF, показывает только прямую зависимость при заданном лаге, исключая влияние промежуточных. Полезна для подбора параметров ARIMA.

• STL-декомпозиция — разбивает ряд на три составляющие: тренд, сезонность и остаток. Основана на методе LOESS.

Как использовать

Установка

Через Docker (проще всего):

docker compose up --build

Или вручную (нужен PostgreSQL 16 и build-essential):

make
make install
psql -d mydb -c "CREATE EXTENSION pg_stl;"

Структура репозитория

Расширение опубликовано под лицензией PostgreSQL License — это permissive лицензия, позволяющая свободно использовать, копировать и модифицировать код без ограничений. Исходный код доступен на GitHub.

Репозиторий: github.com/nadyaloseva/pg_ts_analysis

Расширение состоит из нескольких файлов, каждый из которых отвечает за свою часть работы.

• stl.c — основной файл с реализацией всех алгоритмов на языке C: автокорреляционной функции (ACF), частной автокорреляционной функции (PACF) и STL-декомпозиции. Именно здесь находится вся вычислительная логика.

• pg_stl--1.0.sql — SQL-файл, в котором объявляются функции расширения и описывается их сигнатура. PostgreSQL использует его при установке, чтобы зарегистрировать функции и связать их с реализацией из stl.c.

• pg_stl.control — файл с метаданными расширения: название, версия и описание. PostgreSQL читает его при выполнении команды CREATE EXTENSION.

• Makefile — конфигурация сборки на основе PGXS (системы сборки расширений PostgreSQL). Позволяет собрать расширение командой make и установить его командой make install.

• Dockerfile и docker-compose.yml — позволяют поднять тестовое окружение с PostgreSQL одной командой, без необходимости настраивать базу вручную.

• pg_python_testing.ipynb — Jupyter-ноутбук, в котором сравниваются результаты работы расширения с аналогичными функциями из Python-библиотеки statsmodels. Использовался для проверки корректности реализации и замера производительности.

Использование в СУБД

ACF (автокорреляционная функция)

Она вычисляет корреляцию ряда с его же значениями, сдвинутыми на несколько шагов назад. Простыми словами — насколько похоже поведение ряда сегодня на то, что было день, неделю или месяц назад. Это позволяет определить период сезонности, обнаружить тренд, а также использовать результат для подбора параметра q в ARIMA-моделях.

PACF (частная автокорреляционная функция)

Вторая функция — частичная автокорреляционная. В отличие от ACF, она измеряет только прямую связь между значениями, исключая влияние промежуточных лагов. Это позволяет точнее понять структуру зависимостей и определить параметр p для ARIMA.

В основе алгоритма лежит система линейных уравнений Юла-Уокера для AR-модели. Для оптимизации расчета используется рекурсивный алгоритм Дурбина-Левинсона, который решает систему пошагово, используя результаты предыдущего шага

STL-декомпозиция

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

Алгоритм работает итерационно: внешний цикл снижает влияние выбросов, внутренний — поочерёдно уточняет тренд и сезонность. Сглаживание выполняется методом локальной регрессии LOESS, которая подстраивается под характер данных в каждой точке

Дополнительно в расширение добавлен алгоритм Хольта-Винтерса, созданный Pugolovok Alexander (tg: @alexander_pu)

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

Функция поддерживает два типа сезонности: 'mult' (мультипликативная, когда амплитуда сезонных колебаний растёт вместе с трендом) и 'add' (аддитивная, когда амплитуда постоянна).

Итого

pg_stl — это рабочий инструмент для тех, кто хочет анализировать временные ряды прямо внутри PostgreSQL. ACF и PACF работают заметно быстрее Python-аналогов. STL пока медленнее на больших рядах, но зато данные не покидают базу и нет внешних зависимостей.

Буду рада обратной связи!