Re: How to restrict schema size per tenant

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