PostgreSQL для бэкенд-разработчиков: 7 паттернов, которые я использую каждый день

EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT * FROM orders
WHERE user_id = 12345
  AND status = 'pending'
ORDER BY created_at DESC
LIMIT 10;

-- В postgresql.conf
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = all
 
-- Рестарт PostgreSQL, затем:
CREATE EXTENSION pg_stat_statements;

SELECT
    substring(query, 1, 100) as short_query,
    calls,
    total_exec_time,
    mean_exec_time,
    max_exec_time,
    stddev_exec_time
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

SELECT
    substring(query, 1, 100) as short_query,
    calls,
    total_exec_time,
    mean_exec_time,
    (total_exec_time / sum(total_exec_time) OVER ()) * 100 AS percent_total
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

SELECT
    sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) * 100 AS cache_hit_ratio
FROM pg_statio_user_tables;

CREATE TABLE products (
    id BIGSERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    category TEXT NOT NULL,
    price NUMERIC(10,2) NOT NULL,
 
    -- Гибкие атрибуты в JSONB
    attributes JSONB NOT NULL DEFAULT '{}',
 
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
 
-- GIN индекс для быстрого поиска по JSONB
CREATE INDEX idx_products_attributes ON products USING GIN (attributes);
 
-- Индекс для конкретного поля (ещё быстрее)
CREATE INDEX idx_products_color ON products ((attributes->>'color'));

-- Вставка продукта с атрибутами
INSERT INTO products (name, category, price, attributes)
VALUES (
    'iPhone 15 Pro',
    'electronics',
    999.99,
    '{
        "brand": "Apple",
        "storage": "256GB",
        "color": "Natural Titanium",
        "screen": "6.1 inch",
        "processor": "A17 Pro"
    }'::jsonb
);
 
-- Поиск всех чёрных футболок размера M
SELECT id, name, price, attributes
FROM products
WHERE category = 'clothing'
  AND attributes @> '{"color": "black", "size": "M"}';
 
-- Обновление конкретного атрибута (не перезаписывая остальные)
UPDATE products
SET attributes = attributes || '{"discount": 15}'::jsonb
WHERE id = 42;
 
-- Удаление атрибута
UPDATE products
SET attributes = attributes - 'discount'
WHERE id = 42;
 
-- Поиск по вложенным полям
SELECT name, attributes->'specs'->>'processor' as cpu
FROM products
WHERE attributes->'specs'->>'processor' LIKE '%A17%';

-- ❌ МЕДЛЕННО: Поиск через ->> (не использует GIN индекс)
SELECT * FROM products
WHERE attributes->>'color' = 'red';
 
-- ✅ БЫСТРО: Поиск через @> (использует GIN индекс)
SELECT * FROM products
WHERE attributes @> '{"color": "red"}';

-- ❌ МЕДЛЕННО: B-tree индекс на JSONB колонке (почти бесполезен)
CREATE INDEX idx_bad ON products (attributes);
 
-- ✅ БЫСТРО: GIN индекс для поиска по содержимому
CREATE INDEX idx_good ON products USING GIN (attributes);
 
-- ✅ ЕЩЁ БЫСТРЕЕ: Индекс на конкретное поле
CREATE INDEX idx_best ON products ((attributes->>'color'));

-- ❌ ОЧЕНЬ МЕДЛЕННО: SUM по JSONB полю
SELECT SUM((attributes->>'price')::numeric) FROM products;
-- Не использует индексы, приводит тип для каждой строки
 
-- ✅ РЕШЕНИЕ: Вынести критичные поля в отдельные колонки
ALTER TABLE products ADD COLUMN price NUMERIC(10,2);
UPDATE products SET price = (attributes->>'price')::numeric;
-- Теперь SUM(price) работает быстро

-- ❌ ПЛОХО: Обновление одного поля перезаписывает весь JSONB
UPDATE products
SET attributes = attributes || '{"views": 1001}'::jsonb
WHERE id = 123;
-- При каждом UPDATE создаётся новая версия ВСЕГО JSONB объекта
 
-- ✅ РЕШЕНИЕ: Счётчики и часто меняющиеся поля — в отдельные колонки
ALTER TABLE products ADD COLUMN views INTEGER DEFAULT 0;
UPDATE products SET views = views + 1 WHERE id = 123;
-- UPDATE только одного INT — в 10 раз быстрее

-- Это ТРИ РАЗНЫЕ вещи:
INSERT INTO products (attributes) VALUES (NULL);              -- NULL
INSERT INTO products (attributes) VALUES ('{}');              -- Пустой объект
INSERT INTO products (attributes) VALUES ('{"color": null}'); -- Ключ с null
 
-- Поиск работает по-разному:
WHERE attributes IS NULL               -- Только первый
WHERE attributes = '{}'                -- Только второй
WHERE attributes->>'color' IS NULL     -- Второй и третий
WHERE attributes ? 'color'             -- Только третий

-- Индекс на всю таблицу (95% мусора)
CREATE INDEX idx_orders_status ON orders (status);

