Full-text-search in Synapse 1.71.0rc1 may be prohibitively expensive (DB IO)

[DMRobertson]

From Matrix.org's monitoring:

In the "Toast table blocks read from disk/buffer" and "Toast index block read from buffer" graphs, there are intermittent blue spikes corresponding to the event_search table.

Correlated: peaks in "total txn time" due to search_rooms

And federation send PDU lag:

and event send time:

(Above graphs only showing the main process).

The event persister also saw pain at similar times:

It's a little tricky to interpret these, because the m.org database was updated this morning (2nd Nov 9.30 UTC) and there was some expensive background processing by postgres afterwards. (All times UTC in the graphs.)

Our suspicion is that the changes in #11635 are to blame. I reverted it, #13410 and #14311 on the hotfixes branch (37307a5) and deployed to matrix.org. We haven't seen the event persisters flare up since then... but it's not completely clear that the changed mentioned were the cause.

I think we should (regrettably) back out the changes on the release branch too, before a final 1.71.0 release.

[DMRobertson]

I should have added: in the slow query logs, we saw inserts into event search tables that took >=10 seconds.

E.g. 2022-11-02 00:40:57.355 UTC [matrix event_persister1] LOG:  duration: 10687.079 ms  statement: 
                    INSERT INTO event_search

[H-Shay]

Are the inserts slow due to poor indexing, or what else would cause such a slow insert?

[DMRobertson]

Consensus: go ahead with this but keep an eye out for performance.

[DMRobertson]

#14402 should fix some pain related to SELECTS related to event searches, which might explain some of this pain.