postgres-advanced-patterns

Start with EXPLAIN ANALYZE to inspect the query plan, then add appropriate indexes such as B-tree for equality, partial indexes for filtered queries, or composite indexes for multi-column lookups. Avoid SELECT * and prefer EXISTS over IN for large subqueries.

Window functions like ROW_NUMBER, RANK, SUM OVER, and LAG/LEAD compute values across row sets without grouping. Use PARTITION BY to rank within categories and frame clauses like ROWS BETWEEN 6 PRECEDING AND CURRENT ROW for moving averages.

Use GIN indexes for JSONB containment queries and full-text search on tsvector columns. Use GiST for geometric data, range exclusion constraints, and tsvector search when index size matters more than build speed.

Yes, PostgreSQL supports declarative range, list, and hash partitioning. Create a parent table with PARTITION BY RANGE on a column like created_at, then attach child partitions for specific value ranges to improve query pruning and maintenance.

Use SELECT ... FOR UPDATE SKIP LOCKED to let multiple workers claim rows without blocking each other. Combine with appropriate transaction isolation levels like SERIALIZABLE for critical operations or REPEATABLE READ for consistent snapshots.

Materialized views store stale snapshot data and require manual or scheduled REFRESH to update. REFRESH MATERIALIZED VIEW CONCURRENTLY avoids locking reads but requires a unique index on the view.