Log large queries with PGbouncer

Question

Is there a way to ask pgbouncer to log any query that generates a result set larger than, say, 5MB?

Answer 1

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