From: | Ron <ronljohnsonjr(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: typical active table count? |
Date: | 2023-06-27 19:08:57 |
Message-ID: | b71a61b9-53aa-bc51-931e-b2a04ccf187b@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Adrian Klaver | 2023-06-27 19:10:30 | Re: typical active table count? |
Previous Message | Zahir Lalani | 2023-06-27 18:59:34 | regex failing |