Use receipts `event_stream_ordering` instead of joins (#17032)

Resurrecting https://github.com/matrix-org/synapse/pull/13918.

This should reduce IOPs incurred by joining to the events table to
lookup stream ordering, which happens in many receipt handling code
paths. Like the previous PR I believe sufficient time has passed between
the original migration in DB schema 72 and now to merge this as-is. It's
highly unlikely that both the migration is still ongoing AND (active)
users still have any receipts prior to that date.

In the unlikely event there is a receipt without a populated
`event_stream_ordering` synapse will behave just as it does now when
receipts exist for events that don't (yet): for push action calculation
the receipts are just ignored.

I've removed the validation on event IDs as this is already covered
here:

https://github.com/element-hq/synapse/blob/59ceabcb9798793cd4312fdbcced4e612aeda84d/synapse/handlers/receipts.py#L189-L192

1 files changed, 1 insertions, 0 deletions

diff --git a/changelog.d/17032.misc b/changelog.d/17032.misc
new file mode 100644
index 0000000000..b03f6f42e5
--- /dev/null
+++ b/changelog.d/17032.misc
@@ -0,0 +1 @@
+Use new receipts column to optimise receipt and push action SQL queries. Contributed by Nick @ Beeper (@fizzadar).