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;
