postgres-patterns

Match the query pattern to the index type: B-tree for equality and range comparisons, composite indexes for multi-column filters, GIN for jsonb containment and full-text search, and BRIN for time-series ranges. Put equality columns before range columns in composite indexes.

Enable the pg_stat_statements extension and query it for statements with high mean_exec_time, for example filtering where mean_exec_time exceeds 100 milliseconds. Sort by mean_exec_time descending to find the worst offenders.

Use bigint for IDs, text instead of varchar(255) for strings, timestamptz instead of timestamp for time values, numeric(10,2) for money instead of float, and boolean for flags. These choices avoid precision, timezone, and storage pitfalls.

Wrap the auth function call in a SELECT subquery, such as USING ((SELECT auth.uid()) = user_id). This prevents the function from being re-evaluated for every row, significantly improving policy performance on large tables.

OFFSET pagination is O(n) because the database must scan and discard all skipped rows. Use cursor (keyset) pagination instead, filtering with WHERE id > last_id and ordering by id, which runs in O(1) with an index.