Архитектура Postgres¶

Процессы¶

Когда клиент подключается к Postgres, запрос сначала принимает postmaster и форкает отдельный backend-процесс postgres на каждое клиентское подключение.

postmaster — управляющий процесс: инициализирует shared memory, стартует фоновые процессы,слушает TCP/Unix socket, принимает подключения, форкает backend-процессы, следит за авариями и инициирует recovery.
postgres (backend на сессию) — аутентификация, чтение параметров, парсинг/планирование/выполнение SQL, работа с shared buffers, WAL, блокировками и MVCC, выдача результата клиенту.
background writer — сглаживает запись dirty-страниц из shared buffers.
wal writer — регулярно сбрасывает WAL buffers в WAL files.
checkpointer — ставит контрольные точки, ограничивает длину recovery.
log writer — пишет текстовые серверные логи.
archiver — отправляет завершённые WAL-сегменты в архив при включённом archive_mode.
stats collector (или современная stats subsystem) — собирает статистику для мониторинга и планировщика.

Разделяемая память Postgres¶

Это сегмент RAM, доступный всем процессам Postgres. Он используется для различных буферов:

Shared Buffers
- Главный кэш страниц таблиц/индексов (обычно страница 8 KB).
- Чтение: ищем страницу в shared buffers, при промахе читаем с диска и кладём туда.
- Запись: меняем страницу в shared buffers, помечаем dirty; на диск уйдёт позже, но WAL должен быть записан раньше.
WAL Buffers
- Буфер журнала предзаписи. Изменения сначала формируются в WAL buffers, затем сбрасываются в WAL files.
- wal writer сглаживает запись, но конкретный коммит может сам инициировать flush для durability.
CLOG buffers - Commit LOG, буфер для хранения данных о статусе проведения транзакций.
Lock space - буфер для хранения данных о блокировках, использующихся экземпляром БД.

Память вне shared_buffers¶

work_mem — сортировки/хэш/агрегации/joins; лимит на операцию, а не на сессию.
maintenance_work_mem — VACUUM, CREATE INDEX, ALTER TABLE.
temp_buffers — временные таблицы в сессии.
Локальная память backend, стек, ОС page cache.

Хранение и журналы¶

WAL files — журнал предзаписи; нужен для восстановления и репликации. Правило: сначала WAL на диск, потом data pages.
Data files — файлы таблиц/индексов в base/...; хранят страницы по 8 KB с tuple (xmin/xmax/ctid).
Log files — текстовые диагностические логи сервера (ошибки, подключения, slow queries, checkpoints, autovacuum, deadlocks, replication events).
Archive files — архивированные WAL-сегменты для PITR, если archive_mode включён.

WAL¶

WAL (Write Ahead Log) - техология обеспечения сохранности данных при сбоях.

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

Принцип работы

Каждой записи (т.е. каждому изменению) присваивается свой LSN. WAL буферы хранятся в разделяемой памяти. Процесс WAL writer записывает эти буферы на диск. При сбое система может восстановить изменения по WAL.

LSN (Log Sequence Number)

Это указатель на запись в WAL-журнале. Прерставляет собой 64-битное число.

Контрольная точка (checkpoint)

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

Создаётся процессом checkpointer в двух случаях:

Заполнен WAL буфер (max_wal_size)
Через время checkpoint_timeout (дефолт 300с.)

Восстановление после сбоев

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

Типы восстановления:

Crash recovery: автоматическое, после сбоя
Point in time recovery (PITR): на определённый момент времени
Streaming replication: репликация на основе WAL

UNDO / REDO журналы

......

Checkpointer и восстановление¶

Это момент, когда postgres гарантирует, что все изменённые страницы до определённой точки WAL записаны в data files. Checkpoint нужен, чтобы при crash recovery не проигрывать слишком длинный WAL-отрезок.

