Supabase Postgres Best Practices

Supabase Postgres Best Practices

Comprehensive performance optimization guide for Postgres, maintained by Supabase. Contains rules across 8 categories, prioritized by impact to guide automated query optimization and schema design.

When to Apply

Reference these guidelines 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
• Working with Row-Level Security (RLS)

Rule Categories by Priority

How to Use

Read individual rule files for detailed explanations and SQL examples:

references/query-missing-indexes.md
references/query-partial-indexes.md
references/_sections.md

Each rule file contains:

• Brief explanation of why it matters
• Incorrect SQL example with explanation
• Correct SQL example with explanation
• Optional EXPLAIN output or metrics
• Additional context and references
• Supabase-specific notes (when applicable)

References

• https://www.postgresql.org/docs/current/
• https://supabase.com/docs
• https://wiki.postgresql.org/wiki/Performance_Optimization
• https://supabase.com/docs/guides/database/overview
• https://supabase.com/docs/guides/auth/row-level-security

Review Workflow

Select only the rule files that match the task. Start with correctness and access control, then measure performance before proposing tuning changes.

1. Identify the database version, hosting constraints, workload shape, and whether the task changes data or only reviews SQL.
2. Read the relevant query-, conn-, security-, schema-, lock-, data-, or monitor- references; avoid loading the full rule set by default.
3. Preserve existing RLS and privilege boundaries. Treat disabling RLS, broadening grants, destructive DDL, and production writes as separate, explicitly authorized actions.
4. For performance claims, capture the query shape and an EXPLAIN or EXPLAIN (ANALYZE, BUFFERS) result in a safe environment. Do not infer an improvement from the presence of an index alone.
5. Verify migrations on a representative schema, including rollback or a forward-fix path, lock duration, and compatibility with concurrent traffic.

Acceptance Evidence

• Query tuning: compare plans and measured latency or buffer usage under the same parameters and representative data distribution.
• Index changes: confirm intended scans use the index and account for write, storage, and maintenance overhead.
• RLS or privilege changes: test allowed and denied paths with the actual roles; service-role success is not proof that end-user policies work.
• Connection changes: verify pool mode, transaction semantics, prepared statement compatibility, connection limits, and saturation behavior.
• Schema changes: verify constraints, backfill behavior, lock exposure, and the recovery procedure before production rollout.

Boundaries

• Never run EXPLAIN ANALYZE on a mutating statement in production without an explicitly safe transaction and authorization; plain EXPLAIN is the safer default for uncertain statements.
• Do not present generic PostgreSQL advice as a Supabase platform guarantee.
• Static SQL review and local tests do not prove production capacity, failover, replication health, or zero-downtime migration behavior.