PostgreSQL : Документация: 17: 53.4. Протокол потоковой репликации : Компания Postgres Professional

RU
EN
RU EN

53.4. Протокол потоковой репликации
Пред.	Наверх	Глава 53. Клиент-серверный протокол	Начало	След.

53.4. Протокол потоковой репликации #

Чтобы инициировать потоковую репликацию, клиент передаёт в стартовом сообщении параметр replication. Логическое значение true (или on, yes, 1) этого параметра указывает обслуживающему процессу перейти в режим передатчика данных физической репликации. В этом режиме вместо SQL-операторов клиент может выдавать только ограниченный набор команд репликации, показанный ниже.

Если параметр replication имеет значение database, обслуживающий процесс должен перейти в режим передатчика данных логической репликации. При этом выполняется подключение к базе данных, заданной в параметре dbname. В режиме логической репликации могут выполняться как команды репликации, показанные ниже, так и обычные SQL-команды.

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

Для тестирования команд репликации вы можете установить соединение для репликации, запустив psql или другую программу на базе libpq со строкой подключения, включающей параметр replication, например так:

psql "dbname=postgres replication=database" -c "IDENTIFY_SYSTEM;"

Однако часто полезнее использовать pg_receivewal (для физической репликации) или pg_recvlogical (для логической).

Команды репликации записываются в журнал работы сервера, когда включён параметр log_replication_commands.

В режиме репликации принимаются следующие команды:

IDENTIFY_SYSTEM #

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

systemid (text): Уникальный идентификатор системы, идентифицирующий кластер. По нему можно определить, что базовая резервная копия, из которой инициализировался резервный сервер, получена из того же кластера.
timeline (int8): Идентификатор текущей линии времени. Также полезен для того, чтобы убедиться, что резервный сервер согласован с главным.
xlogpos (text): Текущее положение сохранённых данных в WAL. Позволяет узнать, с какой позиции в журнале предзаписи может начаться потоковая передача.
dbname (text): Подключённая база данных или NULL.

SHOW имя #

Запрашивает у сервера текущее значение параметра времени выполнения. Эта команда подобна SQL-команде SHOW.

имя: Имя параметра времени выполнения. Доступные параметры описаны в Главе 19.

TIMELINE_HISTORY лин_врем #

Запрашивает с сервера файл истории для линии времени лин_врем. Сервер возвращает набор результатов в одной строке, содержащей два поля. Эти поля обозначены как имеющие тип text, но фактически они содержат просто байты, не в текстовой кодировке:

filename (text): Имя файла с историей линии времени, например 00000002.history.
content (text): Содержимое файла с историей линией времени.

CREATE_REPLICATION_SLOT имя_слота [ TEMPORARY ] { PHYSICAL | LOGICAL модуль_вывода } [ ( параметр [, ...] ) ] #

Создаёт слот физической или логической репликации. Слоты репликации описаны подробно в Подразделе 26.2.6.

имя_слота: Имя создаваемого слота. Заданное имя должно быть допустимым для слота репликации (см. Подраздел 26.2.6.1).
модуль_вывода: Имя модуля вывода, применяемого для логического декодирования (см. Раздел 47.6).
TEMPORARY: Это указание отмечает, что данный слот репликации является временным. Временные слоты не сохраняются на диске и автоматически удаляются при ошибке или завершении сеанса.

Поддерживаются следующие параметры:

TWO_PHASE [ boolean ]: Если параметр включён (по умолчанию он отключён), этот слот логической репликации поддерживает декодирование двухфазной фиксации. С этим параметром декодируются и передаются серверу команды, связанные с двухфазной фиксацией: PREPARE TRANSACTION, COMMIT PREPARED и ROLLBACK PREPARED. Транзакция будет декодироваться и передаваться во время PREPARE TRANSACTION.
RESERVE_WAL [ boolean ]: Если параметр включён (по умолчанию он отключён), этот слот физической репликации резервирует WAL немедленно. Без этого указания WAL резервируется только при подключении клиента потоковой репликации.
SNAPSHOT { 'export' | 'use' | 'nothing' }: Эти указания выбирают, что делать со снимком, создаваемым при инициализации логического слота. С указанием 'export', подразумеваемым по умолчанию, этот снимок будет экспортироваться для использования в других сеансах. Это указание нельзя использовать внутри транзакции. С указанием 'use' снимок будет использоваться для текущей транзакции, в которой выполняется команда. Это указание должно использоваться в транзакции, при этом команда CREATE_REPLICATION_SLOT должна быть первой в этой транзакции. Наконец, с 'nothing' снимок будет использоваться только для логического декодирования в обычном режиме, и ничего больше с ним делать нельзя.
FAILOVER [ boolean ]: Если параметр имеет значение true, слот может синхронизироваться с резервными серверами, что позволяет возобновлять логическую репликацию после аварийного переключения узлов. Значение по умолчанию — false.

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

