The Operational Database
The operational database is the storage layer the application itself talks to. When a customer taps Place Order, the app inserts 1 row; a courier accepting the order updates that row, and a refresh of the tracking screen reads it. Each of those happens hundreds of times a second across the fleet, and each has to finish fast enough that the person holding the phone never notices.
| Operation | Rows touched | Columns touched | Time allowed |
|---|---|---|---|
| Place order | 1 insert | All 6 | Under 50 ms |
| Courier picks up | 1 update | status | Under 50 ms |
| Customer views order | 1 read | All 6 | Under 50 ms |
| Monthly revenue by city | 1.4 billion reads | 3 | Minutes, taken from the rows above |
Row layout, built for one record
Every operation above wants 1 record, often with all its columns, so the operational database stores each row's columns together on disk. One read brings back the whole order. An index on order_id lets the database jump to that row's position without reading its neighbours, the way a book's index sends you to 1 page. The same layout that cost the warehouse in the previous section is exactly right here, because nobody at checkout wants a column.
ACID transactions
Placing an order changes more than 1 row. The app inserts the order, decrements the restaurant's count of a limited dish and records the payment, and those 3 writes have to behave as 1. The operational database wraps them in a transaction and promises 4 properties, remembered by the letters ACID.
- Atomic: If the payment write fails, the order insert and the stock decrement both roll back, so no half-order is left behind.
- Consistent: A stock count declared non-negative never goes below 0, and every order's customer_id points to a real customer.
- Isolated: If 2 customers order the last portion at the same instant, they do not both receive it; 1 waits and sees the count at 0.
- Durable: Once the app receives the commit, a power loss 1 second later does not lose the order.
| Step | Write | If this step fails |
|---|---|---|
| 1 | Insert the order row | Nothing has been written; the customer sees an error |
| 2 | Decrement the dish's stock count | Step 1 rolls back; no order without stock |
| 3 | Record the payment | Steps 1 and 2 roll back; no stock lost without payment |
| 4 | Commit | All 3 writes become permanent together |
OLTP: thousands of tiny operations
This access pattern has a name: online transaction processing, OLTP. It means many operations per second, each touching a handful of rows by key and needing an answer in milliseconds, and any of them may be a write. The database is tuned for it: indexes to find rows, locks to keep transactions isolated, and a log that makes commits durable. Those features cost something on every operation, which is fine when the operation touches 1 row and ruinous when it touches 1.4 billion.
- 450
- 9 min
Why the analytical query belongs elsewhere
One Tuesday an analyst pointed the monthly revenue query at the live database at 6:40 in the evening, the dinner peak. With no index on placed_at, the database had no way to skip to last month, so it read all 6 columns of all 1.4 billion orders to sum 1 column of 60 million of them. The read held disk and processor until 6:49, and in that window the slowest checkouts went from 40 ms to over 2 seconds. Customers saw spinners and some gave up. The query returned a correct number; it ran in the wrong place.
The remedy is the pipeline shape you already know. A batch job copies the day's new and changed orders out each night, or a stream carries each change within seconds, and the copy lands in the lake and the warehouse. Analysts read the copy, and so do the dashboards and models. The operational database goes back to the job it does well: serving 1 row right now under 4 guarantees.
Each of 4 teams proposes a read against the live operational database. Which one is the read it handles well?
- Count the orders each city placed in each hour across the last 12 months, for a seasonality chart.
- Join orders to refunds to find every customer refunded more than 3 times since launch.
- Fetch order 8841207 by its id and set its status to picked_up when the courier confirms.
- List the 100 restaurants with the highest average order value since the app launched.
About this lesson
This section is part of the Storage Layers: Beginner lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.