Home / Client Stories / Enterprise Operations Platform / Scaling the Database
Solution Story · Data Architecture

Fast at 700,000 work orders — by design, not hardware

Most enterprise systems get slower every year as data piles up. This one didn't. The trick wasn't bigger servers or heroic index tuning — it was recognizing that yesterday's data and today's data have completely different jobs.

Part of
Enterprise Operations Platform engagement
Scale
700k+ work orders · millions of history records
Working set
7,000–10,000 open orders at any time
Result
Sub-second screens for 8+ years of growth
The problem

Success creates data. Data creates slowness.

In the early years, every work order stayed in the operational tables forever — and it worked fine, because volumes were small. Then the business grew. Repairs accumulated year over year: orders, quotes, QC records, photos, timeline events. The system was heading toward the classic enterprise fate — a platform that gets slower with every success it processes.

~99% of all data is closed, historical work — needed for audits and reporting, not for today's screens
■ 7–10k active orders — what users actually touch daily700k+ total orders, millions of child records

The insight: operational users care about today's repairs. Historical data serves audits, customer history, and analytics. Serving both workloads from the same tables means optimizing for neither.

How we decided

Four options. Three rejected.

Everyone reaches for hardware first. We evaluated the standard playbook before choosing the architectural answer.

✕ Rejected

Bigger hardware

Scale the infrastructure, keep the schema.

Why not: rising cost forever, limited long-term benefit — the data keeps growing faster than the budget.
✕ Rejected

Heavy index optimization

Continuously redesign indexes as tables grow.

Why not: ever-growing index maintenance, slower writes, and it only delays the problem instead of solving it.
✕ Rejected

Table partitioning

Let the database engine split tables internally.

Why not: added database complexity with limited benefit for the application's actual query patterns.
✓ Accepted

Working vs. history tables

Two logical data areas: a small hot set for open work, an unlimited archive for closed work — connected by background jobs.

Why: matches how the business actually uses data. Operational screens scan thousands of rows, not millions.
What we built

Data with a lifecycle, like the business it models

A work order is born, lives through its repair, closes — and then changes jobs: from operational record to business memory. The database mirrors exactly that.

Working tables HOT · SMALL · HEAVILY INDEXED Open work orders (7–10k) Active repairs & QC in progress Pending shipments & approvals every daily click lands here — and stays fast History tables READ-MOSTLY · UNLIMITED GROWTH Closed work orders (700k+) Complete timelines & QC records Millions of audit events Customer service history reporting, audits, analytics — with relationships intact nothing is ever deleted background archival validate closure → copy with relationships → update references → remove from working set runs continuously · zero user interaction
Closed work migrates automatically, relationships preserved. Users never notice — that's the point.

Asynchronous everything

Archival, sync processing, report preparation, maintenance — anything not needed in the user's click path runs as a background job. Interactive workflows never wait for housekeeping.

Full history, zero deletion

Every closed order keeps its complete timeline, QC evidence, and approvals in the history schema. Audits and customer-history questions get instant, complete answers.

No big-bang migration, ever

The split was introduced into a live production system — incrementally, without downtime, without a rewrite. The platform never stopped taking orders while its foundations were rebuilt.

Results

Growth stopped being a performance problem

700k+
work orders, millions of child records
~1%
of data carries ~99% of daily traffic
500+
tables evolved without a rewrite
8+
years of growth, responsiveness intact
Optimize for the workload users actually perform — not for the total amount of data you store.
The principle that outperformed every hardware upgrade
Keep reading

More from this engagement

Flagship story

The platform its acquirer couldn't replace

The full eight-year story: 700k+ work orders, 2,000+ users, portals, contracts, BI — and the migration that never happened.

Read the story → Solution story

Don't replace the ERP — surround it

System of record vs. system of engagement: the boundary that held for a decade.

Read the story → Solution story

Reporting as a platform

Self-service BI, dashboards, and PowerPoint presentations generated from live operational data.

Read the story →

Is your system getting slower every quarter?

Performance problems are usually lifecycle problems in disguise. We find the real cause before recommending hardware.

Book a Strategy Call