slot_name (text): Имя создаваемого слота репликации.
consistent_point (text): Позиция в WAL, в которой слот достиг согласованного состояния. Это самая ранняя позиция, с которой может начаться трансляция через этот слот репликации.
snapshot_name (text): Идентификатор снимка, экспортированного командой. Этот снимок действителен до тех пор, пока через это соединение не будет выполнена следующая команда или соединение не будет закрыто. Null, если созданный слот — физический.
output_plugin (text): Имя модуля вывода, используемого созданным слотом репликации. Null, если созданный слот — физический.

CREATE_REPLICATION_SLOT имя_слота [ TEMPORARY ] { PHYSICAL [ RESERVE_WAL ] | LOGICAL модуль_вывода [ EXPORT_SNAPSHOT | NOEXPORT_SNAPSHOT | USE_SNAPSHOT | TWO_PHASE ] } #

Такой вариант записи команды CREATE_REPLICATION_SLOT по-прежнему поддерживается для совместимости со старыми версиями.

ALTER_REPLICATION_SLOT имя_слота ( параметр [, ...] ) #

Изменяет определение слота репликации. Слоты репликации подробно описаны в Подразделе 26.2.6. Эта команда сейчас поддерживается только для слотов логической репликации.

имя_слота: Имя изменяемого слота. Заданное имя должно быть действительным для слота репликации (см. Подраздел 26.2.6.1).

Поддерживается следующий параметр:

FAILOVER [ boolean ]: Если параметр имеет значение true, слот может синхронизироваться с резервными серверами, что позволяет возобновлять логическую репликацию после аварийного переключения узлов.

READ_REPLICATION_SLOT имя_слота #

Считывает информацию о слоте репликации. Возвращает кортеж со значениями NULL, если указанный слот репликации не существует. Эта команда поддерживается только для слотов физической репликации.

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

slot_type (text): Тип слота репликации: physical или NULL.
restart_lsn (text): restart_lsn слота репликации.
restart_tli (int8): Идентификатор линии времени, связанной с restart_lsn, в текущей истории линии времени.

START_REPLICATION [ SLOT имя_слота ] [ PHYSICAL ] XXX/XXX [ TIMELINE лин_врем ] #

Указывает серверу начать потоковую передачу WAL, начиная с позиции XXX/XXX в WAL. Если указывается параметр TIMELINE, передача начинается на линии времени лин_врем, иначе выбирается текущая линия времени сервера. Сервер может вернуть в ответ ошибку, например, если запрошенный сегмент WAL уже потерян. Если проблем не возникает, сервер возвращает сообщение CopyBothResponse, а затем начинает передавать поток WAL клиенту.

Если в параметрах передаётся имя_слота, сервер будет отражать состояние репликации в этом слоте и отслеживать, какие сегменты, а если включён режим hot_standby_feedback, то и в каких транзакциях, всё ещё нужны этому резервному серверу.

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

После передачи всех записей WAL на линии времени, не являющейся текущей, сервер завершает потоковую передачу, выходя из режима копирования. Когда клиент подтверждает завершение передачи, также выходя из режима копирования, сервер возвращает набор результатов в одной строке с двумя столбцами, сообщая таким образом о следующей линии времени в истории сервера. В первом столбце передаётся идентификатор следующей линии времени (типа int8), а во втором — позиция в WAL, в которой произошло переключение (типа text). Обычно в этой же позиции завершается передача потока WAL, но возможны исключения, когда сервер может передавать записи WAL из старой линии времени, которые он сам ещё не воспроизвёл до переключения. Наконец сервер передаёт два сообщения CommandComplete (одно говорит о завершении CopyData, а второе — о завершении самой команды START_REPLICATION), после чего он готов принять следующую команду.

