postgres-patterns

Match the query pattern to the index type: B-tree for equality and range comparisons, GIN for jsonb containment and full-text search, and BRIN for time-series ranges. For multi-column filters, use a composite index with equality columns first, then range columns.

Enable the pg_stat_statements extension, then query it for statements where mean_exec_time exceeds your threshold, ordered by mean_exec_time descending. This surfaces the slowest queries along with call counts so you can prioritize optimization.

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

Cursor pagination using WHERE id > last_id with ORDER BY and LIMIT runs in O(1) time, while OFFSET scans and discards rows in O(n) time. For large tables or infinite-scroll APIs, cursor pagination is the recommended approach.

RLS policies that call auth.uid() directly re-evaluate the function for every row. Wrapping it in a subselect, like (SELECT auth.uid()) = user_id, lets the planner cache the value and dramatically improves policy performance.

Query pg_constraint joined with pg_attribute for foreign key constraints, then check pg_index for a matching index on those columns. Foreign keys without indexes cause slow joins and locking issues during deletes on the referenced table.