postgresql-development

When to Use

• Designing PostgreSQL schemas with proper constraints, types, and indexes.
• Writing advanced queries using CTEs, window functions, JSONB, or array operations.
• Analyzing and optimizing slow queries with EXPLAIN ANALYZE.
• Setting up indexing strategies (B-tree, GIN, GiST, partial, covering indexes).
• Configuring connection pooling with PgBouncer or built-in poolers.
• Managing migrations, transactions, isolation levels, and data integrity.
• Tuning PostgreSQL server parameters for workload characteristics.
• Backup, restore, and disaster recovery with pg_dump and pg_basebackup.

Critical Patterns

• Constraints at the Database Level: Enforce NOT NULL, CHECK, UNIQUE, and foreign keys in the schema — never rely solely on application-level validation. The database is the last line of defense.
• Index Deliberately: Every index has a write cost. Create indexes based on actual query patterns from EXPLAIN ANALYZE, not speculation. Monitor with pg_stat_user_indexes to find unused ones.
• Use Appropriate Types: Use uuid (not VARCHAR) for identifiers, timestamptz (not timestamp) for times, text (not VARCHAR(n)) for variable strings, numeric for money, and jsonb (not json) for document data.
• CTEs Are Not Optimization Barriers (since PG 12): PostgreSQL can inline CTEs. Use them freely for readability. Add MATERIALIZED only when you explicitly need to force a single evaluation.
• Connection Pooling Is Mandatory: PostgreSQL forks a process per connection. Always use PgBouncer (or Supavisor, pgcat) in front of PostgreSQL in production. Target max 100-200 actual connections.
• Transactions Should Be Short: Long-running transactions hold locks and block autovacuum. Keep transactions under a few seconds. For bulk operations, use batched commits.
• Always EXPLAIN ANALYZE: Never guess at query performance. Use EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) to see actual row counts, buffer hits, and execution time.