Postgresql – Postgres tablespace size management

postgresqlpostgresql-10tablespaces

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?

Best Answer

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.

Related Question