Supabase Postgres Best Practices

Postgres best practices

Use for SQL/schema reviews, slow queries, connection issues, RLS and database diagnostics. Read only the relevant rules below. These are examples, not commands to execute automatically: adapt table names, roles, data types and workload to the actual project. Example speedups and plans are illustrative, not measurements of the user's database. Verify version-dependent behavior against current PostgreSQL/Supabase documentation.

Use PI Harness read-only gateway capabilities for connected project inspection. SQL execution requires an available, configured local Supabase CLI or psql through run_command, with current approvals and the correct target. Reading a rule does not authorize a migration, privilege change, statistics reset or database maintenance. EXPLAIN ANALYZE actually executes its statement; choose bounded read queries and avoid executing writes merely to inspect their plan.

Rule index

Query performance

• Create Composite Indexes for Multi-Column Queries
• Use Covering Indexes to Avoid Table Lookups
• Choose the Right Index Type for Your Data
• Add Indexes on WHERE and JOIN Columns
• Use Partial Indexes for Filtered Queries

Connection management

• Configure Idle Connection Timeouts
• Set Appropriate Connection Limits
• Use Connection Pooling for All Applications
• Use Prepared Statements Correctly with Pooling

Security and RLS

• Apply Principle of Least Privilege
• Enable Row Level Security for Multi-Tenant Data
• Optimize RLS Policies for Performance

Schema design

• Add Constraints Safely in Migrations
• Choose Appropriate Data Types
• Index Foreign Key Columns
• Use Lowercase Identifiers for Compatibility
• Partition Large Tables for Better Performance
• Select Optimal Primary Key Strategy

Concurrency and locking

• Use Advisory Locks for Application-Level Locking
• Prevent Deadlocks with Consistent Lock Ordering
• Keep Transactions Short to Reduce Lock Contention
• Use SKIP LOCKED for Non-Blocking Queue Processing

Data access

• Batch INSERT Statements for Bulk Data
• Eliminate N+1 Queries with Batch Loading
• Use Cursor-Based Pagination Instead of OFFSET
• Use UPSERT for Insert-or-Update Operations

Monitoring

• Use EXPLAIN ANALYZE to Diagnose Slow Queries
• Enable pg_stat_statements for Query Analysis
• Maintain Table Statistics with VACUUM and ANALYZE

Advanced features

• Use tsvector for Full-Text Search
• Index JSONB Columns for Efficient Querying

Verification

Compare plans and timings on representative data, check index/write overhead and connection limits, and verify allowed and denied RLS cases under actual client roles. A superuser query is not an RLS test. Preserve supplied schemas and generate migrations only for authorized changes. For hosted services, check which configuration operations are supported before using administrative SQL examples.

License

Rules are adapted from Supabase under the MIT license.