I'm trying to diagnose a sudden jump in disk usage in one of my databases (running 11.4). Typically the database grows by ~1 GB per day, but recently in the span of 24h it grew by nearly 100 GB.
Looking at various statistics, I concluded the growth came from a particular TOAST table. The table is used for a
BYTEA column which stores an encrypted blob of a couple of MBs. There are ~1000 rows, but the TOAST table takes up 400 GB.
These rows are updated frequently for a few days after they're first created and then aren't touched again.
The DB is a multi-AZ RDS deployment, so my best hypothesis is that replication fell behind, causing a bunch tuples to be kept alive and making the TOAST table suddenly grow.
Is that a sensible hypothesis? How can I confirm if it's the case? What are possible workarounds to avoid this hitting us again?
pg_toasttable has ~900k live tuples
- Dead tuples were at 5.2M this morning but at 1.7M now
pg_table_sizehasn't budged in a few hours for the
pg_toasttable, despite continued activity on the underlying table