What is a database index, and what are the costs of adding one?

What the interviewer wants to hear

• An index is a separate data structure, usually a B-tree, that speeds up lookups
• It helps WHERE, JOIN and ORDER BY on the indexed columns
• It uses extra disk and memory
• Every INSERT, UPDATE and DELETE must also update the index, so writes get slower
• Unused or duplicate indexes should be removed

Model answer

An index is an extra data structure that lets the database find rows without scanning the whole table, a bit like the index at the back of a book. The most common type is a B-tree, which keeps values sorted, so it helps equality lookups, ranges, joins and ORDER BY on those columns. The cost is that indexes take disk space and memory, and every INSERT, UPDATE or DELETE must update each index on the table, which slows writes. Too many indexes also make the planner's job harder. So I add indexes based on real slow queries, check that they are used with EXPLAIN, and regularly look for unused or duplicate indexes, for example with pg_stat_user_indexes in PostgreSQL, and drop them.

More easy Database Administrator questions

• Explain the ACID properties of a database transaction with a simple example.
• How would you design a basic backup strategy for a production database?
• What is normalization, and when would you choose to denormalize?
• How do you find the slowest queries on a database server?
• What checks would you do at the start of a normal day as a DBA?
• What is database replication, and what is the difference between synchronous and asynchronous replication?
• When would you use a logical backup like pg_dump or mysqldump, and when a physical backup?
• What basic steps would you take to secure a new production database?
• Explain the main SQL join types and give an example of when you would use each.