Данные WAL передаются в серии сообщений CopyData; за подробностями обратитесь к Разделу 53.6 и Разделу 53.7. (Это позволяет перемежать их с другой информацией; в частности, сервер может передать сообщение ErrorResponse, если он столкнулся с проблемами, уже начав передачу потока.) Полезная нагрузка каждого сообщения CopyData от сервера к клиенту содержит данные в одном из следующих форматов:

XLogData (B) — данные журнала транзакций #

Byte1('w')

Указывает, что в этом сообщении передаются данные WAL.

Int64

Начальная точка данных WAL в этом сообщении.

Int64

Текущее положение конца WAL на сервере.

Int64

Показания системных часов сервера в момент передачи, в микросекундах с полуночи 2000-01-01.

Byten

Фрагмент потока данных WAL.

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

Primary keepalive message (B) — Сообщение об активности ведущего #

Byte1('k'): Указывает, что это сообщение об активности отправителя.
Int64: Текущее положение конца WAL на сервере.
Int64: Показания системных часов сервера в момент передачи, в микросекундах с полуночи 2000-01-01.
Byte1: Значение 1 означает, что клиент должен ответить на это сообщение как можно скорее, во избежание отключения по тайм-ауту. Со значением 0 это не требуется.

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

Standby status update (F) — Обновление состояния резервного сервера #

Byte1('r'): Указывает, что это сообщение передаёт обновлённое состояние получателя.
Int64: Положение следующего за последним байтом WAL, полученным и записанным на диск на резервном сервере.
Int64: Положение следующего за последним байтом WAL, сохранённым на диске на резервном сервере.
Int64: Положение следующего за последним байтом WAL, применённым на резервном сервере.
Int64: Показания системных часов клиента в момент передачи, в микросекундах с полуночи 2000-01-01.
Byte1: Если содержит 1, клиент запрашивает от сервера немедленный ответ на это сообщение. Так клиент может запросить отклик сервера и проверить, продолжает ли функционировать соединение.

Hot standby feedback message (F) — Сообщение обратной связи горячего резерва #

Byte1('h'): Указывает, что это сообщение обратной связи горячего резерва.
Int64: Показания системных часов клиента в момент передачи, в микросекундах с полуночи 2000-01-01.
Int32: Текущее глобальное значение xmin данного резервного сервера, не учитывающее catalog_xmin всех слотов репликации. Если и это значение, и следующее catalog_xmin, равны 0, это воспринимается как уведомление о том, что через данное подключение больше не будут передаваться сообщения обратной связи горячего резерва. Последующие ненулевые сообщения могут возобновить работу механизма обратной связи.
Int32: Эпоха глобального идентификатора транзакции xmin на резервном сервере.
Int32: Наименьшее значение catalog_xmin для всех слотов репликации на резервном сервере. Значение 0 показывает, что на резервном сервере нет catalog_xmin, либо обратная связь горячего резерва отключена.
Int32: Эпоха идентификатора транзакции catalog_xmin на резервном сервере.

START_REPLICATION SLOT имя_слота LOGICAL XXX/XXX [ ( имя_параметра [ значение_параметра ] [, ...] ) ] #

Указывает серверу начинать потоковую передачу WAL для логической репликации с позиции XXX/XXX в WAL или с confirmed_flush_lsn слота (см. Раздел 52.19), если это значение больше. Таким образом клиентам не приходится обновлять локальный статус LSN, если не нужно обрабатывать данные. Однако в случаях, когда репликация начинается не с запрошенного LSN, некоторые ошибки на стороне клиента могут остаться незамеченными. Поэтому прежде чем передавать START_REPLICATION, имеет смысл запросить значение confirmed_flush_lsn и убедиться в том, что оно соответствует ожиданиям.

Сервер может вернуть в ответ ошибку, например, если такого слота нет. Если проблем не возникает, сервер возвращает сообщение CopyBothResponse, а затем начинает передавать поток WAL клиенту.

