Optimize partition deletion workflow in `deleteOldPartitions()`

[pashagolub]

The current implementation of the deleteOldPartitions() function directly drops old partitions using DROP TABLE. This approach locks the parent table, potentially causing contention and delays in high-concurrency environments.

Objective is refactor the deleteOldPartitions() function to use ALTER TABLE ... DETACH PARTITION CONCURRENTLY before dropping partitions. This will reduce locking on the parent table and improve efficiency in partition management.to

[f9n]

Hi there,

We're using pgwatch in a distributed architecture — with agents running independently on each VM and pushing metrics to a shared postgresql sink. In this setup, functions like deleteOldPartitions() and maintainUniqueSources() put noticeable pressure on the sink database, especially in terms of locking and contention under high concurrency.

I have a couple of suggestions/questions regarding this:

• Agent-Side Conditional Execution:
  Would it be possible to make functions like deleteOldPartitions() and maintainUniqueSources() conditional, based on whether the agent "owns" the unique source? Ideally, these tasks could be skipped on regular metric-collecting agents using something like a --admin-mode or --skip-maintenance-tasks flag. This would allow us to disable maintenance tasks on distributed agents and delegate them to a dedicated node.

• Centralized Admin Control / Subcommand Support:
  It would be really helpful to have these maintenance tasks exposed via dedicated subcommands in the pgwatch binary (e.g., pgwatch maintain, pgwatch cleanup). That way, we could run them from a centralized controller node or via a cronjob, completely decoupled from the metric ingestion agents.

Additionally — slightly off-topic, but related — in a distributed setup, we often have cases where agents (or the systems they monitor) are decommissioned, but their entries and metrics remain in the sink database indefinitely. It would be great to have a feature like pgwatch cleanup --stale-sources to remove dbname references that haven't ingested any data in, say, the last 7 days. This would align well with the idea of a dedicated admin/controller node that handles cleanup and maintenance.

Happy to move this to a separate discussion if you'd prefer, but since it relates to partition cleanup and maintenance, I wanted to mention it here first.

Really appreciate all the great work — thanks! ❤️

I apologize if there is something obvious I missed - I am still new to pgwatch. 😅

[pashagolub]

Hey @f9n. Thanks for your ideas!

Why are you using several pgwatch instances? The whole idea of pgwatch is the one rules the world! :)

[f9n]

Hi there,

We also wish a single pgwatch instance could act as a central observability agent and handle all our sources 😢 Unfortunately, we have a large pool — currently 52,406 unique dbname definitions. When using just one pgwatch instance, we encounter metric collection lags and can't view the desired data in real time. That’s why we decided to distribute the metric collection workload.

To avoid the overhead of centralized maintenance, we’ve deployed pgwatch as a distributed observability agent on all VMs, all pushing metrics to a centralized PostgreSQL sink cluster. Our goal is to manage the entire pool through a single set of dashboards (one grafana) and a central database.

That's why having the ability to run maintenance tasks externally — separate from the metric collection agents — would be incredibly helpful for us.

[pashagolub]

ok, I see.... The straightforward solution I see, is to start pgwatches with unrealistic hign retention period parameter and have some kind of scheduled job via pg_cron\pg_timetable.

Does it sound ok to try?

[f9n]

I agree that increasing the retention period could help — though it seems like that would mainly affect the deleteOldPartitions part. Our centralized sink cluster is still quite new (less than a week old), so we haven’t yet hit the point where partition deletion becomes a problem 😆 . But your approach does sound like it could solve that when the time comes. 🤟

Right now, the part that puts the most pressure on our sink cluster is actually the maintainUniqueSources function. The recursive query that runs every 24h is already affecting the health of the database quite noticeably. 😞

[pashagolub]

Let me think about it.... The easiest way would be to have some kind of meta data pointing to the last successful operation

[f9n]

Sure, no problem. For now, we’ve implemented a quick short-term solution on our side — we might have skipped a few things just to move fast 😔 fyi.

But if you prefer a different approach, we’d be more than happy to contribute based on your guidance.
Looking forward to hearing from you! ❤️

• https://github.com/f9n/pgwatch/releases/tag/v3.6.0%2Bty0.1.0

[pashagolub]

@f9n can you please check #861? It should now work fine in multi agent environment

[f9n]

I think this really solves the problem - great work! 🙌 I like your approach. We will try to test it in our stage environment as soon as possible.

Any chance to merge it to the master branch and have a new release soon?