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

Jonathan Marks <jonathanaverymarks(at)gmail(dot)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 (grabbing that 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 across many 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