Featured image of post DuckDB Space Reclaiming, Tested: VACUUM Doesn't Shrink, CHECKPOINT Doesn't Truncate, COPY FROM DATABASE Actually Compacts

DuckDB Space Reclaiming, Tested: VACUUM Doesn't Shrink, CHECKPOINT Doesn't Truncate, COPY FROM DATABASE Actually Compacts

DuckDB's VACUUM does not reclaim disk space and CHECKPOINT merges row groups but never truncates the file. This post measures a 1M-row database across 4 stages (536KB -> 798KB -> ...), breaks down the real division of labor among VACUUM, CHECKPOINT and COPY FROM DATABASE, and shows the runnable commands that shrink a 49%-inflated file back to 536KB on DuckDB 1.5, with official references.

A Common (Wrong) Intuition

If you’ve used PostgreSQL or SQLite, you carry a mental model: delete a lot of data, run VACUUM, and the file gets smaller. So it’s tempting to treat DuckDB’s VACUUM as the switch that shrinks a .duckdb file.

It is not. The official docs say it up front:

The VACUUM statement only has basic support in DuckDB and is mostly provided for PostgreSQL-compatibility. … The VACUUM statement does not reclaim space.

In other words, DuckDB’s VACUUM neither reclaims disk space nor shrinks the file. Its real jobs are a syntax-compatibility placeholder for Postgres users and (via VACUUM ANALYZE table(col)) rebuilding per-column statistics.

So how do you actually get the file down in size after deleting lots of rows? This post measures a 1-million-row database across four stages and pins down the true division of labor among VACUUM / CHECKPOINT / COPY FROM DATABASE.

Scope: one focused topic — space reclamation — measured on DuckDB 1.5 (Variegata). Every number is reproducible with the commands at the end.

1. Three Tools, Three Roles (the short version)

ToolReal effectDoes the .duckdb file get smaller?
VACUUM; / VACUUM ANALYZE;Whole-database: no-opNo
VACUUM my_table(my_col);Rebuilds stats for that table’s columnNo (touches stats, not space)
CHECKPOINT; / FORCE CHECKPOINT;Flushes WAL into the data file and merges qualifying row groupsReuses free space, but does not truncate
COPY FROM DATABASE a TO b;Copies content into a fresh database (a compaction rewrite)Yes, the file genuinely shrinks

In one line: the only reliable way to make the on-disk file smaller is COPY FROM DATABASE; CHECKPOINT stops further growth; VACUUM is essentially unrelated to space.

2. Minimal Reproducible Experiment: 1M Rows, Delete Half, Measure 4 Sizes

Everything below runs directly in the DuckDB CLI. File sizes are measured with bash stat -c '%s' (bytes).

Step 0: build 1M rows, checkpoint

CREATE TABLE t AS SELECT range AS id, 'payload_' || repeat('x', 40) AS payload FROM range(1000000);
CHECKPOINT;
$ stat -c '%s' exp.duckdb
536576        # S0: 1M rows, after build + checkpoint

Step 1: delete half (id % 2 = 0), no checkpoint yet

DELETE FROM t WHERE id % 2 = 0;
$ stat -c '%s' exp.duckdb
798720        # S1: the file got *bigger* after the delete (delete marks + WAL)

Note the counterintuitive bit: you deleted 50% of the data and the file grew (536KB -> 798KB). DuckDB is append-only; a delete just writes tombstones into the WAL rather than erasing in place.

Step 2: run VACUUM

VACUUM;
$ stat -c '%s' exp.duckdb
798720        # S2: identical to S1 — VACUUM has zero effect on size

Step 3: run CHECKPOINT

CHECKPOINT;
$ stat -c '%s' exp.duckdb
798720        # S3: still not truncated, size unchanged

This is the step that most surprises people: run CHECKPOINT, look at the file, see no shrink, and conclude “reclamation didn’t work.” It actually did reclaim — just not in the way you expected.

3. Why CHECKPOINT Doesn’t Truncate the File

DuckDB uses a single-file format. The official “Reclaiming Space” page opens with:

DuckDB uses a single-file format, which has some inherent limitations w.r.t. reclaiming disk space.

The mechanism: at checkpoint time, DuckDB merges adjacent row groups whose deletes cross a threshold and marks that space as reusable. It does not shorten the file’s byte length — the file stays at its historical high-water mark, and the freed region is simply reused on the next writes instead of growing the file further.

So S3 staying flat is correct behavior. “Reclaim” ≈ “stop growing as fast,” not “immediately smaller on disk.”

