Use Database Indexes Without Killing Writes

Database indexing is a fundamental technique for optimizing query performance, particularly in read-heavy applications. However, indexes are not without…

Database indexing is a fundamental technique for optimizing query performance, particularly in read-heavy applications. However, indexes are not without their costs. Every index added to a table introduces overhead for write operations (INSERT, UPDATE, DELETE), as the database must maintain the index structure in addition to the base table data. This article explores strategies for designing and managing indexes to achieve significant read performance gains while minimizing the impact on write throughput.

Understanding Index Mechanics and Write Overhead

When a record is inserted, updated, or deleted, the database must not only modify the primary table data but also update all associated indexes. This involves:

• Disk I/O: Writing index entries to disk, potentially leading to random I/O if the index is large and fragmented.
• CPU Cycles: Traversing B-tree structures, rebalancing pages, and maintaining sorted order.
• Locking: Index modifications can introduce contention, especially with heavily accessed indexes, impacting concurrency for write operations.
• Transaction Log/WAL Overhead: Index changes are typically logged, increasing the volume of data written to the transaction log (Write-Ahead Log in PostgreSQL) and potentially impacting recovery times.

The severity of this overhead is proportional to the number and complexity of indexes on a table, the size of the indexed columns, and the frequency of writes. For example, updating an indexed VARCHAR(255) column will generally incur more overhead than updating an indexed INT column, due to variable-length key comparisons and potential page splits.

Strategic Index Placement for Reads

The core principle of indexing for read performance is to cover columns used in query predicates, joins, and ordering. Common candidates for indexing include:

• WHERE clause conditions: Columns frequently used in equality (=), range (<, >, BETWEEN), or pattern matching (LIKE 'prefix%') operations.
• JOIN conditions: Foreign key columns, and often the primary keys they reference, are prime candidates for indexing.
• ORDER BY and GROUP BY clauses: Indexes can allow the database to retrieve data pre-sorted, avoiding costly in-memory sorts.
• DISTINCT operations: Can often leverage an index to find unique values efficiently.

Composite Indexes and Leftmost-Prefix Rule

Composite (multi-column) indexes are powerful but must be designed with the "leftmost-prefix rule" in mind. An index on (column_A, column_B, column_C) can be used for queries filtering on:

• column_A
• column_A, column_B
• column_A, column_B, column_C

It cannot directly satisfy queries filtering only on column_B or column_C, or column_B, column_C. Choose the order of columns in a composite index carefully, placing the most selective columns, or those most frequently used in WHERE clauses, first.

Example: PostgreSQL Composite Index

CREATE INDEX idx_orders_customer_date ON orders (customer_id, order_date DESC);
-- This index can be used for:
--   SELECT * FROM orders WHERE customer_id = 123;
--   SELECT * FROM orders WHERE customer_id = 123 AND order_date >= '2023-01-01';
--   SELECT * FROM orders WHERE customer_id = 123 ORDER BY order_date DESC;
-- It will NOT be used effectively for:
--   SELECT * FROM orders WHERE order_date >= '2023-01-01';

Minimizing Write Impact: Targeted Indexing

Partial Indexes (Conditional Indexes)

For tables where only a subset of rows is frequently queried or modified, partial indexes can significantly reduce index size and write overhead. A partial index only includes entries for rows that satisfy a specified WHERE clause. This is extremely beneficial for tables with status flags (e.g., active, pending, completed).

Example: PostgreSQL Partial Index

CREATE INDEX idx_tasks_active_priority ON tasks (priority DESC) WHERE status = 'active';
-- This index only contains entries for tasks where status is 'active'.
-- It's smaller, faster to update, and provides high performance for queries like:
--   SELECT * FROM tasks WHERE status = 'active' ORDER BY priority DESC LIMIT 10;
-- Updates to tasks with status != 'active' will not affect this index.

Updates to columns *not* part of the index or the WHERE clause also do not affect the index, even if the row is part of the indexed subset. This can greatly reduce write amplification.

Covering Indexes (Index-Only Scans)

A covering index is a composite index that includes all the columns required by a query, both in the WHERE clause and the SELECT list. When a query can be satisfied entirely by an index, the database doesn't need to access the main table (heap), leading to an "index-only scan." This significantly reduces I/O.

Example: PostgreSQL Covering Index

