postgresql

muzhicaomingwang's avatarfrom muzhicaomingwang

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.

⭐1stars🔀0forks📁View on GitHub🕐Updated Jan 10, 2026
[SQL and Databases]

When & Why to Use This Skill

This PostgreSQL DBA skill provides comprehensive expertise for optimizing database performance, reliability, and scalability. It streamlines complex tasks such as schema design, query tuning using EXPLAIN/ANALYZE, and high-concurrency management. By offering structured workflows for indexing, partitioning, and safe migrations, it helps developers and DBAs eliminate performance bottlenecks and maintain healthy production environments.

Use Cases

  • Query Optimization: Diagnosing slow-running queries and implementing efficient indexing strategies (B-tree, GIN, Partial) to reduce latency.
  • Schema Architecture: Designing robust database schemas with proper data types (JSONB, TIMESTAMPTZ) and constraints to ensure data integrity and long-term maintainability.
  • Database Maintenance: Tuning autovacuum settings and managing table bloat to ensure consistent performance under heavy workloads.
  • Safe Production Migrations: Planning and executing DDL changes with detailed rollout and backout strategies to minimize downtime and risk.
  • Scaling & High Availability: Implementing table partitioning for large datasets and managing replication/failover configurations for mission-critical applications.
namepostgresql
descriptionPostgreSQL 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).