Хранение временных данных в PostgreSQL / Habr

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

В статье будут рассмотрены основные методы хранения временных данных в PostgreSQL:

Обычные таблицы;
Нежурналируемые таблицы;
Материализованные преставления;
Временные таблицы;
Память серверного процесса (с использованием расширения pg_variables).

Обзор

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

Нежурналируемые таблицы работают так же, как обычные, только изменения не проходят через журнал WAL. В случае некорректной остановки экземпляра таблица усекается. Нежурналируемые таблицы не очень распространены, поскольку их нельзя использовать на репликах. На реплики обычно переносят долгие запросы. На мастере использование нежурналируемых таблиц в логике приложения ограничено тем, что они подобно обычным таблицам подвержены разрастанию цепочек версий строк и нуждаются в очистке блоков (vacuum и HOT cleanup).

Материализованные представления

Материализованные представления (materialized views, MV) обычно используются в хранилищах данных. Они сохраняют результат произвольного запроса, к которому можно многократно обращаться из любых сессий. Данные хранятся до удаления или обновления представления. При изменении данных, на основе которых создано материализованное представление, оно не обновляется. Представление можно обновить, но ресурсоемкость такого обновления идентична его созданию. MV можно обновлять с опцией CONCURRENTY, что позволяет не блокировать выборки из MV. В любом случае, внесение изменений (INSERT, UPDATE и др.) в строки MV не допускаются.

Команда CREATE MATERIALIZED VIEW подобна CREATE TABLE AS за исключением того, что она запоминает запрос, порождающий MV, так что это представление можно позже обновить командой REFRESH. Временных MV не бывает. С точки зрения физического хранения и выборки строк, MV сходны с таблицами. Например, можно создавать индексы, выбирать для столбцов стратегию хранения PLAIN, EXTERNAL. Для REFRESH CONCURRENTLY нужно создать уникальный индекс на любой столбец или столбцы (составной индекс):

create unique index on mv(filler, bbalance);
refresh materialized view concurrently mv;

Из всех типов индексов уникальными могут быть только индексы btree, причем индекс не должен быть частичным (использовать WHERE). MV можно кластеризовать (упорядочить хранение строк в соответствии с индексом):

cluster mv;
ERROR:  there is no previously clustered index for table "mv"
alter materialized view mv cluster on mv_filler_bbalance_idx;
ALTER MATERIALIZED VIEW
cluster mv;
CLUSTER

Фактор кластеризации помогает планировщику выбирать Index Scan вместо Bitmap Index Scan.

Быстрого обновления (fast refresh) в ванильном PostgreSQL нет, MV полностью перестраивается при REFRESH. Автоматического переписывания запроса (query rewrite) на использование MV нет, имя MV нужно использовать в запросе явно.

В 17.5 версии СУБД Tantor Postgres появилось расширение pg_ivm (Incremental View Maintenance), которое позволяет обновлять материализованные представления при внесении изменений в строки таблиц, на которые создано материализованное представление. Другими словами, оно реализует логику fast refresh.

Преимущества и недостатки материализованных представлений

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

select * from unlogged_table;
ERROR:  cannot access temporary or unlogged relations during recovery
create temp table t(n numeric);
ERROR:  cannot execute CREATE TABLE in a read-only transaction

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

С точки зрения производительности у MV в сравнении с обычными таблицами вряд ли есть преимущества. MV могут использоваться для упрощения миграции с других СУБД как аналог материализованных представлений со схожим синтаксисом команд. Удобным фактором защиты от ошибок может быть то, что MV зависит от исходных таблиц — так же, как и обычные представления. Например, удалить таблицу без удаления MV нельзя:

С точки зрения производительности, преимущества у MV в сравнении с обычными таблицами вряд ли есть. MV могут использоваться для упрощения миграции с других СУБД как аналог материализованных представлений в других СУБД со схожим синтаксисом команд. Удобным для защиты от ошибок может быть то, что MV зависит от исходных таблиц, так же как и обычные представления. Например, удалить таблицу без удаления MV нельзя:

drop table pgbench_branches;
ERROR:  cannot drop table pgbench_branches because other objects depend on it
DETAIL:  materialized view mv depends on table pgbench_branches
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

