Why reports should never run on the warehouse database

Ask a warehouse manager what the database is for and the answer is immediate: it tells the floor what to do next. Which order to pick, which tote to pack, which dock door the truck is at. Every second it spends on anything else is a second the floor is waiting.

Now ask who else runs queries against it. The finance team's month-end extract, the regional manager's morning dashboard, and the customer service tool that refreshes order status every thirty seconds. A consultant's one-off analysis that turned into a scheduled job two years ago and never got turned off.

None of these people are doing anything wrong. They are all sharing one server with the pickers, and the pickers did not agree to share.

Reports and the floor compete for the same machine

A database server has a fixed amount of processor, memory and disk throughput. A picking transaction needs a sliver of each, thousands of times an hour. A report that sums six months of shipments needs a large slice of all three, once. When they run at the same time, the report does not politely wait; the database splits its attention between the two, and the picking transactions get slower.

On a quiet Tuesday nobody notices. The server has room, the report finishes, the scanners respond in a fraction of a second. The problem shows up on the day the server has no room to spare.

Peak is when it matters

The busiest day of the year is also the day everyone wants a report. Volume is up, head office wants hourly numbers, customers are refreshing tracking pages, and the same reports that were harmless in March are now being run against a server already working hard. That is the moment the scanners start taking two seconds instead of a fraction, and a two-second scanner delay across a floor of pickers is a lot of lost throughput.

The reflex is to buy a bigger server. That buys headroom for one peak, at the price of a hardware step every time volume grows, and it still leaves the reports on the same machine as the floor. The reflex is treating the symptom.

Move the reports, keep the database

The alternative is to take the reporting load off the warehouse database entirely, without touching the warehouse system. A small service beside the database watches the tables that reports need and sends every added, changed or deleted row outward to a central copy in your cloud account. After the first copy only changes are sent, compressed and encrypted per site, and the central copy is current to within a couple of minutes.

Reports read from that central copy. All of them: the dashboards, the month-end extract, the customer portal, the consultant's forgotten job. The warehouse database goes back to doing one thing, which is running the floor.

This is not a claim about speed. The central copy is not faster than the warehouse database and we do not sell it as such. The claim is about headroom: a server that no longer serves reports has capacity it did not have before, and that spare capacity is what keeps it steady when the peak arrives.

What headroom buys you

Headroom is resilience. A server with room to spare absorbs a surge; a server running close to its limit falls over at the first one. Removing the reporting load is the cheapest way we know to move a warehouse server from the second condition to the first, because it removes work rather than adding hardware.

It also changes who owns the risk. Today, a badly written report can slow the floor and nobody finds out until the pickers complain. When reports run against the central copy, a badly written report is slow for the person who ran it, and the floor never knows.

The service itself is built to be a good guest. It holds back when the site's machines are busy, so even the work of sending changes outward gives way to the floor. If the internet link drops, changes queue on site and resume when the link returns; nothing is lost, and nothing on the floor stops.

The rule, stated plainly

The warehouse database exists to run the warehouse. Anything that reads from it for another purpose is borrowing capacity from the floor, and the loan comes due on the busiest day of the year. Reports should read from a copy, and the copy should be kept current by sending changes outward, not by running exports against the database at night.

There is one honest limit to state. The central copy lags the warehouse by a couple of minutes, so it is the wrong place for a floor supervisor who needs the next pick right now. That supervisor stays on the warehouse system, which is exactly where the warehouse system's capacity should go.

Every report on the warehouse database is a loan against the floor, repayable at peak.

redfly supplies the service that keeps the central copy current across every site; your team builds the reports against it, with our support.