Is there a way to ask pgbouncer to log any query that generates a result set larger than, say, 5MB?
1 Answer
In short - no
You can show stats;
to get the total_received
and total_sent
per database. Assuming you can check the stats before query and after, calculating the difference won't give you the query result size. Even in ideal env, when no other parallel sessions exist. Eg:
t=# copy (select id from t) to '/tmp/1';
COPY 1043482
t=# \! du -h /tmp/1
7.0M /tmp/1
checking received:
t=# select 40493353-40493297;
?column?
----------
56
(1 row)
hm, so the size of a query result is 7MB, but received bytes are 56. Ah! I saved result of query on server! So pgbouncer technically indeed did not receive large set of data, ok - do it to client:
t=# \copy (select objectid from pond_user) to '/tmp/1';
COPY 1043482
t=# select 40493442-40493353;
?column?
----------
89
(1 row)
Same story... Maybe lets check bytes sent?..
t# select (355612622-343149820)/(1024*1024);
?column?
----------
11
(1 row)
11MB... Not particularly precise match.
SO:
pgbouncer does not log the result set size, You can use show stats
assuming you can isolate sessions in time and your estimations are to get a very rough approximation for sizes.
also
People are using iptraf
or such for the same, Somebody logs long taking queries with log_min_duration_statement
and then repeat them with CREATE TABLE AS
to get the size, some even write patches for client.
even also
Analytic approach here leads to even more complications:
https://stackoverflow.com/questions/13570613/making-sense-of-postgres-row-sizes
Measure the size of a PostgreSQL table row
Although mycrosoft and mysql offers such approximation in cosy way