CREATE INDEX idx_products_category_name_price ON products (category_id, product_name, price);
-- For a query like:
--   SELECT product_name, price FROM products WHERE category_id = 5 ORDER BY product_name;
-- The database can perform an index-only scan, as all required data (category_id, product_name, price)
-- is present within the index itself. This is true even though 'category_id' isn't explicitly selected,
-- it's used for filtering and is part of the index.

MySQL offers INCLUDE columns (starting with MySQL 8.0.20) for this purpose, where non-key columns can be added to the index without being part of the primary sorting key, similar to PostgreSQL's INCLUDE clause (since 11).

Example: PostgreSQL INCLUDE clause (since 11)

CREATE INDEX idx_products_category ON products (category_id) INCLUDE (product_name, price);
-- This index sorts by category_id, but also stores product_name and price to allow index-only scans
-- for queries similar to the previous example, without making product_name and price part of the B-tree's primary sort order.

Indexing Foreign Keys

Always index foreign key columns. This not only speeds up JOIN operations but also typically prevents full table scans when performing referential integrity checks (e.g., cascading deletes or updates) or when validating parent records during child table inserts/updates.

Auditing and Maintenance

Monitoring Index Usage

Regularly audit index usage to identify unused or rarely used indexes. Each unused index is pure write overhead with no read benefit. Databases provide views for this purpose:

• PostgreSQL: pg_stat_user_indexes and pg_stat_all_indexes. Look for idx_scan (number of index scans initiated) and idx_tup_fetch (number of live rows fetched via the index). An idx_scan count of 0 (or very low relative to table scans) over a significant period (e.g., weeks or months) indicates a candidate for removal.
• MySQL: sys.schema_unused_indexes (from MySQL 5.7+). For more detail, check performance_schema.table_io_waits_summary_by_index_usage.
• SQL Server: sys.dm_db_index_usage_stats.

PostgreSQL Example Query for Unused Indexes:

SELECT
    schemaname,
    relname AS table_name,
    indexrelname AS index_name,
    pg_size_pretty(pg_relation_size(i.indexrelid)) AS index_size,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch
FROM
    pg_stat_user_indexes usi
JOIN
    pg_index i ON usi.indexrelid = i.indexrelid
WHERE
    idx_scan = 0
    AND usi.schemaname = 'public' -- Adjust schema as needed
    AND i.indisunique IS FALSE -- Don't drop unique indexes without careful consideration
    AND i.indisprimary IS FALSE -- Don't drop primary key indexes
ORDER BY
    pg_relation_size(i.indexrelid) DESC;

Dropping Unused Indexes

Once identified, unused indexes can be safely dropped. This will immediately reduce write overhead and free up disk space. Always perform this in a controlled environment and consider a rollback plan, especially for production systems.

DROP INDEX IF EXISTS idx_old_unused_index;

Reindexing and Fragmentation

Over time, frequent inserts, updates, and deletes can cause indexes (especially B-tree indexes) to become fragmented. Fragmentation can lead to increased I/O as more disk pages need to be read to traverse the index. Periodically rebuilding or reindexing can defragment indexes and reclaim space, but this is a resource-intensive operation that creates significant write load during its execution. Tools like REINDEX in PostgreSQL or OPTIMIZE TABLE (for MyISAM, InnoDB rebuilds index internally) in MySQL can be used.

For PostgreSQL, REINDEX TABLE your_table_name; or REINDEX INDEX your_index_name; can be used. Consider using CONCURRENTLY for non-blocking reindexing in production environments (PostgreSQL 9.2+):

REINDEX INDEX CONCURRENTLY idx_orders_customer_date;

This creates a new index, switches to it, and then drops the old one, minimizing downtime but taking longer and requiring more temporary disk space.

Trade-offs and Considerations

Common Pitfalls

• Over-indexing: Adding too many indexes without analyzing their necessity. This is the most common cause of unnecessary write overhead. Always profile and measure.
• Indexing all columns: Believing more indexes automatically mean faster queries. Each index is a cost.
• Not understanding leftmost-prefix rule: Creating composite indexes with incorrect column order, rendering them ineffective for many queries.
• Indexing low-cardinality columns: Indexing columns with very few unique values (e.g., a "gender" column with 'M'/'F' values) often doesn't provide significant performance benefits, as the database might opt for a full table scan anyway. Bitmap indexes (in some databases like Oracle) can be an exception here.
• Ignoring index usage statistics: Not regularly auditing and removing unused indexes, allowing "dead weight" to accumulate and slow down writes.
• Not considering index rebuilds: Allowing indexes to become severely fragmented, leading to degraded read performance over time due to increased I/O.