I'm using Postgres 9.6.1 on RDS, and seeing orders-of-magnitude higher write throughput than I expect to see. I'm seeing a WriteThroughput around 40,000,000 Bytes/Second. I've looked at the following to try to track down where it is coming from:
I installed pg_stat_statements and have been periodically running:
SELECT sum(shared_blks_dirtied) from pg_stat_statements
And it looks like my queries are only dirtying at most about 30 blocks per second. A block is 8kB, right? So that's only 240 kB per second. (I also checked temp blocks and local blocks and I'm barely dirtying them or writing them at all. I also confirmed that I'm not hitting the limit of unique statements that pg_stat_statements will track).
I'm also looking at my logs, and I see checkpoints running every 5 minutes about this size:
LOG: checkpoint complete: wrote 8538 buffers (0.4%); 0 transaction log file(s) added, 0 removed, 3 recycled; write=269.825 s, sync=0.021 s, total=269.921 s; sync files=2349, longest=0.010 s, average=0.000 s; distance=39599 kB, estimate=39599 kB
So that's approximately 40 MB / 5 minutes = 133 kB per second, which is the same order of magnitude I'm seeing in pg_stat_statements.
So I'm a little confused... am I doing the math wrong, or is there somewhere else I should be looking to see what's generating all that write throughput?
One other thing to mention: I have a lot of tables and schemas: approximately 25,000 schemas and 2 million total relations. (And I create / drop new schemas a couple times a minute generally). I've found that autovacuum can't keep up with this, even with very aggressive configuration, so I am manually vacuuming and analyzing tables based on my write patterns, and I've adjusted the autovacuum thresholds upwards so that in practice my manual vacuuming almost always gets there before autovacuum runs on it. I've confirmed that my manual vacuums do show up in pg_stat-statements, so I'd assume that any writes they generate are already accounted for in my above analysis, but maybe not?