On Fri, 18 Jul 2025 at 12:54, Craig Ringer
<craig.rin...@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
