PostgreSQL Deep Dive для Go-разработчика

Зачем знать: На уровне Middle 2 ты уже не просто “пишешь SQL” — ты понимаешь, как PostgreSQL обрабатывает запросы внутри: какие процессы запущены, что такое WAL и MVCC, почему VACUUM так важен. Без этого знания ты не сможешь дебажить медленные запросы, объяснить replication lag, выбрать правильный индекс, настроить connection pooling. PostgreSQL — стандарт де-факто для production в 2026 году, и Go-разработчик уровня Middle 2 должен знать его на уровне DBA-junior.

Содержание

1. Концепция и архитектура PostgreSQL
2. Под капотом: MVCC, WAL, Vacuum
3. Gotchas
4. Production-практики
5. Вопросы для собеседования
6. Practice
7. Источники

---

1. Концепция и архитектура PostgreSQL

1.1 Process-per-connection

PostgreSQL — multi-process архитектура (в отличие от MySQL, который thread-per-connection). На каждое клиентское соединение создаётся отдельный backend process (форкается от postmaster).

                    ┌─────────────────────────────┐
                    │      postmaster (PID 1)     │
                    │   (main supervisor)         │
                    └────────┬────────────────────┘
                             │ fork()
        ┌────────────────────┼────────────────────────────┐
        ▼                    ▼                            ▼
┌──────────────┐    ┌──────────────┐         ┌──────────────────┐
│  backend 1   │    │  backend 2   │   ...   │ background       │
│  (client A)  │    │  (client B)  │         │ workers:         │
└──────────────┘    └──────────────┘         │ - wal writer     │
                                              │ - bgwriter       │
                                              │ - autovacuum     │
                                              │ - checkpointer   │
                                              │ - stats collector│
                                              │ - logical repl.  │
                                              └──────────────────┘
                             │
                    ┌────────▼─────────┐
                    │  Shared memory   │
                    │  (Shared Buffers,│
                    │   WAL Buffers,   │
                    │   Locks)         │
                    └──────────────────┘
                             │
                    ┌────────▼─────────┐
                    │   Disk (data,    │
                    │   pg_wal/, base/)│
                    └──────────────────┘

Следствия:

• Каждый backend ест 5-10 MB RAM минимум. 1000 соединений = ~10 GB RAM только на процессы.
• Поэтому обязательно использовать pooler (pgbouncer, pgcat, или пул в Go-приложении).
• В Go: pgx имеет встроенный пул соединений. Не открывай сырые соединения для каждого запроса.

1.2 Shared Buffers

Shared Buffers — основной кэш страниц (по 8 KB по умолчанию). Все backend-процессы делят этот кэш.

Дефолт: 128MB. Production: 25% RAM (но не больше 8 GB обычно — дальнейший рост даёт мало пользы из-за полагания на OS page cache).

SHOW shared_buffers;  -- 128MB

-- В postgresql.conf:
shared_buffers = 8GB
effective_cache_size = 24GB   -- подсказка планировщику: сколько в OS cache

1.3 WAL (Write-Ahead Log)

Все изменения сначала пишутся в WAL, потом — в data files. Это гарантирует durability (D из ACID) и enable point-in-time recovery (PITR).

COMMIT;
  ↓
1. Запись в WAL buffer (RAM)
2. fsync WAL на диск (или async, если synchronous_commit = off)
3. Только тогда COMMIT считается успешным
4. Изменения в shared buffers — данные в base/ файлах обновятся ЛЕНИВО (checkpoint)

WAL files лежат в pg_wal/ (раньше pg_xlog/). Каждый файл — 16 MB по умолчанию.

Checkpoint — периодическая запись dirty buffers на диск. Параметры:

• checkpoint_timeout = 5min
• max_wal_size = 1GB (триггер по объёму WAL)
• checkpoint_completion_target = 0.9

1.4 MVCC: Multi-Version Concurrency Control

PostgreSQL не блокирует читателей пишущими и наоборот. Это делается через MVCC: каждая строка имеет версии.

Каждая tuple (строка) хранит:

• xmin — ID транзакции, создавшей строку
• xmax — ID транзакции, удалившей/обновившей строку (0, если строка живая)
• ctid — физический адрес

SELECT xmin, xmax, ctid, * FROM users LIMIT 5;
--   xmin   | xmax | ctid  | id |  name
-- ---------+------+-------+----+--------
--   12345  |   0  | (0,1) |  1 | Alice
--   12346  |12347 | (0,2) |  2 | Bob (deleted by tx 12347)

Tuple visibility:

• Транзакция видит строку, если xmin < my_txid AND (xmax = 0 OR xmax > my_txid).
• Это работает в READ COMMITTED и REPEATABLE READ по-разному (через snapshot).

Последствие: UPDATE = INSERT новой версии + пометка старой xmax. Поэтому таблицы “пухнут” (bloat), и нужен VACUUM.

1.5 Vacuum и Autovacuum

Dead tuples — строки, которые уже невидимы ни одной транзакции (xmax < oldest active txid). Они занимают место на диске и в индексах.

VACUUM освобождает место в существующих страницах (но не возвращает в OS). VACUUM FULL — переписывает таблицу полностью (ACCESS EXCLUSIVE LOCK, нельзя в production!).

Autovacuum — background процесс, запускающий VACUUM/ANALYZE автоматически.

Триггеры autovacuum (по умолчанию):

• autovacuum_vacuum_threshold = 50 + autovacuum_vacuum_scale_factor = 0.2 (20%)
• То есть VACUUM запускается, когда dead_tuples > 50 + 0.2 * total_tuples.

Для больших таблиц (миллиарды строк) дефолт слишком ленив — настраивать per-table:

ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor = 0.01,  -- 1% вместо 20%
  autovacuum_analyze_scale_factor = 0.005
);

---

2. Под капотом / Архитектура

2.1 EXPLAIN и EXPLAIN ANALYZE

EXPLAIN показывает план запроса (без выполнения), EXPLAIN ANALYZE — выполняет и показывает actual time.

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT u.name, COUNT(o.id)
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE u.country = 'KZ'
GROUP BY u.id;

Пример вывода:

GroupAggregate  (cost=1234.56..1456.78 rows=100 width=40) (actual time=45.123..56.789 rows=98 loops=1)
  Group Key: u.id
  Buffers: shared hit=234 read=56
  ->  Sort  (cost=...) (actual time=...)
        Sort Key: u.id
        Sort Method: quicksort  Memory: 25kB
        ->  Hash Left Join  (cost=...) (actual time=...)
              Hash Cond: (o.user_id = u.id)
              ->  Seq Scan on orders o
              ->  Hash
                    ->  Bitmap Heap Scan on users u
                          Recheck Cond: (country = 'KZ')
                          ->  Bitmap Index Scan on users_country_idx
                                Index Cond: (country = 'KZ')
Planning Time: 0.456 ms
Execution Time: 57.123 ms

Ключевые узлы плана:

Узел	Когда используется	Сложность
Seq Scan	Чтение всех строк подряд. Хорошо для маленьких таблиц или когда читаем > 5-10%	O(N)
Index Scan	По индексу, читая строки из heap	O(log N) + random IO
Index Only Scan	Все нужные столбцы в индексе (covering). Самый быстрый.	O(log N)
Bitmap Index Scan	Строит битмапу, потом Bitmap Heap Scan читает страницы. Хорошо при большом diapason.	O(log N) + sequential IO
Nested Loop	Для каждой строки внешней таблицы — поиск во внутренней. Хорошо при малых наборах.	O(NM) или O(Nlog M) с индексом
Hash Join	Строит hash от меньшей таблицы, проходит по большей. Лучше всего для equi-join больших данных.	O(N+M)
Merge Join	Если обе стороны отсортированы по join-ключу. Эффективно для big-big join.	O(N+M)

cost vs actual time:

• cost=startup..total — оценка планировщика (в условных единицах).
• actual time=startup..total rows=X loops=Y — реальное время в ms.
• Если cost сильно расходится с actual — статистика устарела (ANALYZE нужен) или плохая корреляция.

BUFFERS показывает количество страниц:

• shared hit — из shared_buffers
• shared read — с диска (или OS cache)
• Если shared read большой — кэш не справляется.

2.2 Типы индексов

B-tree (default)

Сбалансированное дерево. Для равенства и диапазонов (=, <, >, BETWEEN, ORDER BY).

CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_orders_created_at ON orders(created_at DESC);

Hash

Только для =. До PG 10 не был crash-safe. Редко используется, т.к. B-tree почти всегда не хуже.

GIN (Generalized Inverted Index)

Для составных значений: массивы, JSONB, full-text search, ltree.

-- JSONB поиск
CREATE INDEX idx_users_meta ON users USING GIN (metadata jsonb_path_ops);
SELECT * FROM users WHERE metadata @> '{"role": "admin"}';

-- Full-text search
CREATE INDEX idx_articles_search ON articles USING GIN (to_tsvector('russian', body));
SELECT * FROM articles WHERE to_tsvector('russian', body) @@ to_tsquery('russian', 'постгрес');

-- Массивы
CREATE INDEX idx_tags ON posts USING GIN (tags);
SELECT * FROM posts WHERE tags @> ARRAY['go', 'postgres'];

GiST (Generalized Search Tree)

Для геометрии (PostGIS), ranges, full-text (медленнее GIN), trigram.

-- Geo (требует PostGIS)
CREATE INDEX idx_locations_geom ON locations USING GIST (geom);

-- Range
CREATE INDEX idx_reservations_period ON reservations USING GIST (period);  -- tsrange

BRIN (Block Range Index)

Очень компактный. Хранит min/max по блокам. Подходит для отсортированных данных (time series).

CREATE INDEX idx_logs_ts_brin ON logs USING BRIN (ts) WITH (pages_per_range = 32);
-- Размер: KB вместо GB для B-tree
-- Скорость поиска хуже, но достаточно для big data

Partial index

Индекс только на часть таблицы.

CREATE INDEX idx_orders_pending ON orders(created_at) WHERE status = 'pending';
-- Размер маленький, ускоряет только запросы со status='pending'

Composite index

Порядок колонок критичен. Индекс (a, b, c) работает для запросов по a, (a, b), (a, b, c), но НЕ по b или c отдельно (с PG 12+ есть partial — Index Skip Scan, но это редкость).

CREATE INDEX idx_orders_user_status_date ON orders(user_id, status, created_at);
-- Хорошо: WHERE user_id = 1
-- Хорошо: WHERE user_id = 1 AND status = 'paid'
-- Плохо: WHERE status = 'paid'  -- индекс не используется!

Правило: сначала колонки с equality, потом с range.

Covering index (INCLUDE)

Добавляет колонки в индекс, но они не участвуют в дереве (только в leaf-ах). Позволяет Index Only Scan без UPSERT в дерево.

CREATE INDEX idx_orders_user_id_cover ON orders(user_id) INCLUDE (total, status);
-- Запросы SELECT total, status FROM orders WHERE user_id = X — Index Only Scan

CREATE INDEX CONCURRENTLY

Обычный CREATE INDEX берёт SHARE lock — блокирует записи. В production используй CONCURRENTLY:

CREATE INDEX CONCURRENTLY idx_orders_status ON orders(status);

⚠️ CONCURRENTLY медленнее (2 прохода), не работает внутри транзакции. Если упал — индекс остаётся INVALID (нужно DROP и пересоздать).

2.3 Query Planner

Планировщик решает, какой план выполнения использовать на основе статистики:

• pg_statistic — внутренняя таблица.
• pg_stats — view над ней.
• ANALYZE обновляет статистику (autovacuum analyze запускает регулярно).

-- Посмотреть селективность колонки
SELECT n_distinct, most_common_vals, most_common_freqs, correlation
FROM pg_stats WHERE tablename = 'orders' AND attname = 'status';

Cost-based:

• seq_page_cost = 1.0 (стоимость sequential страницы)
• random_page_cost = 4.0 (default; для SSD ставят 1.1)
• cpu_tuple_cost = 0.01
• Эти параметры влияют на выбор плана.

Debug плана:

-- Запретить seq scan, чтобы планировщик попробовал индекс
SET enable_seqscan = off;
EXPLAIN ANALYZE SELECT ...;
RESET enable_seqscan;

⚠️ Это только для debug — не оставлять в production.

2.4 Vacuum в деталях

-- Сколько dead tuples
SELECT schemaname, relname, n_live_tup, n_dead_tup,
       round(n_dead_tup::numeric / NULLIF(n_live_tup, 0), 3) AS dead_ratio
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC LIMIT 10;

Bloat — раздутие таблицы или индекса из-за dead tuples.

Чтобы оценить bloat: расширение pgstattuple:

CREATE EXTENSION pgstattuple;
SELECT * FROM pgstattuple('orders');
-- tuple_count, dead_tuple_count, free_space, free_percent

Решения от bloat:

1. VACUUM — освобождает место в существующих страницах.
2. VACUUM FULL — переписывает таблицу. Берёт ACCESS EXCLUSIVE LOCK → недоступна для всех. НЕ в production!
3. pg_repack — расширение, переписывает таблицу/индекс онлайн (без блокировок).
4. REINDEX CONCURRENTLY (PG 12+) — пересоздать индекс онлайн.

2.5 Connection Pooling: PgBouncer

Modes:

Mode	Поведение	Когда применять
session	Соединение клиента закреплено за backend на всю сессию	Дефолт. Поддерживает все фичи (SET, prepared statements).
transaction	После COMMIT соединение возвращается в пул	Самый популярный. Высокое соотношение клиентов к backend. ⚠️ SET LOCAL, prepared statements проблемны.
statement	После каждого statement — возврат в пул	Совсем экстремально. Транзакции не поддерживаются.

Transaction mode pitfalls:

• PREPARE/EXECUTE — protocol-level prepared statements теряются.
• SET (без LOCAL) сохраняется в backend и “загрязняет” пул.
• LISTEN/NOTIFY ломается.

PostgreSQL 14+ добавил built-in pooling в виде protocol-level prepared statements для PgBouncer. С PG 17 (2024) поддержка named prepared statements в PgBouncer 1.21+ — теперь можно использовать transaction mode + prepared statements.

В Go (pgx) есть свой connection pool:

import "github.com/jackc/pgx/v5/pgxpool"

pool, err := pgxpool.New(ctx, "postgres://user:pass@host/db?pool_max_conns=20")
defer pool.Close()

2.6 Replication

Streaming replication (physical):

• Primary шлёт WAL records на replica.
• Replica применяет WAL, оставаясь в синке.
• Sync или async режим.

