Option to report query cost with results via a NoticeResponse

[BenHutchisonSeek]

Problem: Databases exposed to the internet via intermediate services can be vulnerable to "getting DOSed", ie accidentally, or even deliberately, having all their compute or IO capacity consumed by a small number of client(s) issuing a lot of expensive operations.

Rate limiting by client ID or IP address is a typical way to defend against such scenarios. In the case of database queries however, they can differ hugely in cost, so limiting purely on the number of requests may be insufficient.

A key step in rationing access to a shared database would be the ability to report on how much a given query cost (or even an estimate of that).

Desired Feature: A database-wide setting to enable cost data to be reported alongside query results, by emitting a NoticeResponse containing the cost number as structured text or JSON, in eg the same units used by the internal cost optimizer.

Alternatives:

• Use monitoring to understand the cost profile of the queries emitted by clients. This will work but it requires a human operator in the loop so is very high latency. If someone starts DOSing a database with queries, it may be minutes or hours to detect and address the situation, and its pretty costly to need a DB admin to

• Rate limit based on number of DB operations and assume their costs are roughly equivalent. The problem is some applications have user configurable filters, page sizes, date ranges, geographical filters and the like that affect the cost of a query.

Jira issue: CRDB-4437

[andreimatei]

We have a number of efforts in this area. However, doing anything with NoticeResponses is not something we've considered before. I'm very skeptical that reporting info over this channel would be useful, unless you tell me otherwise. How would you even get these reports? The client drivers generally discard these packages. And even if you do manage to get the reports, what exactly would an app do with them?

We're trying improve the statements page that we already have in the Admin UI to include more information on the cost of queries. There's also various ideas for allowing users to configure limits per query / per transaction / per client application, but nothing very concrete yet. Separately, @ajwerner is trying to make CRDB behave better under "DOS". Like, don't crash and ideally maintain good throughput. cc @awoods187

[BenHutchisonSeek]

So the Postgres JDBC driver exposes NoticeResponse as a SQLWarning rather than discarding it

What would I do with it? parse it back into an number in the servicer layer and add it to the relevant client's rate limit.

[asubiotto]

cc @otan

[otan]

We support notices now but would like clarification of requirements. I thought we were floating supporting something like this in future but didn't realise there was a task. Cc @awoods187

[github-actions[bot]]

We have marked this issue as stale because it has been inactive for 18 months. If this issue is still relevant, removing the stale label or adding a comment will keep it active. Otherwise, we'll close it in 10 days to keep the issue queue tidy. Thank you for your contribution to CockroachDB!

[github-actions[bot]]

We have marked this issue as stale because it has been inactive for 18 months. If this issue is still relevant, removing the stale label or adding a comment will keep it active. Otherwise, we'll close it in 10 days to keep the issue queue tidy. Thank you for your contribution to CockroachDB!