Database Indexing Costs: Write Overhead, Bloat and When Not to Index

A sensor database ingests temperature readings all day. Someone adds three indexes to make dashboards faster. A week later the nightly load that used to finish before breakfast is still running at lunch, and the disk is nearly twice as full as expected. Nothing is wrong with the indexes' design. They are simply not free. This last lesson puts numbers on the bill and gives you a way to decide which indexes deserve to live.

Every index is a second copy you must keep in sync

An insert into a table with three secondary indexes is four writes: one to the table and one into the right leaf of each index, plus any page splits along the way. Deletes remove entries from every index, and an update that changes an indexed column removes one entry and adds another. People call this write amplification: one logical change becomes several physical ones, each possibly on a different page.

The script below loads 200,000 random readings into a SQLite file three times: with no extra index, with one composite index, and with three. It then reports the file size and the load time. Sizes are stable across runs; the times below are from one run on one machine and will differ on yours, so compare the ratios.

import os
import random
import sqlite3
import tempfile
import time

def build(extra_indexes):
    path = os.path.join(tempfile.mkdtemp(), "readings.db")
    db = sqlite3.connect(path)
    db.execute("""CREATE TABLE readings (
        id INTEGER PRIMARY KEY, sensor_id INTEGER, taken_at TEXT, celsius REAL, note TEXT)""")
    for ddl in extra_indexes:
        db.execute(ddl)
    random.seed(3)
    rows = [(random.randint(1, 500),
             f"2025-{random.randint(1, 12):02d}-{random.randint(1, 28):02d}T{random.randint(0, 23):02d}:00",
             round(random.uniform(-5, 40), 1), "ok") for _ in range(200_000)]
    t0 = time.perf_counter()
    with db:
        db.executemany(
            "INSERT INTO readings (sensor_id, taken_at, celsius, note) VALUES (?,?,?,?)", rows)
    secs = time.perf_counter() - t0
    db.close()
    return path, secs

def mb(path):
    return os.path.getsize(path) / 1_000_000

setups = {
    "no extra index":      [],
    "1 index":             ["CREATE INDEX i1 ON readings (sensor_id, taken_at)"],
    "3 indexes":           ["CREATE INDEX i1 ON readings (sensor_id, taken_at)",
                            "CREATE INDEX i2 ON readings (taken_at)",
                            "CREATE INDEX i3 ON readings (celsius)"],
}
print(f"{'setup':16}{'file MB':>9}{'insert s':>10}")
for name, ddl in setups.items():
    path, secs = build(ddl)
    print(f"{name:16}{mb(path):>9.1f}{secs:>10.2f}")

# bloat: delete most rows, observe the file
path, _ = build(setups["3 indexes"])
db = sqlite3.connect(path)
print("\nbefore delete :", f"{mb(path):.1f} MB")
with db:
    db.execute("DELETE FROM readings WHERE id % 10 != 0")
print("after delete  :", f"{mb(path):.1f} MB", "| rows left:", db.execute("SELECT count(*) FROM readings").fetchone()[0])
print("free pages    :", db.execute("PRAGMA freelist_count").fetchone()[0], "of", db.execute("PRAGMA page_count").fetchone()[0])
db.execute("VACUUM")
print("after VACUUM  :", f"{mb(path):.1f} MB")

setup             file MB  insert s
no extra index        7.9      0.24
1 index              14.0      1.29
3 indexes            23.2      8.89

before delete : 23.2 MB
after delete  : 23.2 MB | rows left: 20000
free pages    : 4708 of 5663
after VACUUM  : 2.2 MB

The load with three indexes took roughly thirty-seven times as long as the bare table in this run, and the file nearly tripled in size. Part of that is the random key order in this test, which forces frequent page splits; loading already-sorted keys is cheaper, though I did not measure that here. The lesson holds regardless: write cost scales with the number of indexes, and so does storage.

Space that does not come back by itself

The second half of the output shows another cost. After deleting 90% of the rows the file is exactly the same size, with 4,708 of 5,663 pages sitting on the free list. SQLite reuses those pages for later inserts but does not hand them back to the operating system until you run VACUUM, which rebuilds the file at 2.2 MB. Other engines have the same shape with different mechanics: PostgreSQL leaves dead row versions that autovacuum reclaims for reuse, and heavily updated indexes can end up bloated and need REINDEX, while MySQL's InnoDB offers OPTIMIZE TABLE. In every case, an index that is half empty still costs memory and disk.

