Data — Relational versus NoSQL, Indexes, Schemas, and the Things That Bite

The Monday morning crash

Harsha had been at the manufacturing company in Hyderabad for fourteen months when the Monday morning crash happened. The company made automotive parts, employed three hundred people, and ran most of its operations on a fourteen-year-old inventory system. The database had grown quietly to about forty million rows. The original engineers who had built it had long since left.

A new feature had been requested. A dashboard, on the home page, showing real-time stock levels by warehouse, by category, by supplier. Harsha wrote the query. It worked. On the staging environment, with a few hundred thousand rows of test data, the dashboard loaded in eight seconds. That felt slow to him, but his manager said it was acceptable for an internal tool. He pushed the change to production on Friday evening.

Monday morning, when the actual users hit the dashboard, the query took seventy-five seconds. Other queries in the application started to queue behind it. The whole system slowed to a crawl. For twenty minutes, the shop floor could not check stock, the procurement team could not place orders, and the sales team could not confirm deliveries. The CEO sent a message asking what was happening.

The senior engineer on call walked over to Harsha's desk. She did not look angry. She looked the way senior engineers look when they have seen this exact bug at least three times before. She asked him to share his screen, pulled up the query, typed EXPLAIN in front of it, and pointed at the output. The database was doing a full scan of a forty-million-row table. There was no index on the column he was filtering by.