-- Partial index только для активных заказов (5% от таблицы)
CREATE INDEX idx_orders_active
ON orders (created_at, user_id)
WHERE status IN ('pending', 'processing');

-- Индекс только для неудалённых записей (soft delete)
CREATE INDEX idx_users_active
ON users (email)
WHERE deleted_at IS NULL;
 
-- Индекс только для будущих событий
CREATE INDEX idx_events_upcoming
ON events (start_time)
WHERE start_time > NOW();
 
-- Индекс только для неоплаченных счетов
CREATE INDEX idx_invoices_unpaid
ON invoices (due_date, customer_id)
WHERE paid_at IS NULL;
 
-- Композитный partial index для сложных условий
CREATE INDEX idx_orders_priority
ON orders (created_at DESC)
WHERE status = 'processing'
  AND priority = 'high'
  AND assigned_to IS NULL;

-- PostgreSQL автоматически использует partial index
EXPLAIN ANALYZE
SELECT * FROM orders
WHERE status = 'pending'
  AND user_id = 12345
ORDER BY created_at DESC
LIMIT 10;
 
-- Index Scan using idx_orders_active on orders
-- Buffers: shared hit=42

-- Было
CREATE INDEX idx_shipments_status ON shipments (status);
-- Размер: 40GB
 
-- Стало
CREATE INDEX idx_shipments_active
ON shipments (status, estimated_delivery)
WHERE status IN ('pending', 'in_transit', 'out_for_delivery');
-- Размер: 2GB

SELECT * FROM users
WHERE LOWER(first_name || ' ' || last_name) LIKE '%john doe%';
-- Медленно, индекс не используется

-- Куча кода для триггера
-- Легко забыть обновить
-- Ещё один источник багов

CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    first_name TEXT NOT NULL,
    last_name TEXT NOT NULL,
    email TEXT NOT NULL UNIQUE,
 
    -- Generated column (автоматически вычисляется)
    full_name TEXT GENERATED ALWAYS AS (
        first_name || ' ' || last_name
    ) STORED,
 
    -- Можно индексировать!
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
 
-- Индекс для быстрого поиска по полному имени
CREATE INDEX idx_users_full_name ON users USING GIN (
    to_tsvector('english', full_name)
);

-- Полнотекстовый поиск работает мгновенно
SELECT id, full_name, email
FROM users
WHERE to_tsvector('english', full_name) @@ to_tsquery('english', 'john & doe');
 
-- Generated column обновляется автоматически
UPDATE users
SET first_name = 'Jane'
WHERE id = 42;
-- full_name автоматически станет "Jane Doe"

-- Вычисление возраста из даты рождения
CREATE TABLE employees (
    id BIGSERIAL PRIMARY KEY,
    birth_date DATE NOT NULL,
 
    age INTEGER GENERATED ALWAYS AS (
        EXTRACT(YEAR FROM AGE(birth_date))
    ) STORED
);
 
-- Автоматическое вычисление total для заказа
CREATE TABLE order_items (
    id BIGSERIAL PRIMARY KEY,
    order_id BIGINT NOT NULL,
    quantity INTEGER NOT NULL CHECK (quantity > 0),
    price NUMERIC(10,2) NOT NULL CHECK (price >= 0),
 
    total NUMERIC(10,2) GENERATED ALWAYS AS (
        quantity * price
    ) STORED
);
 
-- URL slug из заголовка
CREATE TABLE articles (
    id BIGSERIAL PRIMARY KEY,
    title TEXT NOT NULL,
 
    slug TEXT GENERATED ALWAYS AS (
        LOWER(
            REGEXP_REPLACE(
                REGEXP_REPLACE(title, '[^a-zA-Z0-9\s-]', '', 'g'),
                '\s+', '-', 'g'
            )
        )
    ) STORED UNIQUE
);
 
-- JSON поле для поиска (из нескольких полей)
CREATE TABLE products (
    id BIGSERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    description TEXT,
    category TEXT NOT NULL,
 
    search_vector tsvector GENERATED ALWAYS AS (
        to_tsvector('english',
            COALESCE(name, '') || ' ' ||
            COALESCE(description, '') || ' ' ||
            COALESCE(category, '')
        )
    ) STORED
);
 
CREATE INDEX idx_products_search ON products USING GIN (search_vector);

ALTER TABLE articles
ADD COLUMN search_vector tsvector
GENERATED ALWAYS AS (
    setweight(to_tsvector('russian', COALESCE(title, '')), 'A') ||
    setweight(to_tsvector('russian', COALESCE(body, '')), 'B') ||
    setweight(to_tsvector('russian', COALESCE(array_to_string(tags, ' '), '')), 'C')
) STORED;
 
CREATE INDEX idx_articles_search ON articles USING GIN (search_vector);

