PostgreSQL Database Expertise

Key Principles

• Always analyze query plans with EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) before and after optimization
• Choose the right index type for the access pattern: B-tree for equality and range, GIN for full-text and JSONB, GiST for geometric and range types, BRIN for naturally ordered large tables
• Normalize to third normal form by default; denormalize deliberately with materialized views or JSONB columns when read performance demands it
• Use transactions appropriately; keep them short to reduce lock contention and MVCC bloat
• Monitor with pg_stat_statements for slow query identification and pg_stat_user_tables for sequential scan detection

Techniques

• Write CTEs with

  WITH

  for readability but be aware that prior to PostgreSQL 12 they act as optimization barriers; use

  MATERIALIZED

  /

  NOT MATERIALIZED

  hints when needed
• Apply window functions like

  ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC)

  for top-N-per-group queries
• Use JSONB operators (

  ->

  ,

  ->>

  ,

  @>

  ,

  ?

  ) with GIN indexes for semi-structured data stored alongside relational columns
• Implement table partitioning with

  PARTITION BY RANGE

  on timestamp columns for time-series data; combine with partition pruning for fast queries
• Run

  VACUUM (VERBOSE)

  and

  ANALYZE

  after bulk operations; configure

  autovacuum_vacuum_scale_factor

  per-table for heavy-write tables
• Use

  pgbouncer

  in transaction pooling mode to handle thousands of short-lived connections without exhausting PostgreSQL backend processes

Common Patterns

• Covering Index: Add

  INCLUDE (column)

  to an index so that queries can be satisfied from the index alone without heap access (index-only scan)
• Partial Index: Create

  CREATE INDEX ON orders (created_at) WHERE status = 'pending'

  to index only the rows that queries actually filter on
• Upsert with Conflict: Use

  INSERT ... ON CONFLICT (key) DO UPDATE SET ...

  for atomic insert-or-update operations without application-level race conditions
• Advisory Locks: Use

  pg_advisory_lock(hash_key)

  for application-level distributed locking without creating dedicated lock tables

Pitfalls to Avoid

• Do not use

  SELECT *

  in production queries; specify columns explicitly to enable index-only scans and reduce I/O
• Do not create indexes on every column preemptively; each index adds write overhead and vacuum work proportional to the table's update rate
• Do not use

  NOT IN (subquery)

  with nullable columns; it produces unexpected results due to SQL three-valued logic; use

  NOT EXISTS

  instead
• Do not set

  work_mem

  globally to a large value; it is allocated per-sort-operation and can cause OOM with concurrent queries; set it per-session for analytical workloads