Re: Starting Postgres when there is no disk space

On Wed, May 1, 2019 at 10:25 PM Igal Sapir <igal(at)lucee(dot)org> 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