Зарегистрированные, незарегистрированные и временные таблицы в PostgreSQL

В PostgreSQL существует множество различных типов таблиц. Каждый из них предназначен для конкретных задач. Наиболее распространенной и известной является таблица кучи. О его устройстве я писал в другой статье. Стандартная таблица позволяет хранить строки, обновлять данные и выполнять запросы OLAP и OLTP.

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

Зарегистрированные таблицы в PostgreSQL

В PostgreSQL журналируемая таблица — это тип таблицы, для которой все изменения данных записываются в журнал упреждающей записи, который используется для восстановления после сбоя и репликации.

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

Когда мы запускаем транзакцию, PostgreSQL сохраняет события в лог-файлы. Файлы журнала в PostgreSQL — это файлы предупреждений: файлы WAL или файлы журнала с опережающей записью. Сначала они записывают в себя изменение, которое вы хотите внести, и только потом это изменение применяется к самой таблице, расположенной в памяти или на диске.

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

• Атомарность.
• Последовательность.
• Изоляция.
• Долговечность.

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

Файлы журналов обеспечивают возможность восстановления данных, если по какой-либо причине сервер или процесс вышли из строя. Когда процесс повторно активируется, реляционная база данных восстанавливает себя, считывая файлы журнала. Для этого Postgres использует алгоритм семейства ARIES (Algorithms for Recovery and Isolation Exploiting Semantics), который:

1. Создает списки отмены и повтора.
2. Воспроизведение транзакций из списка повторов с условием выполнения/не выполнения возникшей контрольной точки.
3. Откатывает транзакции из списка отмены для сохранения согласованности данных.

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

Давайте рассмотрим простой пример:

BEGIN;
  INSERT INTO test.log VALUES (1, ‘Peter’);
COMMIT | ROLLBACK ;

Я начинаю транзакцию со слова BEGIN, затем вставляю его в заранее созданную таблицу Logged test.log. И я вставляю тестовую запись с Питером и 1 в качестве идентификатора. После этого я делаю либо COMMIT, либо ROLLBACK.

В результате наша простая транзакция записывает каждый свой шаг на уровне лог-файла:

Незарегистрированные таблицы в PostgreSQL

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

BEGIN;
  INSERT INTO test.unlog VALUES (1, ‘Peter’);
COMMIT | ROLLBACK ;

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

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

Журналируемая таблица. Я создаю стандартную журнальную таблицу, по умолчанию регистрируемую:

CREATE TABLE test.log
(id       INTEGER, 
 name VARCHAR);

BEGIN;
   SELECT txid_current( );
   SELECT pg_current_wal_lsn( );
   -- 1/AC0AFC8

   SELECT pg_walfile_name(pg_current_wal_lsn( ));
   -- 00000001000000010000000A

   INSERT INTO test.log VALUES (1,'test1');
   INSERT INTO test.log VALUES (2,'test2');
   INSERT INTO test.log VALUES (3,'test3');

COMMIT;

Первая функция txid_current возвращает номер транзакции. Далее я получаю текущий LSN (комментарием в коде я указал номер, который мне вернули после выполнения функции pg_current_wal_lsn). Я также получаю этот номер для функции pg_walfile_name, и в результате ее выполнения я получаю имя текущего файла, которое соответствует этой записи LSN.

Чтобы понять, как это понимать, вернемся к аналогии с ключом и журналом на стойке регистрации. Я сделал запись в журнале, что это я взял ключ. Это не что иное, как LSN. Если много людей часто берут ключи, то журнал (как и блокнот) рано или поздно закончится. Затем вам нужно будет получить новый журнал (например, записную книжку). В мире журналируемых таблиц новый журнал — это новый файл WAL; он появляется, когда текущий заполнен. По умолчанию размер файла упреждающей записи в PostgreSQL составляет 16 МБ. Вы можете изменить размер в файле конфигурации postgresql.conf.

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

С помощью утилиты pg_waldump, входящей в установочные пакеты PostgreSQL, мы можем получить ссылки на нашу сессию и нашу транзакцию из файла вытесняющей записи. Это делается с помощью различных клавиш для командной строки. Я указываю номер LSN, с которого я хочу начать дамп этого бинарного файла, и указываю имя файла WAL, который я получил в рамках своей сессии.

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

1. ВСТАВИТЬ+ИНИТ.
2. ВСТАВИТЬ со смещением 2.
3. ВСТАВИТЬ со смещением 3.

В конце находим COMMIT — нашу запись данных. Данные регистрируются, а затем публикуются для общего доступа для всех пользователей.

Незарегистрированные таблицы. Теперь давайте посмотрим, как ведет себя незарегистрированная таблица. Тест такой же, только ранее я создал таблицу с опцией unlogged. Код сообщает базе данных, что моя таблица не зарегистрирована.