Checkpointer заставляет dirty buffers записаться на диск и фиксирует checkpoint record в WAL -> ограничивает объём WAL для crash recovery; слишком частые checkpoints дают пики I/O.
Recovery после сбоя: читается последний checkpoint, затем проигрывается WAL до актуального состояния, возвращая committed-изменения и очищая незавершённые.

После аварийного завершения postgres делает recovery. - читает последний checkpoint - проверяет состояние кластера - воспроизводит WAL начиная с checkpoint - возвращает committed-изменения - очищает последствия незавершённых транзакций

Статистика и планировщик¶

Чтобы оптимизатор выбирал правильный план, postgres хранит статистику: - cardinality таблиц - распределение значений - null fraction - most common values - histogram bounds - correlation

Эти данные обновляются через: ANALYZE, autovacuum/analyze.

Если статистика плохая или устарела, planner может выбрать ужасный план: - seq scan вместо index scan - nested loop вместо hash join - неверный порядок join

Postgres оптимизирует не магически, а по статистическим оценкам стоимости.

Planner опирается на статистику: cardinality, распределения значений, null fraction, MCV, histogram bounds, correlation.
Источники статистики: ANALYZE, autovacuum analyze.
Плохая или устаревшая статистика ведёт к неверным планам (seq scan вместо index scan, неверный join order/метод).

Steal/NO-Steal, Force/NO-Force, ARIES¶

STEAL -- СУБД может вытеснить страницу из буфера и записать её на диск, даже если на этой странице есть изменения незафиксированной транзакции. То есть: T1 еще не сделала COMMIT, но памяти мало, СУБД записывает страницу P на диск. Получается: на диске уже есть результат незавершенной транзакции. Если T1 потом сделает ROLLBACK или система упадет до commit, нужно уметь откатить эти изменения на диске. Значит при STEAL нужен механизм UNDO.

NO-STEAL -- СУБД не имеет права записывать страницу на диск, если на ней есть изменения незавершенной транзакции.

FORCE -- В момент COMMIT, СУБД обязана записать на диск все страницы данных, измененные транзакцией. То есть commit не считается завершенным, пока все измененные данные реально не попадут на диск. Если после commit будет сбой, ничего доделывать не надо: данные уже на диске. Это медленно: commit становится тяжелым, надо синхронно писать много страниц данных.

NO-FORCE -- В момент COMMIT не требуется немедленно записывать на диск все измененные страницы данных. На commit достаточно надежно записать лог, а сами страницы данных можно сбросить позже. После commit может оказаться, что: транзакция уже подтверждена, а часть её данных еще не попала на диск. Если в этот момент случится сбой, надо будет повторить изменения committed-транзакции. Значит при NO-FORCE нужен механизм REDO.

Summary: - STEAL - нужен UNDO, потому что на диск могли попасть изменения незавершенной транзакции. - NO-STEAL - незавершенные изменения на диск не попадали. - FORCE - к моменту commit все изменения уже записаны на диск. - NO-FORCE - нужен REDO, потому что committed транзакция могла не успеть записать все страницы на диск.

Самое частоиспользуемое: STEAL + NO-FORCE.

ARIES - Algorithms for Recovery and Isolation Exploiting Semantics. ARIES использует журналирование и позволяет: - откатывать незавершенные транзакции - повторять подтвержденные изменения - быстро восстанавливаться после сбоя

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

ARIES ведет лог записей о действиях над страницами. Обычно в log-записи есть: • идентификатор транзакции • идентификатор страницы • тип операции • старое значение или достаточная информация для undo • новое значение или достаточная информация для redo • LSN — log sequence number ARIES после сбоя: 1. Analysis 2. Redo 3. Undo

1) REDO в Postgres обеспечивает WAL — журнал предзаписи. Перед тем как измененная страница данных попадет на диск, информация об изменении должна быть записана в WAL. Это и есть принцип write-ahead logging. Если сервер упал, Postgres при старте читает WAL и повторяет нужные изменения, которых может не быть в data files. Это и есть REDO-поведение.

