Optimize Postgres Queries with Supabase Best Practices

Supabase Postgres Best Practices is a performance optimization guide for Postgres, maintained by Supabase. It contains rules across 8 categories, prioritized by impact, to guide automated query optimization and schema design. The skill is MIT licensed and comes from the supabase/agent-skills repository.

The categories are ranked in priority order, each with an impact rating and a filename prefix:

1. Query Performance (query-), CRITICAL: slow queries, missing indexes and inefficient query plans, described as the most common source of Postgres performance issues.
2. Connection Management (conn-), CRITICAL: connection pooling, limits and serverless strategies, important for high-concurrency or serverless deployments.
3. Security and RLS (security-), CRITICAL: Row-Level Security policies, privilege management and authentication patterns.
4. Schema Design (schema-), HIGH: table design, index strategies, partitioning and data type selection, the foundation for long-term performance.
5. Concurrency and Locking (lock-), MEDIUM-HIGH: transaction management, isolation levels, deadlock prevention and lock contention.
6. Data Access Patterns (data-), MEDIUM: N+1 query elimination, batch operations, cursor-based pagination and efficient data fetching.
7. Monitoring and Diagnostics (monitor-), LOW-MEDIUM: pg_stat_statements, EXPLAIN ANALYZE, metrics collection and performance diagnostics.
8. Advanced Features (advanced-), LOW: full-text search, JSONB optimization, PostGIS, extensions and other advanced Postgres features.

Each rule lives in its own file under a references folder, and every rule file contains a brief explanation of why it matters, an incorrect SQL example with explanation, a correct SQL example with explanation, optional EXPLAIN output or metrics, additional context and references, and Supabase-specific notes where applicable.

Two rules are bundled in the material. The first, "Add Indexes on WHERE and JOIN Columns" (CRITICAL, with an estimated 100-1000x faster queries on large tables), explains that filtering or joining on unindexed columns causes full table scans that get slower as tables grow. The fix is an index on the frequently filtered column, and for JOINs the foreign key side should be indexed. The second, "Use Partial Indexes for Filtered Queries" (HIGH, with an estimated 5-20x smaller indexes and faster writes and queries), explains that a partial index only includes rows matching a WHERE condition. For example, when queries always filter on active users, the index should exclude soft-deleted rows:

-- Index only includes active users
create index users_active_email_idx on users (email)
where deleted_at is null;

Other partial index uses shown are indexing only pending orders, since status rarely changes once an order is completed, and indexing only non-null values such as a product SKU.

Use it when writing SQL queries or designing schemas, implementing indexes or query optimization, reviewing database performance issues, configuring connection pooling or scaling, optimizing for Postgres-specific features, or working with Row-Level Security.

Its limitations are stated plainly. Use it only when the task clearly matches its upstream product or API scope. Verify commands, API behavior, pricing, quotas, credentials and deployment effects against current official documentation before making changes. Do not treat generated examples as a substitute for environment-specific tests, security review, or user approval for destructive or costly actions.

The input is a query, schema or database configuration to write, review or optimize. To apply a rule, read the individual rule files for detailed explanations and SQL examples; the guide points to files such as query-missing-indexes and query-partial-indexes, plus a sections file that defines the categories. The output is guidance grounded in those rules: which index or schema change to make, with the incorrect and correct forms of the SQL.

The guide is built for Postgres and Supabase. Its references are the official PostgreSQL documentation, the Supabase docs, the PostgreSQL wiki page on performance optimization, the Supabase database overview and the Supabase guide to Row-Level Security.

Developers and database engineers who write or review Postgres queries and schemas, particularly on Supabase, and want prioritized, impact-rated rules with incorrect and correct SQL side by side.