element-hq / element-hq/synapse

aggregation_keys created before the size was limited can break sql indexes via dumps

Open
#17,156 4 comments 0 reactions 0 assignees View on GitHub
A-Database O-Uncommon S-Tolerable T-Defect
Dominant language
Python
Stars
4.6k
Forks
600
Avg merge
5d 22h
Merged PRs (30d)
51

Description

### Description

When dumping and reimporting, postgres does a new index. these are size limited. At some point someone sent an event with ascii art in aggregation_key which landed in my public.event_relations table. This is 3497 chars long in size.

As a result this happens on any dump + import:

```
ERROR: index row size 2920 exceeds btree version 4 maximum 2704 for index "event_relations_relates"
DETAIL: Index row references tuple (9835,12) in relation "event_relations".
TIP: Values larger than 1/3 of a buffer page cannot be indexed.
Consider a function index of an MD5 hash of the value, or use full text indexing.
```

### Steps to reproduce

- have a super long string in your db.

### Homeserver

matrix.midnightthoughts.space

### Synapse Version

v1.106.0

### Installation Method

Docker (matrixdotorg/synapse)

### Database

Postgres 15

### Workers

Single process

### Platform

Kubernetes with a pg cluster

### Configuration

_No response_

### Relevant log output

```shell
This happened on import of a pgdump:

ERROR: index row size 2920 exceeds btree version 4 maximum 2704 for index "event_relations_relates"
DETAIL: Index row references tuple (9835,12) in relation "event_relations".
TIP: Values larger than 1/3 of a buffer page cannot be indexed.
Consider a function index of an MD5 hash of the value, or use full text indexing.
```
```

### Anything else that would be useful to know?

It would be a good idea to have a migration removing faulty long rows as it will break indexes which might in the best case lower performance and in the worst case break backups in ways where the dump has to manually changed, reimported, the row manually deleted and the index recreated for it to work.

A query how I found it was `select event_id, relates_to_id, relation_type, aggregation_key, length(aggregation_key) from public.event_relations where length(aggregation_key) >= 2000 ORDER BY length(aggregation_key) DESC LIMIT 10;`

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.