Reduce size of received_activity table

[Nutomic]

Requirements

• Is this a feature request? For questions or discussions use https://lemmy.ml/c/lemmy_support
• Did you check to see if this issue already exists?
• Is this only a feature request? Do not put multiple feature requests in one issue.
• Is this a backend issue? Use the lemmy-ui repo for UI / frontend issues.

Is your proposal related to a problem?

The received_activity table on lemmy.ml currently has 11 GB. The purpose of this table is to check whenever a new activity is received, to see if we received the same activity before. In that case it is rejected. If its a new activity, it is inserted to the table and processed. Rows are removed after a three month interval. This is really basic functionality and shouldnt take so much space.

The table is currently defined like this:

Column   |            Type             | Collation | Nullable |                    Default
-----------+-----------------------------+-----------+----------+-----------------------------------------------
id        | bigint                      |           | not null | nextval('received_activity_id_seq'::regclass)
ap_id     | text                        |           | not null |
published | timestamp without time zone |           | not null | now()

Here are a few ideas we can use to save space:

• Get rid of id column, its unnecessary
• Change published from full datetime with millisecond precision, store only date or unix timestamp (integer)
• ap_id never needs to be read, only checked if a given value exists. This is an ideal use case for bloom filters which are supported in postgres
• Further reduce the removal interval. If an activity is delivered multiple times, it is probably within minutes or hours of the first send, not months later. Additionally its probably not a big deal if the same activity is received twice, but this hasnt been tested.

cc @dessalines @phiresky

[dessalines]

The simplest is probably the best here, which would be to reduce the removal interval to maybe > 3 days?

Other than that, @dullbananas is doing some work to remove some of the pointless integer primary keys, but I really doubt that's taking up that much. The number of pointless rows is the issue.

[phiresky]

Receiving double events "normally" shouldn't happen at all anymore in 0.19 with the persistent queue (normal server restarts will not resend any activities, just crashes), and if it does it will only affect the most recent ~100 events max.

For non-lemmy AP instances, idk, but it's probably still in the range of minutes / hours.

If there's a bug or more likely someone manually modifies / deletes the federation_queue_state table it will resend all activities however long ago they happened. I see this as somewhat likely to happen but idk if we care.

The linked bloom PG index isn't really relevant for space saving since it's just an index on top of a table (so the data still needs to be in the table).