Can't compact toast tables

[blattersturm]

We have a table with a lot of wasted space in pg_toast storage (~80 GB out of 200 GB total data) and would like to be able to repack it, however specifying pg_toast schema and the pg_toast_oid table leads to the script not finding the table, and toast does not get compacted when we try to compact the original table.

Trying to force it to compact the toast table leads to some errors like the following:

SQL Error: ERROR:  cannot change TOAST relation "pg_toast_19982"
CONTEXT:  PL/pgSQL function pgcompact_clean_pages_842(text,text,integer,integer,integer) line 50 at FOR over EXECUTE statement

Sadly, we can't do a full offline vacuum as we do not have the ability to add any local storage or free up enough disk space for storing 120 GB of data temporarily - it'd be neat if this script were capable of compacting toast. 😕

[Melkij]

Hello This is a known limitation, postgresql does not allow to change toast tables directly.

I can suggest one method for your case:

• take a look on table schema and find which columns may be long and therefore they may use TOAST
• save primary key values SELECT id FROM tablename WHERE pg_column_size(long_column_name) > 800; (for example into temp table)
• VACUUM VERBOSE pg_toast.pg_toast_(your toast id)
• perform fake update on such rows UPDATE tablename SET long_column_name = long_column_name || '' WHERE id in (...) (assuming text type or something like that), so postgresql will think this is another toast value and write new value
• VACUUM VERBOSE pg_toast.pg_toast_(your toast id) again

So postgresql will write new TOAST values in free space near to begin of table. The tail of the table should become empty and empty pages may be cut off by vacuum. Same idea that uses pgcompacttable, but we not known which values are stored in the tail of table and which rows we need update.