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 1pjxRw-000096-HO for pgsql-admin@arkaria.postgresql.org; Wed, 05 Apr 2023 07:20:56 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pjxRu-0004Xq-Qf for pgsql-admin@arkaria.postgresql.org; Wed, 05 Apr 2023 07:20:54 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pjxRu-0004Xg-Fu for pgsql-admin@lists.postgresql.org; Wed, 05 Apr 2023 07:20:54 +0000 Received: from mail-yw1-x112d.google.com ([2607:f8b0:4864:20::112d]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1pjxRs-00019H-AS for pgsql-admin@lists.postgresql.org; Wed, 05 Apr 2023 07:20:53 +0000 Received: by mail-yw1-x112d.google.com with SMTP id 00721157ae682-544787916d9so660361677b3.13 for ; Wed, 05 Apr 2023 00:20:52 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; t=1680679251; h=in-reply-to:from:references:to:content-language:subject:user-agent :mime-version:date:message-id:from:to:cc:subject:date:message-id :reply-to; bh=DVIeVOku8NWM6+dgoaa1uO/tRVeWQDsyLjGJBpP1HsE=; b=TH5uzEQw7KXpQOukzyjWrkJsDXyIMPVmJ0iD4kMrKvMWFs8mDYL7zMjyFzSRjx7GhL MHGolwBcStW3ik68sFLoOoQF3SBJWkFxQLQHzodRCTIN8B1H81wtWuzszhpO8SkhAn4/ sra05PwAjmVBNHxd//eaM+BIm9D1D2/aHVjxxkUbthJpqLbG6cpxYsDiHyy153RmYRjK dOiM87xclGrPEXahuZsckACPzxb7QDDeACIqWNNZNpBKXDwIqxD40YiehEgFx+O+lZuJ xTAzwe81i1VxT8w0ofFajzcCkE1EbSEvF1BKUoONLwPc5DyVhvyF5PHoWHg8/AMrvFl5 NDJQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; t=1680679251; h=in-reply-to:from:references:to:content-language:subject:user-agent :mime-version:date:message-id:x-gm-message-state:from:to:cc:subject :date:message-id:reply-to; bh=DVIeVOku8NWM6+dgoaa1uO/tRVeWQDsyLjGJBpP1HsE=; b=0YLruyEJU4ZjrtSnfqsjM4fdoYwKmyDo5ekvY9mqQo8fHVnTchN0Gy0TnkDOcsnmpk rE/EkPs5XCu1Zi4D7N1V5zO9jBmTxPO8rNtmpPaoacMLqNLhHmSaFFYOcVJxw4ITPq+j iE8kSIZIP6fRnCpjwaK6CL6ABhUw8DPoFGaQlwcizlMlkHme078EclIctqg7vnHBLRh6 bD/Zqv/5R3qqXM0pI/ecTdZe5dPRbTJwJfa5F2GsHq+4vEjEDCVUk5YP5Y96j74+te58 BTG0dV0eT2QFoPFuSBBiKVSLdyquNAKYQsSyFI8w6jfJV62O8iGpkv8vzxwH6Jf6Wikm 0Ing== X-Gm-Message-State: AAQBX9fMQLTw54DRULFMOggH62/LcHfDlaZms3ocQHlcUxvCNMCUFBQE O9FNMf1VdxUWvyvaqH5FHwnIA8B3Nic= X-Google-Smtp-Source: AKy350Z9/CQH5ZMvfhPMk4ngHw9Yeqct/Y3BNU3NjMnuEB42WIzT/yqZte1Vyk+Pl1jvmRcbcgUXUw== X-Received: by 2002:a0d:ea86:0:b0:541:877e:4b70 with SMTP id t128-20020a0dea86000000b00541877e4b70mr4843427ywe.44.1680679251255; Wed, 05 Apr 2023 00:20:51 -0700 (PDT) Received: from ?IPV6:2600:1700:bd2c:b810:c4e6:635a:1871:d77e? ([2600:1700:bd2c:b810:c4e6:635a:1871:d77e]) by smtp.googlemail.com with ESMTPSA id a6-20020a814d06000000b00545a08184a1sm3750234ywb.49.2023.04.05.00.20.50 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Wed, 05 Apr 2023 00:20:50 -0700 (PDT) Content-Type: multipart/alternative; boundary="------------PnvUq4Czv06t3wOXb0Xzwj1y" Message-ID: Date: Wed, 5 Apr 2023 02:20:50 -0500 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.9.0 Subject: Re: Max tables in a cluster? Content-Language: en-US To: pgsql-admin@lists.postgresql.org References: <18750398329.1002ca2ba316.8870907212977424118@zohocorp.com> From: Ron In-Reply-To: <18750398329.1002ca2ba316.8870907212977424118@zohocorp.com> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------PnvUq4Czv06t3wOXb0Xzwj1y Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit On 4/5/23 02:07, Karthik Krishnakumar wrote: > Hi admins, > > Is there a limit to number of tables that a cluster can support? https://www.postgresql.org/docs/13/limits.html > At the moment, one of my clusters has about 250k user tables (plus 150k > toast tables) across a hundred schemas within a single database. > My application team wants to double the number of schemas (and with it the > tables) > I am looking for both pros and cons of doing this and what tuning can help > this cluster perform acceptably. > > My questions are > 1. Is there a limit to number of tables a cluster can support effectively? > 2. My guess is autovacuum would have to be tuned. Current gucs for this are > max_autovacuum_workers = 6 > maintenance_work_mem = 1GB > autovacuum_vacuum_scale_factor = 0.2 > autovacuum_vacuum_cost_delay = 20ms > > Are there any potential problems that I need to think about ? > > My machine specs are as below. > RAM 256GB > 32-core CPU > 7TB SSD > > thanks > karthik > -- Born in Arizona, moved to Babylonia. --------------PnvUq4Czv06t3wOXb0Xzwj1y Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 7bit On 4/5/23 02:07, Karthik Krishnakumar wrote:
Hi admins,

Is there a limit to number of tables that a cluster can support?

https://www.postgresql.org/docs/13/limits.html

At the moment, one of my clusters has about 250k user tables (plus 150k toast tables) across a hundred schemas within a single database.
My application team wants to double the number of schemas (and with it the tables)
I am looking for both pros and cons of doing this and what tuning can help this cluster perform acceptably.


My questions are
1. Is there a limit to number of tables a cluster can support effectively?
2. My guess is autovacuum would have to be tuned. Current gucs for this are
max_autovacuum_workers = 6
maintenance_work_mem = 1GB
autovacuum_vacuum_scale_factor = 0.2
autovacuum_vacuum_cost_delay = 20ms

Are there any potential problems that I need to think about ?

My machine specs are as below.
RAM 256GB
32-core CPU
7TB SSD

thanks
karthik


--
Born in Arizona, moved to Babylonia.
--------------PnvUq4Czv06t3wOXb0Xzwj1y--