Полное руководство по SQL для администраторов PostgreSQL

Запрос карточки пациента требовал соединения 7 таблиц:

-- Исходный запрос: 45 секунд на пациенте с длинной историей
SELECT
    p.last_name, p.first_name, p.birth_date,
    v.visit_date, v.complaint,
    d.diagnosis_code, d.diagnosis_name,
    doc.last_name AS doctor_name, doc.specialization,
    pr.prescription_text, pr.dosage,
    dept.department_name,
    ins.insurance_number, ins.company_name
FROM patients p
    JOIN visits v ON v.patient_id = p.id
    JOIN diagnoses d ON d.visit_id = v.id
    JOIN doctors doc ON doc.id = v.doctor_id
    JOIN prescriptions pr ON pr.visit_id = v.id
    JOIN departments dept ON dept.id = doc.department_id
    JOIN insurance ins ON ins.patient_id = p.id
WHERE p.id = 12345
ORDER BY v.visit_date DESC;

Мы применили стратегическую денормализацию: добавили материализованное представление для часто запрашиваемых данных карточки:

-- Материализованное представление для быстрого доступа
CREATE MATERIALIZED VIEW mv_patient_card AS
SELECT
    p.id AS patient_id,
    p.last_name || ' ' || p.first_name AS patient_name,
    p.birth_date,
    v.id AS visit_id,
    v.visit_date,
    v.complaint,
    d.diagnosis_code,
    d.diagnosis_name,
    doc.last_name AS doctor_name,
    doc.specialization,
    dept.department_name,
    ins.insurance_number
FROM patients p
    JOIN visits v ON v.patient_id = p.id
    LEFT JOIN diagnoses d ON d.visit_id = v.id
    JOIN doctors doc ON doc.id = v.doctor_id
    JOIN departments dept ON dept.id = doc.department_id
    LEFT JOIN insurance ins ON ins.patient_id = p.id;

-- Индекс на материализованном представлении
CREATE UNIQUE INDEX idx_mv_patient_card
    ON mv_patient_card (patient_id, visit_id);

-- Автоматическое обновление каждые 5 минут
SELECT cron.schedule('refresh_patient_card',
    '*/5 * * * *',
    'REFRESH MATERIALIZED VIEW CONCURRENTLY mv_patient_card');

Время запроса карточки сократилось с 45 секунд до 120 мс.

Мы обернули все многошаговые операции в транзакции с savepoints для частичного отката:

-- Запись нового приёма пациента (атомарная операция)
BEGIN;

-- Создаём визит
INSERT INTO visits (patient_id, doctor_id, visit_date, complaint)
VALUES (12345, 67, NOW(), 'Головная боль, повышенное давление')
RETURNING id INTO visit_id;

SAVEPOINT after_visit;

-- Добавляем диагноз
INSERT INTO diagnoses (visit_id, diagnosis_code, diagnosis_name)
VALUES (visit_id, 'I10', 'Эссенциальная гипертензия');

SAVEPOINT after_diagnosis;

-- Добавляем назначение
INSERT INTO prescriptions (visit_id, prescription_text, dosage, duration_days)
VALUES (visit_id, 'Амлодипин', '5 мг, 1 раз в день', 30);

-- Обновляем статистику врача
UPDATE doctor_stats
SET visits_today = visits_today + 1,
    last_visit_at = NOW()
WHERE doctor_id = 67;

COMMIT;

-- При ошибке на любом этапе:
-- ROLLBACK TO SAVEPOINT after_visit;  -- откатить до визита
-- или
-- ROLLBACK;  -- откатить всё

При одновременной работе 3 000 пользователей критически важен правильный уровень изоляции. PostgreSQL поддерживает 4 уровня:

Важная особенность PostgreSQL: уровень Repeatable Read в PostgreSQL защищает от фантомных чтений (в отличие от стандарта SQL, где это не гарантируется). Это связано с реализацией MVCC (Multi-Version Concurrency Control).

Для отчётов «МедИнфо» мы использовали Repeatable Read, чтобы гарантировать консистентность данных во время длительного формирования:

-- Формирование ежедневного отчёта
BEGIN ISOLATION LEVEL REPEATABLE READ;

SELECT
    dept.department_name,
    COUNT(v.id) AS total_visits,
    COUNT(DISTINCT v.patient_id) AS unique_patients,
    AVG(EXTRACT(EPOCH FROM (v.end_time - v.visit_date))) AS avg_duration_sec
FROM visits v
    JOIN doctors doc ON doc.id = v.doctor_id
    JOIN departments dept ON dept.id = doc.department_id
WHERE v.visit_date >= CURRENT_DATE
GROUP BY dept.department_name
ORDER BY total_visits DESC;

COMMIT;

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

-- Правильный паттерн: блокируем в фиксированном порядке
BEGIN;

-- Шаг 1: Явная блокировка строки пациента
SELECT id FROM patients WHERE id = 12345 FOR UPDATE;

-- Шаг 2: Блокировка связанных визитов
SELECT id FROM visits WHERE patient_id = 12345 FOR UPDATE;

-- Шаг 3: Обновления в любом порядке (строки уже заблокированы)
UPDATE patients SET status = 'transferred', current_dept = 5 WHERE id = 12345;
UPDATE visits SET department_id = 5, notes = 'Перевод' WHERE patient_id = 12345;

COMMIT;

Для мониторинга блокировок мы настроили алерт:

-- Запрос для обнаружения активных блокировок
SELECT
    blocked_locks.pid AS blocked_pid,
    blocked_activity.usename AS blocked_user,
    blocking_locks.pid AS blocking_pid,
    blocking_activity.usename AS blocking_user,
    blocked_activity.query AS blocked_query,
    blocking_activity.query AS blocking_query,
    now() - blocked_activity.query_start AS blocked_duration
