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 1qEE3o-0003b5-IV for pgsql-general@arkaria.postgresql.org; Tue, 27 Jun 2023 19:09:08 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1qEE3n-0008KP-6M for pgsql-general@arkaria.postgresql.org; Tue, 27 Jun 2023 19:09:07 +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 1qEE3m-0008Hm-N0 for pgsql-general@lists.postgresql.org; Tue, 27 Jun 2023 19:09:06 +0000 Received: from mail-yb1-xb2d.google.com ([2607:f8b0:4864:20::b2d]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1qEE3f-000miz-EV for pgsql-general@lists.postgresql.org; Tue, 27 Jun 2023 19:09:05 +0000 Received: by mail-yb1-xb2d.google.com with SMTP id 3f1490d57ef6-c2cf29195f8so1623586276.1 for ; Tue, 27 Jun 2023 12:08:59 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20221208; t=1687892938; x=1690484938; 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=GBttbh1lNDKhQ1Jul78A0Fvfvj8PktLfiIIIis6ydV0=; b=rAI11Plq1/CoU/pblH8JsntNUjkscuRUgSpJvA2vNtAHWfibv2zPO2QKvrS/TkQ8cO dfGDwzthVQM/LQ/AoG3CIWoduPLu1uT3uj1T1H4dj/8ckuKBdKOnu/hxi+rpzfA54mT7 0dQybQMc72S2F/wm2jlevx6pyb4FYVcBlJhPwO0AQ0dFZzLtLpxMrDe0f6gdI1njgIyF b3sq0MXGc5HoyyYHTjoenb7XpLEk0SHJpf+Nsa8LsBzBG4AenaGuMsEzJwCZCGPrpiNj DKQ4xdhG77aaTeayrEd8rhB+XbBIG/P6LpIkIyZS8topBa+AM1jhNPIRilA4Jj0+7t+a Ag5g== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20221208; t=1687892938; x=1690484938; 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=GBttbh1lNDKhQ1Jul78A0Fvfvj8PktLfiIIIis6ydV0=; b=Dy/BJHiawtUGP1iJsJGh24D5oyikRLZ4upa3reSD6w7YdvAbip8hQC5inz8gPXsEw1 RDxlUkCY0qTWnhhHblMI6y1ber8n9ZESJkEoGK0AbWRkqdM8onz8kVvPYFtlzrN/INgX RwQCTS5m3n8HRzpBiOuJx9Uw75S4Xc2oU26pD3N8vpXrbtxcpjGbRVVW56GDLaHyksxj iz3Xo88kn3ez7Tx31nS8Zh8+47KVetYeSwwfBVPBk6thOrh+/JNNQiOrtctE8ArSRO2N cX2bJdRBwWjJ+KMdnuf1An3UgCNH3PEDKgqQHXucsneaITHDs83s1MoHzMtuwZj0KS5e kWCg== X-Gm-Message-State: AC+VfDw6IPLcTP4H5I8hz1Jb/zFxBdiOCw8XzZR8dHKbYKf00RAUGPUs 5EMhs9tTY3BJS78rNK2du7PsxuTumUs= X-Google-Smtp-Source: ACHHUZ55DHwHSZE+HF4Kgw4RYcNu20QRxYgfC/EaZrYNq6IrjtAbq7tbugEGxJDDnQTNBd8sjFtRtg== X-Received: by 2002:a25:bc86:0:b0:c25:3076:ffdc with SMTP id e6-20020a25bc86000000b00c253076ffdcmr5213553ybk.1.1687892938210; Tue, 27 Jun 2023 12:08:58 -0700 (PDT) Received: from ?IPV6:2600:1700:bd2c:b810:ec3e:4010:ab89:6ec0? ([2600:1700:bd2c:b810:ec3e:4010:ab89:6ec0]) by smtp.googlemail.com with ESMTPSA id k81-20020a252454000000b00bad03cb9325sm1762521ybk.32.2023.06.27.12.08.57 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Tue, 27 Jun 2023 12:08:57 -0700 (PDT) Content-Type: multipart/alternative; boundary="------------xLjqmiUHgpoVUdhH61b1YscX" Message-ID: Date: Tue, 27 Jun 2023 14:08:57 -0500 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.11.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: Ron In-Reply-To: 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. --------------xLjqmiUHgpoVUdhH61b1YscX Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit 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. More important is the number of tables updated in a single transaction.  Are you updating (really /modifying/: inserts, updates, deletes) lots of rows in all 100 tables in a single transaction, or are multiple users performing one of 20 separate transactions, each modifying 5 tables?  Because that bakes a huge difference. 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.) > For example: > > 1) Is the overall total number of tables for this database in the > thousands, 10s of thousands or 100s of thousands? > > 2) How many CPUs or cores does the server have? > > 3) Are you using partitions and counting each one? What's the number if > you count each partitioned table as a single table? > > 4) Would you characterize this database as SaaS, ie. many copies of a > similar schema? Why not multiple databases with the same definition? > Or is it one very large schema of many different tables? -- Born in Arizona, moved to Babylonia. --------------xLjqmiUHgpoVUdhH61b1YscX Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit 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.

More important is the number of tables updated in a single transaction.  Are you updating (really modifying: inserts, updates, deletes) lots of rows in all 100 tables in a single transaction, or are multiple users performing one of 20 separate transactions, each modifying 5 tables?  Because that bakes a huge difference.

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.)

For example:

1) Is the overall total number of tables for this database in the
thousands, 10s of thousands or 100s of thousands?

2) How many CPUs or cores does the server have?

3) Are you using partitions and counting each one? What's the number if
you count each partitioned table as a single table?

4) Would you characterize this database as SaaS, ie. many copies of a
similar schema?

Why not multiple databases with the same definition?

  Or is it one very large schema of many different tables?


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