Schema & Indexes

Table design, keys, constraints, and how indexes make or break query speed

Why

A well-designed schema prevents entire classes of bugs — referential integrity violations, duplicate records, invalid states. Indexes are the primary lever for query performance: a missing index on a join column can turn a 10ms query into a 10-second full table scan. Understanding when and why indexes help (and when they hurt) is essential for both schema design and query tuning.

Intuition

A B-tree index is like a book's index — it sorts values so the database can jump to a row in O(log n) rather than scanning the whole table O(n). The cost: every write (INSERT, UPDATE, DELETE) must also update the index. Indexes make reads faster and writes slightly slower — the trade-off is almost always worth it for columns used frequently in WHERE, JOIN, and ORDER BY.

Normalization reduces redundancy by splitting data into separate tables linked by foreign keys. The trade-off: fewer writes to update in one place, but more joins to read data back together.

Explanation

Keys and constraints

CREATE TABLE orders ( id SERIAL PRIMARY KEY, -- unique, not null, clustered customer_id INT NOT NULL REFERENCES customers(id) -- foreign key — referential integrity ON DELETE CASCADE, -- delete orders if customer deleted status TEXT NOT NULL CHECK (status IN ('pending','shipped','delivered','cancelled')), amount NUMERIC(10, 2) NOT NULL CHECK (amount > 0), created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), UNIQUE (customer_id, created_at) -- composite unique constraint ); -- Constraints the DB enforces automatically: -- PRIMARY KEY: unique + not null -- FOREIGN KEY: referenced row must exist -- UNIQUE: no duplicates in the column(s) -- CHECK: boolean expression must be true -- NOT NULL: value required

Indexes — types and when to use them

-- Single-column index — speeds up equality and range filters on that column CREATE INDEX idx_orders_customer ON orders(customer_id); -- Composite index — order matters: most selective / most-queried column first CREATE INDEX idx_orders_status_date ON orders(status, created_at); -- Useful for: WHERE status = 'pending' ORDER BY created_at -- NOT useful for: WHERE created_at > '2024-01-01' (leading col not used) -- Covering index — includes extra columns to avoid reading the base table CREATE INDEX idx_orders_covering ON orders(customer_id) INCLUDE (amount, status); -- Query can be answered entirely from the index — no table lookup -- Partial index — index only a subset of rows CREATE INDEX idx_active_orders ON orders(created_at) WHERE status = 'pending'; -- much smaller, faster for pending-only queries -- When NOT to add an index: -- Small tables (full scan is fine), low-cardinality columns (boolean, status), -- heavy-write tables where index maintenance cost outweighs read benefit

← Previous Next →