postgresql-database-engineering_skill

Overview

This skill delivers professional PostgreSQL database engineering guidance for designing, optimizing, and operating production-grade PostgreSQL systems. It covers indexing strategies, query optimization, partitioning, replication, backup and recovery, and high-availability patterns. It is targeted at engineers running high-scale workloads and teams responsible for operational reliability.

How this skill works

The skill inspects schema design, index choices, query plans, and configuration settings and recommends changes based on workload characteristics and cardinality statistics. It explains how to use EXPLAIN ANALYZE, interpret planner estimates, tune memory/WAL/checkpoint parameters, and apply partitioning or replication patterns. It also provides maintenance guidance for VACUUM, autovacuum tuning, connection pooling, and backup/recovery procedures.

When to use it

• Designing schemas and index strategies for performance-sensitive applications
• Diagnosing slow queries and reducing query latency with EXPLAIN ANALYZE
• Planning partitioning for very large tables (100M+ rows) or time-series data
• Configuring streaming or logical replication and planning failover/HA
• Tuning PostgreSQL server parameters for production workloads
• Implementing backup, restore, and disaster recovery processes

Best practices

• Collect and act on accurate statistics: run ANALYZE after bulk loads and set appropriate statistics targets
• Prefer appropriate index types: B-tree for ranges/equality, GIN for jsonb/arrays, BRIN for append-only time-series
• Order composite index columns: equality columns first, range/sort columns last
• Monitor autovacuum and tune thresholds to prevent bloat and transaction wraparound
• Use pgBouncer or PgPool for connection pooling and constrain max_connections at the server
• Test major changes (indexes, partitioning, config) with representative data and EXPLAIN ANALYZE

Example use cases

• Create a partial GIN index on recent JSONB entries to speed common queries while keeping update cost low
• Partition a large events table by month using range partitions and enable partition pruning for fast time-range queries
• Tune work_mem and shared_buffers for a reporting workload performing large hash joins and sorts
• Set up streaming replication with one synchronous standby and automated failover using Patroni
• Detect and remove unused indexes by querying pg_stat_user_indexes to reduce write overhead