4

I have a couple of tables in a PostgreSQL database which are used very frequently (for Insert/Delete purposes). Sometimes, their size of the tables grow up to GB's. How do I reclaim the disk space from these tables without locking them. These tables need to be used almost all the times so I can't afford getting them locked. VACUUM FULL reclaims the disk space however locks the table so I can't use FULL option.

Can someone please suggest a way?

Thanks

P_Ar
  • 377
  • 2
  • 9
  • 25

1 Answers1

4

Often you can avoid the problem by configuring autovacuum sufficiently aggressive that it can keep up with the change rate.

If that doesn't do the trick, or if you have regular mass DELETEs, look into a tool like pg_squeeze or pg_repack.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • There is also pgcompattable which you can read about at https://stackoverflow.com/a/59223609/14731 – Gili Jan 26 '23 at 22:29
  • @Gili I looked at the code, wondering how in the world a client side tool would go about this. While the idea is cute (`VACUUM` the table, then update rows until the last pages are empty, then keep repeating that while relying on `VACUUM` truncation, finally rebuilding the indexes), it will cause a lot of churn and load on the table with those repeated `UPDATES`. – Laurenz Albe Jan 27 '23 at 04:29
  • Fair enough. I wish I could get either `pg_squeeze` or `pg_repack` compiling (not to mention running) for Windows. It doesn't look like there is a way for now... – Gili Jan 27 '23 at 20:22
  • @Gili It should be possible with pg_squeeze, but building on Windows is always tough. – Laurenz Albe Jan 30 '23 at 05:14