Hi all
I have a postgres 10 db which is the model for an MVC app.
On it many (200,000) LOBS get saved, eventually processed, and stored or deleted depending on some conditions.
I have two problems.
The first is about disk space management, i.e. I want that my tablespace occupy only a portion of disk that I'll allow to it, and when the maximum allowed storage is reached, the application has to stop inserting LOBS.
AFAIK there are no ways to set quota on tablespaces, so if the application continues inserting it fills up the whole disk space, not only the one I've chosen to occupy.
So in order to avoid problems I've set up a disk quota at the OS level, but this is a rough solution. Is there any better way to manage this problem?
The second problem is about unused space release. AFAIK, if I delete some LOBS, the pg_largeobject size is exactly the same, so I have to do a vacuum full to release the unused space of it. But for doing this how much additional space do I need? Reading somewhere it seems that I need at list the table size free disk space, so if it's 100 GB, I need to have another 100 GB in order to perform vacuum.
Is it true?
I've got a baffling experience on that. I had deleted 100,000 records, tried to vacuum full, and I got a "no disk space" error. I deleted another 120,000 records chunk (but the table is not empty), retried the vacuum full and this time worked.
Can you help me shedding light on this matter?
Thanks
Francesco