Re: Rapid disk usage spikes when updating large tables with GIN indexes

Jonathan Marks <jonathanaverymarks@gmail.com> writes:
> We turned on log_temp_files and since the last stats reset (about a week ago) we’re seeing 0 temp files altogether
(grabbingthat info from pg_stat_database). 

Hm.

> Another thread we found suggested pg_subtrans — this seems less likely because we’ve been able to replicate this
acrossmany different types of connections etc. but thought it might be a potential source. 

We're running out of other ideas, so maybe, but it's kind of hard to
credit large numbers of gigabytes going into pg_subtrans et al.
Still, you could easily adapt your WAL-size query to track the sizes
of other DB subdirectories and see if anything springs out.  Since
I'm really feeling a bit baffled at this point, I'd suggest watching
all of them:

pg_commit_ts/
pg_dynshmem/
pg_logical/mappings/
pg_logical/snapshots/
pg_multixact/members/
pg_multixact/offsets/
pg_logical/
pg_multixact/
pg_notify/
pg_replslot/
pg_serial/
pg_snapshots/
pg_stat/
pg_stat_tmp/
pg_subtrans/
pg_tblspc/
pg_twophase/
pg_wal/
pg_xact/

            regards, tom lane