10 Database Bottlenecks We Hit Scaling to 5,000 Orders

We fixed the unglamorous things first: bigger disks. Then we gave the PDF service room to breathe — longer timeouts, more browser sessions, Chrome kept warm — which helped, though in hindsight we were treating a symptom we would only cure properly a year later (Case 04). The changes that mattered were structural. Work that ran synchronously inside requests moved into queued jobs. Jobs started carrying lightweight record IDs instead of whole serialized objects, because one stuck serialization had been pinning entire orders. And the single shared queue became three dedicated worker containers. Three. It felt like a fleet at the time.

It took three rounds to actually win. In the first, we treated each incident as it came: remove this lock, log that deadlock (innodb_print_all_deadlocks on, so at least the next one would leave a confession). Every individual fix worked — and the class of problem kept coming back with a new address.

In the second round, April 2025, we went after the class itself and switched the platform to READ COMMITTED: giving up a strictness the Murabaha flow never actually needed, in exchange for dramatically less lock contention.

The third round came during the summer stress tests, when the sell path deadlocked anyway — the global setting was not reaching every transaction, so the hottest services got it explicitly. And the last deadlocks were removed the cheapest way of all: not by locking more, but by making every code path write to tables in the same order, so transactions could no longer meet head-on.

We raised the timeout to 120 seconds the same day — not as a fix, but as anesthesia, so testing could continue while we operated. Then came the actual surgery, a deliberate summer campaign. Buying commodities, creating trader orders, and holding units each got their own queue instead of sharing one line. The search for eligible commodities was separated from order initiation, so five workers could do in parallel what one had been doing alone. Inventory scans switched to cursors. We even dropped foreign keys from the busiest join table — heresy in most engineering conversations, but their integrity checks were pure cost on a table written millions of times a day, and the application already guaranteed those invariants itself. And the units table finally got its first serious indexes.

First, we tuned the press. Longer timeouts, more concurrent sessions, Chrome kept warm and ready. It bought headroom for the next test — and changed nothing about the economics. Still one browser per document, still on the critical path.

Then we replaced the engine. A native PHP library went head-to-head against the browser and won convincingly: ~128 documents a minute against ~18 — seven times the throughput — with no external service, no per-document fees, documents staying on our own infrastructure, and proper Arabic right-to-left out of the box. Better in every way. And still seconds of rendering inside every step.

The winning move came from a different room. Sitting with the business and the Sharia requirements, we asked what the certificate actually had to be — and the answer was: it must exist and be obtainable, not be rendered before the step may complete. So the steps stopped generating PDFs entirely; a certificate renders the first time someone downloads it. And here is the quiet punchline: most certificates are never downloaded at all. Most of that work never needed speeding up. It needed to stop existing.

Generation one: compute the truth on demand. Correct, simple, and ruinously expensive — every order paid the full scan, and those scans were most of the read pressure behind the 94-million-read index in Case 09.

Generation two: the "settled" feature. When an order completes, mark its units as settled back to the market — in bulk, with a cheap flag — so eligibility queries can skip whole classes of units without inspecting them. The scans shrank. But the settlement chain groaned under its own weight (its jobs kept blowing past their time limits and eventually needed a full bulk rewrite), and the per-order scan was still there. Smaller, not gone.

Generation three: stop scanning altogether. If the question is always the same, keep the answer written down. A dedicated table now holds an eligible-quantity counter for every (customer, inventory) pair, and every event that changes the answer — a purchase, a settlement, a supplier switching on — updates the counter in that same moment, atomically. The schema itself refuses to let a counter go negative, and rebuild jobs can reconstruct any counter from the ground truth whenever we have a reason to doubt it.

The version that survived thinks like a very fast cashier. It counts in whole halalas, never fractions — floating-point money that drifts by 0.01 is the difference between exact change and no change at all. Before searching, it checks whether an answer can even exist: if the most valuable 10,000 units on the table cannot reach the amount, the order fails instantly with a clear message instead of hunting for a solution that is not there.

Then it gets greedy: one linear pass through the eligible inventories, taking as many units of each as still fit in the remaining amount. For the vast majority of orders, that single pass lands exactly on zero. When a gap survives, it backtracks — puts a note back, swaps in different ones — and writes down every gap it fails to close, so even a failure comes out as a diagnosis: the market lacks units at these prices.

And it is kept on a deliberate leash: at most 10,000 units per trade, at most three seconds of thinking. One order failing fast with a useful message beats five thousand orders waiting behind a search that refuses to give up.

The fixes read like an apology to the database. One composite index turned the hot lookup from a scan into a seek. A cached "last action" column meant roughly 90% of those history queries no longer needed to run at all. And the scheduler grew up: it loads work in batches, skips anything it touched in the last five minutes, checks what is already queued before dispatching again, and staggers its dispatches with a small random delay — so a thousand jobs stop arriving in the very same second.

The percentile history let us bisect by deployment, and the answer was uncomfortable: the slowdown came from a bug-fix. A certificate had been naming the wrong previous owner; the correction was right, but it did its checking in the middle of every single purchase. We reverted it in production the same week — protecting the step while the fix stayed alive in testing — and then rebuilt the same correction so it did its work off the critical path.

Exclusive holds took years of iteration: counters that tolerate crashes without drifting, a repair job for corrupted holds, overlap middleware on the jobs that could collide — and one queue deliberately left at a single worker, because for that step, correctness genuinely could not be parallelized. Knowing when not to scale is also scaling.

The table's indexes have a history like a battlefield map: added, removed, rebuilt as virtual-column composites, extended, pruned again. Every index makes the reads faster and the millions of ownership writes slower, so each one had to earn its keep with measurements — index hygiene became a recurring chore, not a one-time setup.

One experiment deserves its own confession. We added model-level caching to the hot tables to save reads — and months later reverted it for the write-heavy ones, because under real load the caching cost more than it saved. It stung. It is also exactly why we measure.

And the trigger met its match at settlement scale. The per-row trigger guarantees no ledger row is ever missed — and it became the slowest part of bulk settlement. We did not remove the guarantee; we changed who writes. The settlement rewrite produces the very same ledger rows through set-based bulk statements, thousands at a time, with the trigger stepped around only for that audited path.

The platform we run now starts from a different assumption: every job will eventually die mid-flight — the work is making that boring. Jobs retry on their own, waiting 1, 2, 3, 5, then 30 seconds between attempts: patient enough for a transient fault to clear, quick enough that the order barely notices. If we had to name the single most important mechanism behind zero failed transactions, it is this one — most failures are temporary, and a platform that retries well simply does not lose them.

Critical jobs are declared unique per order across the whole fleet, so a duplicate dispatch from any server is refused at the queue itself, not caught after the damage. The locks learned to die: they expire shortly after the worker timeout, so a crashed worker's lock heals on its own, and an overlapping dispatch politely re-queues after a short delay instead of burning through its retries on the spot. Even deploys stopped being a hazard — a worker being shut down now finishes or hands back its job instead of taking it to the grave.