Allow using `pg_repack` on `state_groups_state` by adding a primary key or UNIQUE constraint

[PeterCxy]

Description:

It would be nice if pg_repack could be used on the state_groups_state table, which often has quite a lot of bloat after purging rooms / history. pg_repack is similar to VACUUM FULL but it can be run online without taking an exclusive lock on the tables.

pg_repack has the following requirement on the table it is run against:

Target table must have a PRIMARY KEY, or at least a UNIQUE total index on a NOT NULL column.

Currently the state_groups_state table does not have a primary key or UNIQUE NOT NULL keys. event_id seems like a great candidate for this, though I'm not sure if setting a TEXT value as primary key would have any performance implications.

EDIT: it seems that none of the columns in the table is actually unique. To allow the table to be used by pg_repack, we might need to add an auto-incrementing separate primary key.

[richvdh]

does pg_repack support UNIQUE constraints which span multiple columns?

the existing state_groups_state_type_idx (on (state_group, type, state_key)) should be a unique key, but for some reason was not created as such.

[PeterCxy]

I'm not sure whether it works with multi-column constraints, but it seems that the decision is made based on the PostgreSQL table's pkid (one of the oids). See: https://github.com/reorg/pg_repack/blob/master/bin/pg_repack.c#L899.

[richvdh]

what does that mean? we don't need a unique key after all?

[PeterCxy]

@richvdh Seems like you only have pkid when there is a unique non-null constraint, but I'm not so familiar with psql internals and not sure if pkid will be non-zero with a multi-column unique constraint.

[ilmari]

@richvdh @PeterCxy pg_repack just requires any unique constraint on a set of non-nullable columns. Whether it's a primary key or or not, or one or more columns doesn't matter, it just has to be able to uniquely identify each row.

[ilmari]

For the record, I successfully repacked state_groups_state with the following commands:

synapse=# create unique index concurrently state_groups_state_type_unique_idx on state_groups_state (state_group, type, state_key);
CREATE INDEX
Time: 149062.622 ms (02:29.063)
synapse=# drop index state_groups_state_type_idx ; 
DROP INDEX
Time: 106.284 ms

and:

# sudo -u postgres pg_repack -d synapse -t state_groups_state -j2
NOTICE: Setting up workers.conns
INFO: repacking table "public.state_groups_state"
LOG: Initial worker 0 to build index: CREATE INDEX index_720890 ON repack.table_231013 USING btree (state_group)
LOG: Initial worker 1 to build index: CREATE UNIQUE INDEX index_1051414 ON repack.table_231013 USING btree (state_group, type, state_key)
LOG: Command finished in worker 0: CREATE INDEX index_720890 ON repack.table_231013 USING btree (state_group)
LOG: Command finished in worker 1: CREATE UNIQUE INDEX index_1051414 ON repack.table_231013 USING btree (state_group, type, state_key)