pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedpg_attribute, pg_class, pg_depend grow huge in count and size with multiple tenants.
12+ messages / 7 participants
[nested] [flat]
* pg_attribute, pg_class, pg_depend grow huge in count and size with multiple tenants.
@ 2020-05-07 17:10 samhitha g <samhithagarudadri@gmail.com>
2020-05-07 20:17 ` Re: pg_attribute, pg_class, pg_depend grow huge in count and size with multiple tenants. David G. Johnston <david.g.johnston@gmail.com>
2020-05-08 13:44 ` Re: pg_attribute, pg_class, pg_depend grow huge in count and size with multiple tenants. Jeff Janes <jeff.janes@gmail.com>
0 siblings, 2 replies; 12+ messages in thread
From: samhitha g @ 2020-05-07 17:10 UTC (permalink / raw)
To: pgsql-performance@lists.postgresql.org
Hi experts,
Our application serves multiple tenants. Each tenant has the schema with a
few hundreds of tables and few functions.
We have 2000 clients so we have to create 2000 schemas in a single database.
While doing this, i observed that the catalog tables pg_attribute,
pg_class, pg_depend grow huge in count and size.
Do you think this will be a challenge during execution of every query ?
When Postgres parses an sql to find the best execution plan, does it scan
any of these catalogs that could eventually take more time?
Any other challenges you have come across or foresee in such cases ?
Thanks,
Sammy.
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: pg_attribute, pg_class, pg_depend grow huge in count and size with multiple tenants.
2020-05-07 17:10 pg_attribute, pg_class, pg_depend grow huge in count and size with multiple tenants. samhitha g <samhithagarudadri@gmail.com>
@ 2020-05-07 20:17 ` David G. Johnston <david.g.johnston@gmail.com>
2020-05-07 20:28 ` Re: pg_attribute, pg_class, pg_depend grow huge in count and size with multiple tenants. Avinash Kumar <avinash.vallarapu@gmail.com>
1 sibling, 1 reply; 12+ messages in thread
From: David G. Johnston @ 2020-05-07 20:17 UTC (permalink / raw)
To: samhitha g <samhithagarudadri@gmail.com>; +Cc: pgsql-performance@lists.postgresql.org
On Thu, May 7, 2020 at 1:05 PM samhitha g <samhithagarudadri@gmail.com>
wrote:
> Our application serves multiple tenants. Each tenant has the schema with a
> few hundreds of tables and few functions.
> We have 2000 clients so we have to create 2000 schemas in a single
> database.
>
That is one option but I wouldn't say you must. If you cannot get
individual tables to be multi-tenant you are probably better off having one
database per client on a shared cluster - at least given the size of the
schema and number of clients.
David J.
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: pg_attribute, pg_class, pg_depend grow huge in count and size with multiple tenants.
2020-05-07 17:10 pg_attribute, pg_class, pg_depend grow huge in count and size with multiple tenants. samhitha g <samhithagarudadri@gmail.com>
2020-05-07 20:17 ` Re: pg_attribute, pg_class, pg_depend grow huge in count and size with multiple tenants. David G. Johnston <david.g.johnston@gmail.com>
@ 2020-05-07 20:28 ` Avinash Kumar <avinash.vallarapu@gmail.com>
2020-05-07 21:08 ` Re: pg_attribute, pg_class, pg_depend grow huge in count and size with multiple tenants. Rory Campbell-Lange <rory@campbell-lange.net>
0 siblings, 1 reply; 12+ messages in thread
From: Avinash Kumar @ 2020-05-07 20:28 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: samhitha g <samhithagarudadri@gmail.com>; pgsql-performance@lists.postgresql.org
Hi,
On Thu, May 7, 2020 at 5:18 PM David G. Johnston <david.g.johnston@gmail.com>
wrote:
> On Thu, May 7, 2020 at 1:05 PM samhitha g <samhithagarudadri@gmail.com>
> wrote:
>
>> Our application serves multiple tenants. Each tenant has the schema
>> with a few hundreds of tables and few functions.
>> We have 2000 clients so we have to create 2000 schemas in a single
>> database.
>>
>
> That is one option but I wouldn't say you must. If you cannot get
> individual tables to be multi-tenant you are probably better off having one
> database per client on a shared cluster - at least given the size of the
> schema and number of clients.
>
I am working on a similar problem.
1 database per each client may be a killer when you have a connection
pooler that creates a pool for a unique combination of (user,database).
>
> David J.
>
>
--
Regards,
Avinash Vallarapu
+1-902-221-5976
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: pg_attribute, pg_class, pg_depend grow huge in count and size with multiple tenants.
2020-05-07 17:10 pg_attribute, pg_class, pg_depend grow huge in count and size with multiple tenants. samhitha g <samhithagarudadri@gmail.com>
2020-05-07 20:17 ` Re: pg_attribute, pg_class, pg_depend grow huge in count and size with multiple tenants. David G. Johnston <david.g.johnston@gmail.com>
2020-05-07 20:28 ` Re: pg_attribute, pg_class, pg_depend grow huge in count and size with multiple tenants. Avinash Kumar <avinash.vallarapu@gmail.com>
@ 2020-05-07 21:08 ` Rory Campbell-Lange <rory@campbell-lange.net>
2020-05-07 21:17 ` Re: pg_attribute, pg_class, pg_depend grow huge in count and size with multiple tenants. Avinash Kumar <avinash.vallarapu@gmail.com>
0 siblings, 1 reply; 12+ messages in thread
From: Rory Campbell-Lange @ 2020-05-07 21:08 UTC (permalink / raw)
To: Avinash Kumar <avinash.vallarapu@gmail.com>; +Cc: David G. Johnston <david.g.johnston@gmail.com>; samhitha g <samhithagarudadri@gmail.com>; pgsql-performance@lists.postgresql.org
On 07/05/20, Avinash Kumar (avinash.vallarapu@gmail.com) wrote:
> >> Our application serves multiple tenants. Each tenant has the schema
> >> with a few hundreds of tables and few functions.
> >> We have 2000 clients so we have to create 2000 schemas in a single
> >> database.
> > That is one option but I wouldn't say you must. If you cannot get
> > individual tables to be multi-tenant you are probably better off having one
> > database per client on a shared cluster - at least given the size of the
> > schema and number of clients.
> >
> I am working on a similar problem.
> 1 database per each client may be a killer when you have a connection
> pooler that creates a pool for a unique combination of (user,database).
One of our clusters has well over 500 databases fronted by pg_bouncer.
We get excellent connection "flattening" using pg_bouncer with
per-database connection spikes dealt with through a reserve pool.
The nice thing about separate databases is that it is easy to scale
horizontally.
Rory
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: pg_attribute, pg_class, pg_depend grow huge in count and size with multiple tenants.
2020-05-07 17:10 pg_attribute, pg_class, pg_depend grow huge in count and size with multiple tenants. samhitha g <samhithagarudadri@gmail.com>
2020-05-07 20:17 ` Re: pg_attribute, pg_class, pg_depend grow huge in count and size with multiple tenants. David G. Johnston <david.g.johnston@gmail.com>
2020-05-07 20:28 ` Re: pg_attribute, pg_class, pg_depend grow huge in count and size with multiple tenants. Avinash Kumar <avinash.vallarapu@gmail.com>
2020-05-07 21:08 ` Re: pg_attribute, pg_class, pg_depend grow huge in count and size with multiple tenants. Rory Campbell-Lange <rory@campbell-lange.net>
@ 2020-05-07 21:17 ` Avinash Kumar <avinash.vallarapu@gmail.com>
2020-05-08 06:31 ` Re: pg_attribute, pg_class, pg_depend grow huge in count and size with multiple tenants. Laurenz Albe <laurenz.albe@cybertec.at>
2020-05-08 13:54 ` Re: pg_attribute, pg_class, pg_depend grow huge in count and size with multiple tenants. Jeff Janes <jeff.janes@gmail.com>
0 siblings, 2 replies; 12+ messages in thread
From: Avinash Kumar @ 2020-05-07 21:17 UTC (permalink / raw)
To: Rory Campbell-Lange <rory@campbell-lange.net>; +Cc: David G. Johnston <david.g.johnston@gmail.com>; samhitha g <samhithagarudadri@gmail.com>; pgsql-performance@lists.postgresql.org
Hi,
On Thu, May 7, 2020 at 6:08 PM Rory Campbell-Lange <rory@campbell-lange.net>
wrote:
> On 07/05/20, Avinash Kumar (avinash.vallarapu@gmail.com) wrote:
> > >> Our application serves multiple tenants. Each tenant has the schema
> > >> with a few hundreds of tables and few functions.
> > >> We have 2000 clients so we have to create 2000 schemas in a single
> > >> database.
>
> > > That is one option but I wouldn't say you must. If you cannot get
> > > individual tables to be multi-tenant you are probably better off
> having one
> > > database per client on a shared cluster - at least given the size of
> the
> > > schema and number of clients.
> > >
> > I am working on a similar problem.
> > 1 database per each client may be a killer when you have a connection
> > pooler that creates a pool for a unique combination of (user,database).
>
> One of our clusters has well over 500 databases fronted by pg_bouncer.
>
> We get excellent connection "flattening" using pg_bouncer with
> per-database connection spikes dealt with through a reserve pool.
>
What if you see at least 4 connections being established by each client
during peak ? And if you serve 4 or 2 connections per each DB, then you
are creating 1000 or more reserved connections with 500 DBs in a cluster.
>
> The nice thing about separate databases is that it is easy to scale
> horizontally.
>
Agreed. But, how about autovacuum ? Workers shift from DB to DB and 500
clusters means you may have to have a lot of manual vacuuming in place as
well.
>
> Rory
>
--
Regards,
Avinash Vallarapu
+1-902-221-5976
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: pg_attribute, pg_class, pg_depend grow huge in count and size with multiple tenants.
2020-05-07 17:10 pg_attribute, pg_class, pg_depend grow huge in count and size with multiple tenants. samhitha g <samhithagarudadri@gmail.com>
2020-05-07 20:17 ` Re: pg_attribute, pg_class, pg_depend grow huge in count and size with multiple tenants. David G. Johnston <david.g.johnston@gmail.com>
2020-05-07 20:28 ` Re: pg_attribute, pg_class, pg_depend grow huge in count and size with multiple tenants. Avinash Kumar <avinash.vallarapu@gmail.com>
2020-05-07 21:08 ` Re: pg_attribute, pg_class, pg_depend grow huge in count and size with multiple tenants. Rory Campbell-Lange <rory@campbell-lange.net>
2020-05-07 21:17 ` Re: pg_attribute, pg_class, pg_depend grow huge in count and size with multiple tenants. Avinash Kumar <avinash.vallarapu@gmail.com>
@ 2020-05-08 06:31 ` Laurenz Albe <laurenz.albe@cybertec.at>
2020-05-08 06:47 ` Re: pg_attribute, pg_class, pg_depend grow huge in count and size with multiple tenants. Avinash Kumar <avinash.vallarapu@gmail.com>
1 sibling, 1 reply; 12+ messages in thread
From: Laurenz Albe @ 2020-05-08 06:31 UTC (permalink / raw)
To: Avinash Kumar <avinash.vallarapu@gmail.com>; Rory Campbell-Lange <rory@campbell-lange.net>; +Cc: David G. Johnston <david.g.johnston@gmail.com>; samhitha g <samhithagarudadri@gmail.com>; pgsql-performance@lists.postgresql.org
On Thu, 2020-05-07 at 18:17 -0300, Avinash Kumar wrote:
> > The nice thing about separate databases is that it is easy to scale
> > horizontally.
>
> Agreed. But, how about autovacuum ? Workers shift from DB to DB and 500 clusters
> means you may have to have a lot of manual vacuuming in place as well.
Just set "autovacuum_max_workers" higher.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: pg_attribute, pg_class, pg_depend grow huge in count and size with multiple tenants.
2020-05-07 17:10 pg_attribute, pg_class, pg_depend grow huge in count and size with multiple tenants. samhitha g <samhithagarudadri@gmail.com>
2020-05-07 20:17 ` Re: pg_attribute, pg_class, pg_depend grow huge in count and size with multiple tenants. David G. Johnston <david.g.johnston@gmail.com>
2020-05-07 20:28 ` Re: pg_attribute, pg_class, pg_depend grow huge in count and size with multiple tenants. Avinash Kumar <avinash.vallarapu@gmail.com>
2020-05-07 21:08 ` Re: pg_attribute, pg_class, pg_depend grow huge in count and size with multiple tenants. Rory Campbell-Lange <rory@campbell-lange.net>
2020-05-07 21:17 ` Re: pg_attribute, pg_class, pg_depend grow huge in count and size with multiple tenants. Avinash Kumar <avinash.vallarapu@gmail.com>
2020-05-08 06:31 ` Re: pg_attribute, pg_class, pg_depend grow huge in count and size with multiple tenants. Laurenz Albe <laurenz.albe@cybertec.at>
@ 2020-05-08 06:47 ` Avinash Kumar <avinash.vallarapu@gmail.com>
2020-05-08 06:53 ` Re: pg_attribute, pg_class, pg_depend grow huge in count and size with multiple tenants. Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 1 reply; 12+ messages in thread
From: Avinash Kumar @ 2020-05-08 06:47 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: Rory Campbell-Lange <rory@campbell-lange.net>; David G. Johnston <david.g.johnston@gmail.com>; samhitha g <samhithagarudadri@gmail.com>; pgsql-performance@lists.postgresql.org
Hi,
On Fri, May 8, 2020 at 3:31 AM Laurenz Albe <laurenz.albe@cybertec.at>
wrote:
> On Thu, 2020-05-07 at 18:17 -0300, Avinash Kumar wrote:
> > > The nice thing about separate databases is that it is easy to scale
> > > horizontally.
> >
> > Agreed. But, how about autovacuum ? Workers shift from DB to DB and 500
> clusters
> > means you may have to have a lot of manual vacuuming in place as well.
>
> Just set "autovacuum_max_workers" higher.
>
No, that wouldn't help. If you just increase autovacuum_max_workers, the
total cost limit of autovacuum_vacuum_cost_limit (or vacuum_cost_limit) is
shared by so many workers and it further delays autovacuum per each worker.
Instead you need to increase autovacuum_vacuum_cost_limit as well when you
increase the number of workers. But, if you do that and also increase
workers, well, you would easily reach the limitations of the disk. I am not
sure it is anywhere advised to have 20 autovacuum_max_workers unless i have
a disk with lots of IOPS and with very tiny tables across all the
databases.
>
> Yours,
> Laurenz Albe
> --
> Cybertec | https://www.cybertec-postgresql.com
>
>
--
Regards,
Avinash Vallarapu
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: pg_attribute, pg_class, pg_depend grow huge in count and size with multiple tenants.
2020-05-07 17:10 pg_attribute, pg_class, pg_depend grow huge in count and size with multiple tenants. samhitha g <samhithagarudadri@gmail.com>
2020-05-07 20:17 ` Re: pg_attribute, pg_class, pg_depend grow huge in count and size with multiple tenants. David G. Johnston <david.g.johnston@gmail.com>
2020-05-07 20:28 ` Re: pg_attribute, pg_class, pg_depend grow huge in count and size with multiple tenants. Avinash Kumar <avinash.vallarapu@gmail.com>
2020-05-07 21:08 ` Re: pg_attribute, pg_class, pg_depend grow huge in count and size with multiple tenants. Rory Campbell-Lange <rory@campbell-lange.net>
2020-05-07 21:17 ` Re: pg_attribute, pg_class, pg_depend grow huge in count and size with multiple tenants. Avinash Kumar <avinash.vallarapu@gmail.com>
2020-05-08 06:31 ` Re: pg_attribute, pg_class, pg_depend grow huge in count and size with multiple tenants. Laurenz Albe <laurenz.albe@cybertec.at>
2020-05-08 06:47 ` Re: pg_attribute, pg_class, pg_depend grow huge in count and size with multiple tenants. Avinash Kumar <avinash.vallarapu@gmail.com>
@ 2020-05-08 06:53 ` Laurenz Albe <laurenz.albe@cybertec.at>
2020-05-08 10:14 ` Re: pg_attribute, pg_class, pg_depend grow huge in count and size with multiple tenants. Avinash Kumar <avinash.vallarapu@gmail.com>
0 siblings, 1 reply; 12+ messages in thread
From: Laurenz Albe @ 2020-05-08 06:53 UTC (permalink / raw)
To: Avinash Kumar <avinash.vallarapu@gmail.com>; +Cc: Rory Campbell-Lange <rory@campbell-lange.net>; David G. Johnston <david.g.johnston@gmail.com>; samhitha g <samhithagarudadri@gmail.com>; pgsql-performance@lists.postgresql.org
On Fri, 2020-05-08 at 03:47 -0300, Avinash Kumar wrote:
> > Just set "autovacuum_max_workers" higher.
>
> No, that wouldn't help. If you just increase autovacuum_max_workers, the total cost limit of
> autovacuum_vacuum_cost_limit (or vacuum_cost_limit) is shared by so many workers and it
> further delays autovacuum per each worker. Instead you need to increase autovacuum_vacuum_cost_limit
> as well when you increase the number of workers.
True, I should have mentioned that.
> But, if you do that and also increase workers, well, you would easily reach the limitations
> of the disk. I am not sure it is anywhere advised to have 20 autovacuum_max_workers unless
> i have a disk with lots of IOPS and with very tiny tables across all the databases.
Sure, if you have a high database load, you will at some point exceed the limits of
the machine, which is not surprising. What I am trying to say is that you have to ramp
up the resources for autovacuum together with increasing the overall workload.
You should consider autovacuum as part of that workload.
If your machine cannot cope with the workload any more, you have to scale, which
is easily done by adding more machines if you have many databases.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: pg_attribute, pg_class, pg_depend grow huge in count and size with multiple tenants.
2020-05-07 17:10 pg_attribute, pg_class, pg_depend grow huge in count and size with multiple tenants. samhitha g <samhithagarudadri@gmail.com>
2020-05-07 20:17 ` Re: pg_attribute, pg_class, pg_depend grow huge in count and size with multiple tenants. David G. Johnston <david.g.johnston@gmail.com>
2020-05-07 20:28 ` Re: pg_attribute, pg_class, pg_depend grow huge in count and size with multiple tenants. Avinash Kumar <avinash.vallarapu@gmail.com>
2020-05-07 21:08 ` Re: pg_attribute, pg_class, pg_depend grow huge in count and size with multiple tenants. Rory Campbell-Lange <rory@campbell-lange.net>
2020-05-07 21:17 ` Re: pg_attribute, pg_class, pg_depend grow huge in count and size with multiple tenants. Avinash Kumar <avinash.vallarapu@gmail.com>
2020-05-08 06:31 ` Re: pg_attribute, pg_class, pg_depend grow huge in count and size with multiple tenants. Laurenz Albe <laurenz.albe@cybertec.at>
2020-05-08 06:47 ` Re: pg_attribute, pg_class, pg_depend grow huge in count and size with multiple tenants. Avinash Kumar <avinash.vallarapu@gmail.com>
2020-05-08 06:53 ` Re: pg_attribute, pg_class, pg_depend grow huge in count and size with multiple tenants. Laurenz Albe <laurenz.albe@cybertec.at>
@ 2020-05-08 10:14 ` Avinash Kumar <avinash.vallarapu@gmail.com>
2020-05-08 13:01 ` Re: pg_attribute, pg_class, pg_depend grow huge in count and size with multiple tenants. MichaelDBA <MichaelDBA@sqlexec.com>
0 siblings, 1 reply; 12+ messages in thread
From: Avinash Kumar @ 2020-05-08 10:14 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: Rory Campbell-Lange <rory@campbell-lange.net>; David G. Johnston <david.g.johnston@gmail.com>; samhitha g <samhithagarudadri@gmail.com>; pgsql-performance@lists.postgresql.org
Hi,
On Fri, May 8, 2020 at 3:53 AM Laurenz Albe <laurenz.albe@cybertec.at>
wrote:
> On Fri, 2020-05-08 at 03:47 -0300, Avinash Kumar wrote:
> > > Just set "autovacuum_max_workers" higher.
> >
> > No, that wouldn't help. If you just increase autovacuum_max_workers, the
> total cost limit of
> > autovacuum_vacuum_cost_limit (or vacuum_cost_limit) is shared by so many
> workers and it
> > further delays autovacuum per each worker. Instead you need to increase
> autovacuum_vacuum_cost_limit
> > as well when you increase the number of workers.
>
> True, I should have mentioned that.
>
> > But, if you do that and also increase workers, well, you would easily
> reach the limitations
> > of the disk. I am not sure it is anywhere advised to have 20
> autovacuum_max_workers unless
> > i have a disk with lots of IOPS and with very tiny tables across all the
> databases.
>
> Sure, if you have a high database load, you will at some point exceed the
> limits of
> the machine, which is not surprising. What I am trying to say is that you
> have to ramp
> up the resources for autovacuum together with increasing the overall
> workload.
> You should consider autovacuum as part of that workload.
>
> If your machine cannot cope with the workload any more, you have to scale,
> which
> is easily done by adding more machines if you have many databases.
>
Agreed. Getting back to the original question asked by Sammy, i think it is
still bad to create 2000 databases for storing 2000 clients/(schemas) for a
multi-tenant setup.
>
> Yours,
> Laurenz Albe
> --
> Cybertec | https://www.cybertec-postgresql.com
>
>
--
Regards,
Avinash Vallarapu
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: pg_attribute, pg_class, pg_depend grow huge in count and size with multiple tenants.
2020-05-07 17:10 pg_attribute, pg_class, pg_depend grow huge in count and size with multiple tenants. samhitha g <samhithagarudadri@gmail.com>
2020-05-07 20:17 ` Re: pg_attribute, pg_class, pg_depend grow huge in count and size with multiple tenants. David G. Johnston <david.g.johnston@gmail.com>
2020-05-07 20:28 ` Re: pg_attribute, pg_class, pg_depend grow huge in count and size with multiple tenants. Avinash Kumar <avinash.vallarapu@gmail.com>
2020-05-07 21:08 ` Re: pg_attribute, pg_class, pg_depend grow huge in count and size with multiple tenants. Rory Campbell-Lange <rory@campbell-lange.net>
2020-05-07 21:17 ` Re: pg_attribute, pg_class, pg_depend grow huge in count and size with multiple tenants. Avinash Kumar <avinash.vallarapu@gmail.com>
2020-05-08 06:31 ` Re: pg_attribute, pg_class, pg_depend grow huge in count and size with multiple tenants. Laurenz Albe <laurenz.albe@cybertec.at>
2020-05-08 06:47 ` Re: pg_attribute, pg_class, pg_depend grow huge in count and size with multiple tenants. Avinash Kumar <avinash.vallarapu@gmail.com>
2020-05-08 06:53 ` Re: pg_attribute, pg_class, pg_depend grow huge in count and size with multiple tenants. Laurenz Albe <laurenz.albe@cybertec.at>
2020-05-08 10:14 ` Re: pg_attribute, pg_class, pg_depend grow huge in count and size with multiple tenants. Avinash Kumar <avinash.vallarapu@gmail.com>
@ 2020-05-08 13:01 ` MichaelDBA <MichaelDBA@sqlexec.com>
0 siblings, 0 replies; 12+ messages in thread
From: MichaelDBA @ 2020-05-08 13:01 UTC (permalink / raw)
To: Avinash Kumar <avinash.vallarapu@gmail.com>; +Cc: Laurenz Albe <laurenz.albe@cybertec.at>; Rory Campbell-Lange <rory@campbell-lange.net>; David G. Johnston <david.g.johnston@gmail.com>; samhitha g <samhithagarudadri@gmail.com>; pgsql-performance@lists.postgresql.org
Hi all,
Since we are talking about multi-tenant databases, the citus extension
<https://www.citusdata.com/product/community; fits in neatly with that
using horizontal partitioning/shards.
Regards,
Michael Vitale
Avinash Kumar wrote on 5/8/2020 6:14 AM:
> Hi,
>
> On Fri, May 8, 2020 at 3:53 AM Laurenz Albe <laurenz.albe@cybertec.at
> <mailto:laurenz.albe@cybertec.at>> wrote:
>
> On Fri, 2020-05-08 at 03:47 -0300, Avinash Kumar wrote:
> > > Just set "autovacuum_max_workers" higher.
> >
> > No, that wouldn't help. If you just increase
> autovacuum_max_workers, the total cost limit of
> > autovacuum_vacuum_cost_limit (or vacuum_cost_limit) is shared by
> so many workers and it
> > further delays autovacuum per each worker. Instead you need to
> increase autovacuum_vacuum_cost_limit
> > as well when you increase the number of workers.
>
> True, I should have mentioned that.
>
> > But, if you do that and also increase workers, well, you would
> easily reach the limitations
> > of the disk. I am not sure it is anywhere advised to have 20
> autovacuum_max_workers unless
> > i have a disk with lots of IOPS and with very tiny tables across
> all the databases.
>
> Sure, if you have a high database load, you will at some point
> exceed the limits of
> the machine, which is not surprising. What I am trying to say is
> that you have to ramp
> up the resources for autovacuum together with increasing the
> overall workload.
> You should consider autovacuum as part of that workload.
>
> If your machine cannot cope with the workload any more, you have
> to scale, which
> is easily done by adding more machines if you have many databases.
>
> Agreed. Getting back to the original question asked by Sammy, i think
> it is still bad to create 2000 databases for storing 2000
> clients/(schemas) for a multi-tenant setup.
>
>
> Yours,
> Laurenz Albe
> --
> Cybertec | https://www.cybertec-postgresql.com
>
>
>
> --
> Regards,
> Avinash Vallarapu
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: pg_attribute, pg_class, pg_depend grow huge in count and size with multiple tenants.
2020-05-07 17:10 pg_attribute, pg_class, pg_depend grow huge in count and size with multiple tenants. samhitha g <samhithagarudadri@gmail.com>
2020-05-07 20:17 ` Re: pg_attribute, pg_class, pg_depend grow huge in count and size with multiple tenants. David G. Johnston <david.g.johnston@gmail.com>
2020-05-07 20:28 ` Re: pg_attribute, pg_class, pg_depend grow huge in count and size with multiple tenants. Avinash Kumar <avinash.vallarapu@gmail.com>
2020-05-07 21:08 ` Re: pg_attribute, pg_class, pg_depend grow huge in count and size with multiple tenants. Rory Campbell-Lange <rory@campbell-lange.net>
2020-05-07 21:17 ` Re: pg_attribute, pg_class, pg_depend grow huge in count and size with multiple tenants. Avinash Kumar <avinash.vallarapu@gmail.com>
@ 2020-05-08 13:54 ` Jeff Janes <jeff.janes@gmail.com>
1 sibling, 0 replies; 12+ messages in thread
From: Jeff Janes @ 2020-05-08 13:54 UTC (permalink / raw)
To: Avinash Kumar <avinash.vallarapu@gmail.com>; +Cc: Rory Campbell-Lange <rory@campbell-lange.net>; David G. Johnston <david.g.johnston@gmail.com>; samhitha g <samhithagarudadri@gmail.com>; Pgsql Performance <pgsql-performance@lists.postgresql.org>
On Thu, May 7, 2020 at 5:17 PM Avinash Kumar <avinash.vallarapu@gmail.com>
wrote:
> Hi,
>
> On Thu, May 7, 2020 at 6:08 PM Rory Campbell-Lange <
> rory@campbell-lange.net> wrote:
>
>> One of our clusters has well over 500 databases fronted by pg_bouncer.
>>
>> We get excellent connection "flattening" using pg_bouncer with
>> per-database connection spikes dealt with through a reserve pool.
>>
> What if you see at least 4 connections being established by each client
> during peak ? And if you serve 4 or 2 connections per each DB, then you
> are creating 1000 or more reserved connections with 500 DBs in a cluster.
>
Does every database spike at the same time?
>
>> The nice thing about separate databases is that it is easy to scale
>> horizontally.
>>
> Agreed. But, how about autovacuum ? Workers shift from DB to DB and 500
> clusters means you may have to have a lot of manual vacuuming in place as
> well.
>
Why would having difference schemas in different DBs change your manual
vacuuming needs? And if anything, having separate DBs will make
autovacuuming more efficient, as it keeps the statistics collectors stats
files smaller.
Cheers,
Jeff
>
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: pg_attribute, pg_class, pg_depend grow huge in count and size with multiple tenants.
2020-05-07 17:10 pg_attribute, pg_class, pg_depend grow huge in count and size with multiple tenants. samhitha g <samhithagarudadri@gmail.com>
@ 2020-05-08 13:44 ` Jeff Janes <jeff.janes@gmail.com>
1 sibling, 0 replies; 12+ messages in thread
From: Jeff Janes @ 2020-05-08 13:44 UTC (permalink / raw)
To: samhitha g <samhithagarudadri@gmail.com>; +Cc: Pgsql Performance <pgsql-performance@lists.postgresql.org>
On Thu, May 7, 2020 at 4:05 PM samhitha g <samhithagarudadri@gmail.com>
wrote:
> Hi experts,
>
> Our application serves multiple tenants. Each tenant has the schema with a
> few hundreds of tables and few functions.
> We have 2000 clients so we have to create 2000 schemas in a single
> database.
>
> While doing this, i observed that the catalog tables pg_attribute,
> pg_class, pg_depend grow huge in count and size.
>
Please attach numbers to "huge". We don't know what "huge" means to you.
"2000 * a few hundred" tables is certainly getting to the point where it
makes sense to be concerned. But my concern would be more about backup and
recovery, version upgrades, pg_dump, etc. not about daily operations.
Cheers,
Jeff
>
^ permalink raw reply [nested|flat] 12+ messages in thread
end of thread, other threads:[~2020-05-08 13:54 UTC | newest]
Thread overview: 12+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-05-07 17:10 pg_attribute, pg_class, pg_depend grow huge in count and size with multiple tenants. samhitha g <samhithagarudadri@gmail.com>
2020-05-07 20:17 ` David G. Johnston <david.g.johnston@gmail.com>
2020-05-07 20:28 ` Avinash Kumar <avinash.vallarapu@gmail.com>
2020-05-07 21:08 ` Rory Campbell-Lange <rory@campbell-lange.net>
2020-05-07 21:17 ` Avinash Kumar <avinash.vallarapu@gmail.com>
2020-05-08 06:31 ` Laurenz Albe <laurenz.albe@cybertec.at>
2020-05-08 06:47 ` Avinash Kumar <avinash.vallarapu@gmail.com>
2020-05-08 06:53 ` Laurenz Albe <laurenz.albe@cybertec.at>
2020-05-08 10:14 ` Avinash Kumar <avinash.vallarapu@gmail.com>
2020-05-08 13:01 ` MichaelDBA <MichaelDBA@sqlexec.com>
2020-05-08 13:54 ` Jeff Janes <jeff.janes@gmail.com>
2020-05-08 13:44 ` Jeff Janes <jeff.janes@gmail.com>
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