Postgresql – Pgbouncer server parameter

I find these multiple closings with age=0 worrying

No, this whole log looks fine and tends to show that pooling is working.

These entries with age=0 being tagged as LOG C, they concern communication between a client and pgBouncer. age=0 just means that the client used the connection for less than 1 second, which is consistent with the dates with milliseconds appearing in that log.

A line 1, a client connects to pgBouncer.

At line 2, there's a first connection made with a new postgres backend.

At line 3, the client quits.

At line 4, a new client arrives and no new connection is made with a backend, which implies that the previous one is reused as expected.

At line 8, a second server connection (LOG S) is made with a new postgres backend, which is necessary since there are two concurrent clients (see sucessful login attempts on the two previous LOG C lines within the same second).

Then the clients quit, and there's no mention of connections to postgres backends being closed, as expected again. They should be kept open until they reach server_lifetime or server_idle_timeout when idle.

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