Tablespace size management questions

From: sandman42(at)libero(dot)it
To: pgsql-admin(at)lists(dot)postgresql(dot)org
Subject: Tablespace size management questions
Date: 2020-02-19 10:59:43
Message-ID: 999725727.55048.1582109983412@mail1.libero.it
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

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

Browse pgsql-admin by date

  From Date Subject
Next Message Raj kumar 2020-02-20 09:44:56 primary key of partitioned table referenced by foreign table, v11, v12 clarification
Previous Message Nagaraj Raj 2020-02-18 19:10:20 Re: DB running out of memory issues after upgrade