On Wed, May 1, 2019 at 10:25 PM Igal Sapir <igal@xxxxxxxxx> wrote:
I have a scheduled process that runs daily to delete old data and do full vacuum. Not sure why this happened (again).
If you are doing a regularly scheduled "vacuum full", you are almost certainly doing something wrong. Are these "vacuum full" completing, or are they failing (probably due to transient out of space errors)?
A ordinary non-full vacuum will make the space available for internal reuse. It will not return the space to filesystem (usually), so won't get you out of the problem. But it should prevent you from getting into the problem in the first place. If it is failing to reuse the space adequately, you should figure out why, rather than just blindly jumping to regularly scheduled "vacuum full". For example, what is it that is bloating, the tables themselves, their indexes, or their TOAST tables? Or is there any bloat in the first place? Are you sure your deletions are equal to your insertions, over the long term average? If you are doing "vacuum full" and you are certain it is completing successfully, but it doesn't free up much space, then that is strong evidence that you don't actually have bloat, you just have more live data than you think you do. (It could also mean you have done something silly with your "fillfactor" settings.)
If you don't want the space to be reused, to keep a high correlation between insert time and physical order of the rows for example, then you should look into partitioning, as you have already noted.
Now that you have the system up again and some space freed up, I'd create a "ballast" file with a few gig of random (to avoid filesystem-level compression, should you have such a filesystem) data on the same device that holds your main data, that can be deleted in an emergency to give you enough free space to at least start the system. Of course, monitoring is also nice, but the ballast file is more robust and there is no reason you can't have both.
Cheers,
Jeff