Также удобна защита от внесения изменений в строки MV.

Если не учитывать миграцию и удобство, проектирующему ELT- или ETL-логику вряд ли имеет смысл специально стремиться использовать MV в PostgreSQL. Причина использования MV в других СУБД заключается в возможности частичного обновления (fast refresh) без полного перестраивания. Именно это делает использование MV в таких СУБД эффективным с точки зрения производительности.

Временные таблицы

Временные таблицы создаются внутри сессии со сроком жизни данных до ее завершения (или завершения транзакции). Для каждой сессии нужно создавать свои временные таблицы. Изменения не журналируются и кэш буферов не используется — вместо него используется локальный кэш в памяти серверного процесса. Размер локального кэша задаётся параметром конфигурации temp_buffers. Параллельные процессы не имеют доступа к локальной памяти серверного процесса и к временным таблицам. Программный код, работающий с временными таблицами, похож на код работы с обычными таблицами. Он поддерживает временные индексы TOAST и не оптимизирован для работы с временными данными. Для хранения данных временных таблиц создаются файлы. Оптимизаций типа "если данные помещаются в память, то не используются файлы" в ванильном PostgreSQL нет. Оптимизация состоит только в том, что fsync по файлам временных таблиц не выполняется, но при больших объемах данных во временных таблицах это не даёт преимуществ в производительности.

В СУБД Tantor Postgres 17 есть оптимизация отложенного создания файлов временных таблиц, если их данные помещаются в локальный кэш. Временные таблицы создаются командой CREATE TEMP[ORARY] TABLE в каждой сессии, в которой их планируется использовать. Временные таблицы создаются командой CREATE TEMP[ORARY] TABLE в каждой сессии, в которой их планируется использовать. На них можно создавать индексы, которые становятся временными. Автоматически создаваемые последовательности создаются временными. В отличие от индексов, можно создать временную последовательность отдельной командой CREATE TEMP[ORARY] SEQUENCE. Для нежурналируемых таблиц также создаются нежурналируемые последовательности, которые можно создать отдельно командой CREATE UNLOGGED SEQUENCE.

Временные объекты создаются во временной схеме, на которую можно ссылаться как pg_temp. В одной и той же схеме нельзя создать отношения с одинаковым именем (таблицы, последовательности, индексы, представления, материализованные представления, внешние таблицы):

create temp table tt(n serial);
CREATE TABLE
create temporary sequence tt;
ERROR:  relation "tt" already exists
\d tt 
                          Table "pg_temp_5.tt"
 Column |  Type   | Collation | Nullable |            Default           
--------+---------+-----------+----------+-------------------------------
 n      | integer |           | not null | nextval('tt_n_seq'::regclass)
Indexes:
    "tt_pkey" PRIMARY KEY, btree (n)

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

select pg_relation_filepath('tt');
 pg_relation_filepath
----------------------
 base/5/t5_16637
\! ls -al $PGDATA/base/5/t5*
8192 /var/lib/postgresql/tantor-se-17/data/base/5/t5_16636
   0 /var/lib/postgresql/tantor-se-17/data/base/5/t5_16637
8192 /var/lib/postgresql/tantor-se-17/data/base/5/t5_16641

Файлы временных отношений создаются в табличных пространствах, названия которых заданы параметром temp_tablespaces. По умолчанию значение пусто, и файлы создаются в табличном пространстве, используемом базой данных по умолчанию:

insert into tt select * from generate_series(1, 200000);
INSERT 0 200000
vacuum analyze tt;
VACUUM
\! ls -al $PGDATA/base/5/t5*
   8192 /var/lib/postgresql/tantor-se-17/data/base/5/t5_16636
7249920 /var/lib/postgresql/tantor-se-17/data/base/5/t5_16637
  24576 /var/lib/postgresql/tantor-se-17/data/base/5/t5_16637_fsm
   8192 /var/lib/postgresql/tantor-se-17/data/base/5/t5_16637_vm
4513792 /var/lib/postgresql/tantor-se-17/data/base/5/t5_16641

Как и для обычных таблиц, вакуумирование создало файлы vm и fsm. Размер всех временных файлов ограничивается параметром конфигурации temp_file_limit:

