Token导航 LogoToken导航TokenDH.com
中转站排行榜
研究检索只读github未标认证来源可访问许可证需确认审计通过

postgres-tuning（Postgres tuning 搜索）

Agent Skill

用于辅助数据库表结构、查询语句、迁移脚本和数据维护任务。它适合让 Agent 分析 schema、编写 SQL、排查查询问题、整理索引或生成迁移建议。使用时需要明确数据库类型、连接环境和目标表，区分只读分析与写入变更；涉及删除、更新、迁移和批量导入时，应优先 dry-run、备份或事务保护，避免误操作。

总安装

1,022

周安装

43

GitHub Stars

10

下载量

358
CodexClaudeCursorGemini CLI

安装说明

本站只整理中文说明和来源信息，不托管安装包，也不代用户安装。

GitHub

来源数

2

许可证

unknown

最后核验

2026-05-01

来源状态

来源可访问

安装方式

通过对话安装

复制提示词发给支持本地命令或 Skills 的 AI 助手，先确认命令和权限，再让它执行。

请帮我安装这个 Agent Skill：postgres-tuning（Postgres tuning 搜索）
来源仓库：https://github.com/oakoss/agent-skills
仓库路径：skills/postgres-tuning
安装命令：
npx skills add https://github.com/oakoss/agent-skills --skill postgres-tuning
安装前请先检查当前环境是否支持对应 CLI，并向我确认将要执行的命令、安装目录、联网范围和文件读写权限；确认后再执行。

命令行安装

复制命令到本机终端执行。该命令会通过 npx skills 从第三方来源获取 Skill；本站只展示命令，不托管安装包，也不自动执行。

skills.shnpx skills
npx skills add https://github.com/oakoss/agent-skills --skill postgres-tuning

简介

用于辅助数据库表结构、查询语句、迁移脚本和数据维护任务。

  • 适合分析 schema、编写 SQL、排查查询性能或生成索引优化建议。
  • 使用时需明确数据库连接环境和目标表，区分只读分析与写入变更。
  • 涉及删除、更新、迁移或批量导入时，应优先 dry-run、备份或启用事务保护。
  • 建议结合数据库负载和使用模式使用，确保调优建议具备实际可操作性和稳定性。

SKILL.md

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

PatternConfiguration / QueryKey Points
Async I/Oio_method = worker or io_uringPG18 default is worker; io_uring Linux-only (kernel 5.1+, requires liburing build flag)
I/O concurrencyio_max_concurrency and io_workersio_workers defaults to 3; io_max_concurrency defaults to -1 (auto-calculated)
Forensic EXPLAINEXPLAIN (ANALYZE, BUFFERS, SETTINGS)PG18 auto-includes BUFFERS with ANALYZE; target Shared Hit > 95%
UUIDv7 primary keysDEFAULT uuidv7()PG18 built-in; time-ordered, monotonic within a session; RFC 9562 compliant
B-tree skip scanComposite index on (a, b)PG18 skips leading column; works best with low-cardinality prefix and equality on trailing columns
Aggressive autovacuumautovacuum_vacuum_scale_factor = 0.01Triggers at 1% row change instead of default 20%
Shared buffersStart at 25% of RAMDo not exceed 40% without benchmarking
work_mem tuningSET work_mem = '64MB' per sessionPrevents sort spills to disk; allocated per operator, not per query
BRIN indexCREATE INDEX USING brin(...)100x smaller than B-tree for physically ordered time-series data
HNSW vector indexUSING hnsw (col vector_cosine_ops)Tune m (default 16) and ef_construction (default 64) for recall vs speed
GIN indexCREATE INDEX USING gin(...)JSONB containment, full-text search, array operators; slower writes
Checkpoint tuningcheckpoint_timeout = 30minSpread writes over 90% of timeout window to avoid I/O storms
WAL compressionwal_compression = zstdAvailable since PG15; reduces WAL I/O 50-70% for write-heavy workloads
Bloat detectionpg_stat_user_tables.n_dead_tupReindex concurrently if bloat > 30%
I/O monitoringSELECT * FROM pg_stat_ioWatch evictions (cache too small) and extends (fast growth)
Checkpoint monitoringpg_stat_checkpointerPG17+ 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

