3

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?

Additional data:

  • The pg_toast table has ~900k live tuples
  • Dead tuples were at 5.2M this morning but at 1.7M now
  • The pg_table_size hasn't budged in a few hours for the pg_toast table, despite continued activity on the underlying table
Improve this question

2 Answers 2

Reset to default
2

If you want to check how bloated the table is, you should install the pgstattuple extension and execute pgstattuple() on the suspected table. If the table is large, as in your case, you can use pgstattuple_approx() to get an approximation.

Regarding your hypothesis, I don't think that Multi-AZ can explain your issue, as PostgreSQL Multi-AZ replication is synchronous. Network issues between master and standby would halt the activity on the master.

Other possible explanation might be:

  1. Autovacuum not being able to catch up with many deletes or updates. This can happen because of database load that is unrelated to that query. Look at your logs and CloudWatch metrics for clues
  2. If you have a regular read-replica (not Multi-AZ) then your hypothesis could explain what has happened. There is a CloudWatch metric that shows the replication lag.
Improve this answer
2
  • Thanks. I was afraid of something like that on replication. We don't have read replicas on this machine. What would I be looking for in the logs with regards to auto vacuum?
    – Felipe
    Commented Aug 21, 2020 at 15:16
  • @Felipe look for autovacuum log messages. pgbadger can help you make more sense of the logs.
    – Jonathan Jacobson
    Commented Aug 21, 2020 at 18:37
2

Apart from the possibility that autovacuum cannot keep up with the UPDATEs, it could also be that you had a long running transaction that kept autovacuum from cleaning up dead TOAST entries.

Improve this answer
4
  • I did look through our application for this but didn't find anywhere that would have caused this. It's possible there was some other issue that caused a long-running query. Is there a way to see these in the logs?
    – Felipe
    Commented Aug 21, 2020 at 15:13
  • A query alone wouldn't do it. There must be some data modification if the transaction. Without extensive logging, you cannot get information about the duration of transactions...
    – Laurenz Albe
    Commented Aug 21, 2020 at 16:29
  • Would there be some indication of autovacuum falling behind?
    – Felipe
    Commented Aug 21, 2020 at 17:16
  • Not really. It will complete normally, but it will fail to clean up dead tuples that the long running transaction might potentially still need. You could set idle_in_transaction_session_timeout to a safely high value to prevent runaway transactions.
    – Laurenz Albe
    Commented Aug 22, 2020 at 2:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.