agora inbox for pgsql-admin@postgresql.org
help / color / mirror / Atom feedHow to restrict schema size per tenant
5+ messages / 3 participants
[nested] [flat]
* How to restrict schema size per tenant
@ 2024-07-05 14:33 niraj nandane <niraj.nandane@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: niraj nandane @ 2024-07-05 14:33 UTC (permalink / raw)
To: pgsql-admin
Hello Team,
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?
--
Thanks,
Niraj Nandane
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: How to restrict schema size per tenant
@ 2024-07-05 15:33 Laurenz Albe <laurenz.albe@cybertec.at>
parent: niraj nandane <niraj.nandane@gmail.com>
0 siblings, 2 replies; 5+ messages in thread
From: Laurenz Albe @ 2024-07-05 15:33 UTC (permalink / raw)
To: niraj nandane <niraj.nandane@gmail.com>; pgsql-admin
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: How to restrict schema size per tenant
@ 2024-07-05 15:41 Scott Ribe <scott_ribe@elevated-dev.com>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
1 sibling, 0 replies; 5+ messages in thread
From: Scott Ribe @ 2024-07-05 15:41 UTC (permalink / raw)
To: niraj nandane <niraj.nandane@gmail.com>; +Cc: pgsql-admin
Only thing I can think of: tablespace per tenant, on separate volumes sized to the desired limits. But that seems like a configuration nightmare since you want this limit across multiple databases.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: How to restrict schema size per tenant
@ 2024-07-06 05:46 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
1 sibling, 1 reply; 5+ messages in thread
From: Laurenz Albe @ 2024-07-06 05:46 UTC (permalink / raw)
To: niraj nandane <niraj.nandane@gmail.com>; pgsql-admin
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: How to restrict schema size per tenant
@ 2024-07-09 03:16 niraj nandane <niraj.nandane@gmail.com>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 0 replies; 5+ messages in thread
From: niraj nandane @ 2024-07-09 03:16 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: pgsql-admin
Thank you guys.
On Sat, Jul 6, 2024 at 11:16 AM Laurenz Albe <laurenz.albe@cybertec.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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2024-07-09 03:16 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-07-05 14:33 How to restrict schema size per tenant niraj nandane <niraj.nandane@gmail.com>
2024-07-05 15:33 ` Laurenz Albe <laurenz.albe@cybertec.at>
2024-07-05 15:41 ` Scott Ribe <scott_ribe@elevated-dev.com>
2024-07-06 05:46 ` Laurenz Albe <laurenz.albe@cybertec.at>
2024-07-09 03:16 ` niraj nandane <niraj.nandane@gmail.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox