There's a persistent myth among junior developers: if a database feels slow, just slap an index onto every column in your WHERE clause and everything will magically fly.
I often run into production tables with 15 columns and 10–12 separate indexes piled on top. On the surface, it looks great: SELECT finds a row in fractions of a millisecond. Meanwhile, the live system starts gasping for air—disks saturate with I/O, transactions stall, and read replicas fall tens of seconds behind.
What's the catch? Indexes don't make a database faster in an absolute sense. An index is an auxiliary data structure that buys faster reads at the cost of a guaranteed tax on every write operation (INSERT, UPDATE, DELETE).
B-Tree Anatomy: What Lives Inside 8-Kilobyte Pages
In relational databases like PostgreSQL and MySQL/InnoDB, the workhorse is a balanced tree—specifically the B-Tree in PostgreSQL's documentation, implemented via the Lehman & Yao algorithm.
The tree lives on disk as fixed 8-kilobyte pages arranged in three layers:
- Root page: the entry point for every lookup.
- Internal pages: routers holding separator keys and pointers down to the next level.
- Leaf pages: the bottom layer storing sorted pairs of
key_valueand tuple identifierTID(table page number + row offset). All leaf pages are linked in a doubly linked list, enabling fast range scans (BETWEEN,>,<).
Finding a row in a B-Tree takes O(log n) work: instead of sequentially scanning the whole table, the planner reads just 3–4 tree pages.
What Happens Under the Hood on INSERT: Page Splits and WAL Floods
Now picture a table with 8 indexes on it. When your app executes a single plain INSERT:
- The database writes the row into the table heap.
- Then it has to walk from root to leaf in all 8 indexes, one after another!
- On each index, it acquires an exclusive lock and inserts the value in sorted order.
That's when the real pain hits: a Page Split. Each B-Tree leaf page is fixed at 8 KB. If there's no free space left, the engine has to allocate a brand-new page, move roughly half (50%) of the entries over, rewrite sibling pointers, and insert a new separator key into the parent page. If the parent page is also full, the split cascades recursively all the way up to the root!
Every page split unleashes a flood of writes into the Write-Ahead Log (WAL). Instead of flushing one tiny row, the database writes megabytes of internal pages to disk. The storage subsystem chokes, and replicas lag behind the primary.
How to Cut the Write Tax: HOT Updates and fillfactor
To avoid paying for indexes with write degradation, engineers lean on three proven techniques:
- Heap-Only Tuples (HOT optimization in PostgreSQL): if an
UPDATEmodifies only columns that aren't part of any index, and there's free space on the same table page, PostgreSQL skips touching the indexes altogether! The new row version is written onto the same page and linked via an internal chain. Index maintenance overhead drops to zero. - Tuning
fillfactor: by default, PostgreSQL packs table pages to 100%. That leaves no room for a new row version onUPDATE, breaking the HOT chain. Loweringfillfactorfrom 100% to 80% on write-heavy tables (ALTER TABLE orders SET (fillfactor = 80)) reserves 20% breathing room per page, pushing fast HOT updates past 95%. - Partial Indexes: instead of indexing the entire table, index only active rows (
WHERE status = 'pending'). That shrinks tree size dramatically and eliminates the write tax on archived records.
Indexes are a line of credit: you borrow microseconds on reads and pay interest in disk I/O on every write. If you want to sharpen your query instincts further, check out our guide on 10 rules for fast SQL: indexes, selectivity, and reading execution plans.

