irahardianto / postgres-idioms

PostgreSQL Idioms and Best Practices

PostgreSQL rewards set-based thinking, explicit joins, and query plan awareness.
Idiomatic PostgreSQL = readable, performant, migration-safe, secure.

Scope: PostgreSQL-specific patterns. For database design principles
(normalization, naming, migration strategy), see @.agents/rules/database-design-principles.md.
For deep-dive references on individual topics, see references/ in this skill directory.

Priority Guide

1. Query Performance & Indexing (CRITICAL)

Always EXPLAIN Before Optimizing

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) SELECT ...;

Red flags in query plans:

• Seq Scan on large table → missing index
• Rows Removed by Filter → poor selectivity or wrong index
• read >> hit in Buffers → data not cached, cold query
• Sort Method: external merge → work_mem too low
• Nested Loop with high row count → consider Hash Join

Index Strategy

Choose the right index type:

Composite indexes — column order matters (leftmost prefix rule):

-- ✅ Equality columns first, range columns last
CREATE INDEX idx_orders_status_date ON orders (status, created_at);
-- Works: WHERE status = 'pending'
-- Works: WHERE status = 'pending' AND created_at > '2024-01-01'
-- FAILS: WHERE created_at > '2024-01-01' (alone — no leftmost match)

Partial indexes for filtered queries (5-20x smaller):

CREATE INDEX idx_users_active_email ON users (email)
  WHERE deleted_at IS NULL;

Covering indexes to avoid heap fetches:

-- INCLUDE non-searchable columns for index-only scans
CREATE INDEX idx_orders_status ON orders (status)
  INCLUDE (customer_id, total);

Always index foreign keys. PostgreSQL does NOT auto-index FK columns:

-- ❌ Missing index on FK — causes Seq Scan on JOIN and CASCADE
CREATE TABLE orders (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id BIGINT REFERENCES customers(id) ON DELETE CASCADE
);

-- ✅ Always create FK index
CREATE INDEX idx_orders_customer_id ON orders (customer_id);

Detect missing indexes with pg_stat_user_tables:

SELECT schemaname, relname, seq_scan, idx_scan,
       seq_tup_read, n_live_tup
FROM pg_stat_user_tables
WHERE seq_scan > 100
  AND n_live_tup > 10000
ORDER BY seq_tup_read DESC;

Index creation on production — always CONCURRENTLY:

CREATE INDEX CONCURRENTLY idx_orders_date ON orders (created_at);
-- Does NOT block writes (but takes longer, requires retry on failure)

2. Connection Management (CRITICAL)

Each PostgreSQL connection costs ~1-3 MB RAM. Unbounded connections crash the database.

Connection Pooling

Use a connection pooler (PgBouncer, pgcat, Supavisor) between app and database.

Critical: Disable prepared statements in your ORM/driver when using transaction mode pooling.

Idle Connection Cleanup

-- Kill idle-in-transaction connections after 30s (holds locks, blocks VACUUM)
ALTER SYSTEM SET idle_in_transaction_session_timeout = '30s';
-- Kill fully idle connections after 10 minutes
ALTER SYSTEM SET idle_session_timeout = '10min';
SELECT pg_reload_conf();

Statement Timeout

-- Prevent runaway queries (set per session or globally)
SET statement_timeout = '30s';
-- Or per transaction
SET LOCAL statement_timeout = '5s';

Monitor Active Connections

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

3. Security & RLS (CRITICAL)

Principle of Least Privilege

-- ❌ Overly broad — any SQL injection becomes catastrophic
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO app_user;

-- ✅ Minimal, specific grants
CREATE ROLE app_readonly;
GRANT USAGE ON SCHEMA public TO app_readonly;
GRANT SELECT ON users, orders, products TO app_readonly;

CREATE ROLE app_writer;
GRANT USAGE ON SCHEMA public TO app_writer;
GRANT SELECT, INSERT, UPDATE ON orders TO app_writer;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO app_writer;
-- No DELETE, no DDL, no other tables

Row Level Security (RLS) for Multi-Tenant Data

RLS enforces data isolation at the database level — defense in depth beyond application filtering:

-- ❌ Application-level filtering only (bug or bypass = data leak)
SELECT * FROM orders WHERE user_id = $current_user_id;

-- ✅ Database-enforced isolation
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
ALTER TABLE orders FORCE ROW LEVEL SECURITY;  -- Applies to table owner too

CREATE POLICY orders_user_isolation ON orders
  USING (user_id = current_setting('app.current_user_id')::BIGINT);

Set the session variable in your application before queries:

SET LOCAL app.current_user_id = '42';
SELECT * FROM orders;  -- Returns only user 42's orders, regardless of query

RLS Performance Optimization

-- ❌ Function called per row (1M rows = 1M function calls)
CREATE POLICY orders_policy ON orders
  USING (get_current_user_id() = user_id);

-- ✅ Wrap in subquery — called once, result cached
CREATE POLICY orders_policy ON orders
  USING (user_id = (SELECT current_setting('app.current_user_id')::BIGINT));

Use SECURITY DEFINER functions for complex permission checks:

