| name | loom-database-design |
| description | Database schema and data model design for relational, NoSQL, time-series, and warehouse systems. Use for ERDs, normalization/denormalization, indexing, migrations, star/snowflake schemas, event sourcing, and OLTP/OLAP performance tuning. |
| allowed-tools | ["Read","Grep","Glob","Edit","Write","Bash"] |
| triggers | ["schema","table","column","migration","ERD","normalize","denormalize","index","foreign key","primary key","constraint","relationship","SQL","DDL","data model","database design","data warehouse","star schema","snowflake schema","time-series","event sourcing","dimension table","fact table","ETL","data pipeline","OLAP","OLTP"] |
Database Design
Overview
Designing schemas and data models across workloads: OLTP (normalized relational, transactional integrity), OLAP (star/snowflake warehouses), NoSQL (document/KV/wide-column), time-series (TimescaleDB/InfluxDB), event sourcing (append-only stores), and ETL/pipeline staging. Most examples are PostgreSQL; principles generalize. The mechanism-level rules — keys, indexing, concurrency, lock-aware DDL, partitioning — live in Expert Practices below; this section is the design method.
Design Method
1. Requirements → model. Entities, attributes, relationships (1:1 / 1:N / M:N); access patterns (read vs write heavy, hot queries); volume, growth, retention; OLTP vs OLAP. The access pattern, not the entities, drives the physical design.
2. Schema per workload:
- OLTP: normalize to 3NF (one home per fact), then derive read models. Surrogate vs natural PK (see Keys). FK cascade rules. Correct types +
CHECK constraints. Deliberate NULL semantics.
- OLAP: star schema (fact + denormalized dimensions); snowflake only when a dimension's cardinality/reuse justifies normalizing it. Surrogate dimension keys. SCD Type 1 (overwrite) / Type 2 (row-versioned history) / Type 3 (prior-value column). Fact tables = FKs + additive measures + degenerate dims.
- Time-series: time as leading PK component; partition by time range; append-only writes; downsample into rollup/continuous-aggregate tables; retention policy that drops old partitions.
- Event sourcing: immutable append-only events (
aggregate_id, event_type, sequence_number, payload, occurred_at); optimistic concurrency via UNIQUE(aggregate_id, sequence_number); projections as derived read models; version the payload for schema evolution; snapshots to bound replay cost.
3. Performance & concurrency, migrations, ETL: these are the highest-defect areas — apply the mechanism rules from Expert Practices: index every child FK column; INCLUDE covering indexes; partial-index literal-match limits; declarative partitioning (never inheritance+triggers); READ COMMITTED anomalies vs SERIALIZABLE+40001 retry; lock-aware DDL (lock_timeout, NOT VALID+VALIDATE, CREATE INDEX CONCURRENTLY); idempotent MERGE/ON CONFLICT upserts with staging tables and audit columns.
Best Practices (rules of thumb — mechanisms in Expert Practices)
- Types:
BIGINT GENERATED ALWAYS AS IDENTITY for surrogate PKs (not SERIAL/INT4); TIMESTAMPTZ never bare TIMESTAMP; NUMERIC/DECIMAL (not FLOAT/money) for currency; TEXT/VARCHAR (not char(n)).
- Index wisely: cover WHERE/JOIN/ORDER BY/GROUP BY, but every index is a write tax — audit
pg_stat_user_indexes for idx_scan = 0 and drop dead ones.
- Normalize first, derive later: normalized system-of-record; denormalize only as materialized views / projections refreshed from it, not duplicated app-side writes.
- Constrain at the DB: PK/FK/UNIQUE/CHECK/NOT NULL.
NOT EXISTS, never NOT IN (nullable subquery).
- Plan for scale early: partition/shard/replicate high-volume tables; CQRS (separate read/write models) at high scale.
- Operational hygiene: audit columns (
created_at/updated_at/created_by); idempotent ETL; version event payloads; test migrations on production-like volumes; maintain an ERD/data dictionary.
Examples
Example 1: E-Commerce Schema (PostgreSQL)
PK choice: gen_random_uuid() (UUIDv4) is random and fragments the B-tree (page splits, low fill) on high-volume tables. It is fine for low-volume tables; for write-heavy OLTP prefer uuidv7() (PG18+) or pg_uuidv7's uuid_generate_v7() (PG14-17) — time-ordered, append-friendly, but embeds an approximate creation timestamp — or BIGINT GENERATED ALWAYS AS IDENTITY when an opaque/distributed ID is not required. (See Expert Practices.)
CREATE TABLE users (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
email VARCHAR(255) NOT NULL UNIQUE,
password_hash VARCHAR(255) NOT NULL,
name VARCHAR(100) NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
deleted_at TIMESTAMPTZ,
CONSTRAINT email_format CHECK (email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$')
);
CREATE TABLE products (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
sku VARCHAR(50) NOT NULL UNIQUE,
name VARCHAR(255) NOT NULL,
description TEXT,
price DECIMAL(10, 2) NOT NULL CHECK (price >= 0),
stock_quantity INTEGER NOT NULL DEFAULT 0 CHECK (stock_quantity >= 0),
category_id UUID REFERENCES categories(id),
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX idx_products_category ON products(category_id);
CREATE INDEX idx_products_price ON products(price);
CREATE INDEX idx_products_name_search ON products USING gin(to_tsvector('english', name));
CREATE TABLE orders (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
user_id UUID NOT NULL REFERENCES users(id),
status VARCHAR(20) NOT NULL DEFAULT 'pending',
total_amount DECIMAL(10, 2) NOT NULL,
shipping_address JSONB NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
CONSTRAINT valid_status CHECK (status IN ('pending', 'paid', 'shipped', 'delivered', 'cancelled'))
);
CREATE INDEX idx_orders_user ON orders(user_id);
CREATE INDEX idx_orders_status ON orders(status);
CREATE INDEX idx_orders_created ON orders(created_at DESC);
CREATE TABLE order_items (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
order_id UUID NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
product_id UUID NOT NULL REFERENCES products(id),
quantity INTEGER NOT NULL CHECK (quantity > 0),
unit_price DECIMAL(10, 2) NOT NULL,
UNIQUE(order_id, product_id)
);
CREATE INDEX idx_order_items_product_id ON order_items(product_id);
Example 2: Migration Script
SET lock_timeout = '200ms';
BEGIN;
ALTER TABLE users
ADD COLUMN loyalty_tier VARCHAR(20) DEFAULT 'bronze',
ADD COLUMN loyalty_points INTEGER DEFAULT 0;
ALTER TABLE users
ADD CONSTRAINT valid_loyalty_tier
CHECK (loyalty_tier IN ('bronze', 'silver', 'gold', 'platinum')) NOT VALID;
COMMIT;
ALTER TABLE users VALIDATE CONSTRAINT valid_loyalty_tier;
BEGIN;
CREATE TABLE loyalty_points_history (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
user_id UUID NOT NULL REFERENCES users(id),
points_change INTEGER NOT NULL,
reason VARCHAR(100) NOT NULL,
reference_type VARCHAR(50),
reference_id UUID,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX idx_loyalty_history_user ON loyalty_points_history(user_id);
CREATE INDEX idx_loyalty_history_created ON loyalty_points_history(created_at DESC);
COMMIT;
Example 3: MongoDB Document Design
{
_id: ObjectId("..."),
email: "user@example.com",
profile: {
name: "John Doe",
avatar_url: "https://..."
},
addresses: [
{
type: "shipping",
street: "123 Main St",
city: "Boston",
state: "MA",
zip: "02101",
is_default: true
}
],
preferences: {
newsletter: true,
notifications: {
email: true,
push: false
}
},
created_at: ISODate("2024-01-15T10:00:00Z")
}
db.users.createIndex({ email: 1 }, { unique: true });
db.users.createIndex({ "addresses.zip": 1 });
db.users.createIndex({ created_at: -1 });
Example 4: Data Warehouse Star Schema (PostgreSQL)
CREATE TABLE dim_date (
date_key INTEGER PRIMARY KEY,
full_date DATE NOT NULL,
day_of_week INTEGER,
day_name VARCHAR(10),
month INTEGER,
month_name VARCHAR(10),
quarter INTEGER,
year INTEGER,
is_weekend BOOLEAN,
is_holiday BOOLEAN
);
CREATE TABLE dim_product (
product_key BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
product_id VARCHAR(50) NOT NULL,
product_name VARCHAR(255) NOT NULL,
category VARCHAR(100),
subcategory VARCHAR(100),
brand VARCHAR(100),
unit_cost DECIMAL(10, 2),
effective_date DATE NOT NULL,
expiration_date DATE,
is_current BOOLEAN DEFAULT TRUE,
UNIQUE(product_id, effective_date)
);
CREATE INDEX idx_dim_product_current ON dim_product(product_id) WHERE is_current = TRUE;
CREATE TABLE dim_customer (
customer_key BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
customer_id VARCHAR(50) NOT NULL,
customer_name VARCHAR(255),
customer_segment VARCHAR(50),
region VARCHAR(100),
country VARCHAR(100),
effective_date DATE NOT NULL,
expiration_date DATE,
is_current BOOLEAN DEFAULT TRUE
);
CREATE TABLE fact_sales (
sale_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
date_key INTEGER NOT NULL REFERENCES dim_date(date_key),
product_key INTEGER NOT NULL REFERENCES dim_product(product_key),
customer_key INTEGER NOT NULL REFERENCES dim_customer(customer_key),
quantity INTEGER NOT NULL,
unit_price DECIMAL(10, 2) NOT NULL,
discount_amount DECIMAL(10, 2) DEFAULT 0,
tax_amount DECIMAL(10, 2) NOT NULL,
total_amount DECIMAL(10, 2) NOT NULL,
cost_amount DECIMAL(10, 2) NOT NULL,
order_number VARCHAR(50),
transaction_time TIMESTAMP NOT NULL
);
CREATE INDEX idx_fact_sales_date ON fact_sales(date_key);
CREATE INDEX idx_fact_sales_product ON fact_sales(product_key);
CREATE INDEX idx_fact_sales_customer ON fact_sales(customer_key);
CREATE INDEX idx_fact_sales_composite ON fact_sales(date_key, product_key, customer_key);
CREATE MATERIALIZED VIEW mv_monthly_sales AS
SELECT
d.year,
d.month,
p.category,
c.region,
SUM(f.quantity) AS total_quantity,
SUM(f.total_amount) AS total_revenue,
SUM(f.cost_amount) AS total_cost,
SUM(f.total_amount - f.cost_amount) AS total_profit
FROM fact_sales f
JOIN dim_date d ON f.date_key = d.date_key
JOIN dim_product p ON f.product_key = p.product_key
JOIN dim_customer c ON f.customer_key = c.customer_key
GROUP BY d.year, d.month, p.category, c.region;
CREATE INDEX idx_mv_monthly_sales ON mv_monthly_sales(year, month, category);
Example 5: Time-Series Database (TimescaleDB)
CREATE TABLE metrics (
time TIMESTAMPTZ NOT NULL,
device_id VARCHAR(50) NOT NULL,
metric_name VARCHAR(100) NOT NULL,
value DOUBLE PRECISION NOT NULL,
tags JSONB,
PRIMARY KEY (time, device_id, metric_name)
);
SELECT create_hypertable('metrics', 'time', chunk_time_interval => INTERVAL '1 day');
CREATE INDEX idx_metrics_device_time ON metrics(device_id, time DESC);
CREATE INDEX idx_metrics_name_time ON metrics(metric_name, time DESC);
CREATE INDEX idx_metrics_tags ON metrics USING gin(tags);
ALTER TABLE metrics SET (
timescaledb.compress,
timescaledb.compress_segmentby = 'device_id, metric_name'
);
SELECT add_compression_policy('metrics', INTERVAL '7 days');
SELECT add_retention_policy('metrics', INTERVAL '90 days');
CREATE MATERIALIZED VIEW metrics_hourly
WITH (timescaledb.continuous) AS
SELECT
time_bucket('1 hour', time) AS hour,
device_id,
metric_name,
AVG(value) AS avg_value,
MAX(value) AS max_value,
MIN(value) AS min_value,
COUNT(*) AS count
FROM metrics
GROUP BY hour, device_id, metric_name;
SELECT add_continuous_aggregate_policy('metrics_hourly',
start_offset => INTERVAL '3 hours',
end_offset => INTERVAL '1 hour',
schedule_interval => INTERVAL '1 hour');
Example 6: Event Sourcing Pattern (PostgreSQL)
CREATE TABLE events (
event_id BIGSERIAL PRIMARY KEY,
aggregate_id UUID NOT NULL,
aggregate_type VARCHAR(100) NOT NULL,
event_type VARCHAR(100) NOT NULL,
event_version INTEGER NOT NULL,
payload JSONB NOT NULL,
metadata JSONB,
occurred_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
sequence_number INTEGER NOT NULL,
CONSTRAINT unique_sequence UNIQUE(aggregate_id, sequence_number)
);
CREATE INDEX idx_events_aggregate ON events(aggregate_id, sequence_number);
CREATE INDEX idx_events_type_time ON events(event_type, occurred_at);
CREATE INDEX idx_events_occurred ON events(occurred_at DESC);
CREATE TABLE snapshots (
snapshot_id BIGSERIAL PRIMARY KEY,
aggregate_id UUID NOT NULL,
aggregate_type VARCHAR(100) NOT NULL,
sequence_number INTEGER NOT NULL,
state JSONB NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
CONSTRAINT unique_snapshot UNIQUE(aggregate_id, sequence_number)
);
CREATE INDEX idx_snapshots_aggregate ON snapshots(aggregate_id, sequence_number DESC);
CREATE TABLE account_balances (
account_id UUID PRIMARY KEY,
current_balance DECIMAL(15, 2) NOT NULL,
last_event_sequence INTEGER NOT NULL,
updated_at TIMESTAMPTZ NOT NULL
);
CREATE OR REPLACE FUNCTION rebuild_account_balance(p_account_id UUID)
RETURNS DECIMAL AS $$
DECLARE
v_balance DECIMAL(15, 2) := 0;
BEGIN
SELECT COALESCE(SUM(
CASE
WHEN event_type = 'AccountCredited' THEN (payload->>'amount')::DECIMAL
WHEN event_type = 'AccountDebited' THEN -(payload->>'amount')::DECIMAL
ELSE 0
END
), 0)
INTO v_balance
FROM events
WHERE aggregate_id = p_account_id
AND aggregate_type = 'Account'
ORDER BY sequence_number;
RETURN v_balance;
END;
$$ LANGUAGE plpgsql;
Example 7: ETL Staging Pattern (PostgreSQL)
CREATE TABLE staging_orders (
order_id VARCHAR(50) PRIMARY KEY,
customer_id VARCHAR(50),
order_date TIMESTAMPTZ,
total_amount DECIMAL(10, 2),
status VARCHAR(20),
source_system VARCHAR(50),
extracted_at TIMESTAMPTZ NOT NULL,
loaded_at TIMESTAMPTZ DEFAULT NOW(),
batch_id VARCHAR(100),
source_hash VARCHAR(64),
is_processed BOOLEAN DEFAULT FALSE
);
CREATE TABLE orders (
order_id VARCHAR(50) PRIMARY KEY,
customer_id VARCHAR(50),
order_date TIMESTAMPTZ,
total_amount DECIMAL(10, 2),
status VARCHAR(20),
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
source_system VARCHAR(50),
source_hash VARCHAR(64),
version INTEGER DEFAULT 1
);
CREATE OR REPLACE FUNCTION merge_orders()
RETURNS VOID AS $$
BEGIN
MERGE INTO orders o
USING (SELECT * FROM staging_orders WHERE NOT is_processed) s
ON o.order_id = s.order_id
WHEN MATCHED AND o.source_hash <> s.source_hash THEN
UPDATE SET
customer_id = s.customer_id,
order_date = s.order_date,
total_amount = s.total_amount,
status = s.status,
updated_at = NOW(),
source_hash = s.source_hash,
version = o.version + 1
WHEN NOT MATCHED THEN
INSERT (order_id, customer_id, order_date, total_amount, status, source_system, source_hash)
VALUES (s.order_id, s.customer_id, s.order_date, s.total_amount, s.status, s.source_system, s.source_hash);
UPDATE staging_orders SET is_processed = TRUE WHERE NOT is_processed;
END;
$$ LANGUAGE plpgsql;
Expert Practices: Idioms, Anti-Patterns & Gotchas
High-signal, mechanism-level guidance. Most examples are PostgreSQL; the principles generalize. Verify version-specific features against your installed version.
Keys, Types & Modeling
Default BIGINT GENERATED ALWAYS AS IDENTITY for surrogate keys. INT4 (SERIAL/serial) caps at 2,147,483,647 and fails hard on INSERT at the limit — high-volume tables hit this in months, and the BIGINT sequence behind a SERIAL never overflows, so watching max(id) understates the risk; migrating INT→BIGINT forces a full-table rewrite under ACCESS EXCLUSIVE. SERIAL is also a non-standard pseudo-type with awkward sequence ownership/permissions/pg_dump. IDENTITY (SQL:2003, PG10+) binds the sequence to the column and blocks accidental manual overrides. Use BY DEFAULT only when importing explicit values.
CREATE TABLE events (id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, name TEXT NOT NULL);
SELECT max(id)::float / 2147483647 AS pct_used FROM high_volume_table;
Prefer UUIDv7 over UUIDv4 when you need UUID keys. UUIDv4 (gen_random_uuid()) is fully random, so every insert lands at an arbitrary B-tree leaf — frequent page splits, ~50-69% page fill vs ~90%+, larger indexes, lower insert throughput as the table grows. UUIDv7 (RFC 9562) puts a 48-bit millisecond timestamp in the high bits, so values append to the right of the index like a BIGINT while staying globally unique — the tradeoff is an embedded approximate creation time. PG18 ships built-in uuidv7(); PG14-17 use the pg_uuidv7 extension. BIGINT IDENTITY is still fastest and smallest for pure sequential inserts — reach for UUIDv7 specifically when distributed/opaque/client-generated IDs are required.
CREATE TABLE orders (id UUID PRIMARY KEY DEFAULT uuidv7());
Always use TIMESTAMPTZ for real-world instants; never bare TIMESTAMP. TIMESTAMP stores a wall-clock picture with no UTC anchor — any offset in the input literal is silently discarded ('2024-01-15 12:00:00+05:30'::timestamp stores 12:00:00), so DST arithmetic and cross-client comparisons silently produce wrong answers. TIMESTAMPTZ converts to UTC on write and to the session zone on read; both are 8 bytes, so there is no storage reason to prefer TIMESTAMP. Related traps: timestamp(N) rounds fractional seconds — use date_trunc('second', ...) to truncate; and store IANA names ('America/New_York'), never fixed-offset abbreviations like 'EST' that ignore DST. Also flag money (locale-dependent rounding, no fractional cents) and char(n) (space-padding, no perf benefit over varchar/text) as anti-patterns.
Avoid EAV; reach for typed columns + a JSONB escape hatch. Entity-Attribute-Value (entity_id, attr_name TEXT, value TEXT) looks schemaless but every value is TEXT (no numeric/date/enum enforcement), reading all attributes of an entity needs one self-join per attribute (defeating indexes and planner statistics), and a typo in attr_name silently creates a new "attribute" no constraint can catch. For dynamic attributes on an otherwise normalized table use a JSONB column (GIN indexing, containment operators, type-aware access) while fixed columns keep their relational guarantees. Reserve EAV for the rare case where attribute names themselves are unstructured and user-controlled.
CREATE TABLE products (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
name TEXT NOT NULL,
price NUMERIC(10,2) NOT NULL CHECK (price >= 0),
extra_attributes JSONB
);
CREATE INDEX idx_products_attrs ON products USING GIN (extra_attributes);
Normalize the system of record; derive denormalized read models. Designing Data-Intensive Applications (Kleppmann) distinguishes the system of record (source of truth, each fact written once, normalized) from derived data (caches, indexes, materialized views, read models computed from it). This dissolves the false "normalize or denormalize" dilemma: normalize so each fact has one home, then DERIVE hot read models via materialized views, event-driven projections, or continuous aggregates — and let the derivation pipeline keep them consistent, not duplicated app-side writes. Storing a pre-computed total on orders and recomputing it in a view creates two sources of truth that drift.
Indexing
Index every foreign key column on the child side — PostgreSQL never does it automatically. It auto-indexes the parent primary key but NOT the referencing child column. Two consequences: (1) joins from child to parent fall back to a sequential scan of the child; (2) every parent DELETE/UPDATE triggers a referential-integrity check that scans the child for referencing rows — once per affected parent row, so deleting N parents is O(N) seq scans (the same cliff hits ON DELETE CASCADE). Documented cases show 50k parent deletes dropping from ~30 minutes to ~100ms after adding the child index. (The RI check uses row-level FOR KEY SHARE locks on referenced rows, not a table-level lock on the child.) Exception: very small child tables where a seq scan beats an index scan.
CREATE INDEX idx_order_items_order_id ON order_items(order_id);
CREATE INDEX idx_order_items_product_id ON order_items(product_id);
SELECT schemaname, relname AS table, indexrelname AS index, idx_scan
FROM pg_stat_user_indexes WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;
Every index is a write tax; don't over-index write-heavy OLTP tables. Each index is a separate B-tree updated on every INSERT/UPDATE/DELETE. Under MVCC an UPDATE writes a new row version and needs new index entries in every index covering the changed columns (HOT updates avoid this only when no indexed column changes and the page has room). Five indexes mean ~5x index write I/O, more WAL, faster bloat, and more autovacuum pressure — a loop where bloat slows scans and tempts yet more indexes. Before adding an index, ask whether a query can be restructured or an existing index extended; the planner usually uses only one index per scan anyway.
Use INCLUDE for covering indexes; index-only scans depend on the visibility map. An index-only scan answers a query entirely from the index when every referenced column (SELECT/WHERE/ORDER BY/GROUP BY) is present. INCLUDE (PG11+) stores payload columns only in leaf pages — they don't widen the tree, don't affect ordering, and don't participate in uniqueness, so UNIQUE INDEX (user_id) INCLUDE (email) enforces uniqueness on user_id alone. Critical dependency: index-only scans require the heap page's visibility-map bit to be set; on write-heavy tables with stale visibility maps the planner falls back to heap fetches. Verify the plan shows Index Only Scan, not Index Scan.
CREATE INDEX idx_users_lookup ON users(user_id) INCLUDE (email, status);
Partial-index predicates match by simple implication at plan time — parameterized queries never use them. A partial index indexes only rows satisfying a predicate (smaller, faster, cheaper to maintain). But the planner uses it only when it can prove at plan time that the query WHERE implies the index predicate — there is no theorem prover; it recognizes only simple inequality implications (x < 1 ⇒ x < 2), otherwise the query predicate must exactly match part of the index predicate. Because matching is at plan time, a prepared x < $1 can never be proven to imply x < 2, so partial indexes intended for hot subsets are silently unused under most ORMs — verify with EXPLAIN (ANALYZE) on the actual parameterized statement. Anti-pattern: many per-value partial indexes (one per status) instead of one composite (status, ...). Useful idiom: partial UNIQUE for soft-delete uniqueness.
CREATE INDEX idx_orders_pending ON orders(user_id, created_at) WHERE status = 'pending';
CREATE UNIQUE INDEX idx_users_email_active ON users(email) WHERE deleted_at IS NULL;
Mark functions IMMUTABLE only if truly immutable; config-dependent ones must be STABLE. Expression indexes require IMMUTABLE, which promises identical output for identical input forever — no DB access, no config/session dependency. Mislabeling a config-dependent function (depends on TimeZone, lc_collate, search_path, current_setting(), or calls now()) lets the planner fold it to a constant at plan time and bake a stale value into cached/prepared plans, silently returning wrong results. A timestamptz::date cast is the classic offender (depends on TimeZone). Use STABLE for functions constant within one statement but session-dependent — they may appear in WHERE but cannot define an index expression.
CREATE FUNCTION lower_email(text) RETURNS text LANGUAGE sql IMMUTABLE STRICT AS $$ SELECT lower($1) $$;
CREATE INDEX idx_users_lower_email ON users(lower_email(email));
GIN on JSONB: pick jsonb_path_ops for containment, disable fastupdate for steady latency. (1) jsonb_ops (default) supports @>, ?, ?|, ?&, etc.; jsonb_path_ops supports only @>/@?/@@ but is smaller and faster — use it when queries are containment-only. Either way, expression access like payload->>'type' = 'login' is NOT GIN-indexable and silently seq-scans. (2) fastupdate (on by default) buffers entries in a pending list; inserts are fast until the list exceeds gin_pending_list_limit, then the next insert pays a synchronous cleanup — a P99/P999 latency spike invisible in averages, costly after bulk loads. Set fastupdate = off for write-heavy/bulk workloads.
CREATE INDEX idx_events_payload ON events USING GIN (payload jsonb_path_ops);
CREATE INDEX idx_docs_tags ON documents USING GIN (tags) WITH (fastupdate = off);
BRIN is tiny on physically-correlated columns, useless on random data. A Block Range INdex stores only a min/max summary per range of consecutive heap pages, so the whole index for a 100M-row table can be kilobytes; the planner skips ranges whose summary can't match. Hard requirement: the column must be well-correlated with physical row order. For append-only tables with a monotonic timestamp/ID (logs, events, metrics) summaries are tight and disjoint — excellent for range scans. For randomly ordered columns (e.g. last_name on OLTP) ranges overlap and BRIN degenerates to a full scan. It is not a substitute for B-tree point lookups — combine them (BRIN on time, B-tree on the lookup key).
CREATE INDEX idx_events_brin ON events USING BRIN (occurred_at);
For pgvector in production prefer HNSW over IVFFlat. IVFFlat must be built on already-populated data (it trains cluster lists) and gives a weaker recall/latency tradeoff. HNSW builds an incremental multi-layer graph, can be created on an empty table, and delivers better recall at a given latency — the recommended default for production/real-time-insert workloads. IVFFlat is fine for batch-rebuild pipelines where build speed beats incrementality. Tune m/ef_construction at build time and hnsw.ef_search at query time.
CREATE INDEX ON embeddings USING hnsw (embedding vector_cosine_ops) WITH (m = 16, ef_construction = 64);
SET hnsw.ef_search = 100;
Querying & Concurrency
NOT IN with a nullable subquery silently returns zero rows — use NOT EXISTS. SQL three-valued logic breaks NOT IN: x NOT IN (a, b, NULL) expands to x <> a AND x <> b AND x <> NULL; the last term is UNKNOWN, UNKNOWN AND anything is UNKNOWN, and WHERE discards it — so one NULL in the subquery empties the entire result set, with no error or warning. This is standard SQL, listed on the PostgreSQL wiki "Don't Do This" (and NOT IN can't be optimized into an anti-join). NOT EXISTS checks row existence rather than a comparison, handles NULLs correctly, and optimizes into an anti-join.
SELECT id FROM products p
WHERE NOT EXISTS (SELECT 1 FROM order_items oi WHERE oi.product_id = p.id);
READ COMMITTED (the default) allows lost updates, vanishing DELETEs, and write skew. Each statement (not transaction) gets a fresh snapshot. An UPDATE/DELETE finds rows committed as of statement start, but if a concurrent transaction already modified a target row, the WHERE is re-evaluated against the new row version after locking — so given hits ∈ {9,10}, one session's UPDATE website SET hits = hits + 1 racing another's DELETE FROM website WHERE hits = 10 can delete zero rows (the 10 became 11 before the DELETE locked it). Cross-row invariants (write skew) are not prevented by READ COMMITTED or REPEATABLE READ; only SERIALIZABLE detects them, aborting one tx with SQLSTATE 40001 — so the app must retry the whole transaction. (A single balance = balance + 100 statement is atomic and safe; the danger is read-then-write across statements and multi-row invariants.)
BEGIN ISOLATION LEVEL SERIALIZABLE;
SELECT sum(value) FROM accounts WHERE group_id = 1;
INSERT INTO accounts (group_id, value) VALUES (1, 50);
COMMIT;
Use MERGE (PG15+) for multi-action sync; keep ON CONFLICT for single-constraint upserts. MERGE does WHEN MATCHED (UPDATE/DELETE) and WHEN NOT MATCHED (INSERT) in one statement — the right tool for syncing a staging set (insert+update+delete in one pass), eliminating the race window of a hand-rolled two-pass function. Important: INSERT ... ON CONFLICT DO UPDATE is still better for a simple upsert against a single unique constraint because it has purpose-built INSERT-race handling; classic MERGE does NOT detect a concurrent INSERT and under READ COMMITTED can still raise a unique violation on its INSERT branch. Cardinality rule: each target row must match at most one source row, or MERGE errors; use stable source ordering to reduce deadlocks.
Migrations & DDL (lock-aware)
ALTER TABLE locks stack — a fast DDL can freeze a table behind one long query. Most ALTER TABLE forms take ACCESS EXCLUSIVE; the trap is the lock queue — a long query holding even ACCESS SHARE makes the DDL wait, and every subsequent SELECT/INSERT/UPDATE/DELETE then queues behind the waiting DDL. So a 3-second DDL can stall all traffic for as long as the slow query runs, then unblock a thundering herd. Mitigate with a low lock_timeout (fail fast) plus retry with exponential backoff + jitter. Rewrite rules: a constant DEFAULT on ADD COLUMN is metadata-only on PG11+, but a volatile default (gen_random_uuid(), now()) and almost any column type change rewrite the whole table.
SET lock_timeout = '200ms';
ALTER TABLE orders ADD COLUMN tier VARCHAR(20) DEFAULT 'standard';
Add FK/CHECK/NOT NULL to large tables with NOT VALID + VALIDATE CONSTRAINT. A plain ADD CONSTRAINT scans the whole table to validate existing rows (CHECK/NOT NULL hold ACCESS EXCLUSIVE for the scan). The two-phase pattern: (1) ADD CONSTRAINT ... NOT VALID enforces on new writes immediately with a brief lock and no scan; (2) VALIDATE CONSTRAINT scans only pre-existing rows under SHARE UPDATE EXCLUSIVE, which does NOT block concurrent DML. NOT VALID works for FK/CHECK on PG9.6+ and for NOT NULL on PG18+. A validated CHECK proving non-null lets a subsequent SET NOT NULL skip its scan. For UNIQUE, build with CREATE UNIQUE INDEX CONCURRENTLY then ADD CONSTRAINT ... USING INDEX.
ALTER TABLE orders ADD CONSTRAINT chk_amount_positive CHECK (total_amount > 0) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT chk_amount_positive;
CREATE INDEX CONCURRENTLY can leave an INVALID index that still costs writes — and IF NOT EXISTS hides it. On failure (lock timeout, unique violation, deadlock, cancellation) it leaves a pg_index row with indisvalid = false. The planner ignores an invalid index for reads (no benefit) but it still receives updates on every write (full write cost). IF NOT EXISTS makes a retry see the existing invalid name, skip creation, and return success while the dead index lingers. Detect via pg_index WHERE NOT indisvalid; recover with DROP INDEX CONCURRENTLY then rebuild, or REINDEX INDEX CONCURRENTLY.
SELECT indexrelid::regclass AS index FROM pg_index WHERE NOT indisvalid;
DROP INDEX CONCURRENTLY IF EXISTS idx_orders_status;
CREATE INDEX CONCURRENTLY idx_orders_status ON orders(status);
Partitioning, Temporal & Generated Columns
Use declarative partitioning (PG10+), not inheritance + trigger routing. Pre-10 partitioning meant table inheritance plus hand-written INSERT triggers — slow, error-prone, and the parent silently accumulates unrouted rows if a trigger is missing. Declarative PARTITION BY RANGE/LIST/HASH automates routing, models bounds as real constraints (enabling plan-time partition pruning), and supports partition-wise joins/aggregates and ATTACH/DETACH. New projects should never use inheritance-based partitioning.
CREATE TABLE measurements (measured_at TIMESTAMPTZ NOT NULL, device_id UUID NOT NULL, value DOUBLE PRECISION NOT NULL)
PARTITION BY RANGE (measured_at);
CREATE TABLE measurements_2025 PARTITION OF measurements FOR VALUES FROM ('2025-01-01') TO ('2026-01-01');
PG18 generated columns are VIRTUAL by default — only STORED can be indexed. Through PG17 generated columns were always STORED (computed on write, persisted). PG18 makes VIRTUAL the default (computed at read time, no storage). VIRTUAL suits write-heavy workloads (no write cost) but cannot be indexed; STORED suits read-heavy/expensive expressions because it can be indexed. Neither may reference other generated columns, subqueries, or non-immutable functions. On PG18, omitting STORED yields a VIRTUAL column, so any index on it errors — add STORED when you intend to index.
CREATE TABLE documents (
body TEXT NOT NULL,
tsv TSVECTOR GENERATED ALWAYS AS (to_tsvector('english', body)) STORED
);
CREATE INDEX ON documents USING GIN (tsv);
PG18 adds native temporal (non-overlapping) constraints — WITHOUT OVERLAPS / PERIOD. PG18 adds temporal constraints over range types: PRIMARY KEY/UNIQUE gain WITHOUT OVERLAPS, and FOREIGN KEY uses PERIOD (not WITHOUT OVERLAPS). This is the canonical pattern for SCD Type 2 dimensions, price/rate history, and reservations, replacing the btree_gist + EXCLUDE USING GIST (... WITH &&) idiom (still required pre-18).
CREATE TABLE product_prices (
product_id INT NOT NULL REFERENCES products(id),
price_cents BIGINT NOT NULL,
valid_period daterange NOT NULL,
PRIMARY KEY (product_id, valid_period WITHOUT OVERLAPS)
);
Operational Gotchas (design with these in mind)
Monitor transaction-ID wraparound — unfrozen XIDs eventually hard-stop all writes. PostgreSQL uses a 32-bit XID with modulo-2^32 comparison; within ~3M XIDs of wraparound it refuses to assign new XIDs, halting all writes and DDL (only VACUUM and read-only queries run). VACUUM prevents this by freezing old rows; the common blockers are long-running transactions, abandoned prepared transactions, and stale replication slots holding xmin. Autovacuum handles freezing by default, but on high-churn systems monitor age(datfrozenxid) and alert with lead time.
SELECT datname, age(datfrozenxid) AS xid_age, 2000000000 - age(datfrozenxid) AS xids_remaining
FROM pg_database ORDER BY age(datfrozenxid) DESC;
Bound WAL retention with max_slot_wal_keep_size when using logical replication / CDC. A logical replication slot retains WAL until the subscriber confirms receipt. With the default -1 (unlimited), a stalled or disconnected CDC consumer (Debezium, pglogical) accumulates WAL until the disk fills and the primary halts all writes. PG13+ lets you cap it (e.g. 50GB); past the cap PostgreSQL invalidates the slot and reclaims WAL rather than filling the disk — losing that stream but keeping the primary alive. Anyone embedding CDC in a pipeline must set this and monitor pg_replication_slots for active = false and wal_status of 'extended'/'lost'.
max_slot_wal_keep_size = 50GB
MongoDB unbounded embedded arrays degrade well before the 16MB document limit. The performance cliff comes far earlier than 16MB: a growing embedded array (comments, events, log lines) rewrites the entire document on each push, so write cost scales with document size, and a multikey index stores one entry per element (10k elements = 10k index entries to maintain). The result is progressively slower writes with no error. Beyond roughly a few hundred elements on frequently-updated documents, switch to the Subset pattern (embed the most recent N, store the rest in a sibling collection fetched via $lookup) or full referencing — trading some read latency for bounded write cost.
Verification Checklist
Schema design:
Migration (large tables):
