# Supabase Postgres Best Practices > PostgreSQL performance optimization guidelines from Supabase. Apply when writing SQL, designing schemas, configuring RLS, or optimizing database performance. Use when this capability is needed. - Skill: `tomevault-io/supabase-postgres-best-practices-9` (Agent Skill, multi-file: 2 files) - Install (CLI): `npx skillmds@latest add tomevault-io/supabase-postgres-best-practices-9` - Raw SKILL.md: https://api.skillmd.com/api/skills/tomevault-io/supabase-postgres-best-practices-9/raw - Safety review: pending - Works with: Claude Code, Claude.ai, OpenAI Codex - Category: Data & Analytics - Author: tomevault-io (https://skillmd.com/u/tomevault-io) - Updated: 2026-09-17 - Page: https://skillmd.com/skills/tomevault-io/supabase-postgres-best-practices-9 --- ## Supabase PostgreSQL Best Practices > Source: https://github.com/supabase/agent-skills ### Rule Categories (Prioritized by Impact) | 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- | ### 1. Query Performance (CRITICAL) - Always add indexes for columns used in WHERE, JOIN, and ORDER BY clauses - Use partial indexes for filtered queries: `CREATE INDEX idx_active ON users(email) WHERE active = true` - Prefer `EXISTS` over `IN` for subqueries - Avoid `SELECT *` - specify only needed columns - Use `EXPLAIN ANALYZE` to verify query plans - Add composite indexes for multi-column queries (column order matters) - Use covering indexes to avoid heap lookups ### 2. Connection Management (CRITICAL) - Use Supabase connection pooler (PgBouncer) for serverless/edge functions - Use transaction mode for short-lived queries - Use session mode only when needed (prepared statements, advisory locks) - Set appropriate pool size limits - Release connections promptly - avoid holding connections during external calls - Use connection timeouts to prevent leaks ### 3. Security & RLS (CRITICAL) - Enable RLS on ALL tables exposed via Supabase API - Write policies using `auth.uid()` and `auth.jwt()` - Avoid functions marked `SECURITY DEFINER` unless necessary - Use `SECURITY INVOKER` as default for functions - Never trust client-side data - validate in policies - Test RLS policies with different roles - Use `USING` for read policies, `WITH CHECK` for write policies ### 4. Schema Design (HIGH) - Use appropriate data types (e.g., `uuid` for IDs, `timestamptz` for times) - Add `NOT NULL` constraints where applicable - Use `CHECK` constraints for data validation - Prefer `text` over `varchar(n)` unless length limit is meaningful - Use partial indexes instead of filtered queries - Design schemas for the access patterns, not just the data model ### 5. Concurrency & Locking (MEDIUM-HIGH) - Use `SELECT ... FOR UPDATE SKIP LOCKED` for queue patterns - Keep transactions short to minimize lock contention - Avoid long-running transactions during migrations - Use advisory locks for application-level coordination - Be aware of lock ordering to prevent deadlocks ### 6. Data Access Patterns (MEDIUM) - Use Supabase client libraries for standard CRUD - Use RPC functions for complex operations - Implement pagination with cursor-based approach (not OFFSET) - Use realtime subscriptions judiciously - Batch operations where possible ### 7. Monitoring & Diagnostics (LOW-MEDIUM) - Monitor `pg_stat_statements` for slow queries - Check `pg_stat_user_indexes` for unused indexes - Monitor connection count and pool utilization - Set up alerts for long-running queries - Review lock waits periodically ### 8. Advanced Features (LOW) - Use CTEs for readable complex queries (but note CTE materialization) - Leverage PostgreSQL extensions (pgvector, pg_trgm, etc.) - Use generated columns for computed values - Consider table partitioning for very large tables - Use LISTEN/NOTIFY for event-driven patterns ### References - Supabase Documentation: https://supabase.com/docs - PostgreSQL Official Docs: https://www.postgresql.org/docs/ - Supabase Agent Skills: https://github.com/supabase/agent-skills For detailed rule files with specific examples, see templates/guides/supabase-postgres/. --- > Converted and distributed by [TomeVault](https://tomevault.io/claim/baekenough) — claim your Tome and manage your conversions.