Huge database queries from POST /_matrix/client/v3/keys/query

[richvdh]

We frequently see a rapid sequence of calls to POST /_matrix/client/v3/keys/query from a single device (usually Element/iOS, afaict from eyeballing the logs).

Each request typically contain queries for a large number of user ids (like, tens of thousands), of which many/most can be on the local server. We then translate that into a single SELECT query on devices and e2e_device_keys_json with a huge WHERE clause (here). This can then consume vast amounts of disk space on the database server, because it logs the query thousands of times (it creates many temporary files, and the query is logged each time).

There are several easy improvements to be made here:

• Firstly, the query should be split up into much smaller batches.
• Secondly, we should use an IN query rather than a huge list of (user_id = foo) OR (user_id = bar). This is more complicated than normal because we need to query on user_id and device_id, however I believe that postgres at least supports IN for tuples. It's more complicated still because we may have a mix of queries, some which match only user_id and some which match both user_id and device_id, so we may need to split the query list in two.
• I don't really know why we query against devices at all. AFAICT the only thing that we pull out of it is the display_name, which is not in the spec for POST /_matrix/client/v3/keys/query (though it is mentioned in the examples and currently returned in practice. Still, I don't think we should return other peoples' device display names here.) Related: Device names are returned over federation by /keys/query even if allow_device_name_lookup_over_federation is false #13114, which discusses this further.

[richvdh]

Related: #12890

[richvdh]

no no, it's definitely not tolerable.