Postgresql

Обычный autovacuum помечает мёртвые строки как свободное место, но не возвращает его файловой системе — таблица физически не уменьшается. VACUUM FULL пересобирает таблицу с нуля и реально сжимает файл на диске, но берёт ACCESS EXCLUSIVE lock на всё время работы — для большой таблицы это часы простоя. pg_repack (расширение + CLI) делает то же без долгой блокировки: создаёт копию таблицы, применяет накопленные за время копирования изменения через триггер, и подменяет таблицу коротким ACCESS EXCLUSIVE только на финальный swap — секунды, а не часы. ...

Обычно здоровье кластера PostgreSQL собирают вручную из pg_stat_statements, pg_stat_activity и логов VACUUM. dasha (github.com/dbulashev/dasha) сводит это в один веб-дашборд: медленные запросы, неэффективные индексы, блокировки, размер таблиц и прогресс VACUUM — с итоговым Health Score по формуле с весами. cd deploy/compose docker compose up -d # веб-интерфейс: http://localhost:3000 Бэкенд на Go 1.26, фронт на Vue 3.5, поддерживает PostgreSQL 14-18, аутентификацию через OIDC и RBAC, снимки состояния кластера для истории. Есть MCP-коннектор — можно подключить ИИ-ассистента прямо к метрикам кластера, и интеграция с Yandex Cloud для поиска по логам. ...

PostgreSQL на каждое соединение форкает отдельный backend-процесс — при сотнях коротких подключений (веб-запросы, serverless-функции) это съедает память и CPU на сам факт подключения, ещё до первого запроса. pgbouncer держит пул готовых соединений к базе и раздаёт их клиентам. [databases] mydb = host=127.0.0.1 port=5432 dbname=mydb [pgbouncer] listen_port = 6432 pool_mode = transaction max_client_conn = 1000 default_pool_size = 20 Режимы pool_mode: session — за клиентом до disconnect, совместимо со всем (advisory locks, prepared statements) transaction — на время транзакции, самый экономный, но ломает session-level фичи statement — на один запрос, только для read-only трафика psql -h 127.0.0.1 -p 6432 -U app mydb # подключение через pgbouncer, не напрямую в базу #postgresql #dba

PostgreSQL на каждое соединение форкает отдельный backend-процесс — при сотнях коротких подключений (веб-запросы, serverless-функции) это съедает память и CPU на сам факт подключения, ещё до первого запроса. pgbouncer держит пул готовых соединений к базе и раздаёт их клиентам. [databases] mydb = host=127.0.0.1 port=5432 dbname=mydb [pgbouncer] listen_port = 6432 pool_mode = transaction max_client_conn = 1000 default_pool_size = 20 Режимы pool_mode: session — за клиентом до disconnect, совместимо со всем (advisory locks, prepared statements) transaction — на время транзакции, самый экономный, но ломает session-level фичи statement — на один запрос, только для read-only трафика psql -h 127.0.0.1 -p 6432 -U app mydb # подключение через pgbouncer, не напрямую в базу #postgresql #dba

Три утилиты, три разных уровня бэкапа — путаница возникает, когда пытаются взаимозаменить логический и физический бэкап там, где это не работает. pg_dump -Fc mydb > mydb.dump # логический дамп одной БД pg_restore -d mydb_new --clean --if-exists mydb.dump pg_basebackup -D /backup/base -Ft -z -P -U replicator # физическая копия кластера pg_dump — логический: SQL-представление данных, можно восстановить одну таблицу из дампа целой БД, версии источника и назначения могут отличаться. pg_basebackup — физический: побайтовая копия PGDATA, восстанавливается только целиком, зато на её основе строится репликация и point-in-time recovery через WAL. Для одной таблицы — pg_dump -t, для кластера с минимальным RPO — pg_basebackup плюс архив WAL. ...

pg_hba.conf решает не “кто может логиниться”, а “каким методом проверять” — и правила читаются сверху вниз, первое совпадение по типу/базе/пользователю/адресу побеждает. # TYPE DATABASE USER ADDRESS METHOD local all postgres peer host all all 127.0.0.1/32 scram-sha-256 host mydb app 10.0.0.0/24 scram-sha-256 peer — только для local (сверяет пользователя ОС с ролью PostgreSQL, пароль не нужен). trust — без пароля вообще, годится только на loopback в закрытом окружении. md5 устарел, scram-sha-256 — текущий стандарт. После правки — pg_ctl reload или SELECT pg_reload_conf();, restart не нужен и обрывает соединения. ...

Запрос висит, в логах тихо — почти всегда блокировка. pg_stat_activity покажет статус active, но не покажет, кто держит блокировку. Для этого — pg_locks. -- цепочка блокировок без ручных JOIN SELECT pid, pg_blocking_pids(pid) AS blocked_by, query FROM pg_stat_activity WHERE cardinality(pg_blocking_pids(pid)) > 0; SELECT pid, locktype, relation::regclass, mode, granted FROM pg_locks WHERE NOT granted; granted = false — это и есть ожидающий процесс. Row-level локи (RowExclusiveLock от UPDATE) конфликтуют не со всеми — а AccessExclusiveLock (от ALTER TABLE, VACUUM FULL) блокирует вообще всё, включая SELECT. DDL в очереди на busy-таблице сам блокирует все следующие запросы, даже read-only. ...

pg_stat_activity — представление с одной строкой на каждое соединение к базе: кто подключён, что выполняет прямо сейчас и сколько это уже длится. SELECT pid, state, now() - query_start AS duration, query FROM pg_stat_activity WHERE state != 'idle' ORDER BY duration DESC; Колонка state — главное, на что смотреть: active — запрос реально выполняется; idle — соединение открыто, но ничего не делает; idle in transaction — самое опасное значение, транзакция открыта и висит, держит блокировки и мешает autovacuum чистить мёртвые строки. ...

EXPLAIN показывает план планировщика (Seq Scan, Index Scan, Nested Loop, Hash Join) без выполнения запроса. EXPLAIN ANALYZE запускает его по-настоящему, добавляя время и число строк на узел. EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42; ⚠️ На INSERT/UPDATE/DELETE EXPLAIN ANALYZE реально пишет в таблицу — оборачивай в BEGIN; ... ROLLBACK;. Буферы (кэш vs диск) — первое, что смотрят при разборе медленного запроса: EXPLAIN (ANALYZE, BUFFERS) .... Главное в плане: cost=startup..total — оценка планировщика, не секунды; rows vs actual rows — расхождение значит устаревшую статистику (ANALYZE table_name); Seq Scan вместо Index Scan — повод проверить индекс. ...

pg_stat_statements — расширение, которое собирает статистику по каждому нормализованному запросу на сервере: сколько раз вызван, суммарное и среднее время выполнения, сколько строк вернул. Без него узкие места ищутся вслепую по логам. Включить: добавить в postgresql.conf и перезапустить кластер, затем создать расширение: shared_preload_libraries = 'pg_stat_statements' CREATE EXTENSION pg_stat_statements; Топ-10 самых тяжёлых запросов по суммарному времени: SELECT query, calls, total_exec_time, mean_exec_time FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10; Сбросить накопленную статистику: SELECT pg_stat_statements_reset();. Для учёта времени на дисковый I/O отдельно от CPU включи track_io_timing = on. ...