XC7 - контент-платформа

Начнем с главного: почему база тормозит?

PostgreSQL - мощный инструмент, но без правильной настройки он быстро превращается в тормозную лошадь. Чаще всего проблемы растут из трех вещей: кривые запросы, отсутствие индексов и стандартные настройки конфига, которые рассчитаны на старый комп. Давайте разберем, что можно сделать прямо сейчас, чтобы база летала.

1. Индексы - ваше все, но с умом

Без индексов PostgreSQL сканирует таблицу целиком. Это больно. Но ставить индекс на каждую колонку - тоже плохая идея: замедляются вставки и разрастается размер базы.

Какие индексы реально работают?

B-tree - стандартный выбор для колонок с уникальными значениями (id, email). Работает с операторами =, <, >, BETWEEN.
Hash - только для точного сравнения =. Если вам нужен поиск по точному совпадению (например, хэш пароля), это ваш вариант.
GIN - для массивов, JSONB и полнотекстового поиска. Если у вас поле tags text[] или data jsonb, GIN спасет.

Пример из жизни. Допустим, у вас таблица заказов:

CREATE INDEX idx_orders_created_at ON orders (created_at);
-- Для поиска по дате это ускорит запрос в 10-50 раз

Но если вы часто ищете по двум полям одновременно, делайте составной индекс:

CREATE INDEX idx_orders_user_date ON orders (user_id, created_at);
-- Порядок важен: сначала селективное поле, потом остальное

Совет: используйте EXPLAIN ANALYZE перед запросом. Если видите Seq Scan на большой таблице - срочно ставьте индекс.

2. Конфигурация: не оставляйте дефолты

PostgreSQL из коробки настроен на минимальные ресурсы. Если у вас сервер с 8 ГБ ОЗУ, а в конфиге стоит shared_buffers = 128MB - вы теряете производительность.

Ключевые параметры, которые нужно менять:

shared_buffers - 25% от ОЗУ. Для 8 ГБ ставьте 2 ГБ.
work_mem - память для сортировок и хэшей. Не ставьте слишком много: если 100 пользователей выполняют сортировку, каждый получит свой кусок. Оптимум - 8-16 МБ.
effective_cache_size - подсказка планировщику о размере кэша ОС. Ставьте 50-75% от ОЗУ.
maintenance_work_mem - для VACUUM и CREATE INDEX. Можно смело ставить 1 ГБ на сервере с 8 ГБ.

Пример настройки в postgresql.conf:

shared_buffers = 2GB
work_mem = 16MB
effective_cache_size = 6GB
maintenance_work_mem = 1GB
random_page_cost = 1.1 # если используете SSD

После изменений перезагрузите кластер. Разницу заметите сразу - особенно на сложных запросах с JOIN и сортировками.

3. VACUUM и автоанализ - не забывайте про уборку

PostgreSQL не удаляет строки физически при DELETE или UPDATE. Он помечает их как мертвые. Со временем таблица раздувается, запросы тормозят.

Автовакуум работает по умолчанию, но его настройки часто слабые. Проверьте:

SHOW autovacuum_vacuum_scale_factor; -- по умолчанию 0.2 (20% изменений)

Для активных таблиц лучше уменьшить этот порог:

ALTER TABLE orders SET (autovacuum_vacuum_scale_factor = 0.05);
ALTER TABLE orders SET (autovacuum_vacuum_threshold = 100);

И не забывайте про ANALYZE. Он обновляет статистику для планировщика. Если после массовой вставки запросы стали тормозить - скорее всего, статистика устарела.

ANALYZE orders;
-- Или для всей базы: vacuumdb --analyze -d yourdb

4. Запросы: что нельзя делать никогда

Даже с идеальными индексами можно убить производительность кривым SQL. Вот что я видел чаще всего в продакшене:

SELECT * FROM huge_table - не тащите все колонки, если нужны только две. Это нагрузка на I/O и сеть.
Функции на колонках в WHERE - WHERE DATE(created_at) = '2024-01-01' убивает индекс. Пишите WHERE created_at >= '2024-01-01' AND created_at < '2024-01-02'.
LIKE '%текст%' - если индекс и нужен, то только триграммный (pg_trgm). Обычный B-tree тут бесполезен.
Вложенные подзапросы вместо JOIN - часто планировщик справляется, но не всегда. Сравнивайте через EXPLAIN.

Пример плохого запроса:

SELECT * FROM orders WHERE total > (SELECT AVG(total) FROM orders);
-- Лучше сделать так:
WITH avg_total AS (SELECT AVG(total) AS avg FROM orders)
SELECT * FROM orders, avg_total WHERE orders.total > avg_total.avg;

Разница может быть в разы, если таблица большая.

5. Партиционирование - когда таблица больше 100 ГБ

Если таблица разрастается до сотен гигабайт, индексы перестают помогать. Тут выручает партиционирование - разбивка таблицы на куски по диапазону (по дате, по id).

Пример для таблицы логов:

CREATE TABLE logs (
 id SERIAL,
 created_at TIMESTAMP NOT NULL,
 message TEXT
) PARTITION BY RANGE (created_at);

CREATE TABLE logs_2024_01 PARTITION OF logs
 FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');
CREATE TABLE logs_2024_02 PARTITION OF logs
 FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');

Теперь запрос с фильтром по дате будет сканировать только одну партицию. Это ускоряет выборку и упрощает удаление старых данных (просто дропаете партицию).

Итог: что делать прямо сейчас

Оптимизация PostgreSQL - это не магия, а система. Если база тормозит, пройдитесь по чеклисту:

Поставьте индексы на колонки, которые чаще всего в WHERE и JOIN.
Поправьте конфиг под ваш сервер - shared_buffers, work_mem, effective_cache_size.
Настройте автовакуум для активных таблиц.
Перепишите самые тяжелые запросы - уберите SELECT *, функции в WHERE, замените LIKE на полнотекст.
Если таблица огромная - подумайте о партиционировании.

Начните с EXPLAIN ANALYZE. Он покажет, где именно база тратит время. Часто проблема оказывается в одной строчке кода, которую вы писали «на скорую руку». Исправьте её - и прирост производительности будет в разы больше, чем от любой настройки конфига.

Полезные советы по оптимизации PostgreSQL