Введение
Оптимизация производительности PostgreSQL критически важна для приложений, работающих в масштабе. Медленные запросы, отсутствующие индексы и некорректная конфигурация могут парализовать производительность приложения. Старшие инженеры должны понимать планирование запросов, стратегии индексирования и настройку конфигурации, чтобы поддерживать отзывчивость систем.
Анализ производительности запросов
EXPLAIN ANALYZE
EXPLAIN ANALYZE SELECT * FROM orders
WHERE user_id = 123
AND created_at > '2024-01-01'
ORDER BY created_at DESC
LIMIT 10;
Интерпретация вывода:
- Seq Scan: Полное сканирование таблицы (часто плохо для больших таблиц)
- Index Scan: Использование индекса (хорошо)
- Index Only Scan: Лучший вариант — данные берутся только из индекса
- Bitmap Heap Scan: Комбинирует несколько условий по индексам
- actual time: Реальное время выполнения
- rows: Фактически обработанные строки
Выявление медленных запросов
-- Включите логирование медленных запросов в postgresql.conf
-- log_min_duration_statement = 1000 -- Логировать запросы > 1 секунды
-- Найти выполняющиеся в данный момент запросы
SELECT pid, now() - query_start AS duration, state, query
FROM pg_stat_activity
WHERE state <> 'idle'
AND query NOT LIKE '% FROM pg_stat_activity %'
ORDER BY duration DESC;
-- Найти длительно выполняющиеся запросы
SELECT datname, pid, state, query, age(clock_timestamp(), query_start) AS age
FROM pg_stat_activity
WHERE state <> 'idle'
ORDER BY age DESC;
Завершение длительно выполняющихся запросов
-- Отменить запрос (мягко)
SELECT pg_cancel_backend(pid);
-- Завершить подключение (принудительно)
SELECT pg_terminate_backend(pid);
Стратегии индексирования
Базовые индексы
-- Индекс по одному столбцу
CREATE INDEX idx_orders_user_id ON orders(user_id);
-- Составной индекс (порядок столбцов важен!)
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at);
-- Уникальный индекс
CREATE UNIQUE INDEX idx_users_email ON users(email);
Частичные индексы
Индексируйте только строки, соответствующие условию:
-- Индексировать только активных пользователей
CREATE INDEX idx_active_users ON users(email)
WHERE status = 'active';
-- Индексировать только недавние заказы
CREATE INDEX idx_recent_orders ON orders(user_id, created_at)
WHERE created_at > '2024-01-01';
Индексы по выражениям
-- Индекс по email в нижнем регистре
CREATE INDEX idx_users_email_lower ON users(LOWER(email));
-- Индекс по извлечённому году
CREATE INDEX idx_orders_year ON orders(EXTRACT(YEAR FROM created_at));
Покрывающие индексы (INCLUDE)
Добавляйте неиндексируемые столбцы, чтобы включить index-only scans:
CREATE INDEX idx_orders_user ON orders(user_id)
INCLUDE (total, status, created_at);
-- Теперь запрос может быть полностью удовлетворён за счёт индекса
SELECT total, status, created_at FROM orders WHERE user_id = 123;
Когда использовать разные типы индексов
| Тип индекса | Сценарий использования |
|---|
| B-tree | По умолчанию, запросы на равенство и диапазоны |
| Hash | Только равенство (редко лучше, чем B-tree) |
| GIN | Полнотекстовый поиск, JSONB, массивы |
| GiST | Геометрические данные, полнотекстовый поиск |
| BRIN | Большие таблицы с естественным порядком |
Индексирование JSONB
-- Индекс GIN для запросов на включение (containment) JSONB
CREATE INDEX idx_products_metadata ON products USING gin(metadata);
-- Запрос: WHERE metadata @> '{"category": "electronics"}'
-- Индексировать конкретный путь JSONB
CREATE INDEX idx_products_category ON products((metadata->>'category'));
Оптимизация запросов
Избегайте SELECT *
-- Плохо
SELECT * FROM orders WHERE user_id = 123;
-- Хорошо
SELECT id, total, status, created_at FROM orders WHERE user_id = 123;
Используйте LIMIT вместе с ORDER BY
-- Плохо: сортирует все строки
SELECT * FROM orders ORDER BY created_at DESC;
-- Хорошо: останавливается раньше благодаря индексу
SELECT * FROM orders ORDER BY created_at DESC LIMIT 10;
Эффективная пагинация
-- Плохо: OFFSET сканирует и отбрасывает строки
SELECT * FROM orders ORDER BY id LIMIT 20 OFFSET 10000;
-- Хорошо: keyset pagination
SELECT * FROM orders
WHERE id < 12345 -- Последний ID с предыдущей страницы
ORDER BY id DESC
LIMIT 20;
Пакетные операции
-- Плохо: много мелких транзакций
INSERT INTO logs (message) VALUES ('log 1');
INSERT INTO logs (message) VALUES ('log 2');
-- ... ещё 1000
-- Хорошо: одна транзакция с несколькими строками
INSERT INTO logs (message) VALUES
('log 1'), ('log 2'), ('log 3'), ...;
-- Или используйте COPY для массовых загрузок
COPY logs(message) FROM '/path/to/data.csv' CSV;
Избегайте N+1 в коде приложения
-- Вместо того чтобы запрашивать users, а затем orders отдельно,
-- используйте JOIN или LATERAL JOIN
SELECT u.*, o.*
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE u.id IN (1, 2, 3);
Оптимизация PostGIS
Запросы к геометрии
-- Сохранить точку
INSERT INTO locations (name, coordinates)
VALUES ('Office', ST_SetSRID(ST_MakePoint(-73.935242, 40.730610), 4326));
-- Запрос как GeoJSON
SELECT ST_AsGeoJSON(ST_Transform(coordinates, 3857))
FROM locations;
-- Найти точки в пределах радиуса
SELECT * FROM locations
WHERE ST_DWithin(
coordinates::geography,
ST_MakePoint(-73.935, 40.730)::geography,
1000 -- Метры
);
-- Пространственный индекс (обязательно!)
CREATE INDEX idx_locations_geo ON locations USING gist(coordinates);
Настройка конфигурации
Настройки памяти
# postgresql.conf
# Разделяемая память для кэширования (25% RAM)
shared_buffers = 4GB
# Память на операцию (sort, hash)
work_mem = 256MB
# Память для операций обслуживания
maintenance_work_mem = 1GB
# Оценка кэша планировщиком запросов
effective_cache_size = 12GB
Настройки подключений
# Максимальное число подключений
max_connections = 200
# Пулинг подключений (используйте PgBouncer для лучшего масштабирования)
Производительность записи
# Настройки WAL для нагрузок с преобладанием записи
wal_buffers = 64MB
checkpoint_completion_target = 0.9
max_wal_size = 4GB
Обслуживание таблиц
VACUUM и ANALYZE
-- Обновить статистику (помогает планировщику запросов)
ANALYZE orders;
-- Освободить место от удалённых строк
VACUUM orders;
-- Полная очистка (блокирует таблицу)
VACUUM FULL orders;
-- Настройки автоматического vacuum
-- autovacuum = on (по умолчанию)
REINDEX
-- Перестроить раздувшийся индекс
REINDEX INDEX idx_orders_user_id;
-- Перестроить все индексы таблицы
REINDEX TABLE orders;
-- CONCURRENTLY (без блокировки, PostgreSQL 12+)
REINDEX INDEX CONCURRENTLY idx_orders_user_id;
Статистика таблиц
-- Проверить размер таблицы
SELECT pg_size_pretty(pg_total_relation_size('orders'));
-- Проверить размер индекса
SELECT pg_size_pretty(pg_relation_size('idx_orders_user_id'));
-- Оценка раздувания таблицы
SELECT schemaname, tablename,
pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename)) as size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname || '.' || tablename) DESC;
Пулинг подключений
Конфигурация PgBouncer
# pgbouncer.ini
[databases]
mydb = host=localhost port=5432 dbname=mydb
[pgbouncer]
listen_port = 6432
listen_addr = *
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 20
Мониторинг
Ключевые запросы
-- Статистика использования индексов
SELECT schemaname, tablename, indexname, idx_scan, idx_tup_read
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC;
-- Неиспользуемые индексы (кандидаты на удаление)
SELECT schemaname, tablename, indexname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0 AND schemaname = 'public';
-- Паттерны доступа к таблицам
SELECT schemaname, relname, seq_scan, idx_scan,
n_tup_ins, n_tup_upd, n_tup_del
FROM pg_stat_user_tables
ORDER BY seq_scan DESC;
-- Коэффициент попаданий в кэш (должен быть > 99%)
SELECT
sum(heap_blks_read) as heap_read,
sum(heap_blks_hit) as heap_hit,
sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) as ratio
FROM pg_statio_user_tables;
Манипуляции с JSONB
Переименование свойств полей JSON
-- Пример: переименовать 'wrongname' в 'name' во вложенном JSONB
UPDATE products AS p
SET metadata = (
jsonb_set(
p.metadata::jsonb #- '{objectfield,wrongname}',
'{objectfield,name}',
p.metadata::jsonb #> '{objectfield,wrongname}'
)
)::json
WHERE p.metadata::jsonb ? 'objectfield'
AND p.metadata::jsonb #> '{objectfield}' ? 'wrongname';
Запросы к массивам JSONB
-- Найти элементы, где массив содержит конкретное значение
SELECT * FROM products
WHERE metadata->'tags' ? 'featured';
-- Запросить элементы вложенного массива
SELECT * FROM products
WHERE metadata @> '{"categories": [{"name": "electronics"}]}';
-- Агрегировать элементы массива JSONB
SELECT id, jsonb_array_elements(metadata->'variants') as variant
FROM products;
Анонимизация данных
Для сред разработки/staging анонимизируйте production-данные:
-- Анонимизировать пользовательские данные для использования вне production
UPDATE users SET
first_name = SUBSTR(MD5(RANDOM()::text), 1, 2 + FLOOR(3*RANDOM())::int),
last_name = SUBSTR(MD5(RANDOM()::text), 1, 4 + FLOOR(4*RANDOM())::int),
address = CONCAT(
SUBSTR(MD5(RANDOM()::text), 1, 5 + FLOOR(5*RANDOM())::int), ' ',
SUBSTR(MD5(RANDOM()::text), 1, 2 + FLOOR(2*RANDOM())::int), ', ',
FLOOR(100*RANDOM())::int, ', ',
SUBSTR(MD5(RANDOM()::text), 1, 2 + FLOOR(3*RANDOM())::int)
),
email = CONCAT(
SUBSTR(MD5(RANDOM()::text), 1, 5 + FLOOR(5*RANDOM())::int), '@',
SUBSTR(MD5(RANDOM()::text), 1, 2 + FLOOR(3*RANDOM())::int), '.test'
),
phone = CONCAT('+1', FLOOR(1000000000 + 8999999999*RANDOM())::bigint);
Поиск больших таблиц
Определите таблицы, потребляющие больше всего места:
SELECT
schemaname AS schema,
tablename AS table,
pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename)) AS total_size,
pg_size_pretty(pg_relation_size(schemaname || '.' || tablename)) AS table_size,
pg_size_pretty(pg_indexes_size(schemaname || '.' || tablename)) AS indexes_size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname || '.' || tablename) DESC
LIMIT 20;
Управление внешними ключами
Временное отключение для массовых операций
-- Отключить проверки внешних ключей (используйте с осторожностью!)
SET session_replication_role = 'replica';
-- Выполнить массовые операции
TRUNCATE TABLE orders CASCADE;
-- Или сложные миграции данных...
-- Снова включить проверки внешних ключей
SET session_replication_role = 'origin';
Чек-лист лучших практик
- [ ] Индексируйте столбцы, используемые в WHERE, JOIN, ORDER BY
- [ ] Используйте EXPLAIN ANALYZE для медленных запросов
- [ ] Реализуйте пулинг подключений
- [ ] Корректно настройте shared_buffers
- [ ] Запускайте ANALYZE после массовых изменений данных
- [ ] Отслеживайте и удаляйте неиспользуемые индексы
- [ ] Используйте keyset pagination для больших наборов данных
- [ ] Поддерживайте статистику в актуальном состоянии
- [ ] Используйте индексы GIN для запросов к JSONB
- [ ] Анонимизируйте данные в не-production средах
Заключение
Оптимизация производительности PostgreSQL требует понимания выполнения запросов, корректного индексирования и настройки конфигурации. Начните с EXPLAIN ANALYZE, чтобы выявить узкие места, добавьте подходящие индексы и настройте конфигурацию в зависимости от нагрузки. Регулярный мониторинг гарантирует, что производительность не будет деградировать со временем.