2) Отдельного UNDO log журнала в Postgres нет. Откат транзакции и невидимость незавершенных/отмененных изменений обеспечиваются в через MVCC: старая версия строки остается, новая версия помечается метаданными транзакции, а если транзакция abort, эти версии просто считаются невидимыми. Позже их убирает VACUUM.

Кластер Postgres¶

Файловая структура¶

PGDATA

Это директория, содержащая все файлы кластера бд:

$PGDATA/
├── base/             # Файлы данных баз
│   ├── 1/            # База postgres (OID=1)
│   ├── 13267/        # Другая база (с OID=13267)
├── global/           # Глобальные объекты кластера
├── pg_wal/           # WAL файлы (раньше pg_xlog)
├── pg_xact/          # Данные о транзакциях (CLOG)
├── pg_multixact/     # Данные о мультитранзакциях
├── pg_subtrans/      # Данные о вложенных транзакциях
├── pg_replslot/      # Данные слотов репликации
├── pg_twophase/      # Данные о двухфазных транзакциях
├── pg_snapshots/     # Экспортированные снимки
├── pg_stat_tmp/      # Временные статистические файлы
├── pg_tblspc/        # Символические ссылки на табличные пространства
├── Postgres.conf   # Основной конфиг кластера
├── pg_hba.conf       # Конфиг аутентификации
└── pg_ident.conf     # Конфиг сопоставления пользователей

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

Дефолтные табличные пространства:

pg_default: $PGDATA/base
pg_global: $PGDATA/global

Создание и использование:

-- создаём табличное пространство
create tablespace myspace location '/tmp/myspace';

-- указываем конкретное табл. пр-во при создании таблички
create table mytable (id int) tablespace myspace;

-- просмотр созданных табличных пространств
\db

Логическая структура¶

кластер бд -> бд -> схема -> таблица и др. объекты

Note

Экземпляр = процессы + память

Кластер = данные + файлы

Кластер может существовать на диске без запущенного экземпляра (когда сервер остановлен) => кластер - физические данные, а экземпляр - процессы, работающие с ними

Кластер бд - Это набор баз данных под управлением одного сервера. - Содержит глобальные объекты: роли, табличные пространства - Фактически является директории PGDATA

База данных - Это набор схем. - Физически соответствует поддиректории в PGDATA/base - Соединение клиента всегда с конкретной бд - Изолированное пространство имён

Схема - Это логическая группировка объектов. - Именованное пространство имен внутри бд - По умолчанию есть схема public (DEFAULT), pg_catalog, information_schema - Объекты в разных схемах могут иметь одинаковые имена

Схема существует внутри конкретной БД. Схема принадлежит какому-то пользователю или роли. В одной БД может быть много схем

Если схему не указать: SELECT * FROM groups; - то Postgres начнет искать таблицу по списку схем из search_path. search_path — это список схем, в которых Postgres ищет объект, если схема явно не указана.

SHOW search_path; -- "$user", public

Это значит: сначала Postgres ищет схему с именем, равным имени пользователя, потом ищет схему public.

Команды:

SET search_path TO studs, test;

SHOW search_path;
SELECT current_setting('search_path');

Системный каталог - набор системных таблиц в которых Postgres хранит служебную информацию о бд и ее объектах. Это обычные таблицы, но системного назначения. Системный каталог физически представлен как обычные таблицы, которые находятся в системной схеме pg_catalog.

В каждой бд есть схема pg_catalog, в этой схеме лежат все системные каталоги, а именно: системные таблицы pg_* и др.

В Postgres есть две группы системных каталогов, часть таблиц в pg_catalog — локальные, а часть — общие для кластера. Логически они доступны через pg_catalog в каждой БД, но физически это общие relation-файлы кластера, а не отдельные независимые копии в каждой базе.

Локальные системные каталоги (для конкретной БД)
- pg_namespace
- pg_class
- pg_attribute
- pg_type
- pg_proc
- pg_constraint
- pg_index
Общие системные каталоги (для всего кластера)
- pg_database
- pg_authid
- pg_auth_members
- pg_tablespace
- pg_roles

