4

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?

Kirilian
  • 3
  • 1
kirilian
  • 41
  • 2
  • 1
    you'll need to roll your own solution: https://www.postgresql.org/message-id/BAY149-w576121FBC100CCF74D469CAE5C0@phx.gbl – Jack Douglas Apr 10 '17 at 11:43

1 Answers1

2

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

Ashiq Ahamed
  • 181
  • 7