How to Optimize a Database, One Step at a Time

A B‑tree index stores column values in a balanced tree structure where each node represents a range of values. During a lookup, the database traverses the tree from the root, following left or right pointers based on the search key, until it reaches the leaf node that contains the target row pointer. Because the tree height grows logarithmically with the number of rows, the engine examines only a few nodes instead of scanning every record in the table.

Consider a students table with identifiers 1–1000. Without an index, a query such as SELECT * FROM students WHERE id = 345 forces the engine to read each of the 1,000 rows sequentially. With a B‑tree index on id, the engine first reads the root node (covering 1–1000), then the child node for 1–500, then the node for 251–500, and finally the leaf that points directly to row 345. This reduces I/O to a handful of page reads.

• Read performance gain: Lookups on indexed columns typically execute in O(log n) time, dramatically lowering latency for point queries and range scans.
• Write overhead: Every INSERT, UPDATE, or DELETE that modifies an indexed column must also modify the B‑tree, causing additional page writes and lock contention.
• Storage cost: Index structures occupy extra disk space, often comparable to the size of the indexed column(s).
• Maintenance impact: Rebuilding or reorganizing indexes after heavy data churn can consume CPU and I/O resources.

To balance these factors, follow a disciplined approach:

1. Identify columns that appear frequently in WHERE, JOIN, or ORDER BY clauses.
2. Create a B‑tree index only on those columns, for example:

   CREATE INDEX students_id_idx ON students (id);

3. Avoid indexing columns that are updated often or that have low cardinality, as the write penalty outweighs read benefits.
4. Monitor index usage with database‑provided statistics (e.g., EXPLAIN plans) and drop unused indexes to reclaim space and reduce write cost.

By understanding the logarithmic search path of B‑tree indexes and the associated write amplification, engineers can design index strategies that accelerate read‑heavy workloads while keeping insert and update latency within acceptable bounds.

Vertical scaling involves increasing the resource capacity of an existing server instance. By upgrading the CPU, memory (RAM), or disk I/O throughput, you allow a single machine to handle higher throughput or larger datasets. This approach remains the most straightforward method for improving database performance because it does not require changes to the application architecture or the distribution of data across multiple nodes.

Engineers typically utilize vertical scaling when a server remains operational but demonstrates resource contention, such as high CPU utilization during peak query volume or excessive disk swapping caused by insufficient RAM. Because a single server maintains a physical ceiling, you should leverage this approach while the machine is still performing within acceptable latency parameters. It is most effective before shifting to distributed architectures like sharding, which introduce significant operational complexity.

Identifying the Point of Diminishing Returns:

• Hardware Saturation: A point is reached where increasing RAM no longer improves throughput because the CPU becomes the bottleneck, or vice versa.
• Cost-to-Performance Ratio: At the upper bounds of enterprise hardware, the financial cost of upgrading to the next tier of CPU or memory may provide marginal performance gains compared to the initial tiers.
• Single-Machine Limits: Vertical scaling cannot bypass the fundamental limitations of a single operating system or storage controller. Once a machine reaches its physical maximum for expansion, adding further resources provides no benefit.

When vertical scaling no longer mitigates performance bottlenecks, it signals that the system has reached its physical capacity. At this stage, engineering teams should transition to horizontal scaling strategies, such as implementing read replicas to offload query traffic or sharding data across multiple servers to distribute write load and storage requirements.

Database read/write splitting offloads query traffic from the primary instance to dedicated replicas. In MySQL, a single source server processes all INSERT, UPDATE, and DELETE operations, while one or more replicas apply a copy of the source’s transaction logs to handle SELECT traffic. PostgreSQL implements a similar architecture using a primary instance and streaming replicas, often referred to as hot standbys.

Implementing this architecture introduces the challenge of replication lag, a short delay caused by the time required for the replica to receive and apply changes from the primary. Engineers must account for eventual consistency, as a read operation executed immediately after a write may return stale data if directed to a replica that has not yet caught up.

To effectively manage this setup, consider the following technical constraints and strategies:

• Write Constraints: Replicas cannot handle write load. All write operations must be routed to the primary, meaning vertical scaling remains necessary for write-heavy workloads.
• Handling Lag: When application logic requires strict read-after-write consistency, route the read query directly to the primary instead of the replica.
• Failover Readiness: If a primary fails, a replica can be promoted to become the new primary. Prior to promotion, verify the replication lag status to ensure no critical, unapplied data is lost from the transaction log.
• Resource Offloading: Distribute read-only traffic (such as heavy analytical reports or indexed lookups) across multiple replicas to free up CPU and memory resources on the primary.

Before implementing replicas, ensure that query optimization and indexing are already in place, as these steps resolve performance bottlenecks more efficiently than adding infrastructure. Use replicas specifically when the read throughput exceeds the capacity of the primary server, even after primary-node resources have been vertically scaled to their effective limit.

Partitioning and sharding both reduce the amount of data a single query must scan, but they operate at different architectural layers. Partitioning keeps the entire logical database on one server; the table is divided into independent pieces (partitions) based on a partition key. The database engine routes a query to the appropriate partition internally, so the application continues to issue standard SQL without awareness of the split.

Sharding moves each partition onto a separate physical server. The shard key determines which server stores a given row, and an external routing layer—often an application‑level service or a proxy—must resolve the key to the correct host before issuing the query. Because each shard is a complete database instance, transactions that span multiple shards become distributed and require additional coordination.

• Scope: Partitioning = one database instance; Sharding = many database instances.
• Routing: Handled internally by the DBMS for partitions; external routing logic required for shards.
• Operational complexity: Partitions add backup/restore granularity; shards add provisioning, monitoring, and cross‑shard transaction handling.
• Scaling limit: Partitioning is limited by the resources of a single server; sharding removes that ceiling by adding servers.

Practical example: a students table contains 10 million rows with a section column (A, B, C…).

-- Partitioning (single server)
CREATE TABLE students (
    id INT,
    name VARCHAR(100),
    section CHAR(1),
    …
) PARTITION BY LIST (section) (
    PARTITION pA VALUES IN ('A'),
    PARTITION pB VALUES IN ('B'),
    PARTITION pC VALUES IN ('C')
);

All queries still run against students, but the engine reads only the relevant partition.

-- Sharding (multiple servers)
# Application routing pseudo‑code
if student.section == 'A' then connect to db‑shard‑1
elsif student.section == 'B' then connect to db‑shard‑2
else connect to db‑shard‑3
end

Because sharding distributes write load as well as storage, it is typically the final step after indexing, vertical scaling, read replicas, and partitioning have been exhausted. When a single database—even with partitions—cannot hold the data volume or sustain the write throughput, engineers move to sharding, accepting the added operational burden of managing multiple database instances and cross‑shard consistency.