public inbox for [email protected]
help / color / mirror / Atom feedFrom: Ron Johnson <[email protected]>
To: pgsql-general <[email protected]>
Subject: Re: Should we document the cost of pg_database_size()? Alternatives?
Date: Thu, 17 Jul 2025 23:34:09 -0400
Message-ID: <CANzqJaBHUaF5=5ebMxZrWJbDnrdCBD7LPW_azHNFQhS90UR4YQ@mail.gmail.com> (raw)
In-Reply-To: <CAGRY4nz94+q_zVxj+dnk7zqm-McBz4mSza_wALKiw2==23MiGQ@mail.gmail.com>
References: <CAGRY4nz94+q_zVxj+dnk7zqm-McBz4mSza_wALKiw2==23MiGQ@mail.gmail.com>
On Thu, Jul 17, 2025 at 8:55 PM Craig Ringer <[email protected]>
wrote:
[snip]
>
> 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.
>
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!
view thread (3+ messages)
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 using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected]
Subject: Re: Should we document the cost of pg_database_size()? Alternatives?
In-Reply-To: <CANzqJaBHUaF5=5ebMxZrWJbDnrdCBD7LPW_azHNFQhS90UR4YQ@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox