Week 6: Constraints, Indexes & Query Performance

1. Constraints: NOT NULL, UNIQUE & CHECK

CREATE TABLE products (
    id    SERIAL PRIMARY KEY,
    sku   TEXT NOT NULL UNIQUE,
    name  TEXT NOT NULL,
    price NUMERIC(10, 2) NOT NULL CHECK (price >= 0)
);

INSERT INTO products (sku, name, price) VALUES ('MOUSE-01', 'Mouse', -5);
-- ERROR: new row violates check constraint "products_price_check"

These aren't application-level validation — they're enforced by Postgres itself, on every insert and update, from every client, forever. Application code should still validate input for a better user experience (a friendly error message beats a raw constraint violation), but the database-level constraint is what actually guarantees the data can never violate the rule, regardless of which application or script writes to it.

2. B-Tree Indexes

Without an index, finding a row means scanning every row in the table — a sequential scan. An index on a column lets Postgres jump directly to matching rows instead, the same way a book's index beats reading every page to find a topic.

CREATE INDEX idx_orders_customer_id ON orders(customer_id);

An index helps a query that filters or joins on that column — but it isn't free: every index adds overhead to every INSERT/UPDATE/DELETE (the index itself has to be maintained), and takes disk space. The practical rule: index columns genuinely used in WHERE, JOIN conditions, or ORDER BY on large tables — not every column defensively.

3. Reading EXPLAIN ANALYZE

EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42;

-- Seq Scan on orders (cost=0.00..1834.00 rows=5 width=20) (actual time=0.02..12.4 rows=5 loops=1)
--   Filter: (customer_id = 42)
--   Rows Removed by Filter: 99995

EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42;

-- Index Scan using idx_orders_customer_id on orders (cost=0.29..8.31 rows=5 width=20) (actual time=0.01..0.02 rows=5 loops=1)
--   Index Cond: (customer_id = 42)

EXPLAIN shows the plan Postgres intends to use; EXPLAIN ANALYZE actually runs the query and reports real timing alongside it. "Seq Scan," reading nearly 100,000 rows to find 5, is the tell that a useful index is missing; "Index Scan" after adding one confirms the fix actually worked — the same measure-before/measure-after discipline this site's Go and Flutter courses both teach for their own performance work.

4. Common Performance Pitfalls

• A function applied to the indexed column — WHERE LOWER(email) = 'asha@example.com' can't use a plain index on email, since the index stores the original values, not their lowercased form. A functional index (CREATE INDEX ... ON users (LOWER(email))) fixes this specifically.
• A leading wildcard in LIKE — LIKE '%asha%' can't use a standard B-tree index at all, since there's no fixed prefix to jump to; LIKE 'asha%' can.
• Indexing a low-cardinality column — an index on a boolean or a status column with only 3 possible values often doesn't help, since Postgres may reasonably decide a sequential scan is cheaper than the overhead of using the index anyway.
• Trusting intuition over EXPLAIN ANALYZE — the only way to know whether an index actually helped, or whether the planner is even using it, is to check.

5. Hands-on Exercise

6. Knowledge Check

Four quick questions. Expand each to check your answer.