Временные (промежуточные) данные - те, которые нужны для обработки в течение транзакции, сессии или ограниченное время. После истечения срока такие данные не нужны. Причина использования временных данных в том, что в одном запросе не всегда можно обработать все данные. Логика приложения может предусматривать обработку данных по частям - разными запросами. В статье рассматриваются и сравниваются способы хранения временных данных в:
1) обычных таблицах;
2) нежурналируемых таблицах;
3) материализованных представлениях;
4) временных таблицах;
5) в памяти серверного процесса, используя расширение pg_variables

Обзор

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

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

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

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

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

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

Для 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 нельзя:

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 логику использовать MV, вряд ли имеет смысл специально стремиться использовать MV в PostgreSQL. Причина использования MV в других СУБД в том, что в них MV могут частично обновляться (fast refresh), а не полностью перестраиваться, что делает использование MV в таких СУБД эффективным с точки зрения производительности.

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

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

В СУБД Tantor Postgres есть оптимизация отложенного создания файлов временных таблиц, если их данные помещаются в память.

Временные таблицы создаются командой 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 есть параметр 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 зарезервированное слово. Этот параметр выполняет команду ANALYZE VERBOSE. После выполнения команды, приводящей к анализу, вызывающему команду передаются уведомления уровня INFO.

У временной таблицы может быть установлено свойство 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;
\o \\
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 ms.

Если на временной таблице создать временный индекс типа 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 ms вместо 7.931 ms):

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 ms вместо 255.623 ms для двух параллельных процессов):

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)

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

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,)

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

Заключение

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

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