Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1jX2dr-0005ox-3a for pgsql-performance@arkaria.postgresql.org; Fri, 08 May 2020 13:02:15 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1jX2do-0007EP-J0 for pgsql-performance@arkaria.postgresql.org; Fri, 08 May 2020 13:02:12 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1jX2do-0007EI-Ag for pgsql-performance@lists.postgresql.org; Fri, 08 May 2020 13:02:12 +0000 Received: from mout.perfora.net ([74.208.4.194]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1jX2dh-00070Y-75 for pgsql-performance@lists.postgresql.org; Fri, 08 May 2020 13:02:11 +0000 Received: from [192.168.1.143] ([98.169.208.7]) by mrelay.perfora.net (mreueus002 [74.208.5.2]) with ESMTPSA (Nemesis) id 0Lw33b-1j39Sr1fxN-017oYz; Fri, 08 May 2020 15:01:56 +0200 Subject: Re: pg_attribute, pg_class, pg_depend grow huge in count and size with multiple tenants. To: Avinash Kumar Cc: Laurenz Albe , Rory Campbell-Lange , "David G. Johnston" , samhitha g , pgsql-performance@lists.postgresql.org References: <20200507210846.GA17040@campbell-lange.net> <17c5b15451fac2ace7a815d1d2c8f86e1d232802.camel@cybertec.at> <8f67073e5a6818a17eef5c44b087640db8939f7d.camel@cybertec.at> From: MichaelDBA Message-ID: <4b0c83b7-c592-cd2e-9897-764fde9d748a@sqlexec.com> Date: Fri, 8 May 2020 09:01:50 -0400 User-Agent: Mozilla/5.0 (Windows NT 10.0; WOW64; rv:52.0) Gecko/20100101 PostboxApp/7.0.14 MIME-Version: 1.0 In-Reply-To: Content-Type: multipart/alternative; boundary="------------2C06A517DFCE624567ACEE64" Content-Language: en-US X-Provags-ID: V03:K1:C61c6aNVZyBfrdlzn75joDkS2RKe7WwDjYErkL1bzH69GZ7CjyD gJCbyvG65bCLqR9GDZG8GXs5NDjm7FjlaV+a2hxDb8Mxy+etAeB0+6pQjG88jsqQHpPCFAd LW8qdKiVJCF2AslywBu7LpvRxvj7bvenwtuhHrlergXttb0NdDcn6hqsBdfvJAe6vmgsClu m7IRT0PJA9MusH+W0thqA== X-Spam-Flag: NO X-UI-Out-Filterresults: notjunk:1;V03:K0:yFprinbYTi4=:ZheYzsVTitUDf6E1al7agY MQA2A8ETilcb2FHnFb6WPw3S2YF//tquysaHO7Y8OErMwOyDc72Ykv8KenAd8Rirf2kSvSxQn eTdW6c2Ya6WaEY6EEqNzvUwapAFnDnoIpVAw7kGgky5tKo4mOxduFJgDlTXIXmisIcdmqN04J r9zfkztJ5U9thOUa+v4NJq6el2VxfNF4fKdxgt0cOjJqjR/iwJF41VgpOEOMwT6pymGXHvuHF hcms0VkjcZ/E5si2z/T+pk/gzxBevCRRxJF68tfndOpPW5u3H2h+ntIzjuCBqPQaDFx79NPS7 oJnzJsMRvs+/OZcsi2D5PvbrkcGQB8OJyyAdxrn8dUNV7GrQrxbu+D3HPB5U/QKpjxmofTDKj 0d9GmRLLaCiQSkoUTpjjBbUGjKm4V4be73U9FWwuyboRGw6ZHmtLhK9Vi36nZVYOZR/7ZphUY tvodtrlLl5Wnp2C9BQmzjwylL4Fqgot358qflXTIPnWHY3GHm+yn5zB85UK2sqS72We62fDgU KVp+rbfZ4gvZ5pfnyk6Vyfi5z2n78dd/uQ5nPy2FDXqlfBoHPL+A0IQy9kr5pVpjcE3/TPGwy k0WI5UfgqB4uLAPJ8pfGwvPQTuZVjMsPcrShsg0DNnIbd6tqWu8BIb4Lu5Smti+z6OsA2XMrg NDW0Ml5ukVwLtIpqEmTRtrqMcuM0Mdq0ICe3CfSM8IxlCyUmhKneKWszYKbX/appcr/vJQfCO u4J5LOcy0KDEOYKHQTuoORwLS/0f5RYBQLXsxNSIKpy5tQ2JJ2PtPFJm1fmZODSN6FvL6a6XQ Bno7Cg/kBgsN/k0NSRsc7/Nhp7AKWsBIMtbGNAIYcKVy5CczJnmE4iCKg0+ARlsv7Nnjvys List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multi-part message in MIME format. --------------2C06A517DFCE624567ACEE64 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit Hi all, Since we are talking about multi-tenant databases, the citus extension 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 > 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 --------------2C06A517DFCE624567ACEE64 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit Hi all,

Since we are talking about multi-tenant databases, the citus extension 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> 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

--------------2C06A517DFCE624567ACEE64--