Данные, передаваемые внутри сообщений CopyBothResponse, имеют тот же формат, что описан для команды START_REPLICATION ... PHYSICAL, включая два сообщения CommandComplete.

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

SLOT имя_слота: Имя слота, из которого передаются изменения. Это имя является обязательным, оно должно соответствовать существующему логическому слоту репликации, созданному командой CREATE_REPLICATION_SLOT в режиме LOGICAL.
XXX/XXX: Позиция в WAL, с которой должна начаться передача.
имя_параметра: Имя параметра, передаваемого модулю вывода логического декодирования для выбранного слота. Параметры, принимаемые стандартным модулем (pgoutput), описаны в Разделе 53.5.
значение_параметра: Необязательное значение, в форме строковой константы, связываемое с указанным параметром.

DROP_REPLICATION_SLOT имя_слота [ WAIT ] #

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

имя_слота: Имя слота, подлежащего удалению.
WAIT: С этим указанием команда будет ждать, пока активный слот не станет неактивным (по умолчанию в этом случае выдаётся ошибка).

UPLOAD_MANIFEST #

Загружает манифест копии при подготовке к инкрементальному резервному копированию.

BASE_BACKUP [ ( параметр [, ...] ) ] #

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

LABEL 'метка'

Устанавливает метку для резервной копии. Если метка не задана, по умолчанию устанавливается метка base backup. Для метки действуют те же правила применения кавычек, что и для стандартных строк SQL при включённым режиме standard_conforming_strings.

TARGET получатель

Указывает серверу, куда передавать резервную копию. Если выбрано значение client, используемое по умолчанию, данные резервной копии передаются клиенту. При значении server файлы копии записываются на сервере в каталог, заданный параметром TARGET_DETAIL. При значении blackhole данные никуда не передаются, а просто отбрасываются.

Чтобы установить значение server, нужно иметь права суперпользователя или роли pg_write_server_files.

TARGET_DETAIL дополнительная_информация

Дополнительная информация о получателе.

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

PROGRESS [ boolean ]

Если параметр включён (по умолчанию он отключён), запрашивается информация, необходимая для отслеживания прогресса операции. Сервер передаёт в ответ приблизительный размер в заголовке каждого табличного пространства, исходя из которого можно понять, насколько продвинулась передача потока. Для вычисления этого размера анализируются размеры всех файлов ещё до начала передачи, и это может негативно повлиять на производительность — в частности, может увеличиться задержка передачи первых данных. Так как файлы базы данных могут меняться во время резервного копирования, оценка размера не будет точной; размер базы может увеличиться или уменьшиться за время от вычисления этой оценки до передачи актуальных файлов.

CHECKPOINT { 'fast' | 'spread' }

Задаёт тип контрольной точки, которая будет выполняться в начале базового копирования. По умолчанию spread (протяжённая).

WAL [ boolean ]

Если параметр включён (по умолчанию он отключён), в резервную копию помещаются необходимые сегменты WAL. При этом в подкаталог pg_wal архива базового каталога будут помещены все файлы с начала до конца копирования.

WAIT [ boolean ]

Если параметр включён (по умолчанию), при копировании ожидается завершение архивации последнего требуемого сегмента WAL либо выдаётся предупреждение, если архивация WAL не включена. Если отключён, при копировании не будет ни ожидания, ни предупреждений, так что обеспечение наличия требуемого журнала становится задачей клиента.

COMPRESSION метод

Указывает серверу метод сжатия данных резервной копии. На данный момент поддерживаются следующие методы: gzip, lz4 и zstd.

COMPRESSION_DETAIL дополнительная_информация

Дополнительная информация для выбранного метода сжатия. Используется только вместе с параметром COMPRESSION. Если значение параметра — целое число, оно обозначает уровень сжатия. В противном случае значение должно представлять собой список ключевых слов, разделённых запятыми, в форме ключевое_слово или ключевое_слово=значение. На данный момент поддерживаются ключевые слова level, long и workers.

Ключевое слово level задаёт уровень сжатия. Для gzip уровень сжатия задаётся целым числом от 1 до 9 (по умолчанию Z_DEFAULT_COMPRESSION или -1), для lz4 — целым числом от 1 до 12 (по умолчанию 0 для максимально быстрого сжатия), а для zstd — целым числом от ZSTD_minCLevel() (обычно -131072) до ZSTD_maxCLevel() (обычно 22), по умолчанию ZSTD_CLEVEL_DEFAULT или 3.