Способы взаимодействия с системным каталогом:

Прямой SQL-запрос к pg_catalog
- SELECT * FROM pg_database;
- SELECT relname FROM pg_class WHERE relkind = 'r';
Через information_schema - это отдельная схема (views) в каждой базе данных, стандартный SQL-интерфейс к метаданным.
- набор представлений, которые построены поверх pg_catalog.
- Плюсы: более понятный и переносимый интерфейс; ближе к стандарту SQL.
- Минусы: не показывает все внутренние детали Postgres; менее богат по возможностям, чем pg_catalog.
- SELECT table_name FROM information_schema.tables WHERE table_schema = 'public';
```
\d information_schema.tables
.columns, .constraints, .schemata, .views, .routines
```
Через метакоманды psql
- \l \dn \dt

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

Например: таблица хранится в pg_class, ее столбцы — в pg_attribute, связь идет через идентификатор таблицы. То есть системный каталог устроен как связанный набор таблиц с внутренними ссылками.

filenode - идентификатор файла на диске, соответствующего объекту БД обычно совпадает с OID, но может отличаться после операций truncate/cluster

PGDATA/
└── base/
    └── 16384/  # OID базы данных
        └── 16385  # filenode таблицы

search_path - Это последовательность схем, которая будет использоваться для идентификации объекта при использовании неполного имени.

По умолчанию сначала ищет в схеме, совпадающей с именем текущего пользователя. А затем в схеме public.

-- создание схемы
create schema studs

-- с указанием конкретной схемы
create table studs.mytable (id int);

-- без указания конкретной схемы
-- будет использована первая схема из search_path -> public
create table mytable (id int)l

MVCC¶

Multi-Version Concurrency Control: Postgres не переписывает строку на месте, а создаёт новую версию; старая помечается как устаревшая и живёт до уборки.

Ключевые сущности¶

XID — 32-битный номер модифицирующей транзакции (монотонно растёт, затем wraparound).
Tuple — хранит xmin (кто создал), xmax (кто удалил/заменил или 0), ctid (физический адрес; после UPDATE старая версия ссылается на новую).
Скрытые поля: xmin, xmax, cmin, cmax.
Snapshot — на старте запроса/транзакции фиксируются границы XID и активные XID; по snapshot и xmin/xmax решается видимость версии.

Диагностика: SELECT xmin, xmax, * FROM table; — увидеть версии; SELECT pg_current_xact_id(); — текущий XID.

Правило видимости¶

Версия видима, если xmin уже committed и не в активных XID snapshot, а xmax либо 0, либо не committed/за пределами snapshot.

Как работают операции¶

INSERT — tuple с xmin=current_xid, xmax=0, ctid на себя; виден только своей транзакции до коммита.
UPDATE — старая версия получает xmax=current_xid, создаётся новая (xmin=current_xid, xmax=0, свой ctid; старая ctid указывает на новую). Разные snapshot видят старую или новую версию.
DELETE — ставит xmax=current_xid; индексы ещё указывают на tuple. После коммита версия невидима, но остаётся до VACUUM.

Плюсы Минусы¶

Плюсы: чтение без блокировок писателей, стабильный снимок для долгих запросов, уровни изоляции RC/RR/Serializable.
Минусы: накапливаются dead tuples и bloat -> нужен VACUUM/autovacuum; сложная логика видимости; риск wraparound из-за 32-битного XID.

Связь с VACUUM¶

VACUUM удаляет мёртвые версии, обновляет visibility map, может freeze старые XID. Без регулярной уборки растут таблицы/индексы, увеличивается I/O и при wraparound кластер может остановиться.

Транзакции¶

Транзакция - логическая единица работы с данными. Объединяет последовательность действий в одну операцию.

Виды транзакций¶

