Skip to main content

Access PgBouncer statistics for Aiven for PostgreSQL®

PgBouncer is used at Aiven as a connection pooler to lower the performance impact of opening new connections to Aiven for PostgreSQL®.

Verify your password encryption method

If you use PGBouncer connection pooling, verify your password encryption method compatibility to ensure successful connections. You may need to migrate to SCRAM-SHA-256 to maintain compatibility as the MD5 password encryption will be deprecated in PostgreSQL 19.

After connecting to PgBouncer, you can display statistics available from PgBouncer, such as:

  • total_xact_count
  • total_query_count
  • total_received
  • total_sent
  • total_xact_time
  • total_query_time
  • total_wait_time
  • avg_xact_count
  • avg_query_count
  • avg_recv
  • avg_sent
  • avg_xact_time
  • avg_query_time
  • avg_wait_time
note

You have the read-only access to PgBouncer statistics since PgBouncer pools are automatically managed by Aiven.

Get PgBouncer URI​

To get the PgBouncer URI, you can use either the Aiven Console or the Aiven CLI client.

PgBouncer URI in the console​

  1. Log in to the Aiven Console, and go to a desired organization, project, and service.
  2. Click Connection pools, and find a desired pool.
  3. Click Actions > Info > Primary Connection URI.

PgBouncer URI with the Aiven CLI​

Use jq to parse the JSON response. Execute the following command replacing SERVICE_NAME and PROJECT_NAME as needed:

avn service get SERVICE_NAME --project PROJECT_NAME --json | jq -r '.connection_info.pgbouncer'

Expect to receive an output similar to the following:

postgres://avnadmin:xxxxxxxxxxx@demo-pg-dev-advocates.aivencloud.com:13040/pgbouncer?sslmode=require

Connect to PgBouncer​

To connect to PgBouncer, use the extracted URI:

psql 'EXTRACTED_PGBOUNCER_URI'

Access statistics​

  1. Enable the expanded display by running:

    pgbouncer=# \x

  2. Show the statistics by running:

    pgbouncer=# SHOW STATS;

Depending on the load of your database, expect an output similar to the following:

database  | total_xact_count | total_query_count | total_received | total_sent | total_xact_time | total_query_time | total_wait_time | avg_xact_count | avg_query_count | avg_recv | avg_sent | avg_xact_time | avg_query_time | avg_wait_time
----------+------------------+-------------------+----------------+------------+-----------------+------------------+-----------------+----------------+-----------------+----------+----------+---------------+----------------+---------------
pgbouncer |                1 |                 1 |              0 |          0 |               0 |                0 |               0 |              0 |               0 |        0 |        0 |             0 |              0 |             0
(1 row)
tip

Run SHOW HELP to see all pgbouncer commands.