-- Включаем RLS для таблицы
CREATE TABLE documents (
    id BIGSERIAL PRIMARY KEY,
    company_id BIGINT NOT NULL,
    title TEXT NOT NULL,
    content TEXT,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
 
-- Включаем RLS
ALTER TABLE documents ENABLE ROW LEVEL SECURITY;
 
-- Политика: пользователь видит только документы своей компании
CREATE POLICY documents_isolation_policy ON documents
    USING (company_id = current_setting('app.current_company_id')::BIGINT);

# Python (psycopg2/psycopg3)
def get_db_connection(user):
    conn = psycopg2.connect(...)
    cursor = conn.cursor()
 
    # Устанавливаем company_id пользователя в сессии
    cursor.execute(
        "SET app.current_company_id = %s",
        (user.company_id,)
    )
 
    return conn
 
# Теперь все запросы автоматически фильтруются!
# Забыть WHERE company_id = ... НЕВОЗМОЖНО
cursor.execute("SELECT * FROM documents WHERE title LIKE %s", ('%report%',))
# PostgreSQL автоматически добавит: AND company_id = current_company_id

-- Политика для SELECT (чтение)
CREATE POLICY documents_select_policy ON documents
    FOR SELECT
    USING (
        company_id = current_setting('app.current_company_id')::BIGINT
    );
 
-- Политика для INSERT (только в свою компанию)
CREATE POLICY documents_insert_policy ON documents
    FOR INSERT
    WITH CHECK (
        company_id = current_setting('app.current_company_id')::BIGINT
    );
 
-- Политика для UPDATE (редактировать только свои документы + только авторы)
CREATE POLICY documents_update_policy ON documents
    FOR UPDATE
    USING (
        company_id = current_setting('app.current_company_id')::BIGINT
        AND created_by = current_setting('app.current_user_id')::BIGINT
    );
 
-- Политика для DELETE (только админы компании могут удалять)
CREATE POLICY documents_delete_policy ON documents
    FOR DELETE
    USING (
        company_id = current_setting('app.current_company_id')::BIGINT
        AND current_setting('app.user_role') = 'admin'
    );
 
-- Суперпользователи обходят RLS
ALTER TABLE documents FORCE ROW LEVEL SECURITY;
-- Теперь даже SUPERUSER не обойдёт политики (кроме таблиц без политик)

-- Все таблицы с данными клиентов
ALTER TABLE contacts ENABLE ROW LEVEL SECURITY;
ALTER TABLE deals ENABLE ROW LEVEL SECURITY;
ALTER TABLE tasks ENABLE ROW LEVEL SECURITY;
 
-- Единая политика для всех таблиц
CREATE POLICY tenant_isolation_policy ON contacts
    USING (company_id = current_setting('app.current_company_id')::BIGINT);
 
-- То же для остальных таблиц

-- ❌ ОШИБКА: current_setting вернёт NULL или ERROR
SELECT * FROM documents;
-- ERROR: unrecognized configuration parameter "app.current_company_id"
 
-- ✅ ПРАВИЛЬНО: Всегда устанавливайте параметр в начале сессии
SET app.current_company_id = 42;
-- Теперь RLS работает

-- SUPERUSER видит ВСЁ, игнорируя политики RLS
-- Даже если вы подключились как postgres
 
-- ✅ РЕШЕНИЕ: FORCE ROW LEVEL SECURITY
ALTER TABLE documents FORCE ROW LEVEL SECURITY;
-- Теперь даже SUPERUSER не обойдёт политики

-- У вас есть политика:
CREATE POLICY select_policy ON documents FOR SELECT
    USING (company_id = current_setting('app.current_company_id')::BIGINT);
 
-- ❌ INSERT не работает!
INSERT INTO documents (company_id, title) VALUES (42, 'Test');
-- ERROR: new row violates row-level security policy
 
-- ✅ РЕШЕНИЕ: Нужна отдельная политика для INSERT
CREATE POLICY insert_policy ON documents FOR INSERT
    WITH CHECK (company_id = current_setting('app.current_company_id')::BIGINT);

-- У вас политика на company_id
CREATE POLICY isolation ON documents
    USING (company_id = current_setting('app.current_company_id')::BIGINT);
 
-- ❌ МЕДЛЕННО: Нет индекса на company_id
-- PostgreSQL делает Seq Scan + фильтрация
 
-- ✅ БЫСТРО: Индекс на company_id
CREATE INDEX idx_documents_company ON documents (company_id);
-- Теперь Index Scan

-- У вас партиционированная таблица с RLS
CREATE TABLE documents (...) PARTITION BY RANGE (created_at);
 
-- ❌ RLS НЕ РАБОТАЕТ на прямых запросах к партициям!
SELECT * FROM documents_2024_01;
-- Вернёт ВСЕ строки, игнорируя политики
 
-- ✅ ПРАВИЛЬНО: Запрашивайте родительскую таблицу
SELECT * FROM documents;
-- RLS применяется, затем partition pruning

-- Попытка получить эксклюзивную блокировку
SELECT pg_try_advisory_lock(12345);
-- Вернёт true, если блокировка получена
-- Вернёт false, если уже занята
 
-- Блокировка с ожиданием
SELECT pg_advisory_lock(12345);
-- Ждёт, пока блокировка освободится
 
-- Освобождение блокировки
SELECT pg_advisory_unlock(12345);
 
-- Автоматическое освобождение при закрытии соединения
-- (не нужно вручную unlock, если процесс упал)

import psycopg2
import hashlib
 
def process_payment_exclusively(payment_id):
    """
    Обрабатываем платёж только если никто другой его не обрабатывает
    """
    conn = psycopg2.connect(...)
    cursor = conn.cursor()
 
    # Генерируем числовой ID из payment_id (advisory lock принимает bigint)
    lock_id = int(hashlib.md5(f"payment:{payment_id}".encode()).hexdigest()[:15], 16)
 
    # Пытаемся получить блокировку
    cursor.execute("SELECT pg_try_advisory_lock(%s)", (lock_id,))
    acquired = cursor.fetchone()[0]
 
    if not acquired:
        print(f"Платёж {payment_id} уже обрабатывается другим worker'ом")
        return False
 
    try:
        # Обрабатываем платёж
        process_payment_logic(payment_id)
        conn.commit()
        return True
    finally:
        # Освобождаем блокировку
        cursor.execute("SELECT pg_advisory_unlock(%s)", (lock_id,))
        conn.close()

-- Блокировка по двум числам (вместо одного bigint)
SELECT pg_try_advisory_lock(user_id, task_id);
 
-- Shared lock (несколько процессов могут читать)
SELECT pg_try_advisory_lock_shared(12345);
 
-- Transaction-level lock (освобождается при COMMIT/ROLLBACK)
SELECT pg_try_advisory_xact_lock(12345);
 
-- Проверка существующих блокировок
SELECT
    locktype,
    objid,
    mode,
    granted,
    pid,
    pg_blocking_pids(pid) as blocking_pids
FROM pg_locks
WHERE locktype = 'advisory';

from celery import Task
import hashlib
 
class ExclusiveTask(Task):
    def __call__(self, *args, **kwargs):
        # Генерируем lock_id из имени задачи + аргументов
        task_hash = hashlib.md5(
            f"{self.name}:{args}:{kwargs}".encode()
        ).hexdigest()[:15]
        lock_id = int(task_hash, 16)
 
        with self.app.pool.acquire() as conn:
            cursor = conn.cursor()
            cursor.execute("SELECT pg_try_advisory_lock(%s)", (lock_id,))
 
            if not cursor.fetchone()[0]:
                self.app.log.warning(f"Задача {self.name} уже выполняется")
                return None
 
            try:
                return self.run(*args, **kwargs)
            finally:
                cursor.execute("SELECT pg_advisory_unlock(%s)", (lock_id,))
 
@app.task(base=ExclusiveTask)
def send_email(user_id, template):
    # Гарантированно выполнится только один раз
    pass

-- Создаём materialized view с агрегированными данными
CREATE MATERIALIZED VIEW monthly_revenue AS
SELECT
    DATE_TRUNC('month', created_at) as month,
    category,
    COUNT(*) as orders_count,
    SUM(total) as total_revenue,
    AVG(total) as avg_order_value,
    COUNT(DISTINCT user_id) as unique_customers
FROM orders
WHERE status = 'completed'
GROUP BY DATE_TRUNC('month', created_at), category
ORDER BY month DESC, total_revenue DESC;
 
-- Индекс для быстрого доступа
CREATE INDEX idx_monthly_revenue_month ON monthly_revenue (month);
 
-- Обновление данных (можно через cron)
REFRESH MATERIALIZED VIEW monthly_revenue;
 
-- Concurrently (без блокировки чтения)
REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_revenue;

-- Мгновенный запрос вместо 30 секунд
SELECT * FROM monthly_revenue
WHERE month >= '2024-01-01'
ORDER BY total_revenue DESC;
 
-- Execution time: 2ms (было 30s)

-- Materialized view с JOIN'ами
CREATE MATERIALIZED VIEW user_activity_summary AS
SELECT
    u.id,
    u.email,
    u.created_at as registered_at,
    COUNT(DISTINCT o.id) as total_orders,
    SUM(o.total) as lifetime_value,
    MAX(o.created_at) as last_order_date,
    COUNT(DISTINCT DATE(o.created_at)) as active_days
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id;
 
-- Для CONCURRENTLY нужен UNIQUE индекс
CREATE UNIQUE INDEX idx_user_activity_id
ON user_activity_summary (id);
 
-- Теперь можно обновлять без блокировки
REFRESH MATERIALIZED VIEW CONCURRENTLY user_activity_summary;

-- Функция для обновления
CREATE OR REPLACE FUNCTION refresh_all_matviews()
RETURNS void AS $$
BEGIN
    REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_revenue;
    REFRESH MATERIALIZED VIEW CONCURRENTLY user_activity_summary;
    RAISE NOTICE 'Materialized views refreshed at %', NOW();
END;
$$ LANGUAGE plpgsql;
 
-- Расписание через pg_cron (расширение)
-- Каждый час в :05 минут
SELECT cron.schedule('refresh-matviews', '5 * * * *', 'SELECT refresh_all_matviews()');
 
-- Или через внешний cron
-- 5 * * * * psql -d mydb -c "REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_revenue"

-- 1. Materialized view для всех метрик
CREATE MATERIALIZED VIEW dashboard_metrics AS
SELECT
    'total_users'::text as metric,
    COUNT(*)::bigint as value,
    NULL::jsonb as details
FROM users
UNION ALL
SELECT
    'active_users_30d',
    COUNT(DISTINCT user_id),
    NULL
FROM events
WHERE created_at > NOW() - INTERVAL '30 days'
UNION ALL
SELECT
    'revenue_today',
    SUM(amount)::bigint,
    jsonb_build_object(
        'count', COUNT(*),
        'avg', AVG(amount)
    )
FROM payments
WHERE DATE(created_at) = CURRENT_DATE
-- ... ещё 9 метрик
;
 
-- 2. Обновление каждые 5 минут
SELECT cron.schedule(
    'refresh-dashboard',
    '*/5 * * * *',
    'REFRESH MATERIALIZED VIEW CONCURRENTLY dashboard_metrics'
);

-- Родительская таблица (партиционированная)
CREATE TABLE events (
    id BIGSERIAL,
    user_id BIGINT NOT NULL,
    event_type TEXT NOT NULL,
    payload JSONB,
    created_at TIMESTAMPTZ NOT NULL
) PARTITION BY RANGE (created_at);
 
-- Партиции по месяцам
CREATE TABLE events_2024_01 PARTITION OF events
    FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');
 
CREATE TABLE events_2024_02 PARTITION OF events
    FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');
 
CREATE TABLE events_2024_03 PARTITION OF events
    FOR VALUES FROM ('2024-03-01') TO ('2024-04-01');
 
-- Индексы создаются для каждой партиции
CREATE INDEX idx_events_user_id ON events (user_id);
CREATE INDEX idx_events_type ON events (event_type);
 
-- PostgreSQL автоматически создаст:
-- idx_events_user_id_2024_01, idx_events_user_id_2024_02, ...

-- Запросы работают прозрачно
INSERT INTO events (user_id, event_type, payload, created_at)
VALUES (12345, 'page_view', '{"url": "/pricing"}', NOW());
-- PostgreSQL автоматически положит в правильную партицию
 
-- Partition Pruning (автоматическая оптимизация)
EXPLAIN ANALYZE
SELECT * FROM events
WHERE created_at >= '2024-03-01'
  AND created_at < '2024-04-01';
 
-- Seq Scan on events_2024_03
-- Прочитает ТОЛЬКО нужную партицию (не все 400GB!)

-- Функция для создания партиций на будущее
CREATE OR REPLACE FUNCTION create_monthly_partitions(
    table_name TEXT,
    start_date DATE,
    end_date DATE
)
RETURNS void AS $$
DECLARE
    partition_date DATE := start_date;
    partition_name TEXT;
    start_range TEXT;
    end_range TEXT;
BEGIN
    WHILE partition_date < end_date LOOP
        partition_name := table_name || '_' || TO_CHAR(partition_date, 'YYYY_MM');
        start_range := TO_CHAR(partition_date, 'YYYY-MM-DD');
        end_range := TO_CHAR(partition_date + INTERVAL '1 month', 'YYYY-MM-DD');
 
        EXECUTE format(
            'CREATE TABLE IF NOT EXISTS %I PARTITION OF %I FOR VALUES FROM (%L) TO (%L)',
            partition_name,
            table_name,
            start_range,
            end_range
        );
 
        RAISE NOTICE 'Создана партиция: %', partition_name;
        partition_date := partition_date + INTERVAL '1 month';
    END LOOP;
END;
$$ LANGUAGE plpgsql;
 
-- Создаём партиции на год вперёд
SELECT create_monthly_partitions('events', '2025-01-01', '2026-01-01');

-- Отключаем старую партицию от родительской таблицы
ALTER TABLE events DETACH PARTITION events_2022_01;
 
-- Теперь это обычная таблица, можно:
-- 1. Сделать pg_dump и удалить
pg_dump -t events_2022_01 mydb > events_2022_01.sql
DROP TABLE events_2022_01;
 
-- 2. Переместить в отдельную схему (архив)
CREATE SCHEMA archive;
ALTER TABLE events_2022_01 SET SCHEMA archive;
 
-- 3. Переместить в другую базу данных

CREATE TABLE products (
    id BIGSERIAL,
    name TEXT NOT NULL,
    category TEXT NOT NULL,
    price NUMERIC(10,2)
) PARTITION BY LIST (category);
 
CREATE TABLE products_electronics PARTITION OF products
    FOR VALUES IN ('laptop', 'phone', 'tablet');
 
CREATE TABLE products_clothing PARTITION OF products
    FOR VALUES IN ('shirt', 'pants', 'shoes');
 
CREATE TABLE products_other PARTITION OF products DEFAULT;
-- DEFAULT партиция для всех остальных значений

-- Полезно когда нет естественного ключа партиционирования
CREATE TABLE user_sessions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id BIGINT NOT NULL,
    data JSONB
) PARTITION BY HASH (user_id);
 
