postgres-patterns

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

Wrap auth function calls in a SELECT subquery, such as USING ((SELECT auth.uid()) = user_id), so the planner evaluates it once per query instead of once per row. This significantly improves RLS policy performance.

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

Enable the pg_stat_statements extension and query it for statements with mean_exec_time above your threshold, ordered by average execution time. The skill provides a ready-made query filtering statements over 100ms.

OFFSET pagination scans and discards all skipped rows, making it O(n) as page depth grows. Cursor pagination using WHERE id > last_id with ORDER BY and LIMIT runs in O(1) regardless of position.

Query pg_constraint joined with pg_attribute for foreign key constraints, then check pg_index for a matching index. The skill includes a detection query that returns tables and columns lacking foreign key indexes.