Postgres tablespace size management

Question

I have a Postgres 10 database 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.

1. The first is about disk space management, i.e. I want that my tablespace occupies 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?

2. 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?

Answer 1

The best way to maintain a size limit for a table (or set of tables or a database) is to create a separate file system with the desired size maximum, define a tablespace on that file system and put the tables into that tablespace.

It is OK to limit size for user tables in that way; exceeding the limit will cause an error, but processing will continue. But you should make sure that PostgreSQL does not run out of space for system tables or (most important) WAL. That's why a separate tablespace is the right solution.

If you are using large objects, you have to take care that the large objects are removed when you delete a table row. That does not happen automatically.

VACUUM (FULL) rewrites a table, so you must have room for both the old and the new table until the operation is finished.

Your “baffling experience” is easily explained like this:

• In your first experiment, there was not enough space to hold a new copy of the table with the rows that were not deleted.

• In your second experiment, the new table was smaller, so the space was sufficient.

Answer 2

I'm going to add to this.

The problem as noted by Laurenz is when its time to clean the deleted rows there must be enough room in the TableSpace to do the vacuum, so using OS disk quota is problematic and the error messages do no reflect what is going on.

I remember reading somewhere that someone on some database had implemented something similar I do not remember so I can not assign the credit.

Postgresql has no means for table space quotas but there is a way to implement one with Triggers, a Table to Track Quota and to Query the size of the table.

this code has not been tested but should get anyone close to a working solution

Create Table table_quota ( 
 table_name text primary key,
 table_size limit integer);
Create or Replace Function table_size_limit(ptable_name, ptuple_size integer)
returns boolean
LANGUAGE 'plpgsql'
COST 100
VOLATILE NOT LEAKPROOF
$code$
declare 
 _r record;
begin
 select pg_table_size(ptable_name) + ptuple_size as new_size, table_size 
 into _r
 from table_quota where table_name = ptable_name;
 if _r.new_size >= table_size then 
 return false;
 end if ;
 return true;
end;
$code$
Create or Replace Function _check_size();
RETURNS trigger
LANGUAGE 'plpgsql'
COST 100
VOLATILE NOT LEAKPROOF
$code$
declare 
begin
 if table_size_limit( 'table_with_quota', octet_length(new.*::text)) then
 return new;
 --- note casting the entire row to string will overstate the size of the row by 
 ---allot but accuracy should not be critical in this use case. 
 else
 Raise exception 'Table has reached its Quota';
end;
$code$

This does not take into account Indexes, so modification will be needed to get that to work