Транзакции бывают явные и неявные. Явные пользователь задаёт самостоятельно через BEGIN, COMMIT и т.п. Если нет явного указания начала транзакции, то ЛЮБОЙ запрос выполняется в рамках неявной транзакции.

Команды управления транзакциями¶

-- Начало транзакции
BEGIN; -- или START TRANSACTION;

-- Инициируем изменение
UPDATE studs.groups SET exam_name = 'april_fools' WHERE max_score = 100;
-- На данном этапе изменения не применились сразу, а буферизировались в памяти и WAL

-- Фиксация изменений - применяем изменения к бд
COMMIT;

-- очередные изменения
UPDATE studs.exams SET ......
UPDATE studs.groups SET ......

-- Отбрасываем созданные изменения
ROLLBACK;

-- Создание точки сохранения внутри транзакции
SAVEPOINT имя_точки;

-- Откат к точке сохранения
ROLLBACK TO имя_точки;

ACID¶

О том - какие гарантии дает бд при работе с параллельными транзакциями

A - Atomicity (атомарность) - либо все операции внутри транзакции выполняются, либо ни одной не выполняется.
C - Consistency (согласованность) - каждая транзакция переводит базу из одного консистентного состояния в другое. Консистентность определяется ограничениями (constraints), триггерами, приложением, бизнес логикой.

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

I - Isolation (изоляция) - параллельные транзакции не должны мешать друг другу. Появляются аномали и уровнем изоляций их можно решать.
D - Durability (надёжность) - после успешного коммита транзакции её изменения сохраняются навсегда, даже при сбое системы. Все изменения (и факт коммита) сначала пишутся в WAL (журнал). После падения - база replay’ит журнал, восстанавливая состояние до последнего коммита.

Аномалии параллельных транзакций¶

Грязное чтение (dirty read) - если одна транзакция видит измененные данные другой незавершенной транзакции
Транзакция А изменила данные, но ещё не закоммитила.
Транзакция B уже читает эти грязные данные.
Потом А делает ROLLBACK -> B опиралась на то, чего как бы никогда не было.
Присутствует на уровне Read Uncommitted.
Неповторяющееся чтение - если один и тот же запрос в рамках транзакции возвращает разные результаты
В начале транзакции B читает строку (баланс = 100).
Параллельно A коммитит UPDATE (баланс = 200).
B снова читает ту же строку в рамках той же транзакции и вдруг видит уже 200.
То есть одна и та же выборка внутри одной транзакции даёт разные результаты.
Присутствует на уровне Read Committed.
Фантомное чтение - если один и тот же запрос в рамках транзакции возвращает разное число записей
В транзакции B: SELECT * FROM orders WHERE status = 'NEW';
Пока B работает, другая транзакция A добавляет новую подходящую строку и коммитит.
B повторяет тот же SELECT и внезапно видит ещё одну строку - фантом.
Присутствует на уровне Repeatable Read.

Изоляция отвечает: какие из этих феноменов допустимы на данном уровне, а какие - нет.

Уровни изоляции¶

postgres использует MVCC (Multi-Version Concurrency Control):

Каждая транзакция видит снимок (snapshot) данных: только те версии строк, которые были действительны на момент начала транзакции (или запроса - зависит от уровня).
Новые версии строк создаются, старые не затираются сразу -> можно читать старую картину мира, пока другие пишут новую.

Дальше включаются уровни изоляции:

READ UNCOMMITED - НЕ ПОДДЕРЖИВАЕТСЯ В postgres
READ COMMITTED
- Каждый запрос внутри транзакции видит актуальные на момент начала запроса коммитнутые данные.
- Можно словить неповторяемые чтения и фантомы.
- Зато меньше блокировок, больше параллелизма.
REPEATABLE READ
- Вся транзакция работает с одним снимком, сделанным при её старте.
- Одну и ту же строку ты видишь одинаковой весь срок жизни транзакции.
- Нет грязных и неповторяемых чтений, но возможны фантомы некоторых типов.
- В postgres это ещё и SSI: при конфликтующих транзакциях одну может откатить.
SERIALIZABLE
- postgres пытается сделать так, будто все транзакции выполнялись строго по очереди, а не параллельно.
- Максимальная изоляция, но может больше откатывать транзакции, если видит опасный конфликт.

