Read
SQL Performance Explained
Covers composite index column order, why a function on a column disables its index, and why OFFSET pagination slows down with every page, with the full text readable free as Use The Index, Luke.
Data Modeling and Databases
Mistakes to catch in review
Lists stored as comma-separated strings or opaque JSON blobs, making queries slow and integrity impossible to enforce.
A price column that allows NULL, so a missing price drops out of the invoice SUM and the customer is under-billed.
A filter on an unindexed column that runs instantly on test data and times out on production data.
Offset pagination that gets slower with every page as the table grows.
Designing schemas, keys and constraints, choosing the right kind of store, and understanding how a database finds data quickly.
Topics
- Relational Modeling and Normalization
- Tables, relationships and normal forms, and the update anomalies normalization prevents.
- Keys and Constraints
- Primary keys, foreign keys, unique and check constraints as rules the database enforces on every write.
- Choosing a Data Store
- Relational, document, key-value, search and time-series stores, matched to the access pattern.
- Indexes
- B-trees, composite and covering indexes, and the write cost every index adds.
- Query Plans
- Reading how the database executes a query, and spotting full scans, bad join orders and deep pagination.
You understand it when you can
- Design a normalized schema for a small domain, with keys and constraints, and explain where you would denormalize and why.
- Read a query plan and say whether an index is used and why.
- Choose a composite index for a given query and justify the column order.
Drill
An agent designed an orders table with a tags column holding comma-separated strings, no foreign key to customers, and a dashboard query that filters on an unindexed created_at column. Find the three problems and predict which query times out first as the table grows.
Start here
Watch
Learn Database Normalization - 1NF, 2NF, 3NF, 4NF, 5NF
Walks one example table through each normal form and shows the insert, update and delete anomalies each step removes, including why a multi-valued column breaks first normal form.
F2023 #08 - B+Tree Indexes (CMU Intro to Database Systems)
Explains how a B+tree finds rows in logarithmic time, why a composite index can only be used from its leftmost columns, and what each insert costs to keep the tree balanced.
PGConf NYC 2022 Understanding PostgreSQL Query Plans (aka EXPLAIN) by Stephen Frost
Reads real EXPLAIN output node by node, separating sequential scans from index scans and estimated from actual row counts, so you can tell when the planner skipped an index and why.
Read
SQL Antipatterns, Volume 1: Avoiding the Pitfalls of Database Programming
Its Jaywalking chapter is exactly the comma-separated tags column, and other chapters cover missing foreign keys and entity-attribute-value tables, each with the query that breaks and the schema that fixes it.
Designing Data-Intensive Applications: The Big Ideas Behind Reliable, Scalable, and Maintainable Systems
Compares relational, document and graph data models against access patterns, then explains how B-trees and LSM-trees store and index data, which is the basis for choosing a data store.
Primary sources
Manual
PostgreSQL Documentation: Using EXPLAIN
The official guide to reading plan nodes, cost estimates and EXPLAIN ANALYZE timings, with worked examples of index scans, bitmap scans and joins.
Manual
PostgreSQL Documentation: Constraints
Defines check, not-null, unique, primary key and foreign key constraints and the ON DELETE actions, the rules the database enforces on every write.