postgresql-best-practices_skill

Overview

This skill captures and applies OpenAI’s PostgreSQL scaling approach exactly from the OpenAI post "Scaling PostgreSQL to the next level" (January 22, 2026). It preserves all named technologies, numbers, dates, section order, and terminology from the source with strict fidelity. Use this skill when building, reviewing, or planning PostgreSQL architecture, partitioning/indexing, query optimization, schema migrations, replication, and load balancing.

How this skill works

The skill distills the source into operational guidance organized into the exact sections required by the source: Use Postgres for everything; Partitioning and indexing strategy; Query optimization; Schema management; Replication and load balancing; Results. It reports and enforces the key facts, technologies, metrics, and workflows from the source without adding outside recommendations or interpretations. If a detail is not in the source, it will explicitly state: Not stated in the source.

When to use it

• When designing platform strategy to keep PostgreSQL as the core datastore
• When planning distributed or analytical workloads requiring Citus or Citus columnar
• When addressing scaling from a baseline of over 4 billion queries per day and over 2,000 database instances
• When optimizing schemas and queries to support over 1 billion monthly users and over 800 million weekly users
• When implementing migrations, replication, and failover with strict source fidelity

Best practices

• Preserve section order and intent exactly as the source specifies
• Apply partitioning and indexing strategies from the source: hash partitioning for large event/log tables, time-range partitioning for append-only datasets, composite index strategy
• Use the named tools and integrations exactly: Citus, Citus columnar, PgBouncer, PgAnalyze, Patroni, Haste, pgroll, pgai

Example use cases

• Plan partitioning and indexing for high-throughput event tables using hash partitioning and composite indexes
• Design analytical pipelines using Citus and Citus columnar and dedicated analytical replicas
• Run migration workflows: canary rollout, async backfills with throttling and checkpointing, automatic rollback on failing health checks using Haste and pgroll
• Optimize query workload: deploy query fingerprinting, execution stats, EXPLAIN-based review workflows, statement-level timeouts and retries
• Build replication and failover: region-aware read replicas, replica lag monitoring and query shedding, failover orchestration with Patroni and PgBouncer integration