Lesson 8 of 11 · 20 min
What indexes cost
By the end of this lesson you can explain the write and storage cost of indexes, measure index sizes, find duplicate and prefix-redundant indexes and drop them, and audit foreign-key columns for missing indexes.
An index is a copy of some columns, kept sorted and kept in sync with the table at all times. That has three costs:
- Writes. Every INSERT adds an entry to every index on the table. Every DELETE leaves entries to clean up. An UPDATE that changes an indexed column (or can't fit the new row version on the same page) adds entries to every index. PostgreSQL has an optimisation called HOT (heap-only tuple) updates that skips index maintenance when no indexed column changes and the page has room, so indexing a frequently updated column also costs you HOT updates.
- Space. Indexes live in their own files, on disk and in memory. Memory spent caching indexes nobody uses is memory not caching data.
- Maintenance and planning. More indexes mean more for VACUUM to clean, longer backups and restores, and more plans for the planner to consider.
So the goal is not 'as many indexes as possible' but the smallest set of indexes that serves the important queries.
This lesson, its 3 graded exercises and every other Members lesson in SQL Quest come with a Formula Quest membership that includes SQL (Single Quest with SQL selected, Quest Trio including SQL, or Quest Unlimited), or with SQL access from your organization.
Free to everyone: all of SQL Foundations and Retrieving and Filtering Data, the first lesson of every track, the playground, the dataset library and the SQL reference. See free lessons