Thread: How to restrict schema size per tenant

On Fri, 2024-07-05 at 20:03 +0530, niraj nandane wrote:
> We are using Postgres schema based tenancy approach for our SaaS application.
> We create schema per tenant. We have Postgres instance in HA mode.
> We have multiple micro services and each service have its own database.
> For eg. Auth service have auth database, audit have audit. Inside each database,
> we create schema per tenant. We want to restrict usage to 10GB per tenant combined
> across all database. Is there any tool or built in way to monitor this in Postgres?

I don't know any.  You'll have to run a query like

SELECT sum(pg_total_relation_size(t.oid)),
       s.nspname
FROM pg_class AS t
   RIGHT JOIN pg_namespace AS s
      ON t.relnamespace = s.oid
WHERE NOT s.nspname LIKE ANY (ARRAY['pg\_catalog','pg\_toast%','information\_schema','pg\_temp%'])
GROUP BY s.nspname;

Yours,
Laurenz Albe

Only thing I can think of: tablespace per tenant, on separate volumes sized to the desired limits. But that seems like
aconfiguration nightmare since you want this limit across multiple databases. 

On Fri, 2024-07-05 at 17:33 +0200, Laurenz Albe wrote:
> On Fri, 2024-07-05 at 20:03 +0530, niraj nandane wrote:
> > We are using Postgres schema based tenancy approach for our SaaS application.
> > We create schema per tenant. We have Postgres instance in HA mode.
> > We have multiple micro services and each service have its own database.
> > For eg. Auth service have auth database, audit have audit. Inside each database,
> > we create schema per tenant. We want to restrict usage to 10GB per tenant combined
> > across all database. Is there any tool or built in way to monitor this in Postgres?
>
> I don't know any.  You'll have to run a query like
>
> SELECT sum(pg_total_relation_size(t.oid)),
>        s.nspname
> FROM pg_class AS t
>    RIGHT JOIN pg_namespace AS s
>       ON t.relnamespace = s.oid
> WHERE NOT s.nspname LIKE ANY (ARRAY['pg\_catalog','pg\_toast%','information\_schema','pg\_temp%'])
> GROUP BY s.nspname;

Sorry, I forgot to restrict the query to tables.  It should be

SELECT sum(pg_total_relation_size(t.oid)),
       s.nspname
FROM pg_class AS t
   RIGHT JOIN pg_namespace AS s
      ON t.relnamespace = s.oid
WHERE NOT s.nspname LIKE ANY (ARRAY['pg\_catalog','pg\_toast%','information\_schema','pg\_temp%'])
  AND t.relkind = 'r'
GROUP BY s.nspname;

Yours,
Laurenz Albe