MIME-Version: 1.0
References: <CAGRY4nz94+q_zVxj+dnk7zqm-McBz4mSza_wALKiw2==23MiGQ@mail.gmail.com>
In-Reply-To: <CAGRY4nz94+q_zVxj+dnk7zqm-McBz4mSza_wALKiw2==23MiGQ@mail.gmail.com>
From: Craig Ringer <craig.ringer@enterprisedb.com>
Date: Fri, 18 Jul 2025 14:10:43 +1200
Message-ID: <CAGRY4nxdkLz17cyF3oVii48V8-kHi+Bhk34GZAVKNHkrEU2dzw@mail.gmail.com>
Subject: Re: Should we document the cost of pg_database_size()? Alternatives?
To: pgsql-general@lists.postgresql.org
Cc: Gregory Bulloch <gregory.bulloch@enterprisedb.com>, 
	Shan Shan Zhao <shanshan.zhao@enterprisedb.com>
Content-Type: text/plain; charset="UTF-8"
Archived-At: <https://www.postgresql.org/message-id/CAGRY4nxdkLz17cyF3oVii48V8-kHi%2BBhk34GZAVKNHkrEU2dzw%40mail.gmail.com>
Precedence: bulk

On Fri, 18 Jul 2025 at 12:54, Craig Ringer
<craig.ringer@enterprisedb.com> wrote:
> [...]
> I recently ran into an issue where a monitoring component was calling
> pg_catalog.pg_database_size() at a ~15s interval on a large
> schema-sharded database. It took so long that the query was timing out
> before the next 15s scrape interval would start.

Fast-follow on this, because I thought to do a local experiment on performance.

I suspect that there's more at work with the reported issue than just
the cost of statting all the files, because even if I create a 100,000
table db with at least 2 indexes per table (and all the auxiliary
forks etc that will result in)

I created 100,000 tables to fake up a DB that looks like a
schema-sharded one, then tested timing on pg_database_size().

test=# select count(1) from pg_class;
 count
-------
 40496
(1 row)

and now

test=# select pg_database_size(current_database());
 pg_database_size
------------------
        660013571
(1 row)

Time: 75.614 ms

... it still takes hardly any time at all.

If I drop my disk cache on my workstation:

echo 3 > /proc/sys/vm/drop_caches

then repeating pg_database_size()  only takes 254 ms the first time,
then back to 74ms.

In other words, even if the DB has many millions of tables, there's no
way that pg_database_size() could reasonably take minutes to run and
contribute meaningfully to a timeout of a monitoring or scrape
process.

Not unless there's something (or several somethings) else badly wrong
on the DB instance - extreme inode cache thrashing, excessive block
device read-ahead, unreasonably high block device I/O latencies, etc.

I still think it's worth mentioning pg_database_size() needing to stat
every file in the docs, but it's clear there's more going on in the
particular case I'm seeing than that alone. I'll try to report back if
I learn anything interesting that explains the rest of the performance
issues.


FYI the crude method used to create the schema since I couldn't be
bothered scripting something sensible up was:

turn fsync off (never do this unless you're happy to completely
destroy all data in your postgres instance), set
max_locks_per_transaction=1000 and restart Pg

then

DO LANGUAGE plpgsql
$$
BEGIN FOR i IN 1..10000 LOOP
  EXECUTE format('CREATE TABLE %I(x serial primary key, y integer
unique)', 'padding_table_b_'||i);
  EXECUTE format('INSERT INTO %I(y) VALUES (0)', 'padding_table_b_'||i);
  IF i % 100 = 0 THEN RAISE NOTICE 'up to: %', i; END IF;
END LOOP;
END;
$$;


--
Craig Ringer
EnterpriseDB