postgresql-optimization

Start by running EXPLAIN (ANALYZE, BUFFERS) to capture the baseline execution plan and latency. Look for sequential scans on large tables, then add an appropriate index or rewrite the query, and confirm the improvement with a second EXPLAIN run.

Create a GIN index on the JSONB column and use containment operators like @> instead of casting to text with LIKE. This lets PostgreSQL use the index for key and path lookups instead of scanning every row.

Use composite B-tree indexes for multi-column filters and ordering, partial indexes for filtered queries, expression indexes for computed values, and GIN indexes for JSONB, arrays, and full-text search. Each index should be justified against its write cost.

OFFSET pagination degrades on large datasets because the database scans and discards skipped rows. Keyset (cursor-based) pagination using WHERE id > last_id with ORDER BY and LIMIT performs consistently regardless of page depth.

Enable the pg_stat_statements extension and query it ordered by total_time to identify the most expensive statements. It reports calls, mean time, rows, and buffer hit percentages for each normalized query.

Avoid indexes that duplicate existing ones, target rarely queried columns, or remain unused according to pg_stat_user_indexes. Every index adds write overhead and storage cost, so each one must be justified by measured query patterns.