-- 4 партиции (hash mod 4)
CREATE TABLE user_sessions_0 PARTITION OF user_sessions
    FOR VALUES WITH (MODULUS 4, REMAINDER 0);
 
CREATE TABLE user_sessions_1 PARTITION OF user_sessions
    FOR VALUES WITH (MODULUS 4, REMAINDER 1);
 
CREATE TABLE user_sessions_2 PARTITION OF user_sessions
    FOR VALUES WITH (MODULUS 4, REMAINDER 2);
 
CREATE TABLE user_sessions_3 PARTITION OF user_sessions
    FOR VALUES WITH (MODULUS 4, REMAINDER 3);

-- 1. Создали новую партиционированную таблицу
CREATE TABLE sensor_readings_new (
    id BIGSERIAL,
    sensor_id BIGINT NOT NULL,
    value NUMERIC,
    recorded_at TIMESTAMPTZ NOT NULL
) PARTITION BY RANGE (recorded_at);
 
-- 2. Создали партиции по месяцам (за 3 года)
SELECT create_monthly_partitions('sensor_readings_new', '2021-01-01', '2024-01-01');
 
-- 3. Миграция данных (по частям, чтобы не убить БД)
-- Скрипт выполнялся 3 дня
DO $$
DECLARE
    start_date DATE := '2021-01-01';
    end_date DATE;
