WAL Archive Size Calculator

In database administration, there’s one time that’s worse than a crash: when you come back to find out that your archive storage was completely full. Not the crash. That’s the silent afternoon where you notice the Write-Ahead Log files is all gone. Now you can’t recover from a base backup because you don’t have log files anymore. And now you’ve closed your recovery window. Getting the size of your WAL archives right is more important than picking the storage type or algorithm for compression. Except then you forgot about replica pins, which extend retention beyond what your policy dictates.

Now let’s look at what creates those logs. Databases deals with bursts of activity, which most folks assume are just an average. A batch import or a nightly vacuum job can triple write load for an hour. Put that into the calculation, along with your hourly rate and a multiplier for busy periods, and it will do the math. What differentiates theory from actual survival is that burst factor. Plan on sizing storage for quiet hours, and you’re going to run out when things are busy.

How to Size Your Database Archive Storage

The data show up fast. Using compression to reduce its size helps lower the bill, but doesn’t alter the fact that it arrives fast. A 2:1 ratio cuts costs, but that’s arithmetic. There’s still some unarchived stuff in the pending queue and there needs to be room for that while the network catches up.

Another concern is replica slots. When PostgreSQL loses connection to a standby server and that server becomes out-of-sync, PostgreSQL will not delete any WAL files that was created while disconnected. Even though these files may be old enough to satisfy your archive policy, they remain on disk consuming disk space on the primary until either the lag is resolved or someone clears them manually. You’re purchasing insurance in case a process becomes stuck, the network experiences a glitch, etc.

It’ll ask how much lag you expect as the worst-case scenario. If you don’t provide one, then when your archive directory fills up (every last byte of disk), it halts the primary database, which can no longer write any new transaction. The tradeoff is that longer retention times have longer restores: If you keep 30 days of logs for auditing, then you’ll have to play back two weeks’ worth of changes when you load a weekly base backup for a restore operation. That’s less efficient and increases the risk of missing logs in the sequence. To avoid this, you’ll have to take more frequent base backups. That spreads out the effort but requires overhead.

The reference tables aligns typical workload types with reasonable defaults. I don’t want the same buffering on my little home server as an application supporting a financial ledger with compliance audit requirements. Match your budget for storage to your real-world recovery requirements instead of how much you’re afraid of losing data.

The last limit is bandwidth. Archiving requires you to ship files across a network, typically through some sort of encrypted tunnel or VPN, which decreases your throughput. Your upload speed must be able to match (or exceed) the rate of data being generated during peaks, otherwise the archive command lag behind. Soon, the pending queue fills and the database stops accepting new writes. Don’t just calculate average daily growth. Plan for spikes! You want to make sure the pipe draining the WAL files is larger than the tap filling them, even while the tap is running full-blast.

The size of this storage tells how much risk you’re willing to tolerate. Do you need a system which fails if it takes an hour for some replica to catch up? Or do you accept that your database will be down for a week while catching up from a disaster? That’s what the number represents, the price of your decision.

How much data are you realy writing? Add a margin for unexpected spikes in traffic. How much latency do you always get when you don’t expect any? When your database is running right, its archive is invisible. When it’s wrong, it shuts down your database before you realize there’s a problem.