The Nightly Import That Started Taking All Day

A client's 45-minute data import gradually became a 14-hour ordeal that bled into business hours, tanked dashboard performance, and made the analytics team distrust their own numbers. The fix wasn't what anyone expected.

Three years ago, someone at this client wrote a perfectly reasonable Python script. Every night at 1 AM, it pulled order and inventory data from their ERP system, transformed it, and loaded it into a PostgreSQL analytics database. The whole thing took about 45 minutes. Dashboards refreshed before anyone got to the office. Life was good.

When I showed up in July, that same job was taking 14 hours. It started at 1 AM and finished — on a good day — around 3 PM. On a bad day, it was still running at 5 PM when the next business cycle started generating the data it was supposed to import tomorrow.

The analytics team had started putting a yellow banner on their dashboards: "Data may be up to 36 hours old." Product decisions were being made on numbers from yesterday morning. The CFO had stopped trusting the revenue dashboard entirely and had someone manually pull numbers from the ERP every afternoon.

How a 45-minute job becomes a 14-hour job

Nobody made one bad decision. The job just grew the way these things do.

The original script imported three tables: orders, order_items, and inventory_snapshots. Each import did a full table truncate and reload. With 200,000 orders and half a million line items, that was fine. Fast, simple, easy to reason about.

Three years later, the company had 8.2 million orders and 31 million line items. The inventory snapshots table — which stored daily snapshots of stock levels across 1,400 SKUs and 12 warehouses — had 6.1 billion rows. Nobody had added partitioning. Nobody had switched to incremental loads. The script still truncated and reloaded everything, every night.

But the raw data volume wasn't the whole story. Over those three years, five more tables had been added to the import. A returns table. A customer_segments table that was rebuilt nightly from a clustering model. A shipping_events table that tracked every status update from three different carriers. Each addition was a small, reasonable PR that nobody questioned.

I asked the engineer who'd added the shipping events table — 94 million rows — whether he'd considered the impact on the import window. He had. "It only added about 20 minutes when I tested it," he said. That was true. He'd tested it with two months of shipping data. Production had three years of it.

The join that went quadratic

The most interesting problem was hiding in the transformation step. Between the raw import and the final analytics tables, there was a materialized view that joined orders with inventory snapshots to calculate "available-to-promise" figures. The query looked innocent:

SELECT o.order_id, o.product_id, o.quantity,
       i.available_qty, i.warehouse_id
FROM orders o
JOIN inventory_snapshots i
  ON o.product_id = i.product_id
  AND i.snapshot_date = o.order_date
WHERE o.order_date >= CURRENT_DATE - INTERVAL '90 days'

When the product catalog had 8,000 SKUs, this was a straightforward hash join. The query planner picked a reasonable plan. But the catalog had grown to 52,000 SKUs, and the inventory snapshots table had no index on (product_id, snapshot_date). At some point — I couldn't determine exactly when — the planner switched from a hash join to a nested loop. The query went from 30 seconds to 47 minutes. Nobody noticed because it was buried inside a cron job that ran at 3 AM.

I found this by looking at pg_stat_statements. The query had accumulated 847 hours of total execution time over the past year. That's not a typo.

What didn't work

The team had tried two fixes before I arrived. The first was throwing hardware at it. They'd upgraded the database from an r5.xlarge to an r5.4xlarge. This cut the job from 18 hours to 14 hours — a $1,200/month improvement that bought them a few weeks before growth ate the headroom.

The second attempt was parallelizing the imports. Instead of loading tables sequentially, they ran them concurrently. This actually made things worse. Six full-table truncate-and-reload operations running simultaneously caused I/O contention that slowed everything down. The parallel version took 16 hours. They reverted it the same day.

What actually fixed it

The fix was architectural, not mechanical. We broke it into three changes shipped over two weeks.

Incremental loads instead of full reloads. The ERP system had an updated_at column on every table — it had been there the whole time, nobody had used it. We replaced the truncate-and-reload with a merge operation that only touched rows modified since the last successful run. The orders table went from loading 8.2 million rows to loading an average of 12,000. Import time for that table dropped from 3 hours to 40 seconds.

Partitioning the inventory snapshots table. We partitioned by month. This was the most painful change — the initial partitioning migration took 6 hours and we had to schedule it over a weekend. But once it was done, the 90-day query window in the materialized view only touched 3 partitions instead of scanning 6.1 billion rows.

Adding the missing composite index. One CREATE INDEX CONCURRENTLY on inventory_snapshots (product_id, snapshot_date) turned the 47-minute nested loop back into a 4-second hash join. This was the fix that made me mildly angry, because someone could have done it two years ago by reading the query plan.

After all three changes, the nightly job ran in 8 minutes. Not 8 hours. Eight minutes.

The part nobody talks about

The technical fixes were straightforward. I've done variations of this at four different clients. The harder problem was organizational.

This job had been degrading for three years. It crossed the 1-hour mark, then the 4-hour mark, then the 8-hour mark. At each threshold, someone noticed, someone complained, and someone applied a band-aid. But nobody ever said: this job is fundamentally broken, let's spend a sprint fixing it properly.

Why? Because the import job wasn't owned by anyone. The data engineer who wrote it originally had left 18 months ago. The analytics team consumed its output but didn't have access to modify the pipeline. The platform team managed the database but considered the import script "an analytics thing." When I asked who was responsible for the job, I got three different answers from three different people, and none of them said "me."

The import window is the canary. When it starts growing, it's telling you that your data model hasn't kept up with your data volume. You can ignore it for a while. But eventually, "a day behind" becomes "two days behind," and then someone important stops trusting the numbers. Once that trust is gone, it takes months to earn back — long after the eight-minute fix is already running.