BEGIN
    WHILE start_date < '2024-01-01' LOOP
        end_date := start_date + INTERVAL '1 month';
 
        RAISE NOTICE 'Мигрируем данные за %', start_date;
 
        INSERT INTO sensor_readings_new
        SELECT * FROM sensor_readings
        WHERE recorded_at >= start_date AND recorded_at < end_date;
 
        COMMIT;
        PERFORM pg_sleep(60); -- Пауза 1 минута между батчами
 
        start_date := end_date;
    END LOOP;
END $$;
 
-- 4. Переключили таблицы
BEGIN;
ALTER TABLE sensor_readings RENAME TO sensor_readings_old;
ALTER TABLE sensor_readings_new RENAME TO sensor_readings;
COMMIT;
 
-- 5. Удалили старые данные (старше 1 года)
DROP TABLE sensor_readings_old_2021_01;
DROP TABLE sensor_readings_old_2021_02;
-- ...

-- ❌ НЕПРАВИЛЬНО: Индекс только на родительской таблице
CREATE INDEX ONLY idx_events_user_id ON events (user_id);
-- Создаст индекс только на parent, но не на партициях!
 
-- ✅ ПРАВИЛЬНО: Индекс распространяется на все партиции
CREATE INDEX idx_events_user_id ON events (user_id);
-- PostgreSQL автоматически создаст индексы на всех существующих и будущих партициях

