On a production PostgreSQL server I am seeing intermittent enormous spikes in data traffic. Up to 400Mbit/s which is causing NETWORK IO waits which in turn cause query timeouts. How can I find out what queries are returning big result sets?
Postgresql lacks metrics for network traffic usage.
There was some patches for such metrics, but nothing related has been merged at the moment.
pg_stat_statements extension could track total number of rows retrieved or affected by the statement (
rows metric). Doesn't help if you are sending large text/json/bytea/etc in one row. But it will tell you the places in which many rows are transmitted.
PS: are you sure the bottleneck is the network IO on postgresql side? 400 Mbps is not too much, I have several gigabytes of upstream traffic on some of the database servers.