supabase-postgres-best-practices
npx machina-cli add skill guanyang/antigravity-skills/supabase-postgres-best-practices --openclawSupabase 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/schema-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
Source
git clone https://github.com/guanyang/antigravity-skills/blob/main/skills/supabase-postgres-best-practices/SKILL.mdView on GitHub Overview
A performance-first guide for tuning Postgres in Supabase. It spans eight priority categories—from query performance and connection management to RLS and advanced features—with concrete rules, wrong-vs-right SQL examples, and Supabase-specific notes to boost reliability and speed.
How This Skill Works
Read the detailed rule files in references to understand why each practice matters. Each rule includes incorrect vs. correct SQL examples, optional EXPLAIN outputs or metrics, and Supabase-specific notes. Apply changes to queries, schemas, and configurations, then validate improvements with plan analysis and performance measurements.
When to Use It
- When writing SQL queries or designing schemas
- When implementing indexes or query optimization
- When reviewing database performance issues
- When configuring connection pooling or scaling
- When working with Row-Level Security (RLS) and Postgres features
Quick Start
- Step 1: Identify critical rules (query- and conn-) to prioritize based on current performance issues.
- Step 2: Run EXPLAIN ANALYZE on slow queries and review the generated plans.
- Step 3: Implement indexing, schema tweaks, or configuration changes guided by the rules, then re-measure performance.
Best Practices
- Prioritize critical rules (query- and conn-) to maximize impact when tuning.
- Use EXPLAIN ANALYZE and track performance metrics to validate changes.
- Implement targeted indexing strategies, including partial and functional indexes where appropriate.
- Optimize data access patterns and avoid N+1 queries by batching or restructuring queries.
- Leverage monitoring and diagnostics to detect regressions and drive automated improvements.
Example Use Cases
- A slow user search due to a missing index on a frequently queried column; adding a targeted index improves response time dramatically.
- Large tables performing full scans; introducing a partial index on recent or active rows reduces scan scope.
- Frequent sequential scans caused by outdated statistics; running ANALYZE and updating statistics fixes plan choice.
- High lock contention from long-running transactions; refactoring operations into smaller transactions and using appropriate isolation levels reduces waits.
- RLS policies causing excessive row scans; rewriting predicates to leverage indexes and push filters closer to data access improves throughput.