An index that is smaller on purpose

When a query only ever concerns a small slice of a table, index only that slice. A parcel-locker system has 50,000 parcels, almost all already collected. Staff only look up the ones still waiting.

import random
import sqlite3

random.seed(2)
db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE parcels (id INTEGER PRIMARY KEY, locker TEXT NOT NULL, state TEXT NOT NULL, picked_up_at TEXT)")
db.executemany("INSERT INTO parcels (locker, state, picked_up_at) VALUES (?,?,?)",
               [(f"L{random.randint(1, 60)}",
                 "waiting" if i % 50 == 0 else "collected",
                 None if i % 50 == 0 else "2025-05-01") for i in range(1, 50001)])
db.execute("CREATE INDEX idx_waiting ON parcels (locker) WHERE state = 'waiting'")
db.execute("ANALYZE")

def show(sql):
    print(sql)
    for row in db.execute("EXPLAIN QUERY PLAN " + sql):
        print("   ", row[3])

show("SELECT id FROM parcels WHERE state = 'waiting' AND locker = 'L7'")
show("SELECT id FROM parcels WHERE state = 'collected' AND locker = 'L7'")
print("rows in table:", db.execute("SELECT count(*) FROM parcels").fetchone()[0],
      "| rows in partial index scope:", db.execute("SELECT count(*) FROM parcels WHERE state='waiting'").fetchone()[0])

SELECT id FROM parcels WHERE state = 'waiting' AND locker = 'L7'
    SEARCH parcels USING COVERING INDEX idx_waiting (locker=?)
SELECT id FROM parcels WHERE state = 'collected' AND locker = 'L7'
    SCAN parcels
rows in table: 50000 | rows in partial index scope: 1000

The partial index holds 1,000 entries instead of 50,000, so it is cheaper to store and to maintain. The second query asks about collected parcels, which the index does not cover, so it scans. PostgreSQL and SQLite support partial indexes; MySQL does not offer them directly. The planner only uses one when the query's WHERE clause implies the index's condition, so keep the two in agreement.

When not to add an index

A maintenance checklist

Monthly, or after a big schema or traffic change:
[ ] List indexes with zero or near-zero use over a long window.
      PostgreSQL: SELECT relname, indexrelname, idx_scan
                  FROM pg_stat_user_indexes ORDER BY idx_scan;
      MySQL (sys schema): SELECT * FROM sys.schema_unused_indexes;
[ ] Find redundant indexes: one is a leftmost prefix of another.
[ ] Compare index size with table size; a large ratio on a
    write-heavy table deserves a second look.
[ ] Refresh planner statistics after bulk loads (ANALYZE).
[ ] Plan the rebuild of bloated indexes (REINDEX, OPTIMIZE TABLE,
    VACUUM) off-peak, and check how much locking your engine needs.
[ ] On a live system, create indexes with the online option your
    engine offers (PostgreSQL: CREATE INDEX CONCURRENTLY).
[ ] Before dropping, wait a full business cycle: month-end reports
    and rare jobs use indexes too. Keep the DDL to recreate it.

Common mistakes

• Dropping an unused index after a quiet week. Symptom: month-end reports that took seconds start timing out on the first of the month.
• Adding an index to fix a slow write. Symptom: the slow INSERT or UPDATE gets slower, because each index adds work on the write path; look at the plan of the lookup inside the statement instead.
• Building a big index on a live table with a locking command. Symptom: writes queue behind the build and the application stalls; use your engine's online or concurrent form.
• Ignoring bloat after mass deletes. Symptom: table and index files stay huge after archiving old data and queries get no faster; reclaim space with the engine's rebuild tool.

How this shows up in projects and interviews

Teams discover index cost in production: slow bulk loads, replication lag after a migration, or a disk alert. A mature answer to should we index this lists the queries it speeds up, the writes it slows down, and how you would check afterwards. Interview scenarios often describe a write-heavy table and ask what you would do about slow inserts; dropping or consolidating indexes is the expected idea. For the query side of the story, revisit SQL Fundamentals and Advanced SQL, and for caching as an alternative way to speed up reads, see Caching.