FROM pg_catalog.pg_locks blocked_locks
    JOIN pg_catalog.pg_stat_activity blocked_activity
        ON blocked_activity.pid = blocked_locks.pid
    JOIN pg_catalog.pg_locks blocking_locks
        ON blocking_locks.locktype = blocked_locks.locktype
        AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database
        AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
        AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
        AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
        AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
        AND blocking_locks.pid != blocked_locks.pid
    JOIN pg_catalog.pg_stat_activity blocking_activity
        ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;

После внедрения единого порядка блокировок количество deadlock-ов снизилось с 15-20 до 0 в день.

PostgreSQL поддерживает 6 типов индексов, каждый для своего сценария:

Для таблицы визитов мы создали набор оптимальных индексов:

-- B-tree для поиска по пациенту и дате
CREATE INDEX CONCURRENTLY idx_visits_patient_date
    ON visits (patient_id, visit_date DESC);

-- Частичный индекс для активных визитов (10% данных)
CREATE INDEX CONCURRENTLY idx_visits_active
    ON visits (doctor_id, visit_date)
    WHERE status = 'in_progress';

-- BRIN для таблицы аудита (250M записей, хронологически упорядочена)
CREATE INDEX CONCURRENTLY idx_audit_created
    ON audit_log USING brin (created_at)
    WITH (pages_per_range = 32);

-- GIN для JSONB-поля с дополнительными данными визита
CREATE INDEX CONCURRENTLY idx_visits_extra_data
    ON visits USING gin (extra_data jsonb_path_ops);

Важно: ключевое слово CONCURRENTLY создаёт индекс без блокировки таблицы — критично для production базы, которая должна оставаться доступной.

Мы обнаружили 34 неиспользуемых индекса, которые замедляли операции записи и занимали 12 ГБ дискового пространства:

-- Поиск неиспользуемых индексов
SELECT
    schemaname || '.' || relname AS table,
    indexrelname AS index,
    pg_size_pretty(pg_relation_size(i.indexrelid)) AS size,
    idx_scan AS times_used
FROM pg_stat_user_indexes ui
    JOIN pg_index i ON ui.indexrelid = i.indexrelid
WHERE idx_scan < 10  -- менее 10 использований
    AND NOT i.indisunique  -- не уникальный
    AND pg_relation_size(i.indexrelid) > 1048576  -- больше 1 МБ
ORDER BY pg_relation_size(i.indexrelid) DESC;

-- Удаление после подтверждения
DROP INDEX CONCURRENTLY idx_old_unused_index;

Удаление 34 неиспользуемых индексов ускорило INSERT-ы на 23% и освободило 12 ГБ дискового пространства.

Отчёт по эффективности врачей с ранжированием по отделениям:

-- CTE для подготовки данных
WITH doctor_metrics AS (
    SELECT
        doc.id AS doctor_id,
        doc.last_name,
        dept.department_name,
        COUNT(v.id) AS visit_count,
        AVG(EXTRACT(EPOCH FROM (v.end_time - v.visit_date)))/60 AS avg_duration_min,
        COUNT(DISTINCT v.patient_id) AS unique_patients
    FROM doctors doc
        JOIN visits v ON v.doctor_id = doc.id
        JOIN departments dept ON dept.id = doc.department_id
    WHERE v.visit_date >= date_trunc('month', CURRENT_DATE)
    GROUP BY doc.id, doc.last_name, dept.department_name
),
department_avg AS (
    SELECT
        department_name,
        AVG(visit_count) AS dept_avg_visits,
        AVG(avg_duration_min) AS dept_avg_duration
    FROM doctor_metrics
    GROUP BY department_name
)
-- Оконные функции для ранжирования
SELECT
    dm.last_name,
    dm.department_name,
    dm.visit_count,
    ROUND(dm.avg_duration_min::numeric, 1) AS avg_min,
    dm.unique_patients,
    RANK() OVER (
        PARTITION BY dm.department_name
        ORDER BY dm.visit_count DESC
    ) AS rank_in_dept,
    ROUND(
        (dm.visit_count - da.dept_avg_visits) / da.dept_avg_visits * 100
    , 1) AS pct_above_avg
FROM doctor_metrics dm
    JOIN department_avg da ON da.department_name = dm.department_name
ORDER BY dm.department_name, rank_in_dept;

Таблица visits с 85 миллионами записей — кандидат на партиционирование по дате:

-- Создание партиционированной таблицы
CREATE TABLE visits_partitioned (
    id BIGSERIAL,
    patient_id BIGINT NOT NULL,
    doctor_id INT NOT NULL,
    visit_date TIMESTAMPTZ NOT NULL,
    complaint TEXT,
    status VARCHAR(20) DEFAULT 'scheduled',
    end_time TIMESTAMPTZ,
    extra_data JSONB
) PARTITION BY RANGE (visit_date);

-- Автоматическое создание партиций по месяцам
CREATE TABLE visits_2026_01 PARTITION OF visits_partitioned
    FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');
CREATE TABLE visits_2026_02 PARTITION OF visits_partitioned
    FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');
CREATE TABLE visits_2026_03 PARTITION OF visits_partitioned
    FOR VALUES FROM ('2026-03-01') TO ('2026-04-01');

-- Индексы создаются для каждой партиции автоматически
CREATE INDEX ON visits_partitioned (patient_id, visit_date DESC);
CREATE INDEX ON visits_partitioned (doctor_id) WHERE status = 'in_progress';

Партиционирование дало двойную выгоду: запросы за текущий месяц сканируют только одну партицию (2-3 миллиона строк вместо 85 миллионов), а удаление старых данных — мгновенная операция DROP TABLE visits_2024_01 вместо длительного DELETE.