Subject: Any risk or overhead considerations for frequently executing queries against catalog tables?

From: Frits Hoogland <[email-masked]>
Date: Sat, 25 Jan 2025 12:23:32 +0100

For monitoring database behaviour and trying to build an history of activity, if I would create an application that creates a single connection and execute something like:
select * from pg_stat_activity;
select * from pg_stat_database;
select * from pg_stat_bgwriter;
select * from pg_stat_wal;
select * from pg_settings;
select * from pg_database;
For which the query is prepared, and execute that every 1 second, would there be any realistic danger or overhead that should be considered?
My thinking is that the data for these catalogs are all in shared memory and when executed serially and do not cause any significant resources to be taken?

Thanks,

Frits Hoogland

---

From: Pavel Stehule <[email-masked]>
Date: Sat, 25 Jan 2025 14:32:36 +0100

Hi

so 25. 1. 2025 v 12:23 odesílatel Frits Hoogland <[email-masked]>
napsal:

> For monitoring database behaviour and trying to build an history of
> activity, if I would create an application that creates a single connection
> and execute something like:
> For which the query is prepared, and execute that every 1 second, would
> there be any realistic danger or overhead that should be considered?
> My thinking is that the data for these catalogs are all in shared memory
> and when executed serially and do not cause any significant resources to be
> taken?
>

The queries to all tables excluding pg_database every 1 sec will have
probably zero impact to performance.

I am not sure about pg_database - it is a very important table, and your
query can block operations that need exclusive lock to this table. So
theoretically, there can be some impact to performance.

Regards

Pavel

> *Frits Hoogland*

---

From: Frits Hoogland <[email-masked]>
Date: Sat, 25 Jan 2025 17:59:55 +0100

Thank you Pavel, that is really useful. I can imagine other people thinking about getting fine grained data from postgres might wonder the same as I do about this.
And really from a computer's perspective I would say that once a second isn't really a high frequency?
If I time the amount of time that these queries take, it's around 20ms (local connection), so there is a relative long time of all the objects including pg_database are not actively queried.

I git grepped the sourcecode, it seems that there is a rowexclusive lock for pg_database manipulation in case of addition, removal and change of a database in dbcommands.c, but I do think your reasoning is based on the columns datfrozenxid and datminmxid?

There is a lock for updating the frozenxid and mxid for a database in (vacuum.c:LockDatabaseFrozenIds, ExclusiveLock), but it seems a select should play nice with that?

btw, it's interesting to see that both datfrozenxid and datminmxid are in place updated, with no read consistency provided.

Frits Hoogland

> On 25 Jan 2025, at 14:32, Pavel Stehule <[email-masked]> wrote:
> 
> Hi
> 
> so 25. 1. 2025 v 12:23 odesílatel Frits Hoogland <[email-masked] <mailto:[email-masked]>> napsal:
> The queries to all tables excluding pg_database every 1 sec will have probably zero impact to performance.
> 
> I am not sure about pg_database - it is a very important table, and your query can block operations that need exclusive lock to this table. So theoretically, there can be some impact to performance.
> 
> Regards
> 
> Pavel
>  
>> 
>> Thanks,
>> 
>> Frits Hoogland
>> 
>> 
>> 
>>

---

From: Pavel Stehule <[email-masked]>
Date: Sat, 25 Jan 2025 19:18:49 +0100

Hi

so 25. 1. 2025 v 18:00 odesílatel Frits Hoogland <[email-masked]>
napsal:

> Thank you Pavel, that is really useful. I can imagine other people
> thinking about getting fine grained data from postgres might wonder the
> same as I do about this.