Setup (primary):

# postgresql.conf
wal_level = replica
max_wal_senders = 10
synchronous_commit = on   # для sync replication

Logical replication (PG 10+):

• Шлёт logical changes (INSERT/UPDATE/DELETE с values), а не raw WAL.
• Можно реплицировать отдельные таблицы, между разными версиями.
• На основе publication/subscription.

-- Primary
CREATE PUBLICATION pub_orders FOR TABLE orders;

-- Replica
CREATE SUBSCRIPTION sub_orders
  CONNECTION 'host=primary user=repl'
  PUBLICATION pub_orders;

HA Solutions:

• Patroni (Zalando) — стандарт де-факто. Использует etcd/Consul/ZK для consensus.
• repmgr — старая альтернатива. Меньше функций.
• Stolon — менее популярна.
• pg_auto_failover — от Citus/Microsoft.

В Yandex Cloud / AWS / GCP — managed Postgres с встроенным failover.

2.7 Read Replicas в Go

Простой подход: два пула.

type DB struct {
    Master  *pgxpool.Pool
    Replica *pgxpool.Pool
}

func (db *DB) WriteTx(ctx context.Context, fn func(tx pgx.Tx) error) error {
    return pgx.BeginTxFunc(ctx, db.Master, pgx.TxOptions{}, fn)
}

func (db *DB) ReadOnly(ctx context.Context, fn func(conn *pgxpool.Conn) error) error {
    conn, err := db.Replica.Acquire(ctx)
    if err != nil {
        return err
    }
    defer conn.Release()
    return fn(conn)
}

⚠️ Replication lag — на replica данные приходят с задержкой (10ms-сек).

• Если ты только что записал и сразу читаешь — может не быть.
• Read-your-writes: после write — следующая read на master.

Сложнее: смарт-роутинг библиотеки (pgx-failover, pgcat) — лагерь умнее.

2.8 Partitioning

С PG 10+ — declarative partitioning.

Range:

CREATE TABLE orders (
    id BIGSERIAL,
    created_at TIMESTAMPTZ NOT NULL,
    user_id INT,
    total NUMERIC
) PARTITION BY RANGE (created_at);

CREATE TABLE orders_2026_q1 PARTITION OF orders
    FOR VALUES FROM ('2026-01-01') TO ('2026-04-01');
CREATE TABLE orders_2026_q2 PARTITION OF orders
    FOR VALUES FROM ('2026-04-01') TO ('2026-07-01');

List:

CREATE TABLE users PARTITION BY LIST (country);
CREATE TABLE users_kz PARTITION OF users FOR VALUES IN ('KZ');
CREATE TABLE users_ru PARTITION OF users FOR VALUES IN ('RU');

Hash:

CREATE TABLE events PARTITION BY HASH (user_id);
CREATE TABLE events_p0 PARTITION OF events FOR VALUES WITH (MODULUS 4, REMAINDER 0);
-- ... p1, p2, p3

Partition pruning — планировщик отбрасывает партиции при запросе:

EXPLAIN SELECT * FROM orders WHERE created_at > '2026-04-15';
-- Идёт только в orders_2026_q2 (если partition pruning работает)

⚠️ Когда нужен partitioning:

• Таблица > 100 GB (или планируется такой рост).
• Очевидный ключ (time, tenant_id).
• Старые партиции можно DROP (efficient deletion).

⚠️ Когда НЕ нужен:

• Маленькие таблицы (< 10 GB) — оверхед без пользы.
• Нет очевидного ключа.
• Запросы без партиционного ключа — будут идти во все партиции.

2.9 Distributed PostgreSQL

Решение	Подход	Особенности
Citus	Sharding-extension для PG	Координатор + workers. Хорош для multi-tenant. Microsoft / Azure.
CockroachDB	Distributed SQL, PG-compatible	Raft-based, strong consistency. Не 100% PG, но близко.
YugabyteDB	PG-compatible, distributed	PG SQL поверх собственного storage (DocDB).
Aurora PostgreSQL	AWS managed	Shared storage layer (не sharding). До 128 TB.
Greenplum	OLAP, MPP	Аналитика, не для OLTP.

В России 2026: PostgresPro (форк), Postgres-XL, YDB как альтернатива.

2.10 Полезные системные view

-- Все текущие соединения и запросы
SELECT pid, usename, application_name, state, query_start, query
FROM pg_stat_activity
WHERE state != 'idle';

-- Замок какой запрос ждёт
SELECT blocked.pid AS blocked_pid, blocked.query AS blocked_query,
       blocking.pid AS blocking_pid, blocking.query AS blocking_query
FROM pg_stat_activity blocked
JOIN pg_stat_activity blocking ON blocking.pid = ANY(pg_blocking_pids(blocked.pid))
WHERE blocked.wait_event_type = 'Lock';

-- Top медленных запросов (требует pg_stat_statements)
SELECT query, calls, total_exec_time, mean_exec_time, rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC LIMIT 20;

