Re: Should we document the cost of pg_database_size()? Alternatives?

From: Craig Ringer <craig(dot)ringer(at)enterprisedb(dot)com>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Cc: Gregory Bulloch <gregory(dot)bulloch(at)enterprisedb(dot)com>, Shan Shan Zhao <shanshan(dot)zhao(at)enterprisedb(dot)com>
Subject: Re: Should we document the cost of pg_database_size()? Alternatives?
Date: 2025-07-18 02:10:43
Message-ID: CAGRY4nxdkLz17cyF3oVii48V8-kHi+Bhk34GZAVKNHkrEU2dzw@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-general

On Fri, 18 Jul 2025 at 12:54, Craig Ringer
<craig(dot)ringer(at)enterprisedb(dot)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

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Ron Johnson 2025-07-18 03:34:09 Re: Should we document the cost of pg_database_size()? Alternatives?
Previous Message Craig Ringer 2025-07-18 00:54:47 Should we document the cost of pg_database_size()? Alternatives?