PostgreSQL Size Quota on Table or Schema

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?

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 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 u can restrict row limit on table

StackExchange Database Administrators Q#170571, answer score: 2