Supabase free tier and full vacuums
I learned something fun about the difference between Postgres vacuum and vacuum full today while trying to trim the fat on an Supabase instance that was getting close to the free tier database size limit.
When a row is deleted from a Postgres table, the underlying tuple on disk is not actually deleted, but marked as deleted. This is enables all the fun MVCC magic. Then Postgres kindly runs a background vacuum job on some cadence to actually removes those tuples from disk and gives you back all that sweet sweet storage. EXCEPT IT DOESN'T. The autovacuum job just marks those "deleted" tuples on disk as "reusable". It does NOT give that freed up disk space back to the OS.
This is not a problem if you're self-hosting a Postgres instance on a dedicated machine. It's a big problem if you're trying to scrape by on the free-tier offering of a service whose limitations are based solely on the claimed resources of the Postgres instance rather than actual disk space consumed.
vacuum full does the job I thought was being done by the autovacuum job. The major downside: a full vacuum requires an exclusive lock on the table while the disk rewrite is taking place. So it could take a decent amount of time for large tables, but if you're already under the 500MB Supabase limit you're likely looking at a few minutes of downtime. There is also the issue with the full vacuum requiring the storage capacity to effectively duplicate your table. This is an issue for me with one of my tables where duplicating the table size would likely cause the instance to exceed the 500MB limit. I'm not entirely sure what would happen in this case considering the instance would likely return below the limit quickly after. It's unclear to me if the available disk space is restricted or if Supabase just monitors for the database size being above 500MB before kicking me into read-only mode. If I ever decide I need to do this I'll be sure to record it for others to see.
Note: I also read about pg_repack which I have not tested, but claims to provide similar functionality to the full vacuum without the exclusive table lock.