Relational Database Performance and Indexing at Scale

Executive Summary

As web applications grow, the relational database almost always becomes the primary scaling bottleneck. While memory and CPU resources can be scaled vertically, structural database problems—such as unindexed tables, long-running transactions, lock contention, and inefficient queries—eventually degrade platform responsiveness. This article details the strategies SazM implements to optimize database performance under heavy user traffic.

Identifying the Bottlenecks: The Indexing Hierarchy

The first step in database optimization is ensuring proper index coverage. An index enables the database engine to find records quickly without scanning every row in a table.

• Primary and Foreign Key Indexes: Every table must have a primary key, and all foreign keys used in joins should have indexes. A missing index on a foreign key causes a full table scan whenever tables are joined.
• Composite Indexes: When queries filter by multiple columns in a WHERE clause, composite indexes covering those columns in order of cardinality are required.
• Index Overhead: Indexes are not free; they increase write amplification because every index must be updated during INSERT, UPDATE, and DELETE operations. Remove unused indexes to improve write performance.

Controlling Transaction Length and Lock Contention

Locking is necessary to maintain transactional integrity, but excessive locking limits concurrency.

• N+1 Query Elimination: This occurs when an application executes one query to fetch a list of parent records, and then loops to run individual queries to fetch child records for each parent. Preloading child records using JOIN operations reduces database roundtrips and lock times.
• Transaction Boundaries: Keep database transactions as short as possible. Do not execute external HTTP requests, file uploads, or complex calculations inside transaction blocks, as this keeps locks active, causing other requests to wait.
• Lock Escapes: Use row-level locking (e.g., SELECT ... FOR UPDATE) instead of table-level locks, and use non-blocking reads (e.g., read committed isolation levels) where appropriate to prevent read-write deadlocks.

Query Profiling with EXPLAIN

Never optimize database queries blindly. Use the database's EXPLAIN statement to view the execution plan for any slow query:

1. Scan Type: Look for ALL (full table scan) or index (full index scan), which indicate that the query is scanning too much data.
2. Key Used: Verify the database is using the index you expect. If it is using a different key or no key at all, query refactoring or index hints may be required.
3. Rows Scanned: Minimize the number of rows the engine must evaluate. If a query scans 100,000 rows to return 10 results, the filter criteria are inefficient.

Scalability Architectures: Read-Write Splitting and Caching

Once query and schema optimizations are maximized, structural architectural changes are required to handle further load:

• Read-Write Splitting: Route all write transactions (INSERT, UPDATE, DELETE) to a primary database node, and distribute read queries (SELECT) across replica nodes.
• Read-Through Caching: Cache expensive query results in a high-speed, in-memory store like Redis. Implement a cache-invalidation policy (such as key-based expiration or event-driven updates) to ensure cache consistency.
• Edge Caching: For static or slow-changing database content, configure cache-control headers to store pages at the CDN edge (e.g., Cloudflare), bypassing application servers entirely.

To assess your database performance bottleneck, use the SazM Database Performance Checklist or consult directly with Saravana Bhava about a custom Performance Optimization audit.