1

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

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.