postgres-patterns

Match the query pattern to the index type: use 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, create a composite index with equality columns first, then range columns.

Use bigint for IDs, text instead of varchar(255) for strings, timestamptz instead of timestamp for time values, numeric(10,2) for money, and boolean for flags. Avoid float for currency and int for IDs that may grow large.

Enable the pg_stat_statements extension, then query it for statements with mean_exec_time above a threshold such as 100ms, ordered by mean execution time. This identifies which queries need indexing or rewriting.

RLS policies that call auth.uid() directly re-evaluate the function per row. Wrap the call in a SELECT subquery, like (SELECT auth.uid()) = user_id, so PostgreSQL caches the result and evaluates it once per query.

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.