AgentSkillsCN

postgresql

精通PostgreSQL DBA技能，擅长表结构设计评审、索引优化、查询调优（通过EXPLAIN/ANALYZE分析）、Vacuum与AutoVacuum管理、并发与锁机制、分区策略、备份与恢复、复制与高可用架构，以及安全的迁移操作。适用于诊断慢查询、设计索引与约束条件，以及在生产环境中稳定运行PostgreSQL数据库等任务。

SKILL.md
--- frontmatter
name: postgresql
description: PostgreSQL DBA skill for schema design review, indexing, query tuning (EXPLAIN/ANALYZE), vacuum/autovacuum, concurrency/locking, partitioning, backup/restore, replication/HA, and safe migrations. Use for tasks like diagnosing slow queries, designing indexes and constraints, and operating Postgres in production.

postgresql

Use this skill for PostgreSQL 相关设计、性能与运维（DBA）任务。

Defaults / assumptions to confirm

  • •Postgres version
  • •Deployment: managed vs self-hosted, single instance vs HA
  • •Connection pooler: pgbouncer?
  • •Workload: OLTP vs OLAP, write-heavy vs read-heavy

Workflow

  1. •Understand workload and query paths
  • •Core tables, top queries, read/write ratio, growth rate.
  • •Latency SLO and peak hours.
  1. •Schema review
  • •Primary key strategy (string IDs vs bigint; be explicit about external IDs).
  • •Types: TIMESTAMPTZ for time, NUMERIC vs BIGINT vs DECIMAL trade-offs.
  • •Constraints: NOT NULL, CHECK, UNIQUE where needed.
  • •JSONB usage: keep structure stable; consider normalization vs JSONB.
  • •Comments: require COMMENT ON TABLE/COLUMN for long-lived schemas.
  1. •Index design
  • •Add indexes for WHERE/JOIN/ORDER BY patterns.
  • •Composite indexes aligned with left-prefix.
  • •Partial indexes for sparse predicates.
  • •GIN for JSONB/array search; btree_gin/pg_trgm if used.
  • •Avoid redundant indexes and over-indexing on write-heavy tables.
  1. •Query tuning
  • •Use EXPLAIN (ANALYZE, BUFFERS) to validate plans.
  • •Watch for seq scans, bad estimates, bloated tables, missing stats.
  • •Consider query rewrites, better predicates, and covering indexes.
  1. •Concurrency and locking
  • •Inspect lock contention; avoid long transactions.
  • •Use appropriate isolation; detect deadlocks and hot rows.
  1. •Maintenance (vacuum / bloat)
  • •Ensure autovacuum is effective; tune thresholds per table if needed.
  • •Monitor bloat and n_dead_tup; use VACUUM (ANALYZE) and reindex when justified.
  1. •Partitioning & scaling
  • •Partition only when there is pruning benefit and operational plan.
  • •Time-based partitions for append-only logs; ensure indexes per partition.
  • •Consider sharding only with strong requirements and stable shard key.
  1. •Operations
  • •Backups: base backup + WAL archiving; restore drills; retention.
  • •Replication: streaming replication, lag monitoring, failover runbook.
  • •Migrations: safe rollout steps, lock-time considerations, backout plan.

Outputs

  • •Index/constraint plan (query → index/constraint → impact/tradeoff).
  • •Migration plan (DDL, rollout sequence, verification, rollback).
  • •Performance report (evidence, root cause, fixes, metrics to monitor).