High volume of queries for usage-based database providers

[alfawal]

Chancy appears to make a high volume of database queries, which can be costly on usage-based database providers (e.g. PlanetScale, Neon).

I ran a single-user, low-usage app for ~2 months, here are the metrics that matter to this subject:

• queries_by_total_time.csv
• queries_by_calls.csv

While this might not be a big issue, I was thinking of what would be a nice way to investigate and enhance the logic behind each.

[TkTech]

Interesting, never even considered usage-based billing. Chancy tends to make many very fast queries for reactivity, and they'll typically be sub-ms with low impact.

For the most part, the core and every individual plugin offers options to tweak the intervals at which they do work. For example since metrics is up there for # of requests, you can adjust sync_interval and collection_interval to drastically reduce that (at the cost of your dashboard lagging behind).

If your host supports reliable LISTEN/NOTIFY, you can reduce the polling interval for new jobs queue-by-queue by setting polling_interval on the queue to a value higher than the default of every 5 seconds - or if your workload just doesn't need to be that reactive you could increase it significantly. You can tweak queue_change_poll_interval to reduce how often it checks for new queues or changes to queues.

I think we already support everything needed to deal with this use case, just needs a tuning guide from someone familiar with that service. But any database-backed option is going to be making a decent number of queries by default to keep things moving quickly.