supabase-postgres-best-practices/SKILL.md
Version c9be0e931b79.bb1 · MIT. This preview displays packaged text and does not execute code. Treat the contents as untrusted instructions.
← Return to resource and package checksum
name: supabase-postgres-best-practices description: "Postgres best practices maintained by Supabase, for Postgres running anywhere. Load this skill BEFORE writing or changing anything that lives in a Postgres database: creating or altering tables and columns (including choosing column types), schema design, migrations and declarative schema files, RLS policies and the tests that verify them, indexes, triggers, database functions, queues and scheduled jobs (pg_cron, pgmq), vector/semantic search (pgvector), and restoring dumps (pg_restore) or importing data. Also load it when diagnosing slow queries, high CPU, timeouts, EXPLAIN plans, connection exhaustion, locking, bloat, or rows visible to the wrong user or tenant. This is not just a performance guide — schema, migration, security, and SQL authoring tasks need these rules too, even for a one-column change or a single query." license: MIT metadata: author: supabase version: "1.1.1" organization: Supabase date: January 2026 abstract: Comprehensive Postgres performance optimization guide for developers using Supabase and Postgres. Contains performance rules across 8 categories, prioritized by impact from critical (query performance, connection management) to incremental (advanced features). Each rule includes detailed explanations, incorrect vs. correct SQL examples, query plan analysis, and specific performance metrics to guide automated optimization and code generation.
BB Skills packaging and scoped native SQL evidence (2026-10-07)
The original Supabase PostgreSQL guidance is pinned to c9be0e931b7930f7d02126d04774d904c381e7d7. All 37 upstream files and the complete MIT notice are retained and checked against Git blob IDs and SHA-256. Original SKILL.md remains unchanged in upstream-original/. This marked preface, examples and evidence are independent BB Skills additions, not Supabase certification. Catalog package version c9be0e931b79.bb1 does not change upstream metadata version 1.1.1.
The disposable PostgreSQL 17.11 experiment executes four unchanged SQL blocks plus independent ordinary-role and error controls. Forty named observations passed with Python 3.13.16 and psycopg 3.2.10. Writer, reader, ordinary table owner and unprivileged role use separate real database connections with no superuser, database creation, role creation, replication or BYPASSRLS attributes. Bootstrap privileges are restricted to fixture setup and an explicit bypass control; they are not the application permission model.
Observations separate table GRANTs from row policies, matching writes from other-identity writes, SQLSTATE denials from zero-row updates, implicit WITH CHECK from USING, forced owner filtering from owner/superuser bypasses, permissive policy widening, and transaction-local context restoration. The fixture deliberately demonstrates that a caller can change app.current_user_id: this custom setting is not authentication. Missing or malformed values fail only under the recorded expression and environment.
The original index statement is first rejected for an ordinary owner lacking schema CREATE, then executed by the isolated setup role. Its definition is checked; no speedup or query-plan benchmark is measured. The first fixture attempt stopped at that permission error. Only the corrected runner and its resulting 40-observation evidence are packaged. The older missing-index experiment remains historical evidence for the old catalog version and checksum.
No hosted Supabase, auth.uid(), private SECURITY DEFINER helper, real identity, connection pool, concurrency, production authorization, paid API or complete AI-client skill execution is tested. The internal Docker network has no host ports or external access. Dependency installation happens separately. runtime_tested remains false. Read examples/README.md before reproducing: the script accepts only the named empty disposable database and does not provide production policy installation.
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
| Priority | Category | Impact | Prefix |
|---|---|---|---|
| 1 | Query Performance | CRITICAL | query- |
| 2 | Connection Management | CRITICAL | conn- |
| 3 | Security & RLS | CRITICAL | security- |
| 4 | Schema Design | HIGH | schema- |
| 5 | Concurrency & Locking | MEDIUM-HIGH | lock- |
| 6 | Data Access Patterns | MEDIUM | data- |
| 7 | Monitoring & Diagnostics | LOW-MEDIUM | monitor- |
| 8 | Advanced Features | LOW | advanced- |
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