maintaining "unique dbnames" is too costly

[markwort]

On a productive pgwatch2 setup using timescaledb, maintining the list of unique dbnames is too taxing.

Every pgwatch2 daemon (of which I have one for every DB instance here, 24 in total) runs UniqueDbnamesListingMaintainer every 24 hours.
This results in full scans of all metric tables, just to maintain the list of active dbnames.

Maybe there should be a way to ensure this maintaining is not done more often than necessary.

[pashagolub]

@kmoppel @kmoppel-cognite can you throw some light on this, please? What is the purpose of such a schedule?

[markwort]

I think this is only used to make sure that the drop-down menus in grafana dashboards, where you can select specific databases

1. shows new databases pretty soon when new metrics show up for the first time
2. doesn't show databases that don't have any metrics (i.e. the database has been stopped for some time and all metrics have been truncated due to retention time settings)

[kmoppel-cognite]

Hey, Yes Julian is almost correct - new databases are though added immediately to the listing on detection.

The proposed workaround should be a big improvement already I think - to make it 100% correct and resource friendly one should do some DDL on the "admin" schema and have some explicit lock structure and track last_modified.

[pashagolub]

I will try to address this issue in the next releases. Meanwhile your quick fix may give us some time. Thanks @kmoppel-cognite

[kmoppel-cognite]

@pashagolub Actually seems, for that 100% correct approach one could also use the existing admin.config table, so no DDL needed. But its probably "fine"-ish already for now