Postgres best practices

PostgreSQL Best Practices: Performance, Security, and Schema Design

Query Performance (Priority 1)

Performance problems are the most common and most impactful database issues. Address them first and continuously.

Always Use EXPLAIN ANALYZE

Never optimize a query without first understanding its execution plan:

EXPLAIN ANALYZE
SELECT u.name, count(o.id)
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE u.status = 'active'
GROUP BY u.name;

Read the output bottom-up. Look for:

Signal	Meaning	Action
Seq Scan	Full table scan	Add an index on the filter column
Nested Loop	Row-by-row join	Check join column indexes
Sort	In-memory or disk sort	Add index matching ORDER BY
Hash Join	Hash-based join	Usually efficient for large sets
Rows Removed	Filtered after scan	Push filter into index
actual rows >> est	Stale statistics	Run ANALYZE tablename

Index Strategy

Create indexes for columns used in WHERE, JOIN, and ORDER BY clauses:

-- Single-column index for equality filters
CREATE INDEX idx_users_status ON users (status);

-- Composite index for multi-column queries (order matters)
CREATE INDEX idx_orders_user_status ON orders (user_id, status);

-- Partial index for filtered queries (smaller, faster)
CREATE INDEX idx_orders_active ON orders (created_at)
  WHERE status = 'active';

-- Covering index to avoid table lookups
CREATE INDEX idx_users_email_name ON users (email) INCLUDE (name);

-- GIN index for JSONB or full-text search
CREATE INDEX idx_profiles_metadata ON profiles USING gin (metadata);

Index Rules

• Index columns that appear in WHERE, JOIN ON, and ORDER BY.
• Order composite index columns by selectivity (most selective first).
• Use partial indexes when queries consistently filter by a fixed condition.
• Drop unused indexes. They slow down writes without helping reads.
• Monitor index usage with pg_stat_user_indexes.

Query Optimization Rules

• Prefer EXISTS over IN for subqueries. EXISTS short-circuits on the first match:

-- Slower
SELECT * FROM users WHERE id IN (SELECT user_id FROM orders);

-- Faster
SELECT * FROM users u WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

• Avoid SELECT *. Specify needed columns to reduce I/O and memory:

-- Bad
SELECT * FROM users WHERE status = 'active';

-- Good
SELECT id, name, email FROM users WHERE status = 'active';

• Use LIMIT with OFFSET carefully. For deep pagination, use keyset pagination:

-- Bad: OFFSET scans and discards rows
SELECT * FROM orders ORDER BY created_at DESC LIMIT 20 OFFSET 10000;

-- Good: keyset pagination
SELECT * FROM orders
WHERE created_at < '2025-01-15T10:00:00Z'
ORDER BY created_at DESC
LIMIT 20;

• Batch bulk operations. Process large updates in chunks to avoid long locks:

-- Process 1000 rows at a time
UPDATE orders SET status = 'archived'
WHERE id IN (
  SELECT id FROM orders WHERE status = 'completed' AND created_at < now() - interval '1 year'
  LIMIT 1000
);

---

Connection Management

Connection Pooling

Use a connection pooler (PgBouncer or Supabase pooler) between the application and PostgreSQL. Direct connections are expensive to create and maintain.

Setting	Recommendation
Pool mode	Transaction mode for most applications
Max connections	Match to available CPU cores (2-4x cores)
Idle timeout	30 seconds for web apps
Statement timeout	30 seconds default, adjust per query

Statement Timeouts

Set timeouts to prevent runaway queries from monopolizing resources:

-- Session-level timeout
SET statement_timeout = '30s';

-- Per-transaction timeout
BEGIN;
SET LOCAL statement_timeout = '5s';
SELECT * FROM expensive_view;
COMMIT;

Connection Hygiene

• Close connections properly in application code. Use connection pools or ORMs that handle lifecycle.
• Set idle_in_transaction_session_timeout to kill abandoned transactions:

ALTER DATABASE mydb SET idle_in_transaction_session_timeout = '60s';

• Monitor active connections with:

SELECT state, count(*) FROM pg_stat_activity GROUP BY state;

---

Security and Row Level Security (RLS)

Enable RLS on All User-Facing Tables

ALTER TABLE profiles ENABLE ROW LEVEL SECURITY;
ALTER TABLE profiles FORCE ROW LEVEL SECURITY;

FORCE ROW LEVEL SECURITY ensures that even the table owner is subject to policies. Without it, the table owner bypasses all RLS policies.

Writing Policies

Use auth.uid() and auth.jwt() for Supabase-integrated RLS:

-- Users can read their own profile
CREATE POLICY "Users read own profile"
  ON profiles FOR SELECT
  USING (auth.uid() = user_id);

-- Users can update their own profile
CREATE POLICY "Users update own profile"
  ON profiles FOR UPDATE
  USING (auth.uid() = user_id)
  WITH CHECK (auth.uid() = user_id);

-- Role-based access using JWT claims
CREATE POLICY "Admins read all profiles"
  ON profiles FOR SELECT
  USING (auth.jwt() ->> 'role' = 'admin');

-- Insert policy with ownership enforcement
CREATE POLICY "Users insert own data"
  ON documents FOR INSERT
  WITH CHECK (auth.uid() = owner_id);

Testing RLS Policies

Test policies by impersonating roles:

-- Impersonate an authenticated user
SET ROLE authenticated;
SET request.jwt.claims = '{"sub": "user-uuid-here", "role": "user"}';

