From: Laurenz Albe <laurenz.albe@cybertec.at>
To: niraj nandane <niraj.nandane@gmail.com>
To: pgsql-admin@postgresql.org
Subject: Re: How to restrict schema size per tenant
Date: Sat, 06 Jul 2024 07:46:15 +0200
Message-ID: <73cc9d9102a5568834c3473f9cb69099efb46768.camel@cybertec.at> (raw)
In-Reply-To: <cacb3b0efc193defb8e595566c9b892833b79341.camel@cybertec.at>
References: <CALpWO+CodegxqSS9MJva1hwj82GUUWwbBJqOu3+0zK5xRhOLwQ@mail.gmail.com>
<cacb3b0efc193defb8e595566c9b892833b79341.camel@cybertec.at>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: laurenz.albe@cybertec.at, niraj.nandane@gmail.com
Subject: Re: How to restrict schema size per tenant
In-Reply-To: <73cc9d9102a5568834c3473f9cb69099efb46768.camel@cybertec.at>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox