Indexes, Performance and Migrations

Indexes

Without an index, finding one email in a million users means scanning every row. An index is a sorted lookup structure, like a book's index.

CREATE INDEX idx_orders_customer ON orders (customer_id);
CREATE UNIQUE INDEX idx_users_email ON users (lower(email));
CREATE INDEX idx_orders_customer_date ON orders (customer_id, created_at DESC);

Index:

• foreign keys you join on (orders.customer_id),
• columns you filter or sort by often,
• columns that must be unique.

Do not index everything: each index slows inserts and updates and uses space.

Reading a query plan

EXPLAIN ANALYZE                -- EXPLAIN QUERY PLAN in SQLite
SELECT * FROM orders WHERE customer_id = 42 ORDER BY created_at DESC LIMIT 10;

Look for Seq Scan on large tables (no index used) and compare estimated and actual row counts. After adding the composite index above, the plan should show an Index Scan and the time should drop dramatically.

The N+1 problem

Loading 50 orders, then running one query per order to get its customer, is 51 queries. Use a join, or in SQLAlchemy selectinload/joinedload:

orders = Order.query.options(selectinload(Order.customer)).limit(50).all()

Migrations

Schemas change as apps grow. A migration is a versioned script that changes the schema, stored in Git, applied in order everywhere.

With Flask-Migrate (Alembic):

flask db migrate -m "add phone to customers"   # generate from model changes
# review the generated file in migrations/versions/
flask db upgrade                               # apply
flask db downgrade                             # undo the last one

Rules:

• Never edit production schemas by hand.
• Review autogenerated migrations: they miss renames and data changes.
• Make changes backward compatible (add nullable column → backfill → enforce), so deploys never break running code. The Database Administration track covers zero-downtime techniques.

Backups before changes

Before any risky migration: take a backup (pg_dump), test the migration on a copy of production data, and know how to roll back.

Final project

Design and build the database for an online course platform: users, courses, lessons, enrolments, progress and payments. Write the schema with constraints and indexes, seed realistic data, and write five report queries (revenue per course per month, completion rates, most active learners, courses with no enrolments, each user's latest lesson using a window function). Check each with EXPLAIN and add indexes where scans appear.

Check yourself

• Which columns usually deserve an index?
• What is the N+1 problem?
• Why should migrations be reviewed before running?