Ключевое слово long включает режим поиска соответствий на большой дистанции для улучшения коэффициента сжатия за счёт повышения нагрузки на память. Этот режим поддерживается только для zstd.

Ключевое слово workers задаёт число параллельных потоков для сжатия. Сжатие в несколько параллельных потоков поддерживается только для zstd.

MAX_RATE скорость

Ограничивает (сдерживает) максимальный объём данных, передаваемый от сервера клиенту за единицу времени. Единица измерения этого параметра — килобайты в секунду. Если задаётся этот параметр, его значение должно быть равно нулю, либо должно находиться в диапазоне от 32 (килобайт/сек) до 1 Гбайта/сек (включая границы). Если передаётся ноль, либо параметр не задаётся, скорость передачи не ограничивается.

TABLESPACE_MAP [ boolean ]

Если параметр включён (по умолчанию он отключён), информация о символических ссылках, существующих в каталоге pg_tblspc, включается в файл tablespace_map. Файл карты табличных пространств содержит имена всех ссылок, содержащихся в каталоге pg_tblspc/, и полный путь для каждой ссылки.

VERIFY_CHECKSUMS [ boolean ]

Если параметр включён (по умолчанию), контрольные суммы проверяются в процессе резервного копирования, когда они присутствуют. При отключении параметра эта проверка опускается.

MANIFEST параметр_манифеста

Когда указывается этот параметр, и он имеет значение yes или force-encode, вместе с копией создаётся и передаётся манифест копии. Манифест содержит список всех файлов, содержащихся в копии, за исключением файлов WAL, которые могут быть добавлены дополнительно. В нём также сохраняется размер, дата последнего изменения и, возможно, контрольная сумма каждого файла. Значение force-encode указывает, что все имена файлов должны кодироваться в шестнадцатеричном виде; по умолчанию кодироваться будут только те имена, которые представлены не байтовыми последовательностями UTF-8. Это значение предназначено в первую очередь для тестирования, чтобы можно было проверить, что клиентские программы могут корректно прочитать закодированные имена. Для совместимости с предыдущими версиями подразумевается значение MANIFEST 'no'.

MANIFEST_CHECKSUMS алгоритм_контрольной_суммы

Задаёт алгоритм контрольных сумм, которые будут рассчитываться для всех файлов, описанных в манифесте копии. В настоящее время поддерживаются алгоритмы NONE (отсутствует), CRC32C, SHA224, SHA256, SHA384 и SHA512. По умолчанию применяется CRC32C.

INCREMENTAL

Запрашивает инкрементальную резервную копию. Обязательно выполните команду UPLOAD_MANIFEST перед началом базового резервного копирования с этим параметром.

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

В первом обычном наборе результатов передаётся начальная позиция резервной копии, в одной строке с двумя столбцами. В первом столбце содержится стартовая позиция в формате XLogRecPtr, а во втором идентификатор соответствующей линии времени.

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

spcoid (oid): OID табличного пространства либо NULL, если это базовый каталог.
spclocation (text): Полный путь к каталогу табличного пространства либо NULL, если это базовый каталог.
size (int8): Приблизительный размер табличного пространства (в килобайтах, размером 1024 байта), если была запрошена информация о прогрессе операции; в противном случае NULL.

За вторым обычным набором результатов следует сообщение CopyOutResponse. Полезной нагрузкой каждого последующего сообщения CopyData будет сообщение в одном из следующих форматов:

new archive (B)

Byte1('n'): Указывает, что это сообщение начинает новый архив. Сервер передаст один архив для основного каталога данных и по одному для каждого дополнительного табличного пространства. Данные в архиве представлены в формате tar («формате обмена ustar», описанном в стандарте POSIX 1003.1-2008).
String: Имя файла этого архива.
String: Для основного каталога данных пустая строка. Для других табличных пространств — полный путь к каталогу, для которого был создан архив.

manifest (B)

Byte1('m'): Указывает, что это сообщение начинает манифест копии.

archive or manifest data (B)

