Refactoring SQL: A Pattern-Oriented Guide to Database Design, Query Optimization, and Schema Refactoring

Most SQL books teach you syntax. This one teaches you how to think like a query optimizer.

If you've ever stared at an execution plan wondering why a query that runs fine in development takes 30 seconds in production, this book is for you. If you've inherited a database where every table is named t_data and every column is VARCHAR(255), this book is for you. If you've been told just add an index without understanding which index, why it works, or when it makes things worse, this book is for you.
Refactoring SQL is not a syntax reference. It's a pattern-oriented field manual for making relational databases faster, cleaner, and more maintainable — without taking them offline.

What's inside

Part I — Foundations. Why database refactoring is fundamentally harder than code refactoring. How the query planner works, how it estimates cost, and how to read execution plans. Seven universal performance principles that every other chapter builds on: read fewer rows, read fewer columns, do less work, reuse work, minimize writes, think in sets, and measure before optimizing.

Part II — Query Refactoring . Predicate pushdown, EXISTS vs IN, converting correlated subqueries to JOINs, window functions as a replacement for self-joins, eliminating unnecessary DISTINCT, and why SELECT * is a liability. A full chapter on keyset pagination and why OFFSET 100000 destroys performance. A chapter on set-based thinking that will change how you write SQL.

Part III — Index Refactoring . B-trees, hash indexes, GiST, GIN, BRIN. Covering indexes, composite indexes, column ordering, partial indexes, functional indexes. An entire chapter on sargability — the most important indexing concept that most books barely mention — covering why WHERE UPPER(name) = 'SMITH' can't use your index, and exactly how to fix it.

Part IV — Schema Refactoring . Renaming tables safely. Splitting and merging tables. Strategic denormalization: when to duplicate data, materialize aggregates, cache counts, and build read models — and how to manage the consistency trade-offs. Partitioning from 100M rows to monthly partitions to archive tables, with real migration scripts.

Part V — Refactoring for Scale . Materialized views, read replicas, caching strategies. Batch writes, event sourcing, async processing. Transactions, isolation levels, MVCC internals, deadlock prevention, optimistic and pessimistic locking — covered in more depth than the typical use transactions advice.

Part VI — Database Smells. 39 smells you can detect and fix today: SELECT *, N+1 queries, huge OFFSETs, DISTINCT addiction, OR abuse, cartesian joins, correlated subqueries, EAV tables, comma-separated values, missing foreign keys, over-indexing, VARCHAR for everything, duplicate indexes, wrong column orders, and more. Each smell includes detection queries and a concrete fix.

Part VII — Practical Refactoring Catalog . The heart of the book. 80 concrete refactorings, each with: the problem SQL, the solution SQL, ASCII execution plans before and after, when to use it, and when not to.
Part VIII — Database Engineering. Monitoring with pg_stat_statements and the Performance Schema. Benchmarking with EXPLAIN ANALYZE done properly. Zero-downtime migrations using the expand-contract pattern for production schema changes.

Parts IX–X — Reference . A complete SQL reference covering data types, constraints, joins, window functions, CTEs, MERGE/UPSERT, materialized views, transactions, isolation levels, JSOrrays, and a comprehensive SQL functions reference with vendor-to-vendor mapping tables. Appended with an SQL:86 to SQL:2023 evolution guide.
What makes this book different