Heavy Disk Usage of Postgres-Database

[genofire]

Background information

• Dendrite version or git SHA: 0.8.5
• Monolith or Polylith?: Monolith
• SQLite3 or Postgres?: PostgreSQL
• Running in Docker?:
• go version:
• Client used (if applicable):

Description

• What is the problem: the database use 30GB (12GB more, after 2 days)
• Who is affected:
• How is this bug manifesting:
• When did this first appear:

dendrite_sum7=# SELECT nspname || '.' || relname AS "relation",
    pg_size_pretty(pg_total_relation_size(C.oid)) AS "total_size"
  FROM pg_class C
  LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
  WHERE nspname NOT IN ('pg_catalog', 'information_schema')
    AND C.relkind <> 'i'
    AND nspname !~ '^pg_toast'
  ORDER BY pg_total_relation_size(C.oid) DESC
  LIMIT 20;
                  relation                  | total_size 
--------------------------------------------+------------
 public.roomserver_state_block              | 11 GB
 public.syncapi_output_room_events          | 6917 MB
 public.roomserver_event_json               | 914 MB
 public.syncapi_current_room_state          | 636 MB
 public.roomserver_events                   | 287 MB
 public.roomserver_previous_events          | 234 MB
 public.syncapi_memberships                 | 131 MB
 public.syncapi_output_room_events_topology | 129 MB
 public.roomserver_state_snapshots          | 91 MB
 public.federationsender_joined_hosts       | 85 MB
 public.roomserver_event_state_keys         | 60 MB
 public.roomserver_membership               | 51 MB
 public.syncapi_presence                    | 38 MB
 public.keyserver_device_keys               | 34 MB
 public.keyserver_one_time_keys             | 32 MB
 public.federationsender_queue_edus         | 14 MB
 public.keyserver_cross_signing_sigs        | 8832 kB
 public.roomserver_redactions               | 5632 kB
 public.syncapi_backward_extremities        | 5488 kB
 public.pushserver_notifications            | 5200 kB
(20 rows)

[NickCao]

Running the same query on my servers shows an astonishing amount of notifications.

                  relation                  | total_size
--------------------------------------------+------------
 public.userapi_notifications               | 19 GB
 public.syncapi_output_room_events          | 1730 MB
 public.roomserver_event_json               | 1282 MB
 public.roomserver_events                   | 382 MB
 public.syncapi_output_room_events_topology | 321 MB
 public.roomserver_previous_events          | 304 MB
 public.roomserver_state_block              | 69 MB
 public.federationsender_queue_json         | 55 MB
 public.syncapi_current_room_state          | 54 MB
 public.federationsender_queue_edus         | 32 MB
 public.mediaapi_media_repository           | 25 MB
 public.keyserver_device_keys               | 20 MB
 public.roomserver_state_snapshots          | 19 MB
 public.account_data                        | 9824 kB
 public.syncapi_memberships                 | 9824 kB
 public.syncapi_invite_events               | 7792 kB
 public.roomserver_invites                  | 7392 kB
 public.roomserver_redactions               | 4864 kB
 public.keyserver_cross_signing_sigs        | 4312 kB
 public.syncapi_notification_data           | 4240 kB
(20 rows)