Выбор режима изоляции транзакции: BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;

VACUUM¶

Зачем: убрать dead tuples, освободить место, обновить visibility map, заморозить старые XID и защититься от wraparound.

Что делает¶

Обходит строки, по xmin/xmax и статусам XID решает: жива или мертва.
Помечает мёртвые версии как свободное место; чистит/помечает записи в индексах.
Обновляет visibility map и статистику планировщика.
VACUUM FREEZE ставит FrozenXid очень старым строкам.

Варианты¶

VACUUM table — мягкая уборка без перестройки файла.
VACUUM FREEZE table — агрессивная заморозка XID.
VACUUM FULL table — перепаковка таблицы с блокировкой; реально уменьшает файл.
Autovacuum — launcher + workers автоматически запускают VACUUM/ANALYZE по порогам вставок/апдейтов/удалений и возрасту XID; основа нормальной эксплуатации.

Wraparound защита¶

XID 32-битный (~2 млрд). Регулярный VACUUM/Autovacuum замораживает старые строки; иначе возможна остановка кластера до уборки.

Если не вакуумить¶

Растут файлы таблиц/индексов, лишний I/O, падает производительность; при близком wraparound сервер может запретить новые транзакции до завершения аварийного VACUUM.

ЖИЗНЕННЫЙ ЦИКЛ SQL ЗАПРОСА¶

Для каждого запроса backend postgres проходит примерно такие стадии: - Parser - SQL превращается в внутреннее дерево разбора. - Analyzer / Rewriter - Проверяются таблицы, колонки, типы, права доступа. - Также срабатывают rewrite rules, view expansion. - Planner / Optimizer - Строится план выполнения: - Seq Scan / Index Scan / Bitmap Heap Scan - Nested Loop / Hash Join / Merge Join - Sort / Aggregate / Gather и т.д. - Executor - План реально выполняется: - читаются страницы - ищутся tuple - ставятся блокировки - обновляются строки - пишется WAL

ЖИЗНЕННЫЙ ЦИКЛ SELECT SQL ЗАПРОСА¶

1) Клиент отправляет SQL -> backend получает текст.

2) Парсинг (Parser) - Лексер/парсер строит дерево разбора.

3) Анализатор / Rewriter - Проверка наличия схем/таблиц/колонок, прав доступа, типов. - Расширение VIEW, RULES, CTE; подстановка default, проверка функций/операторов. - Фиксация snapshot (уровень изоляции) и установка режима блокировок (обычно Access Share).

4) Планировщик / Optimizer (cost-based) - Собирает пути доступа (seq scan, index/bitmap scan, TID scan, parallel варианты). - Оценивает стоимости по статистике (анализ histogram/MCV, correlation, reltuples, relpages, correlation, n_distinct). - Выбирает join order и метод (nested loop / hash / merge / parallel hash), add node Sort/Agg/Gather/Gather Merge. - Формирует итоговый план-дерево executor-узлов.

5) Исполнитель (Executor) - Идёт по плану сверху вниз: узлы-поставщики дают строки потребителям. - Доступ к данным: сначала shared buffers, при промахе чтение с диска -> буфер становится pinned, затем unpinned; при parallel — счётчики блоков в DSM. - Проверка видимости MVCC для каждой найденной версии (snapshot vs xmin/xmax); невидимые версии пропускаются. - Применение фильтров/QUAL, вычисление выражений, проекция колонок. - Узлы Sort/Hash/Agg/Window могут использовать work_mem; при переполнении — temp файлы в pg_temp. - Результат стримится клиенту через сетевой буфер.

6) Память и файловые ресурсы - shared buffers — страницы таблиц/индексов. - work_mem — сортировки/хэш/agg/join; при переполнении — временные файлы. - temp_buffers — временные таблицы сессии. - syscache/catcache — метаданные каталога в памяти backend.

