Re: How to restrict schema size per tenant

Thank you guys.

On Sat, Jul 6, 2024 at 11:16 AM Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>
wrote:

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

--

Thanks,
Niraj Nandane,
Veritas LLC, Pune