PostgreSQL Point-in-Time Recovery: What 35 Days Costs

Retention is the number people argue about when they set up PostgreSQL point-in-time recovery. Outside Amazon RDS, which by AWS's account does not count it, the bill is set by a second number: how much WAL the database writes in a day. On our test server a 755 MB database taking 100 small transactions a second wrote 1.67 GB of it in half an hour. Kept for 35 days, the longest window Amazon RDS and Azure Database for PostgreSQL offer, that comes to 2,790 GB of archived WAL files behind less than 1 GB of data.

CloudSpinx runs PostgreSQL for organizations in Kenya, on servers we operate or in the client's own cloud account, and a managed PostgreSQL quote has to say how far back the database restores. So we measured what that window weighs: PostgreSQL 18.6 with its default WAL and checkpoint settings, pgbench at a fixed 100 transactions a second, one change per run unless noted. Then we priced the result the three ways it gets billed: RDS in Cape Town, Azure's South Africa North region, and an archive you keep yourself on S3.

How much WAL does a PostgreSQL database write in a day?

About 100 times its own size, in our run: 80 GB a day from a 755 MB database. Very little of it is the changes themselves. We ran pg_waldump --stats over the 100 segment files the run archived, and full-page images were 94.7% of the bytes. The full_page_writes documentation explains why: the first time a page changes after a checkpoint, PostgreSQL logs the entire page, and checkpoints come every five minutes by default. Each one restarts the count.

PostgreSQL 18 changed a default that adds to this. Its initdb now enables data checksums by default, the same default behind the checksum refusal on a PostgreSQL 14 upgrade, and with checksums on, the docs say hint bit updates are always WAL-logged, as an image of the whole page when the page has not been logged since the last checkpoint. Those page images show up in pg_waldump as FPI_FOR_HINT, and they were 36.6% of everything the run wrote. We did not run a cluster with checksums off, so that is a share of this run's WAL, not a saving on offer.

Size still matters, through the page count. At the same 100 transactions a second, a 157 MB copy wrote 820 MB in the half hour against the 755 MB copy's 1.67 GB, with nearly the same number of records (1.16 million against 1.24 million). The difference was page images, 92,916 against 199,586: the bigger table spread the same rate of updates over more pages, so more of them were a page's first change since a checkpoint. Your write rate and your table sizes both feed the archive, and the retention setting multiplies whatever they produce.

What RDS, Azure and S3 charge for a 35-day window

They bill different things, which is why the same database can cost nothing on one and real money on another. We ran neither managed service, so what follows is each vendor's own rule, priced from its own price list.

Amazon RDS gives each Region free backup storage equal to the database storage you provision, and charges USD 0.113 a GB-month above that in Cape Town. On what counts, AWS's database blog on backup storage costs is direct: "Transaction log backups required for database point-in-time recovery in the automated backup window don't contribute to backup storage sizing." The RDS user guide describes the charge in snapshot terms too: the free allowance is "spread across all of your snapshots", and you are billed for "the snapshot storage that exceeds your free backup storage allowance". The daily snapshots count, and AWS's estimate for them is data size plus retained snapshots times daily change, minus provisioned storage. AWS's pricing page goes further and says most customers will never pay for backup storage. On RDS the WAL in our figure stays off the invoice by AWS's own account, and the line to watch on your bill is ChargedBackupUsage.

Azure Database for PostgreSQL counts the WAL. Microsoft's backup documentation says that "irrespective of the database size, heavy transactional activity on the server generates more WAL files", which raise backup storage, and its Backup Storage Used metric adds the retained log backups to the snapshots. The free allowance is again your provisioned storage, and South Africa North lists locally redundant backup storage at USD 0.113 a GB-month. Seven days, Azure's default retention, of our run's archive is 558 GB as PostgreSQL writes it. Microsoft's page does not say whether it compresses what it keeps, so the metric on your own server is the number to trust. Every 100 GB it shows over your allowance is USD 11.30 a month, and geo-redundant backup doubles the stored size before the allowance comes off.

An archive you run yourself costs every byte it holds, and it is as readable as the database unless you encrypt it, the same gap a plain MongoDB dump has. On S3 Standard in Cape Town, USD 0.0274 a GB-month up to 51,200 GB, the 35-day archive at stock settings is about USD 71 a month (S3 counts a GB as 1,073,741,824 bytes, so our 2,790 GB bills as 2,599), plus the base backups the window restores from, which at under 1 GB each are a rounding error here.

If you would rather see that sum on your own database than on pgbench, the scoping form asks how far back you need to restore, and you get one monthly figure back with the window in it.

Which settings shrink the WAL archive

Three changes shrank it, by very different amounts, and one setting inflated it.

Checkpoints every 15 minutes instead of 5 (checkpoint_timeout = 15min, with max_wal_size raised to 4GB so the WAL volume did not start one sooner) cut the WAL by 31% (1.16 GB against 1.67 GB), because fewer checkpoints mean fewer first changes. The docs name the price: a longer interval "can increase the amount of time needed for crash recovery."

wal_compression = lz4 compresses the page images as they are written. On pgbench it cut the WAL by 75%, and that number flatters. pgbench's account rows carry a filler column that its own source code describes as a "blank-padded empty string". The same setting on a table of random bytes cut the WAL by 20%. Compressing each archived file with zstd -3 showed the same split: the files kept 8% of their size on pgbench and 63% on random rows. We tested only these two kinds of row, so test on a copy of your own data before you trust either figure.

The setting that inflated it was archive_timeout. It forces a new WAL file after the set interval if anything was written, and the docs warn that a file closed early is "still the same length" as a full one. At 60 seconds and one transaction a second, 28 MB of WAL went to the archive as 31 files of 16 MiB, 496 MiB in all, 18.5 times what was written. Those files shrank to 0.6% of their size under zstd, so compressing each file on the way out takes back nearly all of the padding. With nothing written, no file was forced at all.

Measure your own WAL rate before you choose the window

Two readings an hour apart, at a normal busy hour, give you the number our whole calculation hangs on:

SELECT now(), pg_current_wal_lsn();    -- first reading, note the LSN
-- an hour later, with the first LSN pasted in:
SELECT pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), '3A/5C0016E8'));
SELECT wal_fpi, wal_bytes, stats_reset FROM pg_stat_wal;

Multiply the hourly figure by 24, then by the days in the window. On RDS the CloudWatch metric TransactionLogsGeneration reports the same rate in bytes per second without a query, though by AWS's account it is not what you pay for there. On Azure, read the Backup Storage Used metric.

Where the archive sits matters as much as its size. It carries the pages and rows your writes touched, so a Kenyan database archiving to Cape Town or South Africa North has its data outside Kenya. If the data protection rules that bind you want the data in the country, the archive is part of that question, not an afterthought to it.

When the window is worth paying us to price

A database on RDS with a short retention and no ChargedBackupUsage line on the bill does not need us for this. AWS's rule already keeps the WAL off the invoice, and the snapshots are inside your allowance.

The ones that do are a PostgreSQL server you run yourself, in Nairobi or in someone's cloud, whose archive nobody has ever totaled, and an Azure server whose backup line grows every month while the database does not. Send us the PostgreSQL version, the data size, where it runs, the window you need and two WAL readings an hour apart, through the managed database scoping form (the readings go in the notes box) or on WhatsApp at +254 713 403 044. You get back the archive size for that window at your write rate, the settings we would change and what each saves on your kind of data, and one monthly figure for running the database with that window and a restore tested against it. The sum is free, and sending it obliges you to nothing.