ЖИЗНЕННЫЙ ЦИКЛ INSERT/UPDATE/DELETE SQL ЗАПРОСА¶

1) Клиент -> backend получает текст DML.

2) Парсинг / Анализ / Переписывание - Те же этапы, что у SELECT: синтаксис, объекты, права, типы, rewrite rules. - Фиксация snapshot и захват блокировок stronger: INSERT — RowExclusive, UPDATE/DELETE — RowExclusive + tuple-level locks при исполнении.

3) Планирование - Выбор пути поиска строк для UPDATE/DELETE (seq, index, bitmap, parallel), оценка по статистике. - План может включать RETURNING; для UPDATE — дополнительный ModifyTable node создающий новые версии.

4) Исполнение - INSERT: ищет/расширяет нужную страницу heap, размещает tuple (xmin=current_xid, xmax=0, ctid на себя), добавляет записи в индексы. - UPDATE: находит видимую версию, ставит ей xmax=current_xid, создаёт новую версию (xmin=current_xid, xmax=0, новый ctid); обновляет индексы на новую версию. - DELETE: ставит xmax=current_xid на найденной версии; индексы пока указывают на неё. - Tuple lock: перед изменением — проверка конфликтов/версии, возможен HOT-update если остаётся в той же странице без обновления индексов. - Все изменённые страницы в shared buffers помечаются dirty; WAL-записи формируются в WAL buffers.

5) WAL и коммит - WAL record фиксирует факт вставки/обновления/удаления и изменения индексов/FSM/visibility map. - При COMMIT backend вызывает flush нужной части WAL на диск (fsync через walreceiver/walwriter при необходимости) -> durability. - Dirty data pages могут быть сброшены позже (background writer/checkpointer).

6) Последействия - FSM/Visibility map обновляются, чтобы знать свободное место и пригодность для index-only scan. - Triggers/constraints: BEFORE может модифицировать/отклонить строку; AFTER/DEFERRED выполняются после фиксации действия. - Автовакуум позже уберёт dead tuples (особенно после UPDATE/DELETE) и может freeze XID.

7) Память - shared buffers — изменённые страницы. - WAL buffers — журнал изменений до записи в WAL files. - work_mem — сортировки/хэш при подготовке данных к изменению (например, Merge/Hash join перед UPDATE ... FROM). - temp files — при переполнении work_mem.

Индексы¶

...¶

https://edu.postgrespro.ru/16/dba1-16/dba1_11_data_lowlevel.html
https://edu.postgrespro.ru/16/dba1-16/dba1_12_admin_monitoring.html
https://edu.postgrespro.ru/16/dba1-16/dba1_13_access_overview.html
https://edu.postgrespro.ru/16/dba1-16/dba1_14_backup_overview.html
https://edu.postgrespro.ru/16/dba1-16/dba1_15_replica_overview_physical.html
https://edu.postgrespro.ru/16/dba1-16/dba1_16_replica_overview_logical.html

Роли, Привилегии и Права доступа¶

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

Роли и пользователи - это одно и тоже. Разница только в атрибутах: - Пользователь: роль с атрибутом LOGIN (может подключаться к бд) - Роль: без атрибута LOGIN по умолчанию - группа привилегий

Пользователи могут быть созданы суперпользователем (postgres) и пользователем с ролью CREATEROLE

CREATE ROLE myrole;
CREATE ROLE myrole LOGIN PASSWORD 'password';
CREATE USER myuser PASSWORD 'password';

GRANT - Выдает привилегии.

REVOKE - Забирает привилегии.