I usually work with minute sampling and usually it is good enough
(statistics are cumulative, so you can lose the timestamp, but you never
lose data.

Only when we try to investigate some special case, then I use second
sampling. When you investigate lock issues, then seconds are too much

Regards

Pavel

> If I time the amount of time that these queries take, it's around 20ms
> (local connection), so there is a relative long time of all the objects
> including pg_database are not actively queried.
>
> I git grepped the sourcecode, it seems that there is a rowexclusive lock
> for pg_database manipulation in case of addition, removal and change of a
> database in dbcommands.c, but I do think your reasoning is based on the
> columns datfrozenxid and datminmxid?
>
> There is a lock for updating the frozenxid and mxid for a database in
> (vacuum.c:LockDatabaseFrozenIds, ExclusiveLock), but it seems a select
> should play nice with that?
>
> btw, it's interesting to see that both datfrozenxid and datminmxid are in
> place updated, with no read consistency provided.
>
> *Frits Hoogland*
>
>
>
>

---

From: Frits Hoogland <[email-masked]>
Date: Sat, 25 Jan 2025 21:01:00 +0100

I am looking at whether sampling key database catalog information per second would have any drawback whatsoever.
I think you're saying that you think isn't the case, except maybe for pg_database, and I figure that is because of the frozen and multi xact fields per database.

If the database client application is too unpredictable to know what SQL it will produce, then having runtime data available at that granularity, so it can be reasonably constructed what is going on is very convenient and allows tremendous insight. It would also allow usage of the waitevents to spot any weird behavior, such as short-lived peaks. (pg_stat_statements can do that on a busy database, for example).
And if there is no known drawback, if such a low interval can be organized: why not? I am not saying you are doing it wrong, this is about trying to figure out what are the borders of what would be technically possible without unreasonably affecting the database, a thought experiment.

If course the gathered data needs to be organized so that you don't swamp in it, and it shouldn't lead to the monitoring data swamping the system, either in memory or on disk, but that is a given.

Why would per second be too much for locks? Is there overhead to select from pg_locks, or pg_blocking_pids()?

Again, please realise I am happy and appreciative of the time you take, I am toying with the above described idea.

Frits Hoogland

> On 25 Jan 2025, at 19:18, Pavel Stehule <[email-masked]> wrote:
> 
> Hi
> 
> so 25. 1. 2025 v 18:00 odesílatel Frits Hoogland <[email-masked] <mailto:[email-masked]>> napsal:
> I usually work with minute sampling and usually it is good enough (statistics are cumulative, so you can lose the timestamp, but you never lose data.
> 
> Only when we try to investigate some special case, then I use second sampling. When you investigate lock issues, then seconds are too much
> 
> Regards
> 
> Pavel
>  
>>> On 25 Jan 2025, at 14:32, Pavel Stehule <[email-masked] <mailto:[email-masked]>> wrote:
>>> 
>>> Hi
>>>

---

From: peter plachta <[email-masked]>
Date: Sat, 25 Jan 2025 14:08:29 -0800

DataDog — which implements such metrics for Postgres - has ran into multiple issues doing this type of thing. You may be able to search their bugs / repo to see what they were. I just can’t remember them off hand, it’s been a while.

Sent from my iPhone

On Jan 25, 2025, at 12:01 PM, Frits Hoogland <[email-masked]> wrote:

Frits Hoogland

Hi

so 25. 1. 2025 v 18:00 odesílatel Frits Hoogland <[email-masked]> napsal:

Regards

Pavel

Frits Hoogland

Hi

so 25. 1. 2025 v 12:23 odesílatel Frits Hoogland <[email-masked]> napsal:

Regards

Pavel

Thanks,

Frits Hoogland

---

From: [email-masked]
Date: Sat, 25 Jan 2025 23:42:02 +0100

Thank you, I will look, very interesting!

One thing I found is that because certain statistics are provided after a query has run, measuring them in a fine grained way shows a peak for something that in reality is taking place over a period of time.

If anyone has a url of where this can be found, I would be very grateful!

Verstuurd vanaf mijn iPhone

Op 25 jan 2025 om 23:08 heeft peter plachta <[email-masked]> het volgende geschreven:

Sent from my iPhone

Frits Hoogland

Hi

Regards

Pavel

Frits Hoogland

Hi

Regards

Pavel

Thanks,

Frits Hoogland

---

From: Pavel Stehule <[email-masked]>
Date: Sun, 26 Jan 2025 07:43:24 +0100

so 25. 1. 2025 v 21:01 odesílatel Frits Hoogland <[email-masked]>
napsal:

> I am looking at whether sampling key database catalog information per
> second would have any drawback whatsoever.
> I think you're saying that you think isn't the case, except maybe for
> pg_database, and I figure that is because of the frozen and multi xact
> fields per database.
>
> If the database client application is too unpredictable to know what SQL
> it will produce, then having runtime data available at that granularity, so
> it can be reasonably constructed what is going on is very convenient and
> allows tremendous insight. It would also allow usage of the waitevents to
> spot any weird behavior, such as short-lived peaks. (pg_stat_statements can
> do that on a busy database, for example).
> And if there is no known drawback, if such a low interval can be
> organized: why not? I am not saying you are doing it wrong, this is about
> trying to figure out what are the borders of what would be technically
> possible without unreasonably affecting the database, a thought experiment.
>
> If course the gathered data needs to be organized so that you don't swamp
> in it, and it shouldn't lead to the monitoring data swamping the system,
> either in memory or on disk, but that is a given.
>

when you have a query about 10ms, then the lock 50ms is important overhead.

Surely queries to pg_locks are queries like any other - there is a lot of
overhead with planner, executor, and locking. Fortunately, it is almost CPU
related.

---

From: Jeremy Schneider <[email-masked]>
Date: Sun, 26 Jan 2025 09:56:47 -0800

> On Jan 25, 2025, at 2:42 PM, [email-masked] wrote:
> 

+1

I have also encountered this. I think most stats (pg_stat_statements, block hit/read counts, object access counts, etc) are all updated at query completion time. I think it’s related to the architecture of the statistics collector. The main thing I’ve seen change in real time is fields in pg_stat_activity (like wait events).

As a result, I’m not sure how useful per-second stats on the other views are. Mainly I think it will just tell you which second the query completed, and that’s easy to get from logging (db or app tier). stats from the other views tend to be most useful when normalized over minutes or hours.

I wrote out a Postgres observability wishlist two weeks ago and much of it would probably best be added to pg_stat_activity [1].

I wonder if we might eventually want the underlying pg_stat_activity function to have an argument to exclude pulling data that’s less frequently accessed, similar to how the pg_stat_statements function can exclude query text to be more efficient.

-Jeremy

1: https://ardentperf.com/2025/01/08/postgres-per-connection-statistics/