xmtp
TOAST table bloat due to batch deletions preventing PostgreSQL vacuum cleanup
Description: We currently depend on manual database table pruning based on business logic. This process deletes certain rows in batches rather than truncating or removing contiguous data ranges.
We have observed that this deletion pattern leaves non-contiguous gaps our gateway TOAST table. In PostgreSQL, these gaps prevent VACUUM (including autovacuum) from fully reclaiming and reusing storage space efficiently. As a result, our biggest data tables accumulate bloat over time, impacting both disk usage and performance.
Impact:
- Increased table size and index bloat over time.
- Longer query execution times due to larger indexes and unnecessary heap scans.
- Higher I/O load for both reads and writes.
- Potentially more frequent autovacuum runs, increasing DB CPU usage.
Proposed Actions / Ideas:
- Revisit pruning logic to delete in large contiguous ranges where possible.
- Schedule periodic VACUUM FULL or pg_repack jobs for affected tables.
- Consider partitioning large tables to allow DROP/TRUNCATE operations for old partitions.
- Evaluate whether pruning can be replaced by retention-based partition drops.
we should have more data once we migrate V3 tables. Ping @fbac. I am interested in the final V3->V4 data size.
@mkysel the dev key_packages and welcome_messages tables are almost fully migrated, so we can resume this investigation.
Post partitioning, this should become easier as we should be able to drop entire partitions eventually.