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 1qEGjs-0005me-89 for pgsql-general@arkaria.postgresql.org; Tue, 27 Jun 2023 22:00:44 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1qEGjq-0000fS-Pr for pgsql-general@arkaria.postgresql.org; Tue, 27 Jun 2023 22:00:42 +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 1qEGjq-0000eo-BL for pgsql-general@lists.postgresql.org; Tue, 27 Jun 2023 22:00:42 +0000 Received: from mail-pf1-x42a.google.com ([2607:f8b0:4864:20::42a]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1qEGjn-000qwj-1U for pgsql-general@lists.postgresql.org; Tue, 27 Jun 2023 22:00:41 +0000 Received: by mail-pf1-x42a.google.com with SMTP id d2e1a72fcca58-6686ef86110so2719382b3a.2 for ; Tue, 27 Jun 2023 15:00:39 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=ardentperf-com.20221208.gappssmtp.com; s=20221208; t=1687903236; x=1690495236; h=content-transfer-encoding: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=BytBNMmr1xYFAqyAevXl8+Ou2nvzs51PmHnlM4+2t9M=; b=YU3rGJOzQdTgD6TgUSPLaYaXZNR+FOZe8cdYHaEF6qCwc1g9dxtCo+NDvbGysdFO0N SNkcu4XDWOAI0GOdbVMG9Fu+elBYVQ0CluKsQTcWMwszC5Cj9X8DiahEqo5LsxGiIjDY sVsoTgiMdIsC8fmDX4v6RUWgvlX/ydbIHJCrx2LlUKMjmZmqdY4fZ3uUTmFxcCyTmBtB uzFDTBpdOLc5nEtN5V5PI4uTn7G346QNWt6Z5rOMqHN0T4cR0+Y5OJ4rCJjbT5zgMQGa yM+olX2P+3myr9rRzvRSv6keblrmE8gxGvAUxPT7NmgCSLTJXAwzpp0boh7FgTlfa17w 7Tyg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20221208; t=1687903236; x=1690495236; h=content-transfer-encoding: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=BytBNMmr1xYFAqyAevXl8+Ou2nvzs51PmHnlM4+2t9M=; b=VnqUPSFpUr9bD6YvYN4uyn6jGvxrbXqJdAxIHv8rr3BR9SrQpTdkzqvZVPE0UgeS8z 0bxUAYCmCxZ801PhWfnd5fv8OhcEs+yQZ+Wi+xE9bNL+6DwIIeFXGO2zdiwWSnetbOg7 J7V2wwXayHkXIng/V4OK+a6NZRGA6N6ObyGtzgXzkAcRuGx8cH+3LO0GEFU0rQkWba3u Tsn+/6oN5KdZ9isqgfmutAjpGBbiayZjFHCyHfPtYB+VVnBdYVAOLWzyhZwLfZ4siWGn nXgm3mtvC0v8yCl+Pq3/y1Dl0y3lnB2+ZbzWskHhizJlFfcFjIlIV/6j+cp/tLsVDzH+ B6qA== X-Gm-Message-State: AC+VfDwcuOYr7f6gDXhE8H1zGj1sDvvTrTAfxTn3GrV0EuwdTLoylHzo GhOzyKLs/8F4PZaRwdwqPePfGu/Ku65y8YR93fU= X-Google-Smtp-Source: ACHHUZ42TcQjRbkGJFDhVONksQoxqEvetzKj3bifjgsY5waRRirqyXfY3oih6odXLE5GeVate+mrKg== X-Received: by 2002:a05:6a21:6d93:b0:126:f64b:668e with SMTP id wl19-20020a056a216d9300b00126f64b668emr6726870pzb.5.1687903236283; Tue, 27 Jun 2023 15:00:36 -0700 (PDT) Received: from [10.223.244.156] (54-240-196-191.amazon.com. [54.240.196.191]) by smtp.gmail.com with ESMTPSA id r16-20020a63fc50000000b0054fe6bae952sm6177082pgk.4.2023.06.27.15.00.35 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Tue, 27 Jun 2023 15:00:35 -0700 (PDT) Message-ID: <03a01fb1-e0be-24f2-eadb-8e1a4b4981bf@ardentperf.com> Date: Tue, 27 Jun 2023 15:00:35 -0700 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.15; rv:102.0) Gecko/20100101 Thunderbird/102.12.0 Subject: Re: typical active table count? Content-Language: en-US To: pgsql-general@lists.postgresql.org References: <0cc7b26e-3766-50c9-6212-4e2ed281b822@silentmedia.com> From: Jeremy Schneider In-Reply-To: Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On 6/27/23 12:08 PM, Ron wrote: > On 6/27/23 13:47, Jeremy Schneider wrote: >> On 6/27/23 9:32 AM, Ben Chobot wrote: >>> We certainly have databases where far more than 100 tables are updated >>> within a 10 second period. Is there a specific concern you have? >>> >> Thank Ben, not a concern but I'm trying to better understand how common >> this might be. And I think sharing general statistics about how people >> use PostgreSQL is a great help to the developers who build and maintain it. >> >> One really nice thing about PostgreSQL is that two quick copies of >> pg_stat_all_tables and you can easily see this sort of info. >> >> If you have a database where more than 100 tables are updated within a >> 10 second period - this seems really uncommon to me - I'm very curious >> about the workload. > > 100 tables updates just means /possibly complicated schema/, not > necessarily high volume. > > ... > > And honestly, 100 tables in 10 seconds is 10 tables/second.  If each > gets one insert, that's a laughably slow transaction rate.  (Unless of > course there's 85 indices per table, and foreign keys don't have > supporting indices.) I don't think the math actually works this way on highly concurrent systems. In fact, this morning I connected with a coworker who works on Amazon fulfillment center DBs and there was almost no difference in the number of tables with insert/update/delete regardless of whether you looked at a 10 second window or a 2 second window. I was also able to chat with another coworker at Amazon who got numbers from a couple of their PG databases, and connected w one person on slack at a different company who passed along numbers, and got a few emails from Oracle folks. The numbers reported back to me ranged from 29 to over a hundred. Obviously there are also lots of small databases behind wordpress websites with much less activity, but I found this to be an interesting measure of some respectably busy systems. The original context was a conversation related to logical replication of DB changes. But then I got interested in the general question and topic - and someone on the Oracle side mentioned system tables which is a really good point that hadn't occurred to me yet. The original conversation was concerned with user tables and not system ones, but there would be a fair amount of ongoing system table activity too. Besides partitioning, another interesting dimension of the conversation has been thinking about different categories of workloads. For example: SaaS or multitenant applications with many copies of a similar schema, ISVs, ERPs, or large enterprise databases with lots of development history. All of these categories can easily ramp up the counts. I'm still interested in more data - if anyone reading this can grab a couple snapshots of pg_stat_all_tables and report back numbers for a 10 second window and a 2 second window, that would be amazing! -Jeremy -- http://about.me/jeremy_schneider