pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Ron <ronljohnsonjr@gmail.com>
To: pgsql-general@lists.postgresql.org
Subject: Re: typical active table count?
Date: Tue, 27 Jun 2023 14:08:57 -0500
Message-ID: <b71a61b9-53aa-bc51-931e-b2a04ccf187b@gmail.com> (raw)
In-Reply-To: <d9095fde-ad79-76f9-5030-f2195aee34ed@ardentperf.com>
References: <CA+fnDAbDN0sMTVnCrKAMuu65dyHhqXqp+hKKoO83V6LA-Bos+A@mail.gmail.com>
	<0cc7b26e-3766-50c9-6212-4e2ed281b822@silentmedia.com>
	<d9095fde-ad79-76f9-5030-f2195aee34ed@ardentperf.com>

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.

view thread (8+ messages)  latest in thread

Message-ID: <b71a61b9-53aa-bc51-931e-b2a04ccf187b@gmail.com>
Permalink:  ../b71a61b9-53aa-bc51-931e-b2a04ccf187b@gmail.com/
Also on:    postgresql.org/message-id/b71a61b9-53aa-bc51-931e-b2a04ccf187b@gmail.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-general@postgresql.org
  Cc: ronljohnsonjr@gmail.com
  Subject: Re: typical active table count?
  In-Reply-To: <b71a61b9-53aa-bc51-931e-b2a04ccf187b@gmail.com>
  References: <CA+fnDAbDN0sMTVnCrKAMuu65dyHhqXqp+hKKoO83V6LA-Bos+A@mail.gmail.com> <0cc7b26e-3766-50c9-6212-4e2ed281b822@silentmedia.com> <d9095fde-ad79-76f9-5030-f2195aee34ed@ardentperf.com> <b71a61b9-53aa-bc51-931e-b2a04ccf187b@gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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