set temp_file_limit='1MB';
SET
insert into tt select * from generate_series(200000, 300000);
ERROR:  temporary file size exceeds temp_file_limit (1024kB)

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

При использовании временных таблиц в коде приложения стоит предусматривать выполнение команд VACUUM и ANALYZE для временных таблиц или использовать расширения (например, библиотеку online_analyze), которые автоматически выполняют эти действия. Временные таблицы нуждаются в заморозке строк так же, как и обычные таблицы.

При создании временных отношений в таблицы системного каталога добавляются строки. Частое создание и удаление таблиц приводит к раздуванию, если горизонт базы данных долго удерживается и автовакуум не может очистить таблицы системного каталога. В основном раздуваются таблицы pg_attribute, pg_depend, pg_type, pg_class.

При создании таблицы в таблицу pg_class были добавлены три строки: таблицы, последовательности и индекса. В таблицу pg_attribute было добавлено 17 строк при том, что в таблице tt всего один столбец. В таблицу pg_type была добавлена одна строка. При создании таблицы, в том числе временной, в схеме таблицы создаётся одноимённый тип данных, соответствующий строке таблицы. Посмотрим, как много строк было добавлено в таблицы системного каталога:

select relname, relnamespace::regnamespace 
 from pg_class 
 where relname like 'tt%';
 relname  | relnamespace
----------+--------------
 tt       | pg_temp_5
 tt_n_seq | pg_temp_5
 tt_pkey  | pg_temp_5
(3 rows)
select attname, attrelid::regclass 
 from pg_attribute 
 where attrelid::regclass::text like 'tt%';
  attname   | attrelid
------------+----------
 last_value | tt_n_seq
 log_cnt    | tt_n_seq
 is_called  | tt_n_seq
 ctid       | tt_n_seq
 xmin       | tt_n_seq
 cmin       | tt_n_seq
 xmax       | tt_n_seq
 cmax       | tt_n_seq
 tableoid   | tt_n_seq
 ctid       | tt
 xmin       | tt
 cmin       | tt
 xmax       | tt
 cmax       | tt
 tableoid   | tt
 n          | tt
 n          | tt_pkey
(17 rows)
select typname, typnamespace::regnamespace 
 from pg_type 
 where typname like 'tt%';
 typname | typnamespace
---------+--------------
 tt      | pg_temp_5
(1 row)

Начиная с версии 17.5 в СУБД Tantor Postgres есть параметр enable_temp_memory_catalog, при использовании которого в таблицы системного каталога не вносятся изменения при работе с временными таблицами и другими временными объектами.

Усечение временных таблиц

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

В СУБД Tantor Postgres в редакции SE 1С есть расширение fasttrun, состоящее из одной функции fasttruncate('имя'). При использовании функции временная таблица усекается, название у файлов не меняется, как и строки в таблицах системного каталога. Приложения 1C могут использовать вызов этой функции вместо команды TRUNCATE. Функция работает только с временными таблицами:

select fasttruncate('t');
ERROR:  Relation isn't a temporary table

После вставки или изменении строк во временных таблицах может оказаться полезным пересобрать статистику для планировщика. "1C:Предприятие", начиная с версии 8.3.13, выполняет команду analyze после вставки строк во временную таблицу. Для других приложений, которые этого не делают, можно использовать библиотеку online_analyze. Загружать ее для всех сессий не стоит, так как если статистика собирается отдельной командой, то автоматический сбор об этом не знает и повторяет одно и то же действие, что приводит к лишнему потреблению ресурсов. Более того, статистика собирается синхронно, и это приводит к замедлению выполнения команд, вызывающих срабатывание расширения. Вот пример использования библиотеки на уровне сессии:

load 'online_analyze';
set online_analyze.enable = on;
set "online_analyze.verbose" = on;
set online_analyze.table_type = 'temporary';

Двойные кавычки у второго параметра нужны потому, что verbose — зарезервированное слово. Мне неизвестно, почему автор расширения решил использовать зарезервированное слово. Параметр online_analyze.verbose выполняет команду ANALYZE VERBOSE. После выполнения команды, приводящей к анализу, вызывающему команду передаются уведомления уровня INFO.

