Nightly Batch Misses Deadline After Backup Overlap
Northline Freight runs a 200 GB PostgreSQL order-reconciliation database on an 8-core, 32 GB VM. The nightly batch that closes orders and feeds invoicing must finish by 06:00. For three weeks it has ended at 07:10-07:40, delaying invoices. The database CPU averages 45% but spikes to 70%; disk queue length is persistently above 20; the transaction log grows 15 GB per day. Last month the team added an index on order_date to speed a report, and the nightly full backup was moved from 01:00 to 03:00 to fit a new retention policy, overlapping the batch. The operations manager proposes upgrading to 16 cores. The CFO wants a fix before quarter-end, nine business days away. The DBA is on leave. The team has no production monitoring beyond OS tools and can make one change this weekend.
1. The operations manager wants to double CPU cores. Using the symptoms, explain whether CPU is the limiting resource. What evidence supports your answer?
Read the worked answer
CPU is not the limiting resource. The database averages 45% and spikes to 70%, so there is headroom; a saturated CPU would sit near 100% with run-queue waits. The persistent disk queue above 20 is the stronger signal: the batch is waiting on I/O, not compute. The 15 GB/day transaction log and the backup moved into the 03:00 batch window add write and read I/O on the same storage. Doubling cores would raise cost and may even increase concurrent I/O pressure without shortening the batch. The correct next step is to confirm with wait statistics or OS disk latency, but the available evidence points to I/O contention, not CPU.
2. Recommend one change for the weekend and one follow-up measurement. Justify both against the quarter-end deadline.
Read the worked answer
For the weekend, move the full backup out of the batch window-for example, start it after the batch completes or replace it with a storage snapshot that does not compete for database I/O. This is a configuration change, reversible, and does not require the absent DBA to redesign schemas. It directly removes one known source of disk contention before quarter-end. Follow-up measurement: record batch start/end time, disk queue length, and average disk latency each night for five nights, and compare with the three-week baseline. If batch time returns under 06:00, the backup overlap was a major contributor; if not, escalate to I/O wait analysis. Do not combine this with an index change in the same window, so the effect is attributable.
3. What should the team do about the new index on order_date? Answer as a judgement, not a rule.
Read the worked answer
Do not drop the index as an emergency action. The index on order_date may slow inserts and log growth, but the timeline shows the backup overlap started at the same time as the slowdown, and the index was added for a report. Dropping it could break that report and would add a second uncontrolled change. Instead, check whether the index is actually used (for example, index usage statistics) and compare batch duration before and after the index was added. If it is unused and the batch is stable, schedule removal in a normal change window with a rollback script. If it is used, keep it and address I/O capacity separately.