PostgreSQL Size Quota on Table or Schema

Question

How do one limit the size of a PostgreSQL table? (or schema)

Limit in either bytes or rows would be OK.

I'm starting to think that there's no easy and obvious solution.

I have a lot of identical tables in multiple schemas (my take on multi-tenancy) and I would like to restrict each schema to a certain max size.

I have yet to find anything in Postgres that allows me to turn on any kind of quota. So I suppose you need to build this functionality yourself.

The naive 'solution' would be to do something like:

insert only if select count(*) < max quota.

But that does not feel right.

Anyone with better solutions in mind?

Answer 1

You can create a trigger that checks the number of records in your destination table when an INSERT is used.

The trigger function would look like this:

CREATE OR REPLACE FUNCTION check_number_of_row()
RETURNS TRIGGER AS
$body$
BEGIN
    -- replace 100 by the number of rows you want
    IF (SELECT count(*) FROM your_table) > 100
    THEN 
        RAISE EXCEPTION 'INSERT statement exceeding maximum number of rows for this table' 
    END IF;
END;
$body$
LANGUAGE plpgsql;

And the trigger will be like this:

CREATE TRIGGER tr_check_number_of_row 
BEFORE INSERT ON your_table
FOR EACH ROW EXECUTE PROCEDURE check_number_of_row();

There is no other way I think to set table size in PostgreSQL, but you can restrict row limit on table.

Answer 2

You can create a table in a specific tablespace (see this and this)

You can then limit the size of that table space on the OS level.