The docs add one detail: merging kicks in when ~25% of rows in adjacent row groups are deleted. Delete too little (say 5%) and checkpoint won’t merge those row groups, so no space is freed. Here we deleted 50%, well above the threshold, so merging does happen — visible as “reusable space,” not as truncation.

4. VACUUM’s Real Job: Rebuilding Stats, Not Collecting Space

Break VACUUM into its forms (measured on 1.5):

VACUUM;                  -- whole DB: no-op, does nothing
VACUUM ANALYZE;          -- whole DB: also a no-op
VACUUM t(id);            -- rebuilds stats for column id of table t (silent success)
VACUUM FULL;             -- error: Not implemented Error: Full vacuum option

VACUUM t(id) is the one thing it can actually do: when a column’s statistics (e.g. distinct count) go stale after heavy UPDATE/DELETE, rebuild them so the optimizer picks a better plan. That has nothing to do with disk space.

So “use VACUUM to shrink the file” is, semantically, the wrong tool from the start.

5. Actually Shrinking the File: COPY FROM DATABASE

The official docs point to COPY FROM DATABASE for compaction:

To compact the database, you can create a fresh copy of the database using the COPY FROM DATABASE statement.

Measured (continuing from section 2; the file is now 900k rows at 1,060,864 bytes):

ATTACH 'exp.duckdb' AS db1;
ATTACH 'exp_compact.duckdb' AS db2;
USE db2;
COPY FROM DATABASE db1 TO db2;
CHECKPOINT;
$ stat -c '%s' exp.duckdb; stat -c '%s' exp_compact.duckdb
1060864        # source, inflated
536576         # new DB, compacted; row count unchanged (900k rows)

Size dropped from 1,060,864 to 536,576 — about 49% smaller — with no data loss. That’s the canonical “really shrink the file” move in DuckDB: rewrite into a clean database, then switch to it.

A common production pattern: COPY FROM DATABASE into a new file, verify row counts / checksums, then atomically rename-replace the old file. The source stays read-only throughout, so it’s safe.

6. Two Extras (1.4+ / 1.5)

1. In-memory databases can checkpoint + compress (since 1.4)

ATTACH ':memory:' AS mcmp (COMPRESS);
USE mcmp;
CREATE TABLE x AS SELECT range AS id, repeat('z', 200) AS blob FROM range(50000);
CHECKPOINT;

Before 1.4, in-memory tables didn’t checkpoint or compress. Since 1.4, in-memory tables support both compression and “delete-row vacuuming” — handy for resident memory workloads that churn rows constantly (e.g. an AI agent’s in-memory working set).

2. ART-indexed tables + vacuum_rebuild_indexes (experimental)

Tables with PRIMARY KEY/UNIQUE constraints use ART indexes under the hood. By default, VACUUM skips tables with ART indexes; the experimental vacuum_rebuild_indexes setting lets vacuum compact those tables (rebuilding the indexes afterward), with 0 (the default) disabling it.

Gotcha, measured: this setting cannot be changed while the database is running —

SET vacuum_rebuild_indexes = 200000;
-- Invalid Input Error: Cannot change vacuum_rebuild_indexes setting while database is running

It can only be configured at startup / ATTACH time.

7. Automatic Checkpoint: wal_autocheckpoint

You don’t have to sprinkle CHECKPOINT by hand. DuckDB auto-checkpoints based on WAL size:

SELECT name, value FROM duckdb_settings() WHERE name = 'wal_autocheckpoint';
-- wal_autocheckpoint | 16.0 MiB

The default threshold is 16 MiB: once the WAL reaches 16MB, an automatic checkpoint fires. On write-heavy workloads you can lower it (flush WAL more eagerly, control the file high-water mark); on read-mostly workloads raising it is harmless. Understanding this default explains “why does my .duckdb keep growing” — usually it’s WAL growth triggering checkpoints, while the file never truncates.

8. When to Use What (Decision Table)

Your goalCorrect action
Deleted lots of rows; want to stop growing / reuse free spaceCHECKPOINT (or rely on auto wal_autocheckpoint)
Want the on-disk .duckdb to genuinely shrinkCOPY FROM DATABASE into a new file, then swap
Column stats stale, query plans degradedVACUUM my_table(my_col) to rebuild stats
Reach for VACUUM FULL by habitNot supported on 1.5 — it errors with Not implemented
Want to compact a PK/UNIQUE-constrained tablevacuum_rebuild_indexes (experimental, set at startup)
In-memory resident DB churning rowsATTACH ':memory:' (...) (COMPRESS) + CHECKPOINT (1.4+)

Go Deeper

Reproducibility

Further Reading

📺 Watch video tutorials → OLAP Studio YouTube

Subscribe for more DuckDB & AI automation tutorials