CREATE TABLE test.unlog
(id       INTEGER, 
 name VARCHAR);

BEGIN;
   SELECT txid_current( );
   SELECT pg_current_wal_lsn( );
   -- 1/AC0B0E0

   SELECT pg_walfile_name(pg_current_wal_lsn( ));
   -- 00000001000000010000000A

   INSERT INTO test.unlog VALUES (1,'test1');
   INSERT INTO test.unlog VALUES (2,'test2');
   INSERT INTO test.unlog VALUES (3,'test3');

COMMIT;

Если мы запустим тот же тест, сделаем его дамп и прочитаем через утилиту pg_dump, то увидим только COMMIT. В результатах не будет вставок:

Незарегистрированная таблица — это набор данных, который не контролируется файлом WAL.

Когда незарегистрированные таблицы подходят

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

Самый простой пример — запросы OLAP для создания киосков данных. Если витрины заполняются на основе процесса, вы не боитесь риска потери данных, потому что есть тот самый процесс ETL (иногда ELT), который преобразует данные на уровне SQL-запроса (возможно, не SQL). Вы можете просто перезапустить процесс и снова получить данные.

Заголовки зарегистрированных и незарегистрированных таблиц

Чтобы продолжить сравнение, давайте посмотрим на заголовки наших двух таблиц. Для этого я использую расширение pageinspect, предоставляющее методы для работы со страницами внутри конкретной таблицы. Итак, рассмотрим результат выполнения функции page_header, которая возвращает заголовки страниц номер 0 для обеих таблиц.

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

Если по какой-либо причине процесс PostgreSQL выйдет из строя или будет отключено электричество, это приведет к аварийному отключению вашего экземпляра PostgreSQL. В этом случае при восстановлении системы PostgreSQL применит команду TRUNCATE к незарегистрированной таблице. Эта команда удаляет все строки из таблицы, а значит ваши данные будут потеряны. Следует отметить, что если произошла «правильная» остановка сервера PostgreSQL, например, через операцию systemctl stop postgres, то данные в нелогируемых таблицах будут сохранены при следующем запуске.

Итак, при работе с незарегистрированными таблицами необходимо помнить следующее:

1. Существует риск потери данных.
2. Высокая скорость заполнения/изменения данных.

Как преобразовать зарегистрированную таблицу в незарегистрированную или наоборот

Вы можете преобразовать зарегистрированное и незарегистрированное состояния, применив следующие команды:

ALTER TABLE test.t1 SET LOGGED; 

ALTER TABLE test.t1 SET UNLOGGED;

Важно знать, что когда вы пытаетесь сделать таблицу зарегистрированной из незарегистрированной, процесс изменения временно блокирует трафик SQL, который использует таблицу как часть CRUD операций. Это все операции, которые возможны в таблице с точки зрения пользователя: INSERT, UPDATE, DELETE, SELECT.

Сеансы пользователей, пытающиеся получить доступ к таблице, будут ждать завершения команды ALTER TABLE. То есть у вас будут сеансы ожидания на бэкэнде и/или фронтенде. Конечно, если вы установили таймаут для сеанса, то он будет ждать установленное время.

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

Операция SET UNLOGGED по преобразованию таблицы в незарегистрированную выполняется быстро. Он просто изменяет статус на уровне метаданных PostgreSQL, что ваша таблица больше не регистрируется, и ей больше не нужно записывать в файлы упреждающей записи. Команда SET UNLOGGED временно блокирует уровень метаданных, поэтому нужно сделать небольшое окно в CRUD операциях, чтобы изменение вступило в силу.

Плюсы и минусы нелогируемых таблиц

Подытожим незарегистрированные таблицы в PostgreSQL.

Плюсы:

• Высокая скорость операций UPDATE, DELETE, INSERT. Я проводил свой тест на своем сервере PostgreSQL, и коэффициент для TPS — количество транзакций в секунду — для нелогируемой таблицы был в 9 раз выше, чем для логируемой.
• Любые индексы и таблицы TOAST будут автоматически выведены из логов, так как это зависимые объекты, «наследующие» свойства основного объекта.

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

Минусы:

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

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

Обратимся к временным таблицам. Временные таблицы в PostgreSQL — это тип таблиц, которые существуют только на время сеанса базы данных или транзакции.

CREATE TEMPORARY TABLE tmp
(id       INTEGER,
 name VARCHAR)
ON COMMIT DELETE ROWS
[ON COMMIT PRESERVE ROWS]
[ON COMMIT DROP];

