#601

Globales Ranking · von 601 Skills

postgres-tuning AI Agent Skill

Quellcode ansehen: oakoss/agent-skills

Safe

Installation

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

42

Installationen

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

Installationen

Installationen 42
Globales Ranking #601 von 601

Sicherheitsprüfung

ath Safe
socket Safe
Warnungen: 0 Bewertung: 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

So verwenden Sie diesen Skill

1

Install postgres-tuning by running npx skills add oakoss/agent-skills --skill postgres-tuning in your project directory. Führen Sie den obigen Installationsbefehl in Ihrem Projektverzeichnis aus. Die Skill-Datei wird von GitHub heruntergeladen und in Ihrem Projekt platziert.

2

Keine Konfiguration erforderlich. Ihr KI-Agent (Claude Code, Cursor, Windsurf usw.) erkennt installierte Skills automatisch und nutzt sie als Kontext bei der Code-Generierung.

3

Der Skill verbessert das Verständnis Ihres Agenten für postgres-tuning, und hilft ihm, etablierte Muster zu befolgen, häufige Fehler zu vermeiden und produktionsreifen Code zu erzeugen.

Was Sie erhalten

Skills sind Klartext-Anweisungsdateien — kein ausführbarer Code. Sie kodieren Expertenwissen über Frameworks, Sprachen oder Tools, das Ihr KI-Agent liest, um seine Ausgabe zu verbessern. Das bedeutet null Laufzeit-Overhead, keine Abhängigkeitskonflikte und volle Transparenz: Sie können jede Anweisung vor der Installation lesen und prüfen.

Kompatibilität

Dieser Skill funktioniert mit jedem KI-Coding-Agenten, der das skills.sh-Format unterstützt, einschließlich Claude Code (Anthropic), Cursor, Windsurf, Cline, Aider und anderen Tools, die projektbezogene Kontextdateien lesen. Skills sind auf Transportebene framework-agnostisch — der Inhalt bestimmt, für welche Sprache oder welches Framework er gilt.

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
Kundensupport