Hacker News new | ask | show | jobs
by AlexTrask 2233 days ago
I suggest an improvement, to be able to vacuum without having to have the free space of the table. Example to vacuum a 1Gb table you must have a 1Gb free
2 comments

Is that always the case? I'd expect that only to be necessary with a complete table rewrite, i.e. VACUUM FULL, which is not what (auto)VACUUM does by default.
Even with a full table rewrite, why do you need to rewrite to RAM?
The "you must have a 1Gb free" refers to disk space, not main-memory (RAM).
That is only needed for VACUUM FULL, not for (auto) vacuum.
But sometimes VACUUM FULL is necessary for tables as fragmentation will otherwise never be resolved. A common scenario are tables that store some kind of time series events that are updated while active. Older partitions have no write activity but remain heavily fragmented unless VACUUM FULL is issued. For that you need space at least as big as the table. I don't really understand why as page rewrites could happen partly in place.

Overall I love PostgreSQL but the vacuum/MVCC system of MSSQL and Oracle is much better suited for heavy writes, PostgreSQL needs a lot of tweaking in table structures to handle those.

I totally agree. Postgres' vacuum is definitely its weakest point.

I hoped to see the "pluggable storage" show up in Postgres 13 (including zheap which uses UNDO logs) but apparently that didn't make it.