Backing up a small app database: pg_dump formats and compression, size and time from 10 MB to 1 GB on PostgreSQL 18, measured

A small app's database is easy to back up: one pg_dump a night and a few copies somewhere else. The choices are the format (plain SQL, custom or directory) and the compression (none, gzip, lz4 or zstd), and they change the file size by 4x and the time by more than 60x. We dumped and restored the same app database at 10 MB, 100 MB and 1 GB on PostgreSQL 18, three times each, with every format and method.

Short version: for a 1 GB database, pg_dump -Fc -Z zstd made a 176.7 MiB file in 6.0 seconds. The default -Fc (gzip) made a file 2% smaller in 18.4 seconds, three times longer. lz4 was as fast as zstd but 60% bigger, and zstd:19 saved another 20% for 398 seconds of CPU. Restore time hardly depends on the compression: 14 to 15 seconds for 1 GB with one job, 11 seconds with -j 4. Keeping 23 copies (a week of dailies, a month of weeklies, a year of monthlies) of that 1 GB app costs about 6 cents a month on object storage.

How we measured

The database is a synthetic app with three tables: users (email, name, plan), orders (status, amount, a note on 40% of rows, foreign key to users) and events (kind, timestamp and a small jsonb payload), with a primary key on each and three more indexes. events has ten rows per user and holds about 80% of the data, which is the usual shape: one log-like table outgrows the rest. Strings repeat the way real data does (plans, statuses, paths, browser names) next to random hex values that don't compress. We scaled it by the number of users:

We label the sizes by tables plus indexes, the space your data takes. pg_database_size() also counts the system catalogs every database carries, 7.4 MiB even when empty (see what an empty PostgreSQL 18 database costs), which is why the 10 MB database reports 17.5 MiB.

Every dump and restore ran on the same machine as the server, so network time is not in these numbers. Over a network the dump takes longer, see below.

Dump size and time for 1 GB

What the table shows:

• A dump is smaller than the database. The plain SQL file is 596.9 MiB for 1,016 MiB of tables and indexes: indexes are not dumped (only their CREATE INDEX line, rebuilt on restore), and table pages carry row headers and free space that the text form doesn't.
• zstd is the best default. At its default level (3) it compressed 3.38x, nearly the 3.46x of gzip, and finished in 6.0 s against 18.4 s. pg_dump spent 5.2 s of CPU on it against 18.0 s for gzip, so on a busy server the gap is real CPU, not just waiting.
• Compression costs nothing in time with zstd or lz4. Both took the same 6.0 to 6.1 s as an uncompressed plain dump: reading rows from the server is the slow part, not compressing them. Only gzip was slower than writing the file raw.
• The extremes don't pay. gzip:9 took twice as long as default gzip for a 1% smaller file. zstd:19 made the smallest file, 140.6 MiB, but needed 398 s: 66 times longer for 36 MiB less.
• Parallel dump helps little for one big table. pg_dump's -j works only with the directory format and dumps one table per job. With 4 jobs the dump took 4.6 s against 5.6 s with one, because events is 80% of the data and one job still has to dump it alone.
• Custom without compression is slightly bigger than plain SQL (622.5 against 596.9 MiB), so there is no reason to pick -Z 0.

10 MB and 100 MB

Smaller databases scale down almost linearly. At 100 MB:

And at 10 MB, everything but zstd:19 finished in under half a second:

At these sizes the format choice is about convenience, not time. The compression ratio stayed the same from 10 MB to 1 GB (3.4x for zstd and gzip, 2.1x for lz4), so a dump of your own data will scale the same way: measure one, multiply.

Restore time: the format barely matters

A restore spends its time loading rows with COPY and rebuilding indexes, and the server does that work, not the decompression. For 1 GB, every single-job restore from a custom dump took 14.1 to 14.8 seconds whatever the compression; pg_restore itself used 0.6 to 2.7 s of CPU. The plain SQL file through psql was the slowest, 16.8 s. --single-transaction, which makes the restore all-or-nothing, cost nothing (14.5 s).