-- У вас есть партиции за 2024-2025 год
-- Кто-то пытается вставить запись за 2026 год:
INSERT INTO events (user_id, event_type, created_at)
VALUES (123, 'click', '2026-01-01');
-- ERROR: no partition of relation "events" found for row
 
-- ✅ Решение 1: DEFAULT партиция
CREATE TABLE events_default PARTITION OF events DEFAULT;
-- Все записи, не попадающие в явные партиции, попадут сюда
 
-- ✅ Решение 2: Триггер для автоматического создания партиций
-- Используйте pg_partman (расширение)

-- ✅ FK ИЗ партиционированной таблицы — работает
CREATE TABLE events (
    id BIGSERIAL,
    user_id BIGINT REFERENCES users(id),  -- OK
    created_at TIMESTAMPTZ
) PARTITION BY RANGE (created_at);
 
-- ❌ FK НА партиционированную таблицу — НЕ работает (до PG 16)
CREATE TABLE notifications (
    id BIGSERIAL PRIMARY KEY,
    event_id BIGINT REFERENCES events(id)  -- ERROR в PG < 16
);

-- ❌ НЕПРАВИЛЬНО: VACUUM только родительской таблицы
VACUUM events;  -- Не затронет партиции!
 
-- ✅ ПРАВИЛЬНО: VACUUM каждой партиции
VACUUM events_2024_01;
VACUUM events_2024_02;
-- Или через автоматизацию
 
-- Или включите autovacuum для каждой партиции
ALTER TABLE events_2024_01 SET (autovacuum_enabled = true);

-- У вас партиции по created_at
-- ❌ Запрос БЕЗ ключа партиционирования
SELECT * FROM events WHERE user_id = 123;
-- Сканирует ВСЕ партиции (медленно)
 
-- ✅ Запрос С ключом партиционирования
SELECT * FROM events
WHERE user_id = 123
  AND created_at >= '2024-01-01'
  AND created_at < '2024-02-01';
-- Сканирует только одну партицию (быстро)

-- ❌ МЕДЛЕННО и блокирует таблицу
DELETE FROM events WHERE created_at < '2022-01-01';
-- Если это 100GB данных — база встанет на часы
 
-- ✅ БЫСТРО: отключить и удалить партицию
ALTER TABLE events DETACH PARTITION events_2021_12;  -- мгновенно
DROP TABLE events_2021_12;  -- быстро, не блокирует родительскую таблицу

# postgresql.conf
shared_preload_libraries = 'pg_stat_statements'
 
