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

From: Craig Ringer <[email-masked]>
Date: Fri, 18 Jul 2025 12:54:47 +1200

Hi all

(Long time!)

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.

The resulting filesystem metadata activity was also hammering the
inode cache etc, affecting normal operations.

================
pg_database_size() is expensive
================

The pg_database_size() function will stat every fork of every
relation, which is pretty expensive on a big DB with a lot of extents
and high relation counts. In my toy database:

test=# select pg_database_size('craig');
 pg_database_size
------------------
        338084643
(1 row)

# strace -c -p 1009203
[...running...]

test=# select pg_database_size('craig');
 pg_database_size
------------------
        338084643
(1 row)

output of strace is:

strace: Process 1009203 attached
^Cstrace: Process 1009203 detached
% time     seconds  usecs/call     calls    errors syscall
------ ----------- ----------- --------- --------- ----------------
 91.18    0.001148           2       393           newfstatat
  3.42    0.000043          14         3           sendto
  1.59    0.000020           6         3         1 openat
  1.35    0.000017           4         4           getdents64
  1.27    0.000016           8         2           close
  0.95    0.000012           6         2         1 recvfrom
  0.24    0.000003           1         2           fstat
  0.00    0.000000           0         1           epoll_wait
------ ----------- ----------- --------- --------- ----------------
100.00    0.001259           3       410         2 total

# find base/16385 | wc -l
394

so it's fstatat()ing (almost) every file including all forks, indexes,
extents, etc.

No surprise it was taking forever on a very complex DB.

================
Docs for pg_database_size() don't mention performance impact
================

When I checked the docs to verify my recollection that
pg_database_size() would stat() every relfilenode (all extents, all
forks including maps, etc) I was surprised to see the docs don't say
much at all about it:
https://www.postgresql.org/docs/current/functions-admin.html#FUNCTIONS-ADMIN-DBOBJECT

> pg_database_size ( oid ) → bigint
>
> Computes the total disk space used by the database with the specified name or OID. To use this function, you must have CONNECT privilege on the specified database (which is granted by default) or have privileges of the pg_read_all_stats role.

It strikes me that we should probably warn about the cost of this
function on large DBs in the docs.

================
Lower-impact alternatives?
================

Which leads to ... what's the alternative, then, if we should tell
users not to poll pg_database_size() from monitoring systems?

FS-based sizing isn't really enough
----------------

Asking users to monitor at the filesystem level works, kind-of, but
it'll lead to confusion due to WAL and temp files in simple installs.
To get decent results they will need to have a separate dedicated
volume for pg_wal. And which temp files are counted will differ; IIRC
pg_database_size() does not count extents created by an in-progress
REINDEX etc, but DOES count temp table sizes, for example. FS-based
monitoring will also include things like spilled pg_replslot spilled
reorder buffers, which can be considerable and aren't reasonably
considered part of the "database size" or included in
pg_database_size(). And of course it can see only the sum of all
database sizes on a multi-database postgres instance unless the user
has one volume per database using distinct tablespaces. So
filesystem-based monitoring is not really a proper replacement.

Using relpages from pg_class is closer but problematic
-----------------

Should the user aggregate relpages from pg_class, multiply by the page
size, and call it kind-of-good-enough?

That seems awfully crude, and user-unfriendly at that, though works in
trivial cases:

test=# select pg_size_pretty(pg_database_size('test'));
 pg_size_pretty
----------------
 309 MB
(1 row)

test=# select pg_size_pretty(sum(relpages)*(1024*8)) from pg_class;
 pg_size_pretty
----------------
 308 MB
(1 row)

However, it breaks down pretty easily with anything less trivial. For
one thing, relpages lags behind VACUUM, so quickly growing or changing
tables and temp tables won't generally be captured. E.g. run

CREATE TEMPORARY TABLE bar AS
SELECT repeat('foofoo',x) AS filler1, repeat('morefillerhere',x) AS filler2
FROM generate_series(1,10000) x;

... then pg_database_size() will reflect the temp table, but summing
relpages won't, even if you're running it in the session that created
the temp table. The relpages for bar is initially 0 until after
VACUUM.

For example, I got a pg_database_size() of 322MB, an initial relpages
sum based estimate of 308MB (unchanged from before temp table
creation) and a relpages sum estimate after VACUUM of 319MB. The
on-disk size of the temp table is 11MB.

Meanwhile the actual size of base/16385 is 323MB according to `du` so
... good enough.

But the overall size of the whole datadir is 1093MB at the moment due
to WAL. Excluding WAL it's 356, mostly due to other DBs in the same
instance's base/.

==============
Any other alternatives?
==============

I'm interested in any other options anyone might suggest on better
ways to track DB size.

Obviously there's going to be a trade-off between freshness and cost;
pg_database_size() is expensive because it's immediate and current,
whereas using relpages is inaccurate because it waits for vacuum.

Any suggestions for a middle ground?

If using relpages is considered good-enough, would anyone be
interested if I was to cook up a patch for a more user-friendly
interface like pg_database_size_cached()  to present it to users?

Whew. /novel.

--
Craig Ringer
EnterpriseDB

---

From: Craig Ringer <[email-masked]>
Date: Fri, 18 Jul 2025 14:10:43 +1200

On Fri, 18 Jul 2025 at 12:54, Craig Ringer
<[email-masked]> wrote:
> [...]

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

---

From: Ron Johnson <[email-masked]>
Date: Thu, 17 Jul 2025 23:34:09 -0400

On Thu, Jul 17, 2025 at 8:55 PM Craig Ringer <[email-masked]>
wrote:
[snip]

> FS-based sizing isn't really enough

Whether the filesystem creeps above 90%, 95%, etc because of WAL files or
temp files or because of REINDEX or VACUUM FULL / CLUSTER / PG_REPACK is
irrelevant. it's the filesystem at 100% that will ruin your day,

Thus, we monitor filesystems, and don't monitor database size.

If the alarm does ever go off, *then* I check the cause.  (This isn't as
reactionary as it sounds, because I regularly check replication backlog,
for orphan slots, do REINDEXING and CLUSTER one table at a time, and don't
let junk onto the cluster disk.)

-- 
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!