🐘🔧 Расширение pg_variables: мощная альтернатива временным таблицам в PostgreSQL
Расширение pg_variables для PostgreSQL предлагает альтернативу временным таблицам для эффективной работы с промежуточными данными, но имеет свои преимущества и недостатки, требующие тщательного анализа перед внедрением.
Привет всем!
Я, Ипатов Александр, backend-разработчик в
компании USETECH. Сегодня хочу
поделиться с вами информацией по теме ухода от временных таблиц в PostgreSQL с
помощью расширения pg_variables, которое встречается довольно редко (на мой
скромный взгляд).
Для начала предлагаю разобраться, в каких случаях, в принципе, возникает необходимость расчета промежуточных результатов во временных таблицах.
При разработке программного обеспечения часто возникает задача получения нескольких результирующих наборов на основе промежуточных данных. Например, нужно вычислить несколько промежуточных этапов расчета некоего показателя (продажи по месяцам в первых числах месяца, и аналогичный срез в последних числах месяца), и далее уже расчет неких составных показателей и метрик для оценки бизнеса.
Или такая задача: для некоторых пользователей нужно составить список их друзей и сообщений в социальных сетях. Решить эту задачу можно, получив список пользователей, а затем построив для каждого из них требуемый результирующий набор.
Говоря о практике: мы должны сделать большой SELECT с
несколькими подзапросами / CTE (Common Table Expressions) или их
каскадом в 3-5 шт. / агрегациями /
оконными функциями и пр.
Но это может оказаться затратной операцией, особенно если на основе этого списка нужно построить несколько результатов. В таком случае список придётся получать несколько раз, что приведёт к дополнительным накладным расходам.
Одним из решений этой проблемы могут быть временные таблицы. Однако у них есть ряд недостатков:
- для каждой временной таблицы требуется создавать файл, который необходимо удалить после её уничтожения;
- такие таблицы не очищаются автоматически, что может привести к неограниченному росту системного каталога при наличии длительных активных транзакций;
- кеш операционной системы заполняется данными о созданных файлах для временных таблиц, что может привести к общей деградации производительности.
Как мы знаем, другим решением могут быть массивы. Но и у них есть недостатки:
- они иммутабельны;
- существуют только во время выполнения запроса;
- не позволяют искать по ключу.
Ещё для сессии могут потребоваться временные данные, доступные для всех функций на время выполнения запроса, такие как текущий пользователь, права и так далее.
Наконец, при выполнении запросов только для чтения на реплике может потребоваться хранение временных данных.
Описываемое
расширение pg_variables предназначено для решения подобных проблем. Оно позволяет
определять скалярные сессионные переменные, которые могут быть очень ценными
для хранения идентификатора пользователя, от имени которого выполняется запрос,
и его различных атрибутов
PS: сразу же напишу о двух недостатках, с которыми столкнулся лично:
- очень неудобная работа с расширением, поскольку приходится проходить очень много итераций для поиска ошибок, связей, особенно если мы работаем в сложноподчиненной древовидной структуре зависимых действий, когда в явном виде приходится указывать типы данных;
- если есть необходимость в поиске среди промежуточных данных, то вариант с временными таблицами гораздо более читаемый и удобный в использовании, поскольку с
pg_variables()может потребоваться гораздо больше времени для поиска данных
Использование pg_variables
Модуль pg_variables содержит функции для работы с переменными различных типов (скалярные переменные, переменные записей – интересны больше всего, т. к. аналог работы с временными таблицами, переменные-массивы). Созданные переменные существуют в течение текущей пользовательской сессии
По умолчанию эти переменные создаются без поддержки транзакций. Если переменная успешно создана, она остаётся доступной в течение всего сеанса, даже если происходят откаты транзакций.
Если вы хотите использовать переменную с поддержкой транзакций и точек сохранения, необходимо передать дополнительный флаг is_transactional в последний параметр функции, которая создаёт переменную.
Флаг is_transactional нужно указывать каждый раз, когда вы изменяете значение транзакционной переменной с помощью функций pgv_set() и pgv_insert(). В противном случае возникнет ошибка. Другим функциям передавать этот флаг не нужно.
Если вызовы функций pgv_free() или pgv_remove() откатываются, затронутые транзакционные переменные восстанавливаются. В отличие от них, нетранзакционные переменные удаляются безвозвратно.
Функции и их описание
pgv_insert
pgv_insert(package text, name text, r record, is_transactional bool default false) => void
Вставляет запись в набор переменных для заданного пакета. Если пакет или переменная не существуют, они создаются автоматически. Первый столбец записи r — первичный ключ. Если запись с таким же первичным ключом уже существует или этот набор переменных имеет другую структуру, выдаётся ошибка.
pgv_update
pgv_update(package text, name text, r record) => boolean
Изменяет запись с соответствующим первичным ключом (он задаётся в первом столбце r). Возвращает true, если запись была найдена. Если этот набор переменных имеет другую структуру, выдаётся ошибка.
pgv_delete
pgv_delete(package text, name text, value anynonarray) => boolean
Удаляет запись с соответствующим первичным ключом (он задаётся в первом столбце r). Возвращает true, если запись была найдена.
pgv_select
pgv_select(package text, name text) => set of records
Возвращает записи из набора переменных.
pgv_select
pgv_select(package text, name text, value anynonarray) => record
Возвращает записи из набора переменных с соответствующими первичными ключами (первичный ключ задаётся в первом столбце r).
pgv_select
pgv_select(package text, name text, value anyarray) => set of records
Возвращает записи из набора переменных с соответствующими первичными ключами (первичный ключ задаётся в первом столбце r).
Важная особенность использования функций pgv_update(), pgv_delete() , pgv_select()
Сначала создать пакет и переменную – функция pgv_insert() . Причем тип переменной и тип записи должны быть одинаковыми, иначе выдаётся ошибка, которая преследовала меня долго, поскольку я реализовывал перенос связей между 40 хранимыми процедурами с временными таблицам, которых было много и между разными процедурами была разная взаимосвязь на pg_variables .
Вообще, по большому счету, когда много работаешь с данным расширением, ты привыкаешь к тому, что сначала нужно проверить в хранимой процедуре наличие паркета и переменную, если ее не существует, то в ветке условия IF THEN END IF; сделать pgv_insert(), обязательно с явным указанием типов и созданием уникального идентификатора (uuid_generate, к примеру).
Имея некоторые данные в переменных записей можно работать с ними как с таблицами, например запросы типа:
SELECT * FROM pgv_select(package text, name text, value anyarray) as"t_pgv"
И далее просто очень внимательно следить за типами данных между операциями update / insert
В заключение хочу отметить, что на своем опыте лишь 1 использование дало хороший результат: когда пришлось переписывать 40 взаимосвязанных хранимых процедур, тогда было заметно небольшое улучшение производительности работы родительской хранимой процедуры, поскольку большое количество различных временных таблиц серьезно влияло на производительность всей системы. В остальных случаях pg_variables() не показал высокого повышения эффективности, а лишь затраты на серьезную переработку кода и ухудшение читаемости кода.
И, как обычно, теория это одно, но все проверки по повышению / ухудшению работы баз данных всегда проверяется в бою :)