Byte1('d'): Указывает, что это сообщение передаёт данные архива или манифеста.
Byten: Байты данных.

progress report (B)

Byte1('p'): Указывает, что в этом сообщении передаётся отчёт о выполнении.
Int64: Объём обработанных данных в текущем табличном пространстве в байтах.

После передачи сообщения CopyOutResponse или всех таких сообщений передаётся обычный набор результатов, в котором содержится конечная позиция копии в WAL, в том же формате, что и стартовая позиция.

Архив tar каталога данных и всех табличных пространств будет содержать все файлы в этих каталогах, будь то файлы PostgreSQL или посторонние файлы, добавленные в эти каталоги. Исключение составляют только следующие файлы:

postmaster.pid
postmaster.opts
pg_internal.init (находится в нескольких каталогах)
Различные временные файлы и каталоги, создаваемые в процессе работы сервером PostgreSQL, в частности, файлы и каталоги с именами, начинающимися с pgsql_tmp, и временные отношения.
Нежурналируемые отношения, за исключением слоя инициализации, который необходим при восстановлении для пересоздания нежурналируемого отношения (пустого).
pg_wal, включая подкаталоги. Если в резервную копию включаются файлы WAL, в архив входит преобразованная версия pg_wal, в которой будут находиться только файлы, необходимые для восстановления копии, но не всё остальное содержимое этого каталога
pg_dynshmem, pg_notify, pg_replslot, pg_serial, pg_snapshots, pg_stat_tmp и pg_subtrans копируются как пустые каталоги (даже если это символические ссылки)
Файлы, кроме обычных файлов и каталогов, например, символические ссылки (кроме вышеупомянутых каталогов), а также файлы специальных устройств и файлы операционных систем, пропускаются (символические ссылки в pg_tblspc сохраняются).

Если файловая система сервера поддерживает это, в архив включается информация о владельце, группе и режиме файла.

Во всех вышеперечисленных командах значение_параметра можно не указывать, если это параметр типа boolean. Это равнозначно тому, чтобы указать значение TRUE.

Пред.	Наверх	След.
53.3. Аутентификация SASL	Начало	53.5. Протокол логической потоковой репликации

44.3. Materialized Views
Prev	Up	Chapter 44. The Rule System	Home	Next

44.3. Materialized Views #

Materialized views in Postgres Pro use the rule system like views do, but persist the results in a table-like form. The main differences between:

CREATE MATERIALIZED VIEW mymatview AS SELECT * FROM mytab;

and:

CREATE TABLE mymatview AS SELECT * FROM mytab;

are that the materialized view cannot subsequently be directly updated and that the query used to create the materialized view is stored in exactly the same way that a view's query is stored, so that fresh data can be generated for the materialized view with:

REFRESH MATERIALIZED VIEW mymatview;

The information about a materialized view in the Postgres Pro system catalogs is exactly the same as it is for a table or view. So for the parser, a materialized view is a relation, just like a table or a view. When a materialized view is referenced in a query, the data is returned directly from the materialized view, like from a table; the rule is only used for populating the materialized view.

While access to the data stored in a materialized view is often much faster than accessing the underlying tables directly or through a view, the data is not always current; yet sometimes current data is not needed. Consider a table which records sales:

CREATE TABLE invoice (
    invoice_no    integer        PRIMARY KEY,
    seller_no     integer,       -- ID of salesperson
    invoice_date  date,          -- date of sale
    invoice_amt   numeric(13,2)  -- amount of sale
);

If people want to be able to quickly graph historical sales data, they might want to summarize, and they may not care about the incomplete data for the current date:

CREATE MATERIALIZED VIEW sales_summary AS
  SELECT
      seller_no,
      invoice_date,
      sum(invoice_amt)::numeric(13,2) as sales_amt
    FROM invoice
    WHERE invoice_date < CURRENT_DATE
    GROUP BY
      seller_no,
      invoice_date;

CREATE UNIQUE INDEX sales_summary_seller
  ON sales_summary (seller_no, invoice_date);

This materialized view might be useful for displaying a graph in the dashboard created for salespeople. A job could be scheduled to update the statistics each night using this SQL statement:

REFRESH MATERIALIZED VIEW sales_summary;

