Differentiate maintenance queries from user generated queries

[steve-chavez]

Problem

When querying pg_stat_statements to debug some slow query generated by a user the results obtained will also contain our maintenance queries (generated by the schema cache and in-db config, at startup and reloading), which make it harder to pinpoint the particular query.

One workaround for this is filtering by the patterns of the queries. Like %with role_setting%, with %base_types%, etc; but doing this requires knowing PostgREST implementation details.

Notes

• After doing this, we should document the shape of the SQL queries should not be relied on as discussed on fix: Restore showing LISTEN pgrst in pg_stat_activity #4859 (comment)

[steve-chavez]

A cruder idea is to add a comment to all our maintenance queries. Much more simple in implementation.

[wolfgangwalther]

One idea would be reserving a connection of the pool for only maintenance queries and tagging it with a different application name.

AFAICT, application_name is not available in pg_stat_statements.

One workaround for this is filtering by the patterns of the queries. Like %with role_setting%, with %base_types%, etc; but doing this requires knowing PostgREST implementation details.

I don't see a problem with relying on these implementation details for ad-hoc queries like such a performance analysis. This is just basic SQL usage: You have a big resultset and you filter it down to what you need. I mean.. the user is trying to analyse the performance of a query that is run via PostgREST. So by definition they are into PostgREST internals / implementation details. It's fine to filter our whatever is in the database right now at this point in time.

The point about implementation details is that we should not guarantee them to be exactly the same across any version bump. So this is not stable enough to build a tool upon, which will repeatedly do the same analysis.

I don't see a need for the latter, yet?

[steve-chavez]

AFAICT, application_name is not available in pg_stat_statements.

Right, just removed that. The point stands. The maintenance queries are not something of interest when debugging user queries.

So this is not stable enough to build a tool upon, which will repeatedly do the same analysis.

I've already seen tooling that relies on the query shape of our maintenance queries and that's a problem because it leads to issues like #4857, causing us to break workflows while we evolve our queries.

Thinking more about it, this would be a good use case for query tags as previously discussed on #2506 (comment)

Just having a -- kind: admin for maintenance queries and -- kind: user would solve this.