-- Размеры таблиц и индексов
SELECT relname,
       pg_size_pretty(pg_total_relation_size(relid)) AS total,
       pg_size_pretty(pg_relation_size(relid)) AS table_size,
       pg_size_pretty(pg_indexes_size(relid)) AS index_size
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC LIMIT 20;

2.11 Полезные extensions

• pg_stat_statements — статистика по запросам (must-have в production).
• pgcrypto — функции шифрования, UUID.
• uuid-ossp — UUID generation (с PG 13+ есть встроенный gen_random_uuid()).
• ltree — иерархические данные (tree).
• hstore — key-value (использовать только если нет смысла переходить на JSONB).
• pg_trgm — trigram similarity (fuzzy search).
• pgvector (2026 hype!) — векторный поиск для AI/RAG.
• TimescaleDB — time-series extension.
• PostGIS — геопространственные данные.
• pg_partman — управление партициями.

---

3. Gotchas

3.1 ⚠️ MVCC bloat

Проблема: частые UPDATE приводят к раздутию таблицы и индексов. Дисковое использование растёт, а данных не больше.

Признаки:

• n_dead_tup в pg_stat_user_tables много.
• Размер таблицы растёт, хотя rows примерно постоянно.

Решение: настроить autovacuum агрессивнее, использовать HOT updates (когда обновляется не-индексируемая колонка — versioning внутри страницы).

3.2 ⚠️ idx_scan = 0 — индекс не используется

Проверить:

SELECT relname, indexrelname, idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
WHERE schemaname = 'public'
ORDER BY idx_scan ASC LIMIT 20;

Если idx_scan = 0 в течение долгого времени — индекс можно дропнуть (экономия места + ускорение записи).

3.3 ⚠️ Selectivity и индексы

Если индекс возвращает > 5-10% строк — планировщик может выбрать Seq Scan. Это правильно, не баг.

Если ты ожидаешь Index Scan и не получаешь — проверь:

ANALYZE table_name;  -- обновить статистику

3.4 ⚠️ LIKE 'prefix%' использует B-tree, но LIKE '%suffix' — нет

-- Работает по B-tree
SELECT * FROM users WHERE email LIKE 'admin%';

-- НЕ работает по B-tree (Seq Scan)
SELECT * FROM users WHERE email LIKE '%@gmail.com';

-- Для middle/end search — pg_trgm
CREATE EXTENSION pg_trgm;
CREATE INDEX idx_users_email_trgm ON users USING GIN (email gin_trgm_ops);

3.5 ⚠️ Lock на DDL

ALTER TABLE ... ADD COLUMN с DEFAULT (до PG 11) — переписывает всю таблицу под ACCESS EXCLUSIVE LOCK.

С PG 11+ для константных DEFAULT этого нет (только metadata change). Но:

• ADD COLUMN ... NOT NULL без DEFAULT — ошибка, если есть NULL’ы.
• ALTER COLUMN TYPE — обычно переписывает.
• ADD CONSTRAINT NOT NULL — берёт ACCESS EXCLUSIVE.

Production-safe pattern:

-- Безопасно:
ALTER TABLE orders ADD COLUMN tax NUMERIC DEFAULT 0 NOT NULL;  -- PG 11+

-- Опасно:
ALTER TABLE orders ALTER COLUMN total TYPE BIGINT;  -- переписывает

3.6 ⚠️ SELECT FOR UPDATE и deadlock

-- Tx 1
BEGIN;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
SELECT * FROM accounts WHERE id = 2 FOR UPDATE;

-- Tx 2 (одновременно)
BEGIN;
SELECT * FROM accounts WHERE id = 2 FOR UPDATE;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
-- DEADLOCK!

Решение: всегда брать locks в одном порядке (по id ASC, например).

3.7 ⚠️ Long-running transactions блокируют VACUUM

Транзакция, открытая час, не даёт VACUUM очистить dead tuples, которые были созданы после её старта. Bloat растёт.

-- Найти долгие транзакции
SELECT pid, now() - xact_start AS duration, query
FROM pg_stat_activity
WHERE state != 'idle' AND xact_start IS NOT NULL
ORDER BY duration DESC;

Решение: ограничить через idle_in_transaction_session_timeout, statement_timeout.

3.8 ⚠️ SERIAL и sequence gaps

SERIAL использует sequence. При ROLLBACK или INSERT ... ON CONFLICT DO NOTHING — sequence уже увеличена. Не полагаться на непрерывность ID.

С PG 10+ предпочтительнее GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY:

CREATE TABLE orders (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    ...
);

3.9 ⚠️ JSON vs JSONB

• JSON — хранится как текст, парсится при каждом чтении. Не используй.
• JSONB — binary, индексируемо, быстрее, дедупликация ключей.

-- ВСЕГДА JSONB
ALTER TABLE users ALTER COLUMN metadata TYPE JSONB USING metadata::JSONB;

