Supabase Postgres Best Practices

Comprehensive Postgres performance guide organized by impact priority. Each rule includes incorrect vs. correct SQL examples with explanations.

When to Use

• Writing SQL queries or designing schemas
• Implementing or reviewing indexes
• Debugging slow queries or connection issues
• Configuring connection pooling
• Implementing Row-Level Security (RLS)
• Reviewing database performance

Rule Categories by Priority

See references/ for detailed rule files with full SQL examples.

Installation

OpenClaw / Moltbot / Clawbot

npx clawhub@latest install supabase-postgres

Critical Rules Summary

Query Performance

Always index WHERE and JOIN columns. Unindexed columns cause full table scans that get exponentially slower as tables grow. Create indexes on every column used in WHERE, JOIN, or ORDER BY on large tables.

-- Create index on frequently filtered column
create index orders_customer_id_idx on orders (customer_id);

Choose the right index type. B-tree (default) handles =, <, >, BETWEEN. Use GIN for JSONB/arrays/full-text, BRIN for large time-series tables, Hash for equality-only lookups.

create index products_attrs_idx on products using gin (attributes);  -- JSONB
create index events_time_idx on events using brin (created_at);      -- Time-series

Use partial indexes for queries that always filter on the same condition. They're smaller, faster, and cheaper to maintain.

create index orders_pending_idx on orders (created_at)
  where status = 'pending';

Connection Management

Always use connection pooling. Each Postgres connection uses 1-3MB RAM. Without pooling, 500 concurrent users = 500 connections = crashed database.

• Use PgBouncer or Supabase's built-in pooler
• Transaction mode for most apps (connection returned after each transaction)
• Session mode only when using prepared statements or temp tables
• Formula: pool_size = (CPU cores * 2) + disk_count

Set appropriate connection limits. Monitor with:

select count(*), state from pg_stat_activity group by state;

Security & RLS

Enable RLS for multi-tenant data. Application-level filtering alone is one bug away from exposing all data.

alter table orders enable row level security;
create policy orders_policy on orders
  for all to authenticated
  using ((select auth.uid()) = user_id);  -- Wrap in SELECT for performance

Optimize RLS policies. Wrap function calls in (select ...) so they execute once, not per-row. On a 1M-row table, this is 100x+ faster.

-- BAD: auth.uid() called per row
using (auth.uid() = user_id);

-- GOOD: auth.uid() called once, cached
using ((select auth.uid()) = user_id);

High-Impact Rules Summary

Schema Design

Choose appropriate data types:

create table users (
  id bigint generated always as identity primary key,  -- Not serial
  email text,                      -- Not varchar(n)
  created_at timestamptz,          -- Not timestamp
  is_active boolean default true,  -- Not varchar
  price numeric(10,2)              -- Not float
);

Key guidelines: bigint over int, text over varchar(n), timestamptz over timestamp, numeric over float for money.

Select optimal primary keys: bigint identity for single-database, UUIDv7 for distributed systems. Avoid random UUIDv4 as PK on large tables (causes index fragmentation).

Always index foreign key columns. Postgres does NOT auto-index FKs. Missing FK indexes cause slow JOINs and CASCADE operations.

-- Find missing FK indexes
select conrelid::regclass as table_name, a.attname as fk_column
from pg_constraint c
join pg_attribute a on a.attrelid = c.conrelid and a.attnum = any(c.conkey)
where c.contype = 'f'
  and not exists (
    select 1 from pg_index i
    where i.indrelid = c.conrelid and a.attnum = any(i.indkey)
  );

Concurrency & Locking

Prevent deadlocks with consistent lock ordering. Always acquire locks in a deterministic order (e.g., by ID).

-- Lock rows in ID order before updating
begin;
select * from accounts where id in (1, 2) order by id for update;
update accounts set balance = balance - 100 where id = 1;
update accounts set balance = balance + 100 where id = 2;
commit;

Data Access Patterns

Eliminate N+1 queries. Batch with ANY(array[...]) or use JOINs instead of per-row queries.

-- BAD: 101 round trips
select id from users where active = true;  -- 100 IDs
select * from orders where user_id = 1;    -- repeated 100 times

-- GOOD: 1 round trip
select * from orders where user_id = any($1::bigint[]);

Use cursor-based pagination. OFFSET scans all skipped rows. Keyset pagination is O(1) regardless of page depth.

-- BAD: page 1000 scans 20,000 rows
select * from products order by id limit 20 offset 19980;

-- GOOD: page 1000, same speed as page 1
select * from products where id > $last_id order by id limit 20;

Monitoring & Diagnostics

Use EXPLAIN ANALYZE to diagnose slow queries:

explain (analyze, buffers, format text)
select * from orders where customer_id = 123 and status = 'pending';

What to look for:

• Seq Scan on large tables → missing index
• Rows Removed by Filter → poor selectivity
• Buffers: read >> hit → data not cached
• Sort Method: external merge → increase work_mem

Monitor with pg_stat_statements for aggregate query performance across the system.

Quick Reference

NEVER Do

• NEVER skip connection pooling — Direct connections don't scale
• NEVER use `float` for money — Use numeric for exact arithmetic
• NEVER rely on app-only filtering for security — Use RLS
• NEVER use `OFFSET` for deep pagination — Use keyset/cursor pagination
• NEVER use random UUIDv4 as PK on large tables — Causes index fragmentation
• NEVER create foreign keys without indexes — Postgres doesn't auto-index them

References

Detailed rule files in references/:

Query Performance (Critical):

• query-missing-indexes.md — Index WHERE and JOIN columns
• query-index-types.md — B-tree vs GIN vs BRIN vs Hash
• query-partial-indexes.md — Partial indexes for filtered queries
• query-composite-indexes.md — Multi-column index design
• query-covering-indexes.md — Index-only scans

Connection Management (Critical):

• conn-pooling.md — Connection pooling with PgBouncer
• conn-limits.md — Setting connection limits
• conn-idle-timeout.md — Idle connection management
• conn-prepared-statements.md — Prepared statement pooling

Security (Critical):

• security-rls-basics.md — Row-Level Security fundamentals
• security-rls-performance.md — Optimizing RLS policies
• security-privileges.md — Role and privilege management

Schema Design (High):

• schema-data-types.md — Choosing data types
• schema-primary-keys.md — Primary key strategies
• schema-foreign-key-indexes.md — FK index requirements
• schema-lowercase-identifiers.md — Naming conventions
• schema-partitioning.md — Table partitioning strategies

Concurrency & Locking (Medium-High):

• lock-deadlock-prevention.md — Avoiding deadlocks
• lock-short-transactions.md — Keeping transactions short
• lock-skip-locked.md — Queue processing pattern
• lock-advisory.md — Advisory locks

Data Access (Medium):

• data-n-plus-one.md — Eliminating N+1 queries
• data-pagination.md — Cursor vs offset pagination
• data-batch-inserts.md — Bulk insert patterns
• data-upsert.md — Upsert patterns

Monitoring (Low-Medium):

• monitor-explain-analyze.md — Query plan analysis
• monitor-pg-stat-statements.md — Aggregate query stats
• monitor-vacuum-analyze.md — Table maintenance

Advanced Features (Low):

• advanced-jsonb-indexing.md — JSONB query optimization
• advanced-full-text-search.md — Full-text search setup

External:

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