У временной таблицы может быть установлено свойство ON COMMIT DELETE ROWS. Если в транзакции были обращения к любой из временных таблиц, то при фиксации транзакции возникает задержка, линейно зависящая от числа временных таблиц с этой опцией. Задержка возникает независимо от того, есть ли строки в таблицах. Простыми словами: при использовании ON COMMIT DELETE ROWS будет существенная деградация производительности, эту опцию не стоит использовать. Величина задержки — порядка 0,3 мс на временную таблицу:

\o t.tmp \\
select format('create temp table t%s 
  on commit delete rows as 
  select id::int8, repeat(id::text, 1000) 
  from generate_series(1,2) id;', g.seq) 
from generate_series(1,1000) as g(seq)\gexec 
\timing on \\
begin;
select 1 from t999 limit 1;
commit;
Timing is on.
Time: 0.159 ms
Time: 0.294 ms
Time: 320.688 ms

Сравнение скорости работы обычных таблиц, временных таблиц и таблиц pg_variables

С помощью функций расширения pg_variables можно сохранять как скалярные переменные, так и составные типы (образы строк). Поиск строк может выполняться полным сканированием или по хэшу. Структуры хранятся в локальной памяти процесса, и нет смысла использовать другие способы наподобие btree. Для тестирования воспользуемся таблицей из Демобазы 3.0. Создание временной таблицы:

create temp table tickets1 as select * from tickets;
Time: 520.445 ms
select * from tickets1 where ticket_no='0005432020304';
Time: 338.035 ms
select * from tickets where ticket_no='0005432020304';
Time: 0.310 ms

Создание таблицы в памяти серверного процесса с помощью функции расширения pg_variables:

create extension pg_variables;
select count(*) 
 from (select pgv_insert('bookings', 'tickets', tickets) from tickets);
Time: 1380.584 ms (00:01.381)
\o \\
select * from pgv_select('bookings', 'tickets', '0005432020304'::char(13)) 
 as (ticket_no character(13), book_ref character(6), 
     passenger_id character varying(20), passenger_name text, contact_data jsonb);
   ticket_no   | book_ref | passenger_id | passenger_name | contact_data
---------------+----------+--------------+----------------+--------------
 0005432020304 | F5C81C   | 7257 672943  | OLEG IVANOV    | {"email": "oleg-ivanov_1984@tantorlabs.ru", "phone": "+79037655555"}
(1 row)
Time: 0.337 ms

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

Если на временной таблице создать временный индекс типа btree...

create index on tickets1(ticket_no);
Time: 5615.559 ms (00:05.616)
select * from tickets1 where ticket_no='0005432020304';
Time: 0.381 ms

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

При методе доступа Seq Scan скорость доступа к временной таблице медленнее (8,357 мс вместо 7,931 мс):

explain (analyze, buffers, timing off) 
 select book_ref from tickets1 where passenger_name like '%G IVANOV' limit 10;
 Limit  (cost=638.98..3833.90 rows=10 width=7) (actual rows=10 loops=1)
   Buffers: local hit=499
   ->  Seq Scan on tickets1  (cost=0.00..24281.39 rows=76 width=7) (actual rows=10 loops=1)
         Filter: (passenger_name ~~ '%G IVANOV'::text)
         Rows Removed by Filter: 29658
         Buffers: local hit=499
 Planning Time: 0.088 ms
 Execution Time: 7.581 ms
(8 rows)
Time: 7.931 ms
explain (analyze, buffers, timing off) select book_ref from tickets where passenger_name like '%G IVANOV' limit 10;
 Limit  (cost=639.12..3834.69 rows=10 width=7) (actual rows=10 loops=1)
   Buffers: shared hit=433 read=77
   ->  Seq Scan on tickets  (cost=0.00..24286.39 rows=76 width=7) (actual rows=10 loops=1)
         Filter: (passenger_name ~~ '%G IVANOV'::text)
         Rows Removed by Filter: 29928
         Buffers: shared hit=433 read=77
 Planning Time: 0.090 ms
 Execution Time: 7.989 ms
(8 rows)
Time: 8.357 ms

Доступ к in-memory таблице — на порядок быстрее:

select book_ref from pgv_select('bookings', 'tickets', '0005432020304'::char(13)) 
 as (ticket_no character(13), book_ref character(6), passenger_id character varying(20), 
     passenger_name text, contact_data jsonb) where passenger_name like '%G IVANOV' limit 10;
 book_ref 
----------
 F5C81C
(1 row)
Time: 0.382 ms

Обработка большого числа строк:

select count(*) from tickets1;
 count  
--------
 829071
(1 row)

Time: 185.471 ms
select count(*) from tickets;
 count  
--------
 829071
(1 row)

Time: 61.349 ms

У обычной таблицы время выполнения запроса меньше из-за того, что использовались параллельные процессы.

При использовании pg_variables время обработки всех строк в сотни раз быстрее:

select count(*) from pgv_select('bookings', 'tickets', '0005432020304'::char(13)) 
 as (ticket_no character(13), book_ref character(6), 
     passenger_id character varying(20), passenger_name text, contact_data jsonb);
 count 
-------
     1
(1 row)

Time: 0.424 ms

Распараллеливание

При работе с обычными таблицами возможно распараллеливание, которое существенно ускоряет выполнение запросов (90,443 мс вместо 255,623 мс для двух параллельных процессов):

explain (analyze, buffers, timing off) 
 select book_ref from tickets 
  where passenger_name like '%G IVANOV';

 Gather  (cost=1000.00..19248.68 rows=76 width=7) (actual rows=326 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   Buffers: shared hit=3771 read=10152
   ->  Parallel Seq Scan on tickets  (cost=0.00..18241.08 rows=32 width=7) (actual rows=109 loops=3)
         Filter: (passenger_name ~~ '%G IVANOV'::text)
         Rows Removed by Filter: 276248
         Buffers: shared hit=3771 read=10152
 Planning Time: 0.085 ms
 Execution Time: 90.026 ms
(10 rows)

Time: 90.443 ms

При работе с временными таблицами распараллеливания нет, и время выполнения запроса возрастает в несколько раз (чем больше число строк и параллельных процессов, тем разница заметнее):

explain (analyze, buffers, timing off) 
 select book_ref from tickets1 
  where passenger_name like '%G IVANOV';

 Seq Scan on tickets1  (cost=0.00..24281.39 rows=76 width=7) (actual rows=326 loops=1)
   Filter: (passenger_name ~~ '%G IVANOV'::text)
   Rows Removed by Filter: 828745
   Buffers: local read=13918
 Planning Time: 0.082 ms
 Execution Time: 255.195 ms
(6 rows)

Time: 255.623 ms

Преимущества расширения pg_variables

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

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

Особенности использования pg_variables

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

select pgv_insert('bookings','t2', pgbench_branches) 
 from pgbench_branches; 

 pgv_insert

------------

(1 row)

В примере всего одна строка, но если будет вставлен миллион строк, функция выдаст миллион строк. Чем руководствовался создатель расширения, возвращая пустые строки, — неизвестно. Эту "особенность" можно обойти, используя inline view:

select count(*) from 
 (select pgv_insert('bookings', 'tickets', tickets) 
  from tickets);
 count  
--------
 829071
(1 row)

Второй недостаток: при выборке составных типов приходится указывать детали структуры после “as”:

select * from pgv_select('bookings','t2',1)
 as (bid int, bbalance int, filler character(88));
 bid | bbalance | filler
-----+----------+--------

   1 |        0 |

select pgv_select('bookings','t2',1); 

 pgv_select

------------

 (1,0,)

Помимо деталей структуры, приходится использовать приведение литералов к типу — в примерах были выражения ‘0005432020304’::char(13), а в документации — NULL::int. Без этих конструкций приведения к типу выдаётся ошибка. Очень сложно догадаться, что требуется явное приведение. Расширению много лет, и я думаю, что эти неудобства и создали барьер для широкого использования расширения.

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

Заключение

Мы рассмотрели методы работы с временными данными в PostgreSQL и сравнили функциональность и производительность. Использование временных таблиц преимуществ в производительности не даёт, а недостатки существенны — так, например, временные таблицы не обрабатываются автовакуумом и не могут использоваться на репликах.

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

Хранение временных данных в PostgreSQL