3.10 ⚠️ Timezone

-- Плохо: TIMESTAMP (без TZ)
created_at TIMESTAMP

-- Хорошо: TIMESTAMPTZ
created_at TIMESTAMPTZ DEFAULT now()

TIMESTAMP теряет информацию о таймзоне — баг в распределённых системах. Всегда TIMESTAMPTZ.

3.11 ⚠️ Replication lag и read-your-writes

После записи на master, чтение с replica может не увидеть данные.

// Плохо:
db.Master.Exec(ctx, "INSERT INTO orders ...")
order, _ := db.Replica.QueryRow(ctx, "SELECT * FROM orders WHERE id = $1", id)  // может вернуть not found!

Решения:

1. После write читать с master.
2. Использовать pg_last_wal_replay_lsn() и ждать, пока replica догонит.
3. Использовать sync replication (но slow).

3.12 ⚠️ Транзакция в idle

BEGIN; SELECT ...; -- забыл COMMIT оставляет транзакцию открытой → блокирует VACUUM, держит ресурсы.

// Всегда defer rollback
tx, err := db.Begin(ctx)
if err != nil {
    return err
}
defer tx.Rollback(ctx)  // no-op если COMMIT уже был

// ... работа
if err := tx.Commit(ctx); err != nil {
    return err
}

3.13 ⚠️ count(*) на больших таблицах медленный

Из-за MVCC PostgreSQL не хранит точный count. COUNT(*) проходит таблицу (или индекс).

Альтернативы:

• Приблизительно: SELECT reltuples::BIGINT FROM pg_class WHERE relname = 'orders';
• Точно: кешировать count в отдельной таблице с триггерами.

3.14 ⚠️ IN (...) с большим списком

SELECT ... WHERE id IN (1, 2, ..., 10000) — медленный, парсится долго.

Лучше:

SELECT ... FROM tbl JOIN unnest($1::bigint[]) AS t(id) USING (id);

В pgx:

ids := []int64{1, 2, 3, ...}
rows, _ := pool.Query(ctx, "SELECT * FROM users WHERE id = ANY($1)", ids)

---

4. Production-практики

4.1 Базовый production-checklist

1. WAL archiving включён (archive_mode = on, archive_command).
2. Replication настроен (минимум 1 sync replica).
3. Backups — pg_basebackup или WAL-G/pgBackRest + регулярные тесты восстановления.
4. pg_stat_statements установлен.
5. Connection pooling через PgBouncer или встроенный в pgcat / приложение.
6. Monitoring: Prometheus + postgres_exporter, Grafana дашборды.
7. Slow query log: log_min_duration_statement = 1000 (запросы > 1 сек).
8. statement_timeout на сессию (например, 60 сек) — защита от runaway queries.
9. idle_in_transaction_session_timeout — выкидывать idle транзакции.
10. Autovacuum настроен (per-table для горячих таблиц).
11. High availability: Patroni / managed.

4.2 Tuning параметров (для 32 GB RAM)

shared_buffers = 8GB
effective_cache_size = 24GB
work_mem = 32MB                # на каждую операцию сортировки/хеша!
maintenance_work_mem = 1GB     # для VACUUM, CREATE INDEX
wal_buffers = 16MB
checkpoint_completion_target = 0.9
max_wal_size = 4GB
min_wal_size = 1GB
random_page_cost = 1.1         # SSD
effective_io_concurrency = 200 # SSD
max_connections = 200          # с PgBouncer держим низким

⚠️ work_mem × max_connections × parallel_workers = пик RAM. Поэтому work_mem = 32MB × 200 conn = 6.4 GB только на operations.

4.3 Миграции production-safe

Используйте инструменты:

• goose (Go) — простой
• golang-migrate/migrate — популярный
• atlas — declarative, schema diffs
• pressly/goose (Go) — простой и удобный

Принципы:

1. Каждая миграция должна быть обратимой (down).
2. DDL миграции отдельно от data миграций.
3. Большие изменения — многошаговые:
   • Add nullable column → backfill → set NOT NULL.
   • Add new column → write to both old and new → switch reads → drop old.
4. CREATE INDEX CONCURRENTLY для production.
5. ALTER TABLE — проверять, что не берёт ACCESS EXCLUSIVE.
6. Тестировать миграцию на копии prod (size).

4.4 Безопасный INSERT/UPDATE паттерн

// pgx + retry на serialization failure
import "github.com/jackc/pgx/v5"