При создании временной таблицы нельзя явно указать схему таблицы. Все временные таблицы в PostgreSQL попадают в соответствующую схему с именем pg_temp_xxx, где xxx — номер вашей сессии (например, pg_temp_8 или pg_temp_165). Схема сеанса создается автоматически самим PostgreSQL.

Операция создания имеет правила формирования жизненного цикла временной таблицы на уровне кода:

• ON COMMIT PRESERVE ROWS устанавливается по умолчанию. Это означает, что если вы COMMIT или ROLLBACK данных в транзакциях, строки в таблице будут сохранены до тех пор, пока вы не завершите сеанс. Только после этого таблица будет автоматически удалена.
• Параметр ON COMMIT DROP означает, что если в транзакции содержится COMMIT или ROLLBACK данных, таблица будет немедленно автоматически удалена из сеанса.
• Параметр ON COMMIT DELETE ROWS означает, что структура будет сохранена на уровне фиксации данных в течение COMMIT или ROLLBACK, но строки будут удалены. Но в любом случае при закрытии сессии таблица будет удалена автоматически.

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

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

Когда временные таблицы подходят, а когда нет

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

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

Вы можете временно хранить данные во всем конвейере (серии процессов) и заполнять временную таблицу как часть извлечения и преобразования данных. В конце процесса можно использовать временную таблицу, например, в JOIN операциях с постоянными таблицами. После фиксации данных и выхода из сеанса все временные таблицы будут автоматически удалены. При следующем вызове процесса они создаются снова по-новому и далее по кругу.

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

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

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

Тесты с примерами создания временной таблицы

Давайте посмотрим на следующий тест с созданием временной таблицы внутри транзакции:

BEGIN;
   SELECT txid_current( );
   SELECT pg_current_wal_lsn( );
   -- 1/AC54128

   SELECT pg_walfile_name(pg_current_wal_lsn( ));
   -- 00000001000000010000000A

   CREATE TEMPORARY TABLE temp 
   (id       INTEGER,
    name VARCHAR)
   ON COMMIT PRESERVE ROWS;  

COMMIT;
-- close connection

Перед созданием самой таблицы я получаю ту же информацию, что и в предыдущем примере с логируемой таблицей: номер LSN и имя текущего файла WAL, которое соответствует этой записи LSN.

Посмотрим, что в лог-файле:

При создании временной таблицы произошло 120 событий. То есть само его создание инициирует запись в лог-файл. И как только мы закрываем соединение с БД, происходит еще 40 событий о том, что сама таблица была удалена.

Теперь предварительно создадим временную таблицу в сессии:

CREATE TEMPORARY TABLE temp 
   (id       INTEGER,
    name VARCHAR)
   ON COMMIT PRESERVE ROWS;  

BEGIN;
   SELECT txid_current( );
   SELECT pg_current_wal_lsn( );
   -- 1/AC90368

SELECT pg_walfile_name(pg_current_wal_lsn( ));
   -- 00000001000000010000000A

   INSERT INTO temp VALUES (1,'test1');
   INSERT INTO temp VALUES (2,'test2');
   INSERT INTO temp VALUES (3,'test3');

COMMIT;

При таком тесте лог-файл будет иметь ту же картину, что и для нелогируемой таблицы:

Заголовки зарегистрированных и временных таблиц

Если сравнить заголовки нулевых страниц обычной журналируемой таблицы и временной таблицы, то это будет выглядеть точно так же, как и для нелогируемой таблицы:

Все то же самое, кроме LSN. Для временной таблицы она пуста, и из-за этого содержимое этой таблицы не передается в StandBy. Если в файлах вытесняющих записей нет записи, то и передавать на зависимые серверы в виде физической репликации нечего.

Плюсы и минусы временных таблиц

Плюсы:

• Ускорение операций UPDATE, DELETE, INSERT.
• Любые индексы и таблицы TOAST автоматически становятся временными.
• Для временных таблиц вы не можете вручную определить схему базы данных, потому что она создается автоматически и также автоматически определяется для вашей временной таблицы.

Минусы:

• Содержимое таблицы физически не реплицируется на резервные серверы.
• Каждый раз для новой сессии необходимо создавать временную таблицу.

В следующей серии

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

• Кластеризованные таблицы.
• Зарубежные столы.
• Разделенные таблицы.
• Унаследованные таблицы.

Написано Азатом Якуповым, архитектором данных Quadcode.

Повышение уровня кодирования

Спасибо, что являетесь частью нашего сообщества! Перед тем, как ты уйдешь:

• 👏 Хлопайте за историю и подписывайтесь на автора 👉
• 📰 Смотрите больше контента в публикации Level Up Coding
• 🔔 Подписывайтесь на нас: Twitter | ЛинкедИн | "Новостная рассылка"

🚀👉 Присоединяйтесь к коллективу талантов Level Up и найдите прекрасную работу