PostgreSQL

Prepared statements отделяют SQL-код от данных - параметры ($1, $2) передаются отдельно, делая SQL-инъекцию невозможной. PostgreSQL также кеширует план выполнения prepared statement, ускоряя повторные вызовы.

MVCC (Multi-Version Concurrency Control) - каждая транзакция видит свой снимок данных. Читатели не блокируют писателей и наоборот. При UPDATE создаётся новая версия строки, старая остаётся для текущих транзакций. VACUUM удаляет устаревшие версии.

WAL гарантирует durability (D в ACID): изменения сначала записываются в журнал, потом в таблицы. При сбое PostgreSQL восстанавливается из WAL. WAL также используется для репликации (streaming replication) и point-in-time recovery.

Read Committed - каждая команда в транзакции видит данные, зафиксированные до её начала. Phantom reads возможны. Repeatable Read защищает от phantom reads. Serializable - полная изоляция, но медленнее. PostgreSQL не реализует Read Uncommitted.

MVCC создаёт новые версии строк при UPDATE/DELETE. Старые версии (dead tuples) занимают место. VACUUM помечает их пространство для повторного использования. VACUUM FULL перезаписывает таблицу, но блокирует её.

B-tree - индекс по умолчанию. Поддерживает =, <, >, <=, >=, BETWEEN, IN, IS NULL. Hash - только =. GiST - геометрия, полнотекстовый поиск. GIN - массивы, JSONB. BRIN - большие таблицы с естественной сортировкой (по дате).

EXPLAIN показывает предполагаемый план. EXPLAIN ANALYZE реально выполняет запрос и показывает фактическое время, количество строк на каждом шаге. Важно: ANALYZE выполняет запрос, включая INSERT/UPDATE/DELETE. Для безопасности оборачивайте в транзакцию с ROLLBACK.

Создание соединения с PostgreSQL - дорогая операция (fork процесса, TLS, аутентификация). Пул (pgxpool в Go, PgBouncer как прокси) поддерживает готовые соединения и раздаёт их по запросу. Снижает латентность и ограничивает число одновременных соединений.

DELETE удаляет строки по одной, пишет каждую в WAL, можно с WHERE, срабатывают триггеры. TRUNCATE - мгновенное удаление всех строк, сбрасывает последовательности (RESTART IDENTITY), не вызывает row-level триггеры. Оба можно откатить внутри транзакции.

Синхронная: master ждёт подтверждения записи от реплики - данные не потеряются при падении master. Медленнее (ожидание сети). Асинхронная: master не ждёт - быстрее, но при падении последние записи могут потеряться (replication lag).

pg_stat_statements собирает: количество вызовов, суммарное/среднее время, строки для каждого запроса. Незаменимый инструмент оптимизации - показывает самые медленные и частые запросы. Стандарт для PostgreSQL в production.

PostgreSQL имеет deadlock detector, проверяющий граф ожидания. При цикле откатывает одну транзакцию с ошибкой 'deadlock detected'. Вторая продолжает. Параметр deadlock_timeout (по умолчанию 1 сек). Для предотвращения: единый порядок блокировки ресурсов.

JSON хранит текст как есть (с пробелами, порядком ключей). JSONB парсит и хранит бинарно - быстрее при чтении и фильтрации. JSONB поддерживает GIN-индексы для поиска по ключам/значениям. В 99% случаев используйте JSONB.

ON CONFLICT реализует upsert: если INSERT нарушает unique constraint - выполняется UPDATE. INSERT INTO users (email, name) VALUES ($1, $2) ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name. EXCLUDED - псевдотаблица с новыми значениями. DO NOTHING молча пропускает конфликт.

VIEW - виртуальная таблица, запрос выполняется каждый раз. MATERIALIZED VIEW сохраняет результат на диск - быстрое чтение, но данные устаревают. REFRESH MATERIALIZED VIEW обновляет. CONCURRENTLY - обновление без блокировки чтения. Полезно для тяжёлых аналитических запросов.

Seq Scan вместо Index Scan означает: 1) индекс не существует; 2) запрос возвращает >5-10% таблицы (sequential scan быстрее); 3) устаревшая статистика (ANALYZE обновит); 4) неподходящий тип индекса. Используйте EXPLAIN ANALYZE для фактического плана.

Partial index индексирует только строки, соответствующие условию: CREATE INDEX idx ON orders(status) WHERE status = 'pending'. Занимает меньше места и быстрее обновляется. Эффективен, когда запросы всегда фильтруют по определённому условию.

Покрывающий индекс содержит все столбцы запроса - PostgreSQL выполняет Index Only Scan без обращения к heap. CREATE INDEX idx ON orders(status) INCLUDE (total) - INCLUDE добавляет неключевые столбцы. Значительно ускоряет чтение на горячих запросах.

Transaction pooling: соединение возвращается в пул после каждой транзакции - несколько клиентов разделяют одно соединение. Session pooling: закреплено на всю сессию (наименее эффективно). Statement pooling: после каждого запроса (нет multi-statement транзакций).