CREATE OR REPLACE FUNCTION is_team_member(team_id BIGINT)
RETURNS BOOLEAN
LANGUAGE sql SECURITY DEFINER STABLE
SET search_path = public
AS $$
  SELECT EXISTS (
    SELECT 1 FROM team_members
    WHERE team_members.team_id = is_team_member.team_id
      AND team_members.user_id = current_setting('app.current_user_id')::BIGINT
  );
$$;

SECURITY DEFINER functions bypass RLS on tables they touch.
Always validate the caller identity inside the function.
Always set search_path explicitly to prevent search path injection.

4. Schema Design (HIGH)

Data Types — Choose Correctly

Primary Key Strategy

-- ✅ Single database: IDENTITY (sequential, 8 bytes, SQL-standard)
CREATE TABLE users (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY
);

-- ✅ Distributed systems: UUIDv7 (time-ordered, no fragmentation)
CREATE EXTENSION IF NOT EXISTS pg_uuidv7;
CREATE TABLE orders (
  id UUID DEFAULT uuid_generate_v7() PRIMARY KEY
);

Avoid random UUID v4 as PK on large tables — scattered inserts cause index fragmentation and poor cache locality.

Naming Conventions

• Lowercase snake_case only — PostgreSQL folds unquoted identifiers to lowercase
• Never use double-quoted identifiers unless unavoidable
• Tables: plural (users, orders), or singular if team convention is consistent
• Indexes: idx_{table}_{columns} (e.g., idx_orders_customer_id)
• Constraints: {table}_{columns}_{type} (e.g., orders_customer_id_fk, users_email_unique)

Safe Constraint Migrations

PostgreSQL does NOT support ADD CONSTRAINT IF NOT EXISTS:

-- ❌ Syntax error
ALTER TABLE profiles ADD CONSTRAINT IF NOT EXISTS profiles_email_unique UNIQUE (email);

-- ✅ Idempotent constraint creation
DO $$
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM pg_constraint
    WHERE conname = 'profiles_email_unique'
      AND conrelid = 'public.profiles'::regclass
  ) THEN
    ALTER TABLE public.profiles ADD CONSTRAINT profiles_email_unique UNIQUE (email);
  END IF;
END $$;

Table Partitioning (100M+ rows)

-- ✅ Declarative range partitioning for time-series data
CREATE TABLE events (
  id BIGINT GENERATED ALWAYS AS IDENTITY,
  created_at TIMESTAMPTZ NOT NULL,
  data JSONB
) PARTITION BY RANGE (created_at);

CREATE TABLE events_2024_q1 PARTITION OF events
  FOR VALUES FROM ('2024-01-01') TO ('2024-04-01');
CREATE TABLE events_2024_q2 PARTITION OF events
  FOR VALUES FROM ('2024-04-01') TO ('2024-07-01');

-- Benefits: partition pruning (5-20x faster queries), per-partition VACUUM,
-- easy archival (DROP old partitions vs DELETE + VACUUM)

When to partition:

• Tables exceeding 100M rows
• Time-series data with range queries
• Multi-tenant data with tenant-scoped queries
• High-churn tables where VACUUM is problematic

5. Concurrency & Locking (MEDIUM-HIGH)

Prevent Deadlocks — Consistent Lock Ordering

-- ✅ Acquire locks in PK order before updating
SELECT * FROM accounts WHERE id IN (1, 2) ORDER BY id FOR UPDATE;

Keep Transactions Short

-- ❌ Long transaction holds locks, blocks VACUUM
BEGIN;
  SELECT * FROM orders FOR UPDATE;
  -- ... HTTP call to external service (5s) ...
  UPDATE orders SET status = 'paid';
COMMIT;

-- ✅ Fetch data, process externally, then short write transaction
-- Step 1: read (no lock)
SELECT * FROM orders WHERE id = $1;
-- Step 2: external processing
-- Step 3: short write transaction
BEGIN;
  UPDATE orders SET status = 'paid' WHERE id = $1 AND status = 'pending';
COMMIT;

SKIP LOCKED for Queue Processing

-- ✅ Workers skip locked rows — 10x throughput vs blocking
UPDATE jobs SET status = 'processing', locked_by = $worker_id
WHERE id = (
  SELECT id FROM jobs WHERE status = 'pending'
  ORDER BY created_at LIMIT 1 FOR UPDATE SKIP LOCKED
) RETURNING *;

Advisory Locks for Application-Level Coordination

-- Transaction-scoped: released on COMMIT/ROLLBACK
SELECT pg_advisory_xact_lock(hashtext('daily_report'));

-- Try without blocking (returns false if already locked)
SELECT pg_try_advisory_lock(hashtext('cron_job_xyz'));

6. Data Access Patterns (MEDIUM)

CTEs Over Subqueries for Readability

-- ✅ CTE — readable, debuggable, materializable
WITH active_tasks AS (
    SELECT id, title, priority, user_id
    FROM tasks WHERE status = 'active'
)
SELECT u.name, COUNT(at.id) AS task_count
FROM users u
JOIN active_tasks at ON u.id = at.user_id
GROUP BY u.name;

