Indexes, Query Plans, and Practical Tuning for Developers

Read before you buy: the PDF here is a free sample — the first 20 pages of the 414-page book (payments, sales and reviews on this demo site are sample data). Get the full e-book at thenexbooks on Gumroad.

The query was fast on your laptop. In production it takes seconds.

Almost every slow application eventually leads to the database — and most database performance problems come from a small set of causes: queries that read far more data than they need, missing or mismatched indexes, plans built on wrong estimates, too many round trips from the application, contention on hot rows, and maintenance that fell behind. Once you understand how a database stores data, how the planner chooses a plan, and how indexes really work, you can find and fix these problems methodically instead of by guesswork.

This book teaches those foundations thoroughly and practically, for developers rather than full-time DBAs. We follow one realistic running example all the way: Wavecrest, a chain of fitness studios with a booking rush when next week's schedule opens, a check-in table that grows every day, month-over-month reports, and an ORM-based API that issues more queries than anyone expects.

• 414 pages · 28 chapters + 4 appendices
• A mental model of where database time goes: pages, the buffer cache, I/O, and waiting
• Measuring properly with latency percentiles, throughput, and baselines
• How the planner works, reading EXPLAIN in PostgreSQL and MySQL, and why estimates go wrong
• Nested loop, hash, and merge joins explained
• B-tree internals, composite column order, covering indexes, and clustered tables
• Partial, expression, GIN, GiST, BRIN, and full-text indexes — and when indexes hurt
• Sargable predicates, keyset pagination, top-N queries, reporting, and materialized views
• Fixing N+1 queries and ORM round trips, and bulk loads, updates, and deletes done safely
• Transactions and MVCC, locks and hot rows, deadlocks, and connection pooling
• Vacuum, autovacuum, and InnoDB purge; finding slow queries with pg_stat_statements and Performance Schema
• Partitioning, read replicas, caching, sharding basics, and a complete tuning case study

Table Of Contents

Part I — Performance Foundations

• Ch 1 · Why Databases Get Slow: A Mental Model of Database Performance
• Ch 2 · How a Database Stores Data: Pages, Rows, the Buffer Cache, and I/O
• Ch 3 · Measuring Before Tuning: Latency, Throughput, Percentiles, and Baselines
• Ch 4 · Setting Up a Performance Lab: PostgreSQL, MySQL, Realistic Test Data, and Tools

Part II — Reading Query Plans

• Ch 5 · How the Query Planner Works: Parsing, Planning, and Cost Estimation
• Ch 6 · Reading EXPLAIN in PostgreSQL
• Ch 7 · Reading EXPLAIN in MySQL (with a Note on SQL Server Plans)
• Ch 8 · Statistics, Cardinality Estimates, and Why Plans Go Wrong
• Ch 9 · Join Algorithms: Nested Loop, Hash Join, and Merge Join

Part III — Indexing

• Ch 10 · B-Tree Indexes from the Inside
• Ch 11 · Composite Indexes and Column Order
• Ch 12 · Covering Indexes, Index-Only Scans, and Clustered Tables
• Ch 13 · Partial, Expression, and Specialized Indexes (GIN, GiST, BRIN, Full-Text)
• Ch 14 · When Indexes Hurt: Write Cost, Bloat, Duplicate and Unused Indexes
• Ch 15 · An Indexing Strategy for Real Workloads

Part IV — Writing Fast Queries

• Ch 16 · Query Patterns That Defeat Indexes: Sargability, Functions, Casts, OR, and LIKE
• Ch 17 · Pagination, Sorting, and Top-N Queries
• Ch 18 · Aggregation, Reporting Queries, and Materialized Views
• Ch 19 · Application Access Patterns: N+1 Queries, ORMs, Batching, and Round Trips
• Ch 20 · Bulk Loading, Batch Updates, and Large Deletes

Part V — Concurrency and Operations

• Ch 21 · Transactions, Isolation Levels, and MVCC Performance
• Ch 22 · Locks, Contention, Hot Rows, and Deadlocks
• Ch 23 · Connections and Connection Pooling
• Ch 24 · Memory, Configuration, and Maintenance: Vacuum, Autovacuum, and InnoDB Purge
• Ch 25 · Monitoring and Finding Slow Queries: pg_stat_statements, the Slow Query Log, and Performance Schema

Part VI — Scaling and Case Study

• Ch 26 · Partitioning and Archiving Large Tables
• Ch 27 · Scaling Beyond One Server: Caching, Read Replicas, and Sharding Basics
• Ch 28 · Case Study: Tuning the Wavecrest Booking Database

Appendices

• App A · Database Performance Cheat Sheet
• App B · Diagnostic Query Templates and Reference
• App C · Performance Checklist
• App D · Glossary

WHO IT IS FOR
Application and backend developers who write SQL directly or through an ORM, full-stack developers who own the database along with everything else, junior DBAs and data engineers who want solid mental models, and tech leads who review schemas and migrations. You should know basic SQL; no prior performance-tuning experience is needed.

FORMAT
PDF (A4), 414 pages, English, clickable table of contents and PDF bookmarks. 1st Edition, 2026. Published by thenexbooks.

This book is independent and is not affiliated with or endorsed by the PostgreSQL Global Development Group, Oracle, or Microsoft. PostgreSQL, MySQL, and SQL Server are trademarks of their respective owners. All plan output and timings in the book are illustrative.

Book details