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.steh...@gmail.com> wrote: > > Hi > > so 25. 1. 2025 v 12:23 odesÃlatel Frits Hoogland <frits.hoogl...@gmail.com > <mailto:frits.hoogl...@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 >> >> >> >>
