postgres-tuning — скилл для Claude Code и Codex: настройка PostgreSQL
Скилл postgres-tuning для Claude Code, Codex, Cursor и других агентов, который даёт инструкции по настройке производительности PostgreSQL 17/18+. Охватывает async I/O, анализ планов запросов, индексы, autovacuum, pgvector и PgBouncer.
MIT 136 установок ★ 29 обновлён 2026-08-17
npx skills add oakoss/agent-skills --skill postgres-tuning -a claude-codeЧто делает postgres-tuning
Скилл загружает в контекст агента готовые шаблоны и паттерны для диагностики и настройки PostgreSQL 17 и 18.
Внутри — таблицы с конфигурационными параметрами, SQL-паттерны для курсорной пагинации и очередей, инструкции по мониторинговым представлениям (pg_stat_statements, pg_stat_io, pg_stat_checkpointer, pg_aios), и разбор ключевых изменений между версиями. Для PG18 описаны нативный async I/O через io_method, встроенный uuidv7(), B-tree skip scan и автоматическое включение BUFFERS в EXPLAIN ANALYZE. Для pgvector — настройка HNSW-индекса через параметры m и ef_construction.
Скилл не заменяет инструменты для проектирования схем, кэширования на уровне приложения и выбора СУБД — это явно указано в его границах. Для хранилищ, где используется io_uring, нужен Linux с ядром 5.1+ и сборка PostgreSQL с флагом liburing.
Какие задачи решает
- проанализировать план запроса через
EXPLAIN (ANALYZE, BUFFERS, SETTINGS)и найти узкое место - настроить
autovacuum_vacuum_scale_factorдля таблицы с высокой частотой обновлений - выбрать тип индекса (B-tree, BRIN, GIN, HNSW) под конкретный паттерн запросов
- перенести мониторинг чекпоинтов с
pg_stat_bgwriterнаpg_stat_checkpointerпосле обновления до PG17 - настроить PgBouncer или параметры
io_max_concurrencyиio_workersдля async I/O в PG18
Примеры запросов агенту
- Найди медленные запросы через
pg_stat_statementsи предложи индексы для топ-5 по cumulative time - Настрой autovacuum для таблицы
events, у которой высокийn_dead_tupвpg_stat_user_tables - Создай HNSW-индекс на колонке
embeddingи объясни, как подобратьmиef_construction
Как установить postgres-tuning
1Через Skills CLI (Vercel)
npx skills add oakoss/agent-skills --skill postgres-tuning -a claude-code2Вручную: положить файлы
~/.claude/skills/postgres-tuning/SKILL.mdПапка скилла целиком; для одного проекта — .claude/skills/postgres-tuning/
1Через Skills CLI (Vercel)
npx skills add oakoss/agent-skills --skill postgres-tuning -a codex2Вручную: положить файлы
.agents/skills/postgres-tuning/SKILL.mdДля всех проектов — ~/.agents/skills/
1Через Skills CLI (Vercel)
npx skills add oakoss/agent-skills --skill postgres-tuning -a cursor2Вручную: положить файлы
.cursor/skills/postgres-tuning/SKILL.mdCursor читает и .agents/skills/, и .claude/skills/
1GitHub CLI
gh skill install oakoss/agent-skills skills/postgres-tuning2Вручную: положить файлы
.github/skills/postgres-tuning/SKILL.md1Через Skills CLI (Vercel)
npx skills add oakoss/agent-skills --skill postgres-tuning -a gemini-cli2Командой платформы
gemini skills install https://github.com/oakoss/agent-skills --path skills/postgres-tuningВручную: положить файлы
.devin/skills/postgres-tuning/SKILL.mdСтарый путь .windsurf/skills/ тоже читается
Через Skills CLI (Vercel)
npx skills add oakoss/agent-skills --skill postgres-tuning -a opencodeClaude.ai
Упакуйте папку в zip и загрузите в Settings → Capabilities → Skills
По шагам, с командами для каждой платформы: Как установить скилл в Claude Code: пять способов
Вопросы о postgres-tuning
Как установить postgres-tuning в Claude Code?
Выполните команду npx skills add oakoss/agent-skills --skill postgres-tuning -a claude-code. После этого файл скилла будет доступен по пути ~/.claude/skills/postgres-tuning/SKILL.md.
Что делает скилл postgres-tuning?
Он загружает в контекст агента набор паттернов, SQL-примеров и конфигурационных рекомендаций для настройки производительности PostgreSQL 17/18+. Агент использует их при ответах на вопросы о медленных запросах, индексах, autovacuum, async I/O и pgvector.
Работает ли postgres-tuning с PostgreSQL версий ниже 17?
Скилл ориентирован на PostgreSQL 17 и 18. Часть паттернов специфична для этих версий: pg_stat_checkpointer появился в PG17, uuidv7() и pg_aios — в PG18, wal_compression = zstd — в PG15.
Исходный текст
SKILL.md · MIT
--- name: postgres-tuning description: 'PostgreSQL 17/18+ performance tuning and optimization. Covers async I/O configuration, query plan forensics, index strategies, autovacuum tuning, vector search optimization, connection pooling, declarative partitioning, and practical query patterns. Use when diagnosing slow queries, configuring async I/O, tuning autovacuum, optimizing vector indexes, analyzing execution plans with EXPLAIN BUFFERS, configuring PgBouncer or connection pooling, setting up table partitioning, implementing cursor pagination, or optimizing queue processing.' license: MIT metadata: author: oakoss version: '1.1' user-invocable: false --- # PostgreSQL Tuning ## Overview Optimizes PostgreSQL 17/18+ performance across I/O, query execution, indexing, and maintenance. Covers the native AIO subsystem introduced in PostgreSQL 18 for throughput gains on modern storage, forensic query plan analysis with EXPLAIN BUFFERS (auto-included in PG18), B-tree skip scans for composite indexes, native UUIDv7 generation, and autovacuum tuning for high-churn tables. **When to use:** Diagnosing slow queries, configuring async I/O, tuning shared_buffers and work_mem, optimizing indexes for write-heavy workloads, managing table bloat, pgvector HNSW tuning. **When NOT to use:** Schema design (use a data modeling tool), application-level caching strategy, database selection decisions, ORM query generation. **Key monitoring views:** - `pg_stat_statements` — identifies slow query patterns by cumulative execution time - `pg_stat_io` — granular I/O analysis by backend type, object, and context (PG16+) - `pg_stat_checkpointer` — checkpoint frequency and timing (PG17+; previously in `pg_stat_bgwriter`) - `pg_stat_user_tables` — dead tuple counts for bloat detection and autovacuum monitoring - `pg_statio_user_tables` — buffer cache hit ratios per table - `pg_aios` — in-progress AIO operations (PG18+) ## Quick Reference | Pattern | Configuration / Query | Key Points | | --------------------- | --------------------------------------- | -------------------------------------------------------------------------------------------------- | | Async I/O | `io_method = worker` or `io_uring` | PG18 default is `worker`; `io_uring` Linux-only (kernel 5.1+, requires liburing build flag) | | I/O concurrency | `io_max_concurrency` and `io_workers` | `io_workers` defaults to 3; `io_max_concurrency` defaults to -1 (auto-calculated) | | Forensic EXPLAIN | `EXPLAIN (ANALYZE, BUFFERS, SETTINGS)` | PG18 auto-includes BUFFERS with ANALYZE; target Shared Hit > 95% | | UUIDv7 primary keys | `DEFAULT uuidv7()` | PG18 built-in; time-ordered, monotonic within a session; RFC 9562 compliant | | B-tree skip scan | Composite index on `(a, b)` | PG18 skips leading column; works best with low-cardinality prefix and equality on trailing columns | | Aggressive autovacuum | `autovacuum_vacuum_scale_factor = 0.01` | Triggers at 1% row change instead of default 20% | | Shared buffers | Start at 25% of RAM | Do not exceed 40% without benchmarking | | work_mem tuning | `SET work_mem = '64MB'` per session | Prevents sort spills to disk; allocated per operator, not per query | | BRIN index | `CREATE INDEX USING brin(...)` | 100x smaller than B-tree for physically ordered time-series data | | HNSW vector index | `USING hnsw (col vector_cosine_ops)` | Tune `m` (default 16) and `ef_construction` (default 64) for recall vs speed | | GIN index | `CREATE INDEX USING gin(...)` | JSONB containment, full-text search, array operators; slower writes | | Checkpoint tuning | `checkpoint_timeout = 30min` | Spread writes over 90% of timeout window to avoid I/O storms | | WAL compression | `wal_compression = zstd` | Available since PG15; reduces WAL I/O 50-70% for write-heavy workloads | | Bloat detection | `pg_stat_user_tables.n_dead_tup` | Reindex concurrently if bloat > 30% | | I/O monitoring | `SELECT * FROM pg_stat_io` | Watch `evictions` (cache too small) and `extends` (fast growth) | | Checkpoint monitoring | `pg_stat_checkpointer` | PG17+ moved checkpoint stats out of `pg_stat_bgwriter` | ## Key Version Changes **PostgreSQL 18:** - Native async I/O via `io_method` parameter (reads only; writes remain synchronous) - Built-in `uuidv7()` function with monotonic ordering within a session (RFC 9562) - `uuidv4()` alias for `gen_random_uuid()` and `uuid_extract_timestamp()` for UUIDv7 - B-tree skip scan for composite indexes (equality on trailing columns, low-cardinality prefix) - EXPLAIN ANALYZE auto-includes buffer statistics without specifying BUFFERS - `pg_stat_io` gains byte-level columns (`read_bytes`, `write_bytes`, `extend_bytes`); `op_bytes` removed - `effective_io_concurrency` default changed from 1 to 16 - AIO monitoring via `pg_aios` system view for in-progress I/O operations **PostgreSQL 17:** - Checkpoint statistics moved from `pg_stat_bgwriter` to `pg_stat_checkpointer` - Column renames: `checkpoints_timed` to `num_timed`, `checkpoints_req` to `num_requested` - `buffers_backend` and `buffers_backend_fsync` removed from `pg_stat_bgwriter` (now in `pg_stat_io`) **PostgreSQL 15:** - `wal_compression` expanded from boolean to support `pglz`, `lz4`, and `zstd` algorithms ## Common Mistakes | Mistake | Correct Pattern | | ------------------------------------------------------------------ | -------------------------------------------------------------------------------------------------------- | | Using `uuid_generate_v7()` or `gen_random_uuid()` for ordered keys | PG18 provides built-in `uuidv7()` for time-ordered UUIDs; pre-PG18 use `pg_uuidv7` extension | | Using `max_async_ios` as a configuration parameter | The correct PG18 parameter is `io_max_concurrency` (max concurrent I/O ops per process) | | Querying `pg_stat_bgwriter` for checkpoint statistics on PG17+ | Checkpoint stats moved to `pg_stat_checkpointer` in PG17; columns renamed (`num_timed`, `num_requested`) | | Using SELECT \* in high-frequency queries | Select only needed columns to reduce I/O and improve cache hit ratios | | Ignoring sequential scans on tables over 10k rows | Add targeted indexes on columns used in WHERE, ORDER BY, and JOIN clauses | | Setting shared_buffers above 40% of RAM without testing | Start at 25% and benchmark; excessive allocation causes OS page cache contention | | Leaving autovacuum at default settings for high-churn tables | Tune `autovacuum_vacuum_scale_factor` to 0.01 for tables with frequent UPDATE/DELETE | | Over-indexing columns rarely used in queries | Every extra index slows UPDATE/INSERT and prevents HOT (Heap Only Tuple) updates | | Expecting B-tree skip scan to work with range predicates | PG18 skip scan only works with equality operators on trailing columns | | Ignoring "External Merge Disk" in query plans | Increase work_mem for specific sessions; it indicates sort spills to disk | | Setting `io_method = io_uring` without verifying build flags | PostgreSQL must be built with `--with-liburing` and requires Linux kernel 5.1+ | | Assuming PG18 AIO accelerates writes | AIO in PG18 only covers reads (seq scans, bitmap heap scans, VACUUM); writes remain synchronous | ## Tuning Workflow 1. **Identify** slow queries from `pg_stat_statements` (sort by `total_exec_time`) 2. **Analyze** execution plans with `EXPLAIN (ANALYZE, BUFFERS, SETTINGS)` 3. **Check** buffer hit ratios via `pg_statio_user_tables` (target > 99%) 4. **Monitor** I/O patterns via `pg_stat_io` (watch evictions and disk reads) 5. **Optimize** with targeted indexes, work_mem adjustments, or query rewrites 6. **Verify** improvements by re-running EXPLAIN and comparing costs 7. **Maintain** with aggressive autovacuum settings for high-churn tables ## Delegation - **Discover slow queries and I/O bottlenecks**: Use `Explore` agent to analyze pg_stat_statements, pg_stat_io, and slow query logs - **Execute query plan analysis and index optimization**: Use `Task` agent to run EXPLAIN ANALYZE, create indexes, and verify performance improvements - **Design database scaling and partitioning strategy**: Use `Plan` agent to architect sharding, partitioning, and replication topology ## References - [Async I/O configuration and storage tuning](references/aio-tuning.md) - [Query plan analysis and operator forensics](references/query-plan-analysis.md) - [Indexing strategies and bloat management](references/indexing-and-bloat.md) - [Connection pooling, partitioning, and query patterns](references/pooling-and-patterns.md)
© oakoss · MIT · oakoss/agent-skills