Streams tables are never cleared out

[richvdh]

A number of 'stream' tables are appended-to, but are never cleared out. Such tables include:

• cache_invalidation_stream_by_instance (fixed by Remove old rows from the cache_invalidation_stream_by_instance table automatically. (This table is not used when Synapse is configured to use SQLite.) #15868)
• current_state_delta_stream
• presence_stream (does get cleaned out; has been ever since its creation in Rewrite presence for performance. #582)
• device_inbox (not really a 'stream' table actually, so a separate problem)
• device_federation_outbox
• local_group_updates (removed in Drop tables used for groups/communities. #12967)
• push_rules_stream
• ex_outlier_stream
• public_room_list_stream (now removed)

[richvdh]

(we should probably have a job to clear these out, but the difficulty is figuring out how old is "old")

[richvdh]

A better alternative to figuring out how to clear them out is to get rid of them: #13456

[realtyem]

You may scratch presence_stream off that list above. I dug far enough into the history to see that it is (at the very least) self-cleaned as of Oct 23rd, 2019 (in PR #6231 ) at which point the history becomes more difficult to easily trace. And that self cleaning persists today.

(If you are still finding evidence of this still existing though, please let me know. I'm terribad at using SQL)

[richvdh]

You may scratch presence_stream off that list above. I dug far enough into the history to see that it is (at the very least) self-cleaned as of Oct 23rd, 2019 (in PR #6231 ) at which point the history becomes more difficult to easily trace. And that self cleaning persists today.

thanks, updated.

[realtyem]

My terribad at SQL still stands. It appears that the cleaning on the presence_stream is only removing old rows that contain a specific user that is being updated. So it is cluttered with old offline states. I suppose this is better than nothing, but still not what I suspected. My apologizes. I have no idea how to fix this, and touching the database is where I won't go. Perhaps after #15868 goes through, I can copy/mutate that.