func transferMoney(ctx context.Context, pool *pgxpool.Pool, from, to int64, amount int64) error {
    return pgx.BeginTxFunc(ctx, pool, pgx.TxOptions{
        IsoLevel: pgx.Serializable,
    }, func(tx pgx.Tx) error {
        // Lock in id ASC order
        first, second := from, to
        if first > second {
            first, second = second, first
        }

        var balance int64
        if err := tx.QueryRow(ctx,
            "SELECT balance FROM accounts WHERE id = $1 FOR UPDATE", first,
        ).Scan(&balance); err != nil {
            return err
        }
        if err := tx.QueryRow(ctx,
            "SELECT balance FROM accounts WHERE id = $1 FOR UPDATE", second,
        ).Scan(&balance); err != nil {
            return err
        }

        if _, err := tx.Exec(ctx,
            "UPDATE accounts SET balance = balance - $1 WHERE id = $2", amount, from,
        ); err != nil {
            return err
        }
        if _, err := tx.Exec(ctx,
            "UPDATE accounts SET balance = balance + $1 WHERE id = $2", amount, to,
        ); err != nil {
            return err
        }
        return nil
    })
}

4.5 Bulk operations

Для bulk-insert в Go:

// pgx COPY FROM — самый быстрый
copyCount, err := pool.CopyFrom(
    ctx,
    pgx.Identifier{"orders"},
    []string{"user_id", "total", "status"},
    pgx.CopyFromSlice(len(orders), func(i int) ([]interface{}, error) {
        return []interface{}{orders[i].UserID, orders[i].Total, orders[i].Status}, nil
    }),
)

⚠️ COPY в 10-100x быстрее INSERT. Используй для seed, миграций данных, ETL.

4.6 Observability

Critical metrics:

• TPS (transactions/sec)
• Active connections / waiting connections
• Cache hit ratio (blks_hit / (blks_hit + blks_read) > 0.99)
• Replication lag (bytes, time)
• Dead tuples ratio
• WAL generation rate

-- Cache hit ratio
SELECT sum(blks_hit) / sum(blks_hit + blks_read) AS cache_hit_ratio FROM pg_stat_database;

4.7 pg_repack для онлайн-rebuild

pg_repack --no-superuser-check -k -t orders mydb

• -k — без проверки superuser (для managed Postgres).
• -t orders — конкретная таблица.

Создаёт shadow-таблицу, копирует данные, применяет diff из triggers, swap.

4.8 Logical replication для zero-downtime upgrade

PG 9 → PG 16 без downtime:

1. Setup PG 16 as logical replica of PG 9.
2. Wait sync.
3. Switch application connection to new master.

4.9 Audit логирование

-- pgaudit extension
CREATE EXTENSION pgaudit;
ALTER SYSTEM SET pgaudit.log = 'WRITE, DDL';

4.10 Шифрование

• At rest: TDE (Transparent Data Encryption) — в managed (RDS, CloudSQL) или PostgresPro. В обычном PostgreSQL — на уровне filesystem (LUKS).
• In flight: TLS (ssl = on, sslmode=require в клиенте).
• Column-level: pgcrypto для PII.

---

5. Вопросы для собеседования

1. Чем PostgreSQL отличается от MySQL архитектурно? PG — process-per-connection (multi-process), MySQL — thread-per-connection. PG строже к стандарту SQL, поддерживает MVCC из коробки.

2. Что такое WAL и зачем он нужен? Write-Ahead Log. Все изменения сначала пишутся в WAL, потом в data files. Гарантирует durability и recovery.

3. Что такое MVCC? Как реализован в PostgreSQL? Multi-Version Concurrency Control. Каждая строка имеет xmin/xmax. UPDATE = INSERT новой версии + xmax старой. Читатели не блокируют писателей.

4. Что такое dead tuples и как с ними бороться? Старые версии строк после UPDATE/DELETE. VACUUM очищает их. autovacuum делает это автоматически.

5. В чём разница VACUUM и VACUUM FULL? VACUUM освобождает место в существующих страницах (онлайн). VACUUM FULL переписывает таблицу (ACCESS EXCLUSIVE LOCK — нельзя в production).

6. Что такое bloat? Раздутие таблицы или индекса из-за dead tuples / unused index entries.

7. Какие типы индексов есть в PostgreSQL? B-tree (default), Hash, GIN (массивы, JSONB, FTS), GiST (геометрия, ranges, FTS), BRIN (block-range, time series), SP-GiST.

8. Когда использовать GIN, а когда GiST? GIN — для search-heavy. GiST — для update-heavy и геометрии.

9. Что такое partial index? Индекс на подмножество строк (с WHERE clause). Меньше места и быстрее.

10. Что такое covering index? Индекс с дополнительными колонками через INCLUDE (...). Позволяет Index-Only Scan без перехода в heap.

11. Зачем CREATE INDEX CONCURRENTLY? Не блокирует записи в таблицу. Медленнее (2 прохода). Обязательно в production.

12. Чем EXPLAIN отличается от EXPLAIN ANALYZE? EXPLAIN показывает план без выполнения. EXPLAIN ANALYZE реально выполняет запрос и показывает actual time/rows.

13. Что показывает BUFFERS в EXPLAIN? Сколько страниц прочитано: shared hit (из кеша), shared read (с диска).

14. Как PostgreSQL выбирает между Index Scan и Seq Scan? На основе cost. Если предикат возвращает > 5-10% — обычно Seq Scan.

15. Какие типы JOIN поддерживает PG и когда какой используется? Nested Loop (малые наборы или с индексом), Hash Join (equi-join больших), Merge Join (если обе стороны отсортированы).

16. Что такое pg_stat_statements? Extension для статистики по запросам (количество вызовов, total/mean time). Must-have в production.

17. В чём разница transaction и session mode в PgBouncer? Session: соединение закреплено за backend на всю сессию. Transaction: после COMMIT возвращается в пул (больше клиентов, но ограничения по prepared statements, SET).

18. Что такое streaming replication? Physical replication через копирование WAL records на replica.

19. Чем logical replication отличается от physical? Logical шлёт изменения в виде INSERT/UPDATE/DELETE (можно реплицировать отдельные таблицы, между разными версиями). Physical — raw WAL.

20. Что такое Patroni? Tool для high availability PostgreSQL: автоматический failover, использует etcd/Consul.

21. Как настроить read replica в Go-приложении? Два пула pgxpool (master, replica). Routing: writes → master, reads → replica. Учитывать lag.

22. Когда нужен partitioning? Таблицы > 100 GB, очевидный partition key (time, tenant), необходимость drop старых данных.

23. Что такое partition pruning? Планировщик отбрасывает партиции, которые точно не содержат искомых данных, на основе предиката.

24. Чем отличается JSONB от JSON? JSON — text. JSONB — binary, indexable, faster reads. Всегда используй JSONB.

25. Как проиндексировать JSONB? CREATE INDEX ... USING GIN (col jsonb_path_ops) — для @> оператора.

26. Что такое CTE и когда его использовать? Common Table Expression (WITH name AS (...)). До PG 12 был optimization fence (всегда материализован). С PG 12+ — MATERIALIZED / NOT MATERIALIZED.

27. Что такое skip-locked? SELECT ... FOR UPDATE SKIP LOCKED — пропускает заблокированные строки. Полезно для распределённых очередей.

28. Как реализовать очередь на Postgres? Таблица jobs + SELECT FOR UPDATE SKIP LOCKED + worker’ы. Идиома для до-Kafka использования.

29. Что делать с replication lag? Мониторить через pg_stat_replication.replay_lag. Использовать sync replication для критичных данных. Для read-your-writes — читать с master после write.

30. Какие топовые distributed PostgreSQL варианты в 2026? Citus (sharding extension), CockroachDB (PG-compatible distributed SQL), YugabyteDB, AWS Aurora PostgreSQL, Yandex Managed PostgreSQL.

---

6. Practice

Задача 1: Найти и оптимизировать медленный запрос

1. Включить pg_stat_statements.
2. Найти top-5 медленных по mean_exec_time.
3. Сделать EXPLAIN ANALYZE.
4. Предложить индекс.
5. Сделать CREATE INDEX CONCURRENTLY.
6. Перепроверить план.

Задача 2: Реализовать очередь jobs на PG

Создать таблицу jobs(id, payload, status, locked_at, attempts). Worker берёт job через SELECT FOR UPDATE SKIP LOCKED LIMIT 1. Реализовать retry и dead-letter.

Задача 3: Migration с zero-downtime

Переименовать колонку created в created_at без downtime:

1. Add column created_at (NULL).
2. Backfill data.
3. Application пишет в обе.
4. Application читает из новой.
5. Drop старой.

Задача 4: Bulk insert 1M строк

Через pgx.CopyFrom. Замерить время. Сравнить с обычным INSERT.

Задача 5: Настроить partitioning

Таблица events партиционирована по created_at (RANGE, by month). Реализовать автосоздание партиций через pg_partman или cron.

Задача 6: Анализ bloat

Найти top-5 таблиц с самым большим dead_ratio. Запустить pg_repack на одной из них и проверить размер до/после.

Задача 7: Read replica routing

Реализовать DB struct с master/replica пулами. Логика: if context has “force-master” flag → master, else replica.

---

7. Источники

1. PostgreSQL Documentation — https://www.postgresql.org/docs/current/ (canonical, 2026 — PG 17 LTS).
2. PostgreSQL Internals by Egor Rogov — детальный разбор внутренностей (free book от PostgresPro).
3. The Art of PostgreSQL by Dimitri Fontaine — для разработчиков.
4. Use the Index, Luke — https://use-the-index-luke.com/ — индексы и performance.
5. PostgreSQL Wiki — Performance Tuning — https://wiki.postgresql.org/wiki/Performance_Optimization
6. Crunchy Data Blog — https://www.crunchydata.com/blog/ — глубокие статьи.
7. pgx documentation — https://github.com/jackc/pgx — Go driver.
8. High Performance PostgreSQL for Rails by Andrew Atkinson (2024) — глобально применимо.
9. Postgres Weekly newsletter — https://postgresweekly.com/
10. Habr / Postgres Pro — https://postgrespro.com/blog — на русском.