Re: 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