-- Run queries to verify policy enforcement
SELECT * FROM profiles;  -- Should return only own profile

-- Reset
RESET ROLE;
RESET request.jwt.claims;

RLS Rules

• Apply USING for read/filter operations (SELECT, UPDATE, DELETE).
• Apply WITH CHECK for write validation (INSERT, UPDATE).
• Always test policies with both authorized and unauthorized scenarios.
• Use security definer functions sparingly and audit them for privilege escalation.

---

Schema Design

Primary Keys

Use UUIDs for primary keys. They are globally unique, safe for distributed systems, and do not leak ordering information:

CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name TEXT NOT NULL,
  email TEXT NOT NULL UNIQUE,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

Timestamps

Always use TIMESTAMPTZ (timestamp with time zone), never TIMESTAMP (without time zone):

-- Good
created_at TIMESTAMPTZ NOT NULL DEFAULT now()

-- Bad: ambiguous timezone behavior
created_at TIMESTAMP NOT NULL DEFAULT now()

Constraints Over ENUMs

Prefer CHECK constraints over PostgreSQL ENUMs. ENUMs cannot have values removed and require migration workarounds for changes:

-- Good: flexible, easy to modify
CREATE TABLE orders (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  status TEXT NOT NULL DEFAULT 'pending'
    CHECK (status IN ('pending', 'processing', 'completed', 'cancelled'))
);

-- Bad: rigid, hard to modify
CREATE TYPE order_status AS ENUM ('pending', 'processing', 'completed', 'cancelled');

Soft-Delete Pattern

Use a nullable deleted_at column instead of physically deleting rows:

ALTER TABLE documents ADD COLUMN deleted_at TIMESTAMPTZ DEFAULT NULL;

-- Partial index for efficient queries on active records
CREATE INDEX idx_documents_active ON documents (created_at) WHERE deleted_at IS NULL;

-- Soft delete
UPDATE documents SET deleted_at = now() WHERE id = 'doc-uuid';

-- Query active records only
SELECT * FROM documents WHERE deleted_at IS NULL;

Automatic updated_at Trigger

CREATE OR REPLACE FUNCTION update_updated_at()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = now();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER set_updated_at
  BEFORE UPDATE ON users
  FOR EACH ROW
  EXECUTE FUNCTION update_updated_at();

Apply this trigger to every table that has an updated_at column.

---

Concurrency and Locking

Job Queue Pattern with FOR UPDATE SKIP LOCKED

Process background jobs without contention:

-- Worker picks the next available job
BEGIN;
SELECT id, payload FROM jobs
WHERE status = 'pending'
ORDER BY created_at
LIMIT 1
FOR UPDATE SKIP LOCKED;

-- Process the job, then mark complete
UPDATE jobs SET status = 'completed' WHERE id = 'job-uuid';
COMMIT;

Advisory Locks

Use advisory locks for application-level coordination that does not map to row-level locking:

-- Acquire a session-level advisory lock (blocks until acquired)
SELECT pg_advisory_lock(hashtext('daily-report-generation'));

-- Do work ...

-- Release
SELECT pg_advisory_unlock(hashtext('daily-report-generation'));

Concurrency Rules

• Avoid long-running transactions. They hold locks and prevent autovacuum.
• Use NOWAIT or SKIP LOCKED to avoid blocking on contended rows.
• Keep transactions as short as possible: open, do work, commit.
• Never hold a transaction open while waiting for user input or external API calls.

---

Monitoring

Slow Query Detection with pg_stat_statements

Enable the extension and query for expensive statements:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

SELECT
  query,
  calls,
  mean_exec_time,
  total_exec_time,
  rows
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 20;

Table Health with pg_stat_user_tables

Monitor dead tuples and autovacuum activity:

SELECT
  relname,
  n_live_tup,
  n_dead_tup,
  last_autovacuum,
  last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;

Index Usage

Identify unused indexes consuming disk space and slowing writes:

SELECT
  indexrelname,
  idx_scan,
  idx_tup_read,
  pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

Key Metrics to Monitor

Metric	Source	Warning Threshold
Active connections	pg_stat_activity	> 80% of max_connections
Dead tuples	pg_stat_user_tables	> 10% of live tuples
Cache hit ratio	pg_stat_database	< 99%
Unused indexes	pg_stat_user_indexes	idx_scan = 0 after 30 days
Long-running queries	pg_stat_activity	> 60 seconds
Replication lag	pg_stat_replication	> 1 second

---

Quick Reference

Area	Key Rule
Queries	Always EXPLAIN ANALYZE before optimizing
Indexes	Index WHERE, JOIN, ORDER BY columns; drop unused ones
Connections	Use pooling; set statement timeouts
RLS	Enable + force on all tables; test with impersonation
Schema	UUIDs, TIMESTAMPTZ, CHECK constraints, soft deletes
Concurrency	Short transactions, SKIP LOCKED for queues
Monitoring	pg_stat_statements, pg_stat_user_tables, index usage

---

References

• For the rule index organized by category and impact level, read references/rules-index.md.
• For detailed individual rules with SQL examples, browse references/rules/. Each file covers one rule with impact rating and incorrect/correct SQL. Section prefixes: query- (indexes, performance), conn- (connection management), security- (RLS, privileges), schema- (design), lock- (concurrency), data- (access patterns), monitor- (diagnostics), advanced- (JSONB, full-text search).

---

Dependencies

None -- this is a foundation skill.

Used By

• supabase-patterns