[ please keep the list cc'd ] Jonathan Marks <jonathanaverymarks@xxxxxxxxx> writes: > Thanks for your quick reply. Here’s a bit more information: > 1) to measure the “size of the database” we run something like `select datname, pg_size_pretty(pg_database_size(datname)) from pg_database;` I’m not sure if this includes WAL size. > 2) I’ve tried measuring WAL size with `select sum(size) from pg_ls_waldir();` — this also doesn’t budge. > 3) Our current checkpoint_timeout is 600s with a checkpoint_completion_target of 0.9 — what does that suggest? Hmph. Your WAL-size query seems on point, and that pretty much destroys my idea about a WAL emission spike. pg_database_size() should include all regular and temporary tables/indexes in the named DB. It doesn't include WAL (but we've eliminated that), nor cluster-wide tables such as pg_database (but those seem pretty unlikely to be at issue), nor non-relation temporary files such as sort/hash temp space. At this point I think we have to focus our attention on what might be creating large temp files. I do not see anything in the GIN index code that could do that, especially not if you have fastupdate off. I wonder whether there is something about the particular bulk-insertion queries you're using that could result in large temp files --- which'd make the apparent correlation with GIN index use a mirage, but we're running out of other ideas. You could try enabling log_temp_files to see if there's anything to that. In the grasping-at-straws department: are you quite sure that the extra disk space consumption is PG's to begin with, rather than something outside the database entirely? regards, tom lane
