postgres-patterns

Optimize PostgreSQL queries by applying composite indexes for multi-column filters and covering indexes to include data needed by the query, preventing slow performance and table bloat. This skill provides specific best practices for these indexing strategies.

Cursor pagination in PostgreSQL is best implemented using keyset pagination patterns that avoid OFFSET, ensuring stable and efficient traversal of large datasets. This skill outlines the specific SQL patterns required for this approach.

Configure Row-Level Security (RLS) policies in PostgreSQL by applying the security defaults and policy templates provided to restrict row access. This skill includes configuration guidance for enforcing these security boundaries.

PostgreSQL foreign key queries run slow when foreign keys are unindexed, creating an anti-pattern that degrades join and cascade performance. This skill detects unindexed foreign keys and provides the necessary CREATE INDEX statements.

Partial indexes in PostgreSQL are used for conditional queries by indexing only rows matching a WHERE clause, reducing index size and improving query speed. This skill provides patterns for generating partial index statements.

A UPSERT operation in PostgreSQL is needed to insert rows or update existing ones without conflicts, commonly used for queue processing and data synchronization. This skill covers the specific patterns for executing these operations.