Day 8: Load Test Your System & Find the Hotspots — Then Implement Basic Monitoring and Optimization.

Day 8: Load Test Your System & Find the Hotspots — Then Implement Basic Monitoring and Optimization

The Continuity Contract

In Day 7, we successfully evolved our retail catalog schema by executing an ALTER TABLE operation to add a category_id column to our products table, followed by a zero-downtime data migration to backfill existing records.

Today, we subject that migrated schema to production-level write and read pressure. We will discover how our Day 7 changes behave when hundreds of concurrent users query the catalog. In our next lesson, we will take this highly optimized, indexed database and violently terminate its process mid-transaction to witness Write-Ahead Log (WAL) recovery and restore operations from a physical backup.

The Production Stakes: The Silent Killer of Connection Pools

In 2017, GitLab experienced a major cascading outage. While the public post-mortem focused on an accidental database directory deletion, the underlying instability began hours earlier with database saturation. High-traffic, unindexed queries on critical tables caused PostgreSQL CPU utilization to lock at 100%.

When a database query lacks an index, the engine must perform a Sequential Scan (reading every single page of data from disk or memory). Under heavy concurrent traffic, these sequential scans take hundreds of milliseconds longer than index-assisted queries.

In a process-per-connection database like PostgreSQL, each slow query holds onto its database connection. As requests pile up, the application’s database connection pool quickly exhausts its limits. New requests are forced to wait for an available connection, queuing up until the application server runs out of file descriptors, spikes its own memory, and crashes.

This is the "slow-query connection-exhaustion cascade." It is not a hard crash of the database engine; it is a slow, suffocating death by resource starvation.

Intuition: The Index as a Book Glossary

Imagine you are handed a 1,000-page catalog of retail products and asked to find every item in the "Kitchenware" category.

Without an index, you have no choice but to start on page 1, read every product description, and repeat this all the way to page 1,000. This is a Sequential Scan ($O(N)$ time complexity). If ten customers ask you to do this simultaneously, you will quickly become exhausted and slow down.

Now, imagine the last 5 pages of the catalog contain a pre-sorted glossary:

• Category 12 (Kitchenware): Pages 4, 12, 89, 412...

• Category 13 (Electronics): Pages 15, 92, 104...

Instead of reading the whole book, you flip to the glossary, find "Category 12" in $O(log N)$ time using a binary search pattern, and flip directly to the exact pages listed. This is an Index Scan.

The trade-off? If you add a new product to the catalog, you must write it on page 800 and update the glossary. If you update a product's category, you must modify the glossary. Indexes accelerate reads at the cost of write amplification and storage overhead.

Component Architecture under Load