MistakeCorrect Pattern
Using uuid_generate_v7() or gen_random_uuid() for ordered keysPG18 provides built-in uuidv7() for time-ordered UUIDs; pre-PG18 use pg_uuidv7 extension
Using max_async_ios as a configuration parameterThe 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 queriesSelect only needed columns to reduce I/O and improve cache hit ratios
Ignoring sequential scans on tables over 10k rowsAdd targeted indexes on columns used in WHERE, ORDER BY, and JOIN clauses
Setting shared_buffers above 40% of RAM without testingStart at 25% and benchmark; excessive allocation causes OS page cache contention
Leaving autovacuum at default settings for high-churn tablesTune autovacuum_vacuum_scale_factor to 0.01 for tables with frequent UPDATE/DELETE
Over-indexing columns rarely used in queriesEvery extra index slows UPDATE/INSERT and prevents HOT (Heap Only Tuple) updates
Expecting B-tree skip scan to work with range predicatesPG18 skip scan only works with equality operators on trailing columns
Ignoring "External Merge Disk" in query plansIncrease work_mem for specific sessions; it indicates sort spills to disk
Setting io_method = io_uring without verifying build flagsPostgreSQL must be built with --with-liburing and requires Linux kernel 5.1+
Assuming PG18 AIO accelerates writesAIO 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

适合场景

01

用户想查找某类 Agent Skill 时

02

需要根据任务场景推荐可安装能力包时

03

需要对比不同来源的安装命令和来源信息时

能力概览

能力 1

按任务关键词查找相关 Skills

能力 2

展示可复制的安装命令

能力 3

保留来源站点、仓库和原始说明，方便继续核验

能力 4

展示第三方安全扫描或审计结果

安装后应在对应宿主中按原始 README 的触发条件使用；具体调用方式请以来源页面和 README 为准。

平台分布

Codex

35.25%
按下载量换算126

Claude

29.99%
按下载量换算107

Cursor

16.78%
按下载量换算60

Gemini CLI

9%
按下载量换算32

安全审计

Gen Agent Trust Hub

通过

Socket

通过

Snyk

通过

权限和风险

只读

该 Skill 主要提供规则、说明或参考内容，本身偏只读；真正读写文件、联网或执行命令仍取决于宿主 Agent 的任务。

安装前确认

本站仅展示第三方公开信息，不托管安装包，不提供自动安装或运行环境。安装前应自行审查源码、依赖和命令行为。当前只有一个来源，正式发布前建议补源仓库或其他目录站核验。

来源信息

GitHub 仓库

github.com

/oakoss/agent-skills

SKILL.md 来源

github.com

/oakoss/agent-skills/tree/main/skills/postgres-tuning

skills-sh

skills.sh

/oakoss/agent-skills/postgres-tuning

继续浏览同类 Skills

更多研究检索 Skills（73,675）返回 Skills 目录

上一个 Skill

postgres-tool（Postgres tool 搜索）

用于辅助数据库表结构、查询语句、迁移脚本和数据维护任务。它适合让 Agent 分析 schema、编写 SQL、排查查询问题、整理索引或生成迁移建议。使用时需要明确数据库类型、连接环境和目标表，区分只读分析与写入变更；涉及删除、更新、迁移和批量导入时，应优先 dry-run、备份或事务保护，避免误操作。

下一个 Skill

postgres-tuning（Postgres tuning 搜索）

用于辅助数据库表结构、查询语句、迁移脚本和数据维护任务。它适合让 Agent 分析 schema、编写 SQL、排查查询问题、整理索引或生成迁移建议。使用时需要明确数据库类型、连接环境和目标表，区分只读分析与写入变更；涉及删除、更新、迁移和批量导入时，应优先 dry-run、备份或事务保护，避免误操作。