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

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 <pavel.stehule@gmail.com> wrote:
> 
> Hi
> 
> so 25. 1. 2025 v 12:23 odesílatel Frits Hoogland <frits.hoogland@gmail.com <mailto:frits.hoogland@gmail.com>> 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:
>> 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?
> 
> 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
>> 
>> 
>> 
>>