🐘 Вопросы интервью по PostgreSQL

Ответ: PostgreSQL — это объектно-реляционная система управления базами данных с открытым исходным кодом, известная строгим соответствием стандарту SQL, расширяемостью и надёжностью.

• Полная поддержка ACID-транзакций
• Богатая система типов данных, включая JSON/JSONB, массивы, geometric-типы
• Расширяемость: можно добавлять собственные типы данных, функции, индексы и даже языки (PL/pgSQL, PL/Python)
• Многоверсионное управление конкурентным доступом (MVCC) вместо блокировок на чтение
• Поддержка сложных индексов (GIN, GiST, BRIN) и полнотекстового поиска

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

Ответ: ACID — это набор из четырёх свойств, которые гарантируют надёжность транзакций в базе данных.

• Atomicity (атомарность): Транзакция выполняется целиком либо не выполняется вовсе — PostgreSQL откатывает все изменения при ошибке
• Consistency (согласованность): База данных переходит из одного согласованного состояния в другое, соблюдая все ограничения (constraints, foreign keys)
• Isolation (изолированность): Параллельные транзакции не видят промежуточных изменений друг друга — обеспечивается через MVCC и уровни изоляции
• Durability (устойчивость): После коммита данные сохраняются даже при сбое — благодаря журналу упреждающей записи (WAL, Write-Ahead Log)

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;
                

Ответ: MVCC (Multiversion Concurrency Control) — механизм, при котором PostgreSQL не блокирует строки на чтение: вместо этого каждая транзакция видит свой собственный "снимок" данных на момент начала транзакции.

• При обновлении строки PostgreSQL не изменяет её на месте, а создаёт новую версию строки, помечая старую как устаревшую
• Читающие транзакции не блокируют пишущие, и наоборот — это значительно повышает параллелизм
• Устаревшие версии строк со временем накапливаются и должны быть очищены процессом VACUUM

Ответ: VACUUM — это процесс обслуживания, который удаляет "мёртвые" (устаревшие) версии строк, оставшиеся после UPDATE и DELETE из-за механизма MVCC, освобождая место для повторного использования.

VACUUM orders;              -- очистка таблицы orders
VACUUM ANALYZE orders;      -- очистка + обновление статистики планировщика
VACUUM FULL orders;         -- полная перезапись таблицы, требует блокировки
                

• Обычный VACUUM не блокирует таблицу и может выполняться в фоне (autovacuum делает это автоматически)
• VACUUM FULL физически сжимает таблицу, но требует эксклюзивной блокировки — используется редко
• Без регулярного VACUUM таблицы "раздуваются" (table bloat) и производительность запросов деградирует

Ответ: PostgreSQL поддерживает несколько типов индексов, оптимизированных под разные виды запросов.

CREATE INDEX idx_orders_customer ON orders (customer_id);
CREATE INDEX idx_products_tags ON products USING GIN (tags);
                

Ответ: EXPLAIN показывает план выполнения запроса — как планировщик собирается его выполнить (какие индексы использует, порядок соединения таблиц), не выполняя запрос. EXPLAIN ANALYZE реально выполняет запрос и добавляет к плану фактическое время выполнения каждого шага.

EXPLAIN ANALYZE
SELECT * FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC;
                

• Seq Scan в выводе означает полное сканирование таблицы — обычно сигнал о том, что нужен индекс
• Index Scan означает использование индекса
• Сравнение estimated rows (оценка планировщика) и actual rows (фактическое количество) помогает понять, устарела ли статистика таблицы

Ответ: Уровень изоляции определяет, насколько параллельные транзакции защищены друг от друга от определённых аномалий чтения.

BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- операции
COMMIT;
                

Ответ: PostgreSQL поддерживает два типа для хранения JSON: json хранит данные как текст и парсит при каждом обращении, а jsonb хранит их в бинарном разобранном виде, что быстрее для запросов и поддерживает индексацию.

CREATE TABLE events (
    id SERIAL PRIMARY KEY,
    payload JSONB
);

INSERT INTO events (payload)
VALUES ('{"type": "click", "user_id": 42}');

SELECT payload->>'type' AS event_type
FROM events
WHERE payload->>'user_id' = '42';

CREATE INDEX idx_events_payload ON events USING GIN (payload);
                

Ответ: Оконные функции выполняют вычисления над набором строк ("окном"), связанных с текущей строкой, но, в отличие от GROUP BY, не сворачивают результат в одну строку — каждая исходная строка остаётся в выводе.

SELECT
    employee_id,
    department,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rank_in_dept,
    RANK() OVER (ORDER BY salary DESC) AS overall_rank
FROM employees;
                

• PARTITION BY делит строки на группы, аналогично GROUP BY, но без схлопывания строк
• ROW_NUMBER() присваивает уникальный порядковый номер, RANK() — с пропусками при равных значениях
• Удобны для задач вроде "топ-N в каждой категории", скользящих сумм, сравнения строки с соседними

Ответ: PostgreSQL использует потоковую репликацию (streaming replication), основанную на журнале упреждающей записи (WAL): все изменения данных сначала записываются в WAL, и именно этот поток передаётся на реплики.

• Primary (основной сервер) непрерывно отправляет записи WAL репликам (standby)
• Standby-серверы применяют полученные записи WAL, оставаясь синхронизированными с primary
• Репликация может быть асинхронной (быстрее, но возможна потеря последних транзакций при сбое) или синхронной (primary ждёт подтверждения от реплики, что снижает риск потери данных, но увеличивает задержку)
• Standby-серверы обычно можно использовать для запросов на чтение (hot standby), разгружая primary

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

• Запросы с условием по ключу партиционирования сканируют только нужные партиции (partition pruning), а не всю таблицу
• Удаление старых данных превращается в быстрое удаление партиции вместо медленного DELETE по условию
• Упрощает обслуживание: VACUUM и переиндексация могут выполняться по отдельным партициям

CREATE TABLE orders (
    id SERIAL,
    created_at DATE NOT NULL,
    amount NUMERIC
) PARTITION BY RANGE (created_at);

CREATE TABLE orders_2024 PARTITION OF orders
    FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

CREATE TABLE orders_2025 PARTITION OF orders
    FOR VALUES FROM ('2025-01-01') TO ('2026-01-01');