Атрибуты ролей - LOGIN/NOLOGIN - разрешает/не разрешает подключаться к бд (пользователю) - SUPERUSER/NOSUPERUSER - даёт/не даёт все привилегии - CREATEDB - разрешает создавать бд - CREATEROLE - создане других ролей - REPLICATION - разрешает подключаться для репликации - Bypass RLS - обходить политики защиты строк - PASSWORD - устанавливает пароль - PASSWORD NULL - запретить пользователю вход по паролю - CONNECTION LIMIT n - ограничивает число подключений - VALID UNTIL - срок действия пароли/роли

CREATE ROLE admin WITH LOGIN CREATEDB CREATEROLE PASSWORD 'secret1';

ALTER ROLE myuser WITH CREATEDB;
ALTER ROLE myuser VALID UNTIL '2026-12-31';

DROP ROLE myuser;

Итого, для подключения к бд надо иметь:

LOGIN
CONNECT на нужную бд
разрешение в pg_hba.conf

Группы ролей - Роли можно объединять в группы для более удобного взаимодействия

-- создаём группу
CREATE ROLE devs;

-- добавляем пользователей к группе
GRANT devs TO alice, bob;

GRANT devs TO admin WITH admin option;

Привилегии - Определяют, что может роль делать с объектами БД

SELECT - чтение данных из таблицы/представления
INSERT - добавление данных
UPDATE - изменение данных
DELETE - удаление данных
TRUNCATE - очистка таблицы
REFERENCES - создание внешнего ключа
TRIGGER - создание триггера
CREATE - создание объекта (схемы, таблицы и т.д.)
CONNECT - подключение к БД
TEMPORARY - создание временных таблиц
EXECUTE - выполнение функции/процедуры
USAGE - использование схемы, последовательности и т.д.
ALL PRIVILEGES - все привилегии сразу

Права на базу данных - CONNECT — право подключаться к БД - CREATE — создавать схемы в БД - TEMPORARY / TEMP — создавать временные таблицы

GRANT CONNECT ON DATABASE education TO student1;
GRANT CREATE ON DATABASE education TO teacher;
GRANT TEMP ON DATABASE education TO student1;

Права на схему - USAGE — можно обращаться к объектам схемы - CREATE — можно создавать объекты в схеме

GRANT USAGE ON SCHEMA studs TO student1;
GRANT CREATE ON SCHEMA studs TO teacher;
REVOKE CREATE ON SCHEMA studs FROM PUBLIC;

Права на таблицы - SELECT — читать данные - INSERT — добавлять строки - UPDATE — изменять строки - DELETE — удалять строки - TRUNCATE — очищать таблицу - REFERENCES — создавать внешние ключи, ссылающиеся на таблицу - TRIGGER — создавать триггеры

GRANT SELECT ON studs.exams TO student1;
GRANT INSERT ON studs.exams TO student1;
GRANT UPDATE ON studs.exams TO student1;
GRANT DELETE ON studs.exams TO student1;
GRANT TRUNCATE ON studs.exams TO teacher;
GRANT REFERENCES ON studs.groups TO teacher;
GRANT TRIGGER ON studs.exams TO teacher;
GRANT ALL PRIVILEGES ON studs.exams TO teacher;

Права на представления - SELECT - INSERT, UPDATE, DELETE, если представление обновляемое - TRIGGER - REFERENCES

GRANT SELECT ON studs.exams_view TO student1;

Права на последовательности - USAGE - SELECT - UPDATE

GRANT USAGE, SELECT ON SEQUENCE studs.exams_id_seq TO student1;
GRANT UPDATE ON SEQUENCE studs.exams_id_seq TO teacher;

Права на функции - EXECUTE

GRANT EXECUTE ON FUNCTION calc_score(integer) TO student1;

Наследование привилегий

INHERIT - права унаследованной роли действуют сразу, без дополнительной команды SET ROLE
NOINHERIT - даже если роль включена в другую роль, она не использует ее права автоматически, нужно писать SET ROLE

create role a login noinherit;
create role b; -- по дефолту - INHERIT
create role devs;

grant devs to a; -- автоматическое наследование, SET ROLE - делать не надо

grant devs to b;
set role devs; -- явная активация привилегий группы для NOINHERIT