postgres-patterns

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

Start by checking pg_stat_statements for queries with high mean execution time, then add appropriate indexes such as composite, covering, or partial indexes. Replace OFFSET pagination with cursor pagination using WHERE id > last_id for O(1) performance.

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 rather than varchar or int.

Wrap the auth function call in a SELECT subquery, for example USING ((SELECT auth.uid()) = user_id). This prevents the function from being re-evaluated for every row, which significantly improves policy performance.

Query pg_constraint joined with pg_attribute for foreign key constraints, then check pg_index to confirm no index covers the constrained columns. The skill provides a ready-to-run SQL query for this detection.

Use SELECT ... FOR UPDATE SKIP LOCKED when implementing queue processing, so multiple workers can claim pending jobs without blocking each other. Combine it with an UPDATE ... RETURNING statement to atomically mark and fetch the next job.