Convex database taking up 11 GB of space with few documents

[hikariatama]

Hi! Started using Convex not long ago. My project has quite a lot of document changes and has been running for about 4 months of not so active beta usage (<5 users).

For some reason, self-hosted Convex backend does not clear out old documents, which causes the database to grow uncontrollably fast...

 schemaname |     table_name      | total_size | table_size | index_size 
------------+---------------------+------------+------------+------------
 public     | documents           | 10061 MB   | 6437 MB    | 3625 MB
 public     | indexes             | 34 MB      | 12 MB      | 22 MB
 public     | persistence_globals | 232 kB     | 8192 bytes | 224 kB
 public     | leases              | 24 kB      | 8192 bytes | 16 kB
 public     | read_only           | 8192 bytes | 0 bytes    | 8192 bytes

With 11698730 total documents (revisions) and 499628 distinct ones it seems like a pretty big overhead.

I'm not here to say "Convex is bad", since I'm not really an expert on how it works internally.

If you have a guide on how to clear out those stale documents properly, I would appreciate it <3

[nipunn1313]

Convex uses a document log. Deletes are operated as soft deletes in the document log.
There is an asynchronous process which cleans up old documents, which you can tune with retention knobs (see DOCUMENT_RETENTION_DELAY) which defaults to 80 days.

Any workload that has to iterate over a whole table requires the document log to be within retention for the duration of the job. Example jobs are index backfill, schema validation, export, backups, etc. This is why for the cloud product, we keep this number fairly large by default.

This conversation is inspiring me to reduce the default for self-hosting. Maybe if we reduced it to a few days, that might be good enough - and cover a case like the one you described. For truly large deployments, that may not be enough, but large deployments would require tuning anyway.

For context, even though Postgres/Mysql/other databases are organized differently, they all have this fundamental concept of retention/compaction - where deletes are soft and cleanup happens later. Policy for cleanup can be configured in a variety of ways. Convex's policy is pretty simple as that's been good enough here.