#601

Global Rank · of 601 Skills

postgres-tuning AI Agent Skill

View Source: oakoss/agent-skills

Safe

Installation

npx skills add oakoss/agent-skills --skill postgres-tuning

42

Installs

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

Installs

Installs 42
Global Rank #601 of 601

Security Audit

ath Safe
socket Safe
Alerts: 0 Score: 90
snyk Low
EU EU-Hosted Inference API

Power your AI Agents with the best open-source models.

Drop-in OpenAI-compatible API. No data leaves Europe.

Explore Inference API

GLM

GLM 5

$1.00 / $3.20

per M tokens

Kimi

Kimi K2.5

$0.60 / $2.80

per M tokens

MiniMax

MiniMax M2.5

$0.30 / $1.20

per M tokens

Qwen

Qwen3.5 122B

$0.40 / $3.00

per M tokens

How to use this skill

1

Install postgres-tuning by running npx skills add oakoss/agent-skills --skill postgres-tuning in your project directory. Run the install command above in your project directory. The skill file will be downloaded from GitHub and placed in your project.

2

No configuration needed. Your AI agent (Claude Code, Cursor, Windsurf, etc.) automatically detects installed skills and uses them as context when generating code.

3

The skill enhances your agent's understanding of postgres-tuning, helping it follow established patterns, avoid common mistakes, and produce production-ready output.

What you get

Skills are plain-text instruction files — not executable code. They encode expert knowledge about frameworks, languages, or tools that your AI agent reads to improve its output. This means zero runtime overhead, no dependency conflicts, and full transparency: you can read and review every instruction before installing.

Compatibility

This skill works with any AI coding agent that supports the skills.sh format, including Claude Code (Anthropic), Cursor, Windsurf, Cline, Aider, and other tools that read project-level context files. Skills are framework-agnostic at the transport level — the content inside determines which language or framework it applies to.

Data sourced from the skills.sh registry and GitHub. Install counts and security audits are updated regularly.

EU Made in Europe

Chat with 100+ AI Models in one App.

Use Claude, ChatGPT, Gemini alongside with EU-Hosted Models like Deepseek, GLM-5, Kimi K2.5 and many more.

Start for free View pricing
Customer Support