The Space Does Not Come Back
The previous lesson measured what soft-deleted rows cost, and the obvious conclusion is to remove them for real. Hard delete has its own bill, and it starts with something almost nobody expects.
A real PostgreSQL, a million rows, and 507,591 of them deleted. The file on disk is exactly as big as it was.
A delete run to free disk space frees no disk space. It frees space for the table's own future inserts, which is usually what was wanted and is never what was asked for.
Three Steps, One Rewrite
Every size and every timing is measured against a PostgreSQL this lab started on a scratch directory and tore down.
VACUUM marks the space reusable by future inserts into the same table. It does not hand it back to the operating system, because handing it back means rewriting the file.
The table is a sequence of fixed-size pages. Removing a row makes space inside a page and does nothing to the number of pages, which is what the file size is.
This is also why a table that has churned is bigger than its data. Every page is partly holes, and unless the insert pattern refills them, the table stays at the size of its high water mark forever.
Autovacuum runs the ordinary VACUUM continuously and never runs VACUUM FULL, because the ordinary one can run alongside traffic and the full one cannot.