Keyset Pagination Over OFFSET

-- ❌ OFFSET — O(n) scans all preceding rows, degrades at depth
SELECT * FROM products ORDER BY id LIMIT 20 OFFSET 10000;

-- ✅ Keyset — O(1) consistent performance regardless of page depth
SELECT * FROM products
WHERE (created_at, id) > ($last_created_at, $last_id)
ORDER BY created_at, id LIMIT 20;

UPSERT — Atomic Insert-or-Update

-- ✅ No race conditions, no check-then-insert anti-pattern
INSERT INTO settings (user_id, key, value)
VALUES ($1, $2, $3)
ON CONFLICT (user_id, key)
DO UPDATE SET value = EXCLUDED.value, updated_at = now();

N+1 Query Prevention

-- ❌ N+1: 1 query for list + N queries for details
SELECT id FROM orders WHERE user_id = $1;
-- then for each: SELECT * FROM order_items WHERE order_id = $id;

-- ✅ Single JOIN or batch IN
SELECT o.*, oi.*
FROM orders o
JOIN order_items oi ON o.id = oi.order_id
WHERE o.user_id = $1;

Batch Inserts and Bulk Loading

-- ❌ One INSERT per row (N round-trips, N transaction logs)
INSERT INTO events (type, data) VALUES ('click', '{}');
INSERT INTO events (type, data) VALUES ('view', '{}');

-- ✅ Multi-row INSERT (1 round-trip)
INSERT INTO events (type, data) VALUES
  ('click', '{}'),
  ('view', '{}'),
  ('scroll', '{}');

-- ✅ COPY for massive imports (fastest)
COPY events (type, data) FROM STDIN WITH (FORMAT csv);

Explicit JOIN Syntax

Never use implicit joins in WHERE. Always use explicit JOIN ... ON.

Parameterized Queries

Never concatenate strings into SQL. Always use $1, $2 parameterized queries.
See @.agents/rules/security-principles.md.

7. Monitoring & Diagnostics (LOW-MEDIUM)

pgstatstatements — Top Resource Consumers

-- Enable: add to shared_preload_libraries, restart
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Top 10 queries by total execution time
SELECT query, calls, total_exec_time, mean_exec_time,
       rows, shared_blks_hit, shared_blks_read
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

VACUUM & ANALYZE

-- Run ANALYZE after large data changes (updates planner statistics)
ANALYZE orders;

-- Check autovacuum health
SELECT schemaname, relname, n_dead_tup, last_autovacuum, last_autoanalyze
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC;

Tune autovacuum for high-churn tables:

ALTER TABLE high_churn_table SET (
  autovacuum_vacuum_scale_factor = 0.01,    -- Default 0.20 (20%)
  autovacuum_analyze_scale_factor = 0.005   -- Default 0.10 (10%)
);

Standard VACUUM runs concurrently (no exclusive lock).
Only VACUUM FULL requires exclusive access (rewrites table).

8. Advanced Features (LOW)

Full-Text Search

-- ✅ Use tsvector + GIN index (100x faster than LIKE '%term%')
ALTER TABLE articles ADD COLUMN search_vector tsvector
  GENERATED ALWAYS AS (to_tsvector('english', title || ' ' || body)) STORED;
CREATE INDEX idx_articles_search ON articles USING GIN (search_vector);

SELECT * FROM articles WHERE search_vector @@ to_tsquery('english', 'postgres & performance');

For leading wildcard search (LIKE '%term'), use pg_trgm extension with GIN/GiST index.

JSONB Indexing

-- GIN with jsonb_path_ops: 2-3x smaller, supports @> only
CREATE INDEX idx_events_data ON events USING GIN (data jsonb_path_ops);

-- Expression index for specific key lookups
CREATE INDEX idx_events_type ON events ((data->>'type'));

Anti-Patterns Checklist

• ❌ Missing indexes on foreign keys
• ❌ N+1 queries (use JOIN or batch IN)
• ❌ String concatenation in queries (SQL injection risk)
• ❌ Storing comma-separated values in a single column (use arrays or junction table)
• ❌ OFFSET pagination on large datasets (use keyset)
• ❌ timestamp without timezone (use timestamptz)
• ❌ varchar(n) without reason (use text)
• ❌ Random UUID v4 as PK on large tables (use bigint identity or UUIDv7)
• ❌ Check-then-insert pattern (race condition — use UPSERT)
• ❌ serial for new tables (use bigint generated always as identity)
• ❌ SELECT * in production queries (list specific columns)
• ❌ GRANT ALL to application roles (principle of least privilege)
• ❌ Application-only data filtering without RLS (defense in depth)
• ❌ Long transactions with external I/O inside (hold locks, block VACUUM)
• ❌ Prepared statements with transaction-mode pooling (connection mismatch)
• ❌ Double-quoted identifiers (force case sensitivity, error-prone)

Related

• Database Design Principles: @.agents/rules/database-design-principles.md
• Security Principles: @.agents/rules/security-principles.md
• Performance Optimization Principles: @.agents/rules/performance-optimization-principles.md
• Deep-dive references: references/ directory in this skill