postgres-patterns

PostgreSQL Patterns

This skill is the query- and schema-design layer: how to index a table and shape a query well. For how to ship a schema change safely, see database-migrations. For any table partitioned by hotel_id (most core tables in this organization), the partition-pruning predicate rules in postgres-hotel-partitioning apply on top of everything here — every example below assumes that predicate is already present.

Index selection

Query pattern	Index type	Example
WHERE col = value	B-tree (default)	CREATE INDEX idx ON t (col)
WHERE col > value / range	B-tree	CREATE INDEX idx ON t (col)
WHERE a = x AND b > y	Composite, equality columns first	CREATE INDEX idx ON t (a, b)
WHERE jsonb_col @> '{}'	GIN	CREATE INDEX idx ON t USING gin (jsonb_col)
Full-text tsv @@ query	GIN	CREATE INDEX idx ON t USING gin (tsv)
Append-only time-series ranges	BRIN	CREATE INDEX idx ON t USING brin (created_at)

Composite index column order matters: put equality-tested columns first, range-tested columns last, so the index can seek on the equality prefix and then scan the range within it:

-- Serves: WHERE status = 'pending' AND created_at > '2025-01-01'
CREATE INDEX idx_jobs_status_created ON jobs (status, created_at);

Covering index avoids a heap lookup entirely when the query only needs indexed + included columns:

CREATE INDEX idx_users_email ON users (email) INCLUDE (name, created_at);
-- SELECT email, name, created_at FROM users WHERE email = $1 -- index-only scan

Partial index shrinks the index to the subset that's actually queried frequently:

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

An unindexed foreign key is a common, silent bottleneck (every delete on the parent table triggers a sequential scan of the child to check for references). Find them:

SELECT conrelid::regclass AS table_name, a.attname AS column_name
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)
  );