pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Jeremy Schneider <schneider@ardentperf.com>
To: pgsql-general@lists.postgresql.org
Subject: Re: typical active table count?
Date: Tue, 27 Jun 2023 15:00:35 -0700
Message-ID: <03a01fb1-e0be-24f2-eadb-8e1a4b4981bf@ardentperf.com> (raw)
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>

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






view thread (8+ messages)  latest in thread

Message-ID: <03a01fb1-e0be-24f2-eadb-8e1a4b4981bf@ardentperf.com>
Permalink:  ../03a01fb1-e0be-24f2-eadb-8e1a4b4981bf@ardentperf.com/
Also on:    postgresql.org/message-id/03a01fb1-e0be-24f2-eadb-8e1a4b4981bf@ardentperf.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: schneider@ardentperf.com
  Subject: Re: typical active table count?
  In-Reply-To: <03a01fb1-e0be-24f2-eadb-8e1a4b4981bf@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> <b71a61b9-53aa-bc51-931e-b2a04ccf187b@gmail.com> <03a01fb1-e0be-24f2-eadb-8e1a4b4981bf@ardentperf.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