Another use for a materialized view is to allow faster access to data brought across from a remote system through a foreign data wrapper. A simple example using file_fdw is below, with timings, but since this is using cache on the local system the performance difference compared to access to a remote system would usually be greater than shown here. Notice we are also exploiting the ability to put an index on the materialized view, whereas file_fdw does not support indexes; this advantage might not apply for other sorts of foreign data access.

Setup:

CREATE EXTENSION file_fdw;
CREATE SERVER local_file FOREIGN DATA WRAPPER file_fdw;
CREATE FOREIGN TABLE words (word text NOT NULL)
  SERVER local_file
  OPTIONS (filename '/usr/share/dict/words');
CREATE MATERIALIZED VIEW wrd AS SELECT * FROM words;
CREATE UNIQUE INDEX wrd_word ON wrd (word);
CREATE EXTENSION pg_trgm;
CREATE INDEX wrd_trgm ON wrd USING gist (word gist_trgm_ops);
VACUUM ANALYZE wrd;

Now let's spell-check a word. Using file_fdw directly:

SELECT count(*) FROM words WHERE word = 'caterpiler';

 count
-------
     0
(1 row)

With EXPLAIN ANALYZE, we see:

 Aggregate  (cost=21763.99..21764.00 rows=1 width=0) (actual time=188.180..188.181 rows=1 loops=1)
   ->  Foreign Scan on words  (cost=0.00..21761.41 rows=1032 width=0) (actual time=188.177..188.177 rows=0 loops=1)
         Filter: (word = 'caterpiler'::text)
         Rows Removed by Filter: 479829
         Foreign File: /usr/share/dict/words
         Foreign File Size: 4953699
 Planning time: 0.118 ms
 Execution time: 188.273 ms

If the materialized view is used instead, the query is much faster:

 Aggregate  (cost=4.44..4.45 rows=1 width=0) (actual time=0.042..0.042 rows=1 loops=1)
   ->  Index Only Scan using wrd_word on wrd  (cost=0.42..4.44 rows=1 width=0) (actual time=0.039..0.039 rows=0 loops=1)
         Index Cond: (word = 'caterpiler'::text)
         Heap Fetches: 0
 Planning time: 0.164 ms
 Execution time: 0.117 ms

Either way, the word is spelled wrong, so let's look for what we might have wanted. Again using file_fdw and pg_trgm:

SELECT word FROM words ORDER BY word <-> 'caterpiler' LIMIT 10;

     word
---------------
 cater
 caterpillar
 Caterpillar
 caterpillars
 caterpillar's
 Caterpillar's
 caterer
 caterer's
 caters
 catered
(10 rows)

 Limit  (cost=11583.61..11583.64 rows=10 width=32) (actual time=1431.591..1431.594 rows=10 loops=1)
   ->  Sort  (cost=11583.61..11804.76 rows=88459 width=32) (actual time=1431.589..1431.591 rows=10 loops=1)
         Sort Key: ((word <-> 'caterpiler'::text))
         Sort Method: top-N heapsort  Memory: 25kB
         ->  Foreign Scan on words  (cost=0.00..9672.05 rows=88459 width=32) (actual time=0.057..1286.455 rows=479829 loops=1)
               Foreign File: /usr/share/dict/words
               Foreign File Size: 4953699
 Planning time: 0.128 ms
 Execution time: 1431.679 ms

Using the materialized view:

 Limit  (cost=0.29..1.06 rows=10 width=10) (actual time=187.222..188.257 rows=10 loops=1)
   ->  Index Scan using wrd_trgm on wrd  (cost=0.29..37020.87 rows=479829 width=10) (actual time=187.219..188.252 rows=10 loops=1)
         Order By: (word <-> 'caterpiler'::text)
 Planning time: 0.196 ms
 Execution time: 198.640 ms

If you can tolerate periodic update of the remote data to the local database, the performance benefit can be substantial.

Prev	Up	Next
44.2. Views and the Rule System	Home	44.4. Rules on `INSERT`, `UPDATE`, and `DELETE`

Вход в личный кабинет

Восстановление пароля

Подтверждение аккаунта

Изменение пароля

53.4. Протокол потоковой репликации #

44.3. Materialized Views #