# Настройки pg_stat_statements
pg_stat_statements.max = 10000              # Максимум записей (по умолчанию 5000)
pg_stat_statements.track = all               # Отслеживать все запросы (top/all)
pg_stat_statements.track_utility = on        # Включая DDL (CREATE, DROP, etc.)
pg_stat_statements.track_planning = on       # PostgreSQL 13+: время планирования
pg_stat_statements.save = on                 # Сохранять между перезапусками

sudo systemctl restart postgresql
# Или через pg_ctl
pg_ctl restart -D /var/lib/postgresql/data

-- Подключаемся к базе
\c mydb
 
-- Создаём расширение
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
 
-- Проверка
SELECT * FROM pg_stat_statements LIMIT 5;

SELECT
    substring(query, 1, 80) as short_query,
    calls,
    ROUND(mean_exec_time::numeric, 2) as avg_ms,
    ROUND(total_exec_time::numeric, 2) as total_ms,
    ROUND((total_exec_time / sum(total_exec_time) OVER ()) * 100, 2) as percent_total,
    ROUND(stddev_exec_time::numeric, 2) as stddev_ms,
    ROUND(max_exec_time::numeric, 2) as max_ms
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

SELECT
    substring(query, 1, 80) as short_query,
    calls,
    ROUND(total_exec_time::numeric, 2) as total_ms,
    ROUND(mean_exec_time::numeric, 2) as avg_ms,
    ROUND((total_exec_time / sum(total_exec_time) OVER ()) * 100, 2) as percent_total
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

SELECT
    substring(query, 1, 80) as short_query,
    calls,
    ROUND(mean_exec_time::numeric, 2) as avg_ms,
    ROUND(stddev_exec_time::numeric, 2) as stddev_ms,
    ROUND((stddev_exec_time / NULLIF(mean_exec_time, 0)) * 100, 2) as cv_percent
FROM pg_stat_statements
WHERE calls > 100  -- Достаточно вызовов для статистики
ORDER BY stddev_exec_time DESC
LIMIT 10;

-- Сброс всех данных pg_stat_statements
SELECT pg_stat_statements_reset();
 
-- Сброс для конкретного запроса (PostgreSQL 12+)
SELECT pg_stat_statements_reset(userid, dbid, queryid);

# Включаем auto_explain
shared_preload_libraries = 'pg_stat_statements,auto_explain'
 
# Настройки auto_explain
auto_explain.log_min_duration = 1000         # Логировать запросы > 1 секунды (в мс)
auto_explain.log_analyze = on                # Выполнять ANALYZE (реальные цифры)
auto_explain.log_buffers = on                # Показывать shared buffers
auto_explain.log_timing = on                 # Показывать время каждого узла
auto_explain.log_triggers = on               # Включать триггеры
auto_explain.log_verbose = off               # VERBOSE режим (много вывода)
auto_explain.log_nested_statements = on      # Логировать вложенные запросы
auto_explain.log_format = text               # Формат: text/json/xml/yaml

sudo systemctl restart postgresql

# Логи обычно в /var/log/postgresql/
tail -f /var/log/postgresql/postgresql-15-main.log

# Production: логируем только очень медленные запросы
auto_explain.log_min_duration = 5000         # 5 секунд
 
# Включаем sample_rate для снижения нагрузки
auto_explain.sample_rate = 0.1               # Логировать 10% запросов (PostgreSQL 13+)
 
# Отключаем timing для снижения overhead
auto_explain.log_timing = off                # Меньше overhead на production

# Логирование медленных запросов
log_min_duration_statement = 1000            # Логировать запросы > 1 секунды
 
# Детализация логов
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h '
log_statement = 'none'                       # Не логировать все запросы
log_duration = off                           # Не логировать длительность всех запросов
log_checkpoints = on                         # Логировать checkpoints
log_connections = on                         # Логировать подключения
log_disconnections = on                      # Логировать отключения
log_lock_waits = on                          # Логировать блокировки > deadlock_timeout

SELECT pg_reload_conf();

# Ubuntu/Debian
sudo apt install pgbadger
 
# macOS
brew install pgbadger
 
# Или через CPAN
cpan install pgBadger

# Анализ лога за сегодня
pgbadger /var/log/postgresql/postgresql-15-main.log
 
# Анализ нескольких файлов
pgbadger /var/log/postgresql/postgresql-*.log -o report.html
 
