Search code examples
postgresqlvacuumautovacuum

pg_toast table not reduced in size after autovacuum

I'm on Postgresql 9.3.

I have a monstrous pg_toast table, pg_toast_675632, taking up 27 GB.

Unless I am misunderstanding, this pg_toast_675632 is so large because I had a large json column in the data table that the pg_toast_675632 is related to, my_table. So I massively simplified the json in my_table (i.e from json with 100 elements down to 2-5 elements). However, after autovacuum the pg_toast_675632 is still 27 GB. Is this because autovacuum doesn't return reclaimed space to disk? See the 3rd paragraph of 23.1.2 at this link.

  1. So based on the documentation at the above link do I need to manually run VACUUM (FULL) pg_toast_675632?

I know that VACUUM (FULL) takes a long time and will lock the pg_toast_675632 from writes for the duration.

  1. What are the risks of VACUUM FULL pg_toast_675632? Would this be equivalent to locking my_table since that's what pg_toast-675632 is related to?
Solution

  • Correct autovacuum won't reclaim space.

    You'd have to run VACUUM (FULL) on my_table, that will also free the space in the TOAST table.

    The downside is the ACCESS EXCLUSIVE lock that will prevent all concurrent activity while VACUUM (FULL) runs.

    As superuser you can run VACUUM on a TOAST table directly, but I don't see the advantage.