Parallel restore (pg_restore -j 4) cut 1 GB to 11.6 s from a custom file and 11.0 s from a directory: it loads the three tables and builds the indexes at the same time, but events and its indexes still dominate. It needs a custom or directory dump (not plain SQL, not a pipe) and can't be combined with --single-transaction. On a database with many similar-sized tables the gain is bigger; with one big table, as most small apps have, expect about a quarter off.

Over the network

If your database is hosted, you run pg_dump with its connection string from your own machine or a CI job. Two things change. First, pg_dump compresses on the client: the server sends rows uncompressed (PostgreSQL 18 has no protocol compression), so a 1 GB database sends about 600 MB over the wire however small the file ends up. At 100 Mbit/s that is at least 50 seconds; it, not the compression, sets the dump time. Second, pg_dump must be at least as new as the server: pg_dump 16 refuses to dump a PostgreSQL 18 server. Install the version 18 client (postgresql-client-18 on Debian and Ubuntu from the PostgreSQL apt repository) and check pg_dump --version.

What to keep and how often

For a small app, a nightly dump with a simple rotation covers the usual accidents: a bad migration found the next morning, a deleted customer found a week later, a question about last quarter's data. Seven daily, four weekly and twelve monthly copies are 23 files. This script does it with nothing but pg_dump and find, from cron at night:

Then copy the folder off the machine (rclone sync /backups/app remote:app-backups to any S3-compatible bucket), because a backup on the same disk as the database is not a backup. And once a month restore the newest dump into a scratch database: createdb app_check && pg_restore -j 4 -d app_check app-2026-10-28.dump, then count rows in your main tables. By the table above, that takes 11 seconds per GB, and it is the only test that proves the file is good.

What it costs to store

The worked example: 23 copies at the sizes we measured, priced at Cloudflare R2 ($0.015 per GB-month, 10 GB free) and Backblaze B2 ($6.95 per TB-month, first 10 GB free), before the free allowances:

For one 1 GB app the format decides between 22 and 6 cents a month on R2, and the zstd copies fit in the free 10 GB. The difference matters at scale and in transfer time more than on the bill: 100 apps of 100 MB, dumped with zstd, keep 39 GB, about 59 cents a month on R2 or 27 cents on B2, and Contabo's object storage sells 250 GB with free traffic for €2.49 a month, room for six such fleets. zstd:19 would save 0.9 GB per 1 GB app, 1.3 cents a month on R2, for six and a half minutes of CPU every night: not worth it.

Checklist

1. Dump with pg_dump -Fc -Z zstd: as fast as no compression, under a third of the size, and pg_restore can pick single tables out of it.
2. Use the directory format with -j only when your data is spread over several big tables.
3. Use a pg_dump client at least as new as the server (18 for PostgreSQL 18).
4. Write to a temporary name, check it with pg_restore --list, then rename, so a failed night never replaces a good file.
5. Keep 7 daily, 4 weekly and 12 monthly copies, and copy them off the database machine.
6. Restore the newest dump into a scratch database once a month and count rows. Use pg_restore -j 4 when you need it fast.
7. Expect a restore to take about 11 to 15 seconds per GB on a small server, most of it loading rows and rebuilding indexes.

Backing up a database on systemi

Every systemi app gets its own PostgreSQL 18 database and a connection string in the console, so the commands above work as they are: point pg_dump at your app's connection string from your laptop or a scheduled job. One database per app also means each dump holds exactly one app (why one database per app is cheap), and a restore never touches another app's data.

Sources: our own tests on 28 October 2026 (3 runs, scripts and raw output kept with the article's change request); PostgreSQL 18 documentation, pg_dump (-Z methods gzip/lz4/zstd, custom and directory formats compressed with gzip by default, -j only with the directory format, no dumping from newer servers) and pg_restore (-j only with custom and directory archives from a regular file, not with --single-transaction); prices as listed on 28 October 2026: Cloudflare R2 pricing ($0.015 per GB-month standard, 10 GB-month free), Backblaze B2 pricing ($6.95 per TB-month, first 10 GB free), Contabo Object Storage (from €2.49 a month, 250 GB per slice per Contabo's launch post, free traffic).