# Инкрементальный анализ (для больших логов)
pgbadger --incremental --outdir /var/www/reports/ /var/log/postgresql/*.log

SELECT
    schemaname,
    tablename,
    indexname,
    idx_scan,
    pg_size_pretty(pg_relation_size(indexrelid)) as index_size
FROM pg_stat_user_indexes
WHERE idx_scan = 0               -- Индекс никогда не использовался
  AND indexrelname NOT LIKE '%pkey'  -- Кроме primary keys
ORDER BY pg_relation_size(indexrelid) DESC;

-- Удаление неиспользуемого индекса
DROP INDEX CONCURRENTLY idx_unused;  -- CONCURRENTLY = без блокировки таблицы

SELECT
    schemaname,
    tablename,
    heap_blks_read,
    heap_blks_hit,
    CASE
        WHEN heap_blks_hit + heap_blks_read = 0 THEN 0
        ELSE ROUND((heap_blks_hit::numeric / (heap_blks_hit + heap_blks_read)) * 100, 2)
    END as cache_hit_ratio
FROM pg_statio_user_tables
WHERE heap_blks_read > 0
ORDER BY cache_hit_ratio ASC, heap_blks_read DESC
LIMIT 20;

SELECT
    schemaname,
    tablename,
    n_live_tup,
    n_dead_tup,
    ROUND((n_dead_tup::numeric / NULLIF(n_live_tup, 0)) * 100, 2) as dead_ratio,
    last_vacuum,
    last_autovacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000  -- Больше 1000 мёртвых строк
ORDER BY dead_ratio DESC, n_dead_tup DESC
LIMIT 20;

-- Ручной VACUUM для конкретной таблицы
VACUUM ANALYZE table_name;
 
-- Или агрессивный VACUUM FULL (требует эксклюзивной блокировки)
VACUUM FULL table_name;  -- ОСТОРОЖНО: блокирует таблицу!

groups:
  - name: postgresql
    interval: 30s
    rules:
      # Критичный: Disk space
      - alert: PostgreSQLDiskSpaceHigh
        expr: (node_filesystem_avail_bytes{mountpoint="/var/lib/postgresql"} / node_filesystem_size_bytes) < 0.15
        for: 5m
        labels:
          severity: critical
        annotations:
          summary: "PostgreSQL disk space < 15% ({{ $value }}%)"
 
      # Критичный: Connection count
      - alert: PostgreSQLTooManyConnections
        expr: (pg_stat_database_numbackends / pg_settings_max_connections) > 0.8
        for: 5m
        labels:
          severity: critical
        annotations:
          summary: "PostgreSQL connections > 80% of max"
 
      # Критичный: Replication lag
      - alert: PostgreSQLReplicationLag
        expr: pg_replication_lag > 60
        for: 5m
        labels:
          severity: critical
        annotations:
          summary: "PostgreSQL replication lag > 60s ({{ $value }}s)"
 
      # Предупреждение: Cache hit ratio
      - alert: PostgreSQLLowCacheHitRatio
        expr: pg_stat_database_blks_hit / (pg_stat_database_blks_hit + pg_stat_database_blks_read) < 0.85
        for: 15m
        labels:
          severity: warning
        annotations:
          summary: "PostgreSQL cache hit ratio < 85% ({{ $value }}%)"
 
      # Предупреждение: Long running queries
      - alert: PostgreSQLLongRunningQueries
        expr: pg_stat_activity_max_tx_duration > 600
        for: 5m
        labels:
          severity: warning
        annotations:
          summary: "PostgreSQL query running > 10 minutes"

docker run -p 8080:8080 -e DATABASE_URL=postgres://user:pass@host:5432/dbname ankane/pghero

# Docker
docker run -d \
  -p 9187:9187 \
  -e DATA_SOURCE_NAME="postgresql://user:password@localhost:5432/mydb?sslmode=disable" \
  prometheuscommunity/postgres-exporter
 
# Или через systemd
wget https://github.com/prometheus-community/postgres_exporter/releases/download/v0.15.0/postgres_exporter-0.15.0.linux-amd64.tar.gz
tar xvfz postgres_exporter-0.15.0.linux-amd64.tar.gz
sudo mv postgres_exporter /usr/local/bin/

scrape_configs:
  - job_name: "postgresql"
    static_configs:
      - targets: ["localhost:9187"]

-- Обновили статистику
ANALYZE listings;
 
-- Пересоздали индекс на всякий случай
REINDEX INDEX CONCURRENTLY idx_listings_status;

-- ❌ Это я узнал в момент инцидента
SELECT * FROM pg_stat_statements;
-- ERROR: extension "pg_stat_statements" is not available
 
-- ✅ Проверка ПЕРЕД тем, как случится проблема
SELECT * FROM pg_extension WHERE extname = 'pg_stat_statements';
-- Если пусто — установите СЕЙЧАС

# ❌ ОПАСНО: Логирует ВСЕ запросы > 10ms
auto_explain.log_min_duration = 10
 
# На высоконагруженной системе это создало:
# - 50GB логов за 2 часа
# - Переполнение диска
# - Падение базы

# ✅ БЕЗОПАСНО: Логируем только реально медленные
auto_explain.log_min_duration = 5000         # 5 секунд для production
auto_explain.sample_rate = 0.1               # 10% запросов (PostgreSQL 13+)

# PgBouncer config
application_name_add_host = 1  # Добавляет hostname в application_name

# Python (psycopg2)
conn = psycopg2.connect(
    "...",
    application_name="my-app-worker-1"  # Явно указываем имя приложения
)

SELECT usename, application_name, query, calls
FROM pg_stat_activity;

# Мониторинг WAL директории
df -h /var/lib/postgresql/15/main/pg_wal
 
# Алерт в Prometheus
node_filesystem_avail_bytes{mountpoint="/var/lib/postgresql/15/main/pg_wal"}