The PostgreSQL stats tables in the stats schema expose runtime statistics collected by ProxySQL for all PostgreSQL traffic, connection pools, and user activity. These tables provide visibility into query performance, backend health, error rates, and resource usage for PostgreSQL deployments proxied through ProxySQL. They are the primary tool for monitoring and troubleshooting ProxySQL when operating in PostgreSQL mode.

Table of Contents

• stats_pgsql_client_host_cache
• stats_pgsql_commands_counters
• stats_pgsql_connection_pool
• stats_pgsql_errors
• stats_pgsql_free_connections
• stats_pgsql_global
• stats_pgsql_prepared_statements_info
• stats_pgsql_processlist
• stats_pgsql_query_digest
• stats_pgsql_query_rules
• stats_pgsql_users

stats_pgsql_client_host_cache

Table stats_pgsql_client_host_cache keeps records of all failed connection attempts performed by clients when the client error limit feature is enabled, i.e. when pgsql-client_host_cache_size is set to a value bigger than 0. Each entry tracks the client IP address, the number of consecutive errors originating from that address, and the timestamp of the most recent error.

CREATE TABLE stats_pgsql_client_host_cache (
    client_address VARCHAR NOT NULL,
    error_count INT NOT NULL,
    last_updated BIGINT NOT NULL
)

The fields have the following semantics:

• client_address: the client address from which the connection failure was detected.
• error_count: the total number of consecutive connection errors originated from the client address.
• last_updated: the time of the last connection error in microseconds.

Example:

Admin> SELECT * FROM stats_pgsql_client_host_cache;
+----------------+-------------+---------------+
| client_address | error_count | last_updated  |
+----------------+-------------+---------------+
| 10.200.1.5     | 3           | 104099712044  |
+----------------+-------------+---------------+
1 row in set (0.00 sec)

Related tables:

• pgsql-client_host_cache_size global variable controls whether this feature is enabled.
• stats_pgsql_errors — per-backend error tracking across users and databases

stats_pgsql_client_host_cache_reset

Querying the stats_pgsql_client_host_cache_reset table is equivalent to querying stats_pgsql_client_host_cache, with the only difference that the client host cache is cleared at the end of the SELECT statement.

stats_pgsql_commands_counters

Table stats_pgsql_commands_counters keeps records of all types of queries executed against PostgreSQL backends, and collects statistics based on their execution time, grouping them into latency buckets. This table is useful for understanding the distribution of query latencies across command types and identifying slow command categories.

CREATE TABLE stats_pgsql_commands_counters (
    Command VARCHAR NOT NULL PRIMARY KEY,
    Total_Time_us INT NOT NULL,
    Total_cnt INT NOT NULL,
    cnt_100us INT NOT NULL,
    cnt_500us INT NOT NULL,
    cnt_1ms INT NOT NULL,
    cnt_5ms INT NOT NULL,
    cnt_10ms INT NOT NULL,
    cnt_50ms INT NOT NULL,
    cnt_100ms INT NOT NULL,
    cnt_500ms INT NOT NULL,
    cnt_1s INT NOT NULL,
    cnt_5s INT NOT NULL,
    cnt_10s INT NOT NULL,
    cnt_INFs INT NOT NULL
)

The fields have the following semantics:

• Command: the type of SQL command that has been executed. Examples: SELECT, INSERT, UPDATE, DELETE, BEGIN, COMMIT, ROLLBACK, etc.
• Total_Time_us: the total time spent executing commands of that type, in microseconds.
• Total_cnt: the total number of commands of that type executed.
• cnt_100us, cnt_500us, ..., cnt_10s, cnt_INFs: the total number of commands of the given type which executed within the specified time limit and the previous one. For example, cnt_500us is the number of commands which executed within 500 microseconds but more than 100 microseconds because there is also a cnt_100us field. cnt_INFs is the number of commands whose execution exceeded 10 seconds.

Admin> SELECT * FROM stats_pgsql_commands_counters ORDER BY Total_cnt DESC LIMIT 1\G
*************************** 1. row ***************************
      Command: SELECT
Total_Time_us: 284507312048
    Total_cnt: 7812430
    cnt_100us: 823
    cnt_500us: 1948210
      cnt_1ms: 2204017
      cnt_5ms: 2196308
     cnt_10ms: 381204
     cnt_50ms: 540012
    cnt_100ms: 118743
    cnt_500ms: 158204
       cnt_1s: 14813
       cnt_5s: 18022
      cnt_10s: 3801
     cnt_INFs: 4273
1 row in set (0.01 sec)

Related tables:

• stats_pgsql_query_digest — per-query breakdown of execution time
• stats_pgsql_global — global query counters including Questions and Slow_queries

stats_pgsql_connection_pool

This table exports statistics on PostgreSQL backend servers. Servers are identified based on their hostgroup, address, and port. The information available covers connections, queries, and traffic. Each row represents a backend server within a hostgroup, providing an aggregate view of pool health and throughput for that server.

CREATE TABLE stats_pgsql_connection_pool (
    hostgroup INT,
    srv_host VARCHAR,
    srv_port INT,
    status VARCHAR,
    ConnUsed INT,
    ConnFree INT,
    ConnOK INT,
    ConnERR INT,
    MaxConnUsed INT,
    Queries INT,
    Bytes_data_sent INT,
    Bytes_data_recv INT,
    Latency_us INT
)

Each row represents a backend server within a hostgroup. The fields have the following semantics:

• hostgroup: the hostgroup in which the backend server belongs. Note that a single backend server can belong to more than one hostgroup.
• srv_host, srv_port: the TCP endpoint on which the PostgreSQL backend server is listening for connections.
• status: the status of the backend server. Can be ONLINE, SHUNNED, OFFLINE_SOFT, or OFFLINE_HARD. See the description of the pgsql_servers table for more details about what each status means.
• ConnUsed: how many connections are currently used by ProxySQL for sending queries to the backend server.
• ConnFree: how many connections are currently free. They are kept open to minimize the time cost of sending a query to the backend server.
• ConnOK: how many connections were established successfully.
• ConnERR: how many connections were not established successfully.
• MaxConnUsed: high water mark of connections used by ProxySQL for sending queries to the backend server.
• Queries: the number of queries routed towards this particular backend server.
• Bytes_data_sent: the amount of data sent to the backend. This does not include metadata (packet headers).
• Bytes_data_recv: the amount of data received from the backend. This does not include metadata (packet headers, OK/ERR packets, field descriptions, etc.).
• Latency_us: the current ping time in microseconds, as reported from the Monitor module.

Admin> SELECT hostgroup hg, srv_host, status, ConnUsed, ConnFree, ConnOK, ConnERR FROM stats_pgsql_connection_pool WHERE ConnUsed+ConnFree > 0 ORDER BY hg, srv_host;
+----+--------------------+--------+----------+----------+--------+---------+
| hg | srv_host           | status | ConnUsed | ConnFree | ConnOK | ConnERR |
+----+--------------------+--------+----------+----------+--------+---------+
| 10 | pg-primary-001     | ONLINE | 42       | 258      | 310    | 0       |
| 11 | pg-primary-001     | ONLINE | 0        | 1        | 1      | 0       |
| 11 | pg-replica-001     | ONLINE | 0        | 8        | 8      | 0       |
| 20 | pg-primary-002     | ONLINE | 7        | 93       | 101    | 1       |
| 21 | pg-replica-002     | ONLINE | 0        | 1        | 1      | 0       |
+----+--------------------+--------+----------+----------+--------+---------+
5 rows in set (0.00 sec)

Admin> SELECT hostgroup hg, srv_host, Queries, Bytes_data_sent, Bytes_data_recv, Latency_us FROM stats_pgsql_connection_pool WHERE ConnUsed+ConnFree > 0 ORDER BY hg, srv_host;
+----+--------------------+---------+-----------------+-----------------+------------+
| hg | srv_host           | Queries | Bytes_data_sent | Bytes_data_recv | Latency_us |
+----+--------------------+---------+-----------------+-----------------+------------+
| 10 | pg-primary-001     | 5831240 | 6421847392      | 98332148811     | 14231      |
| 11 | pg-primary-001     | 42      | 113422          | 1849            | 14231      |
| 11 | pg-replica-001     | 41003   | 105472188       | 3248671         | 87         |
| 20 | pg-primary-002     | 614823  | 782011449       | 187320148       | 83412      |
| 21 | pg-replica-002     | 5       | 4121            | 612             | 142        |
+----+--------------------+---------+-----------------+-----------------+------------+
5 rows in set (0.00 sec)

Related tables:

• pgsql_servers — defines the backend servers that appear in this table
• stats_pgsql_free_connections — detailed view of individual idle connections
• stats_pgsql_global — global connection pool counters

stats_pgsql_connection_pool_reset

Querying the stats_pgsql_connection_pool_reset table is equivalent to querying stats_pgsql_connection_pool, with the only difference that all statistics are reset to 0 at the end of the SELECT statement.

stats_pgsql_errors

This table tracks errors reported by the PostgreSQL backend servers during query execution. Servers are identified based on their hostgroup, address, and port. Each distinct combination of hostgroup, hostname, port, username, database, and SQL state code is tracked as a separate entry, allowing operators to identify which backend servers and user/database combinations are generating errors and how frequently.

CREATE TABLE stats_pgsql_errors (
    hostgroup INT NOT NULL,
    hostname VARCHAR NOT NULL,
    port INT NOT NULL,
    username VARCHAR NOT NULL,
    client_address VARCHAR NOT NULL,
    database VARCHAR NOT NULL,
    sqlstate VARCHAR NOT NULL,
    count_star INTEGER NOT NULL,
    first_seen INTEGER NOT NULL,
    last_seen INTEGER NOT NULL,
    last_error VARCHAR NOT NULL DEFAULT '',
    PRIMARY KEY (hostgroup, hostname, port, username, database, sqlstate)
)

The fields have the following semantics:

• hostgroup: the hostgroup in which the backend server belongs. Note that a single backend server can belong to more than one hostgroup.
• hostname, port: the TCP endpoint on which the PostgreSQL backend server is listening for connections.
• username: the username of the backend server user associated with the connection that generated the error.
• client_address: the frontend client address that was connected to ProxySQL when the error occurred.
• database: the database the query was targeting when the error was generated.
• sqlstate: the PostgreSQL SQLSTATE error code (a 5-character code per the SQL standard, e.g. 42P01 for undefined table, 28P01 for invalid password).
• count_star: the number of times this error was seen for the user/database/hostgroup/hostname/port/sqlstate combination since the last reset of PostgreSQL error statistics.
• first_seen: Unix timestamp when this entry was first recorded since the last reset of error statistics.
• last_seen: Unix timestamp when this entry was last seen.
• last_error: the exact error text for the last error of this entry.

Admin> SELECT * FROM stats_pgsql_errors LIMIT 2\G
*************************** 1. row ***************************
      hostgroup: 10
       hostname: pg-primary-001
           port: 5432
       username: app_user
 client_address: 10.0.1.50
       database: appdb
       sqlstate: 28P01
     count_star: 2
     first_seen: 1700000110
      last_seen: 1700000320
     last_error: password authentication failed for user "app_user"
*************************** 2. row ***************************
      hostgroup: 10
       hostname: pg-primary-001
           port: 5432
       username: app_user
 client_address: 10.0.1.51
       database: appdb
       sqlstate: 42P01
     count_star: 1
     first_seen: 1700000210
      last_seen: 1700000210
     last_error: relation "missing_table" does not exist
2 rows in set (0.00 sec)

Related tables:

• stats_pgsql_connection_pool — per-backend connection and query statistics
• pgsql_servers — defines the backend servers referenced in this table

stats_pgsql_errors_reset

Querying the stats_pgsql_errors_reset table is equivalent to querying stats_pgsql_errors, with the only difference that all statistics are reset to 0 at the end of the SELECT statement.

stats_pgsql_free_connections

This table provides information about free (idle) connections currently held in ProxySQL’s PostgreSQL connection pool. Each row represents an individual idle connection to a PostgreSQL backend server that is available for reuse. This table is useful for inspecting connection age, current session state, and how much traffic individual pooled connections have carried.

CREATE TABLE stats_pgsql_free_connections (
    fd INT NOT NULL,
    hostgroup INT NOT NULL,
    srv_host VARCHAR NOT NULL,
    srv_port INT NOT NULL,
    user VARCHAR NOT NULL,
    database VARCHAR,
    init_connect VARCHAR,
    time_zone VARCHAR,
    sql_mode VARCHAR,
    idle_ms INT,
    statistics VARCHAR,
    pgsql_info VARCHAR
)

Each row represents an idle connection to a PostgreSQL backend server within a hostgroup. The fields have the following semantics:

• fd: the file descriptor of ProxySQL’s connection to the backend server.
• hostgroup: the hostgroup in which the backend server belongs. A single backend server can belong to more than one hostgroup.
• srv_host, srv_port: the TCP endpoint of the PostgreSQL backend server to which the connection is made.
• user: the username used for the backend server connection.
• database: the database the connection is currently targeting.
• init_connect: initialization commands sent when the connection was created, if any.
• time_zone: the time zone that was specified on connection creation, if any.
• sql_mode: the current SQL mode of the connection.
• idle_ms: how long, in milliseconds, since the connection was last used.
• statistics: a JSON object containing statistics related to the connection, including information about how much query traffic the connection has handled, how old the connection is, and how much usage it has received as part of the connection pool.
• pgsql_info: additional metadata about the connection to the PostgreSQL backend, such as the server version and character encoding in use.

Admin> SELECT fd, hostgroup, srv_host, srv_port, user, database, idle_ms FROM stats_pgsql_free_connections LIMIT 3;
+----+-----------+----------------+----------+----------+--------+---------+
| fd | hostgroup | srv_host       | srv_port | user     | database | idle_ms |
+----+-----------+----------------+----------+----------+--------+---------+
| 18 | 10        | pg-primary-001 | 5432     | app_user | appdb  | 245     |
| 21 | 10        | pg-primary-001 | 5432     | app_user | appdb  | 734     |
| 29 | 11        | pg-replica-001 | 5432     | app_user | appdb  | 1103    |
+----+-----------+----------------+----------+----------+--------+---------+
3 rows in set (0.00 sec)

Related tables:

• stats_pgsql_connection_pool — aggregate connection pool statistics per backend server
• pgsql_servers — defines the backend servers referenced in this table

stats_pgsql_global

One of the most important tables in the stats schema is stats_pgsql_global, which exports counters related to various ProxySQL internals for PostgreSQL traffic. The table uses a key-value structure where each row is a named statistic variable and its current value.

CREATE TABLE stats_pgsql_global (
    Variable_Name VARCHAR NOT NULL PRIMARY KEY,
    Variable_Value VARCHAR NOT NULL
)

Each row represents a global statistic at the proxy level related to PostgreSQL traffic, including:

• Key Memory Usage
• Prepared Statements
• Processing Time
• Global Connections
• Threads / Workers
• Connection Pooling
• Transactions
• SQL Statements

The same output is available using the SHOW PGSQL STATUS command. Example:

Admin> SELECT * FROM stats_pgsql_global LIMIT 5;
+------------------------------+----------------+
| Variable_Name                | Variable_Value |
+------------------------------+----------------+
| ProxySQL_Uptime              | 48712          |
| Active_Transactions          | 0              |
| Client_Connections_aborted   | 0              |
| Client_Connections_connected | 6              |
| Client_Connections_created   | 6              |
+------------------------------+----------------+
5 rows in set (0.00 sec)

Variable description:

• ProxySQL_Uptime: the total uptime of ProxySQL in seconds.
• Active_Transactions: a count of how many client connections are currently processing a transaction.
• Client_Connections_aborted: client connections that failed or were closed improperly.
• Client_Connections_connected: client connections that are currently connected.
• Client_Connections_created: total number of client connections created.
• Client_Connections_non_idle: number of client connections currently handled by the main worker threads.
• Server_Connections_aborted: backend connections that failed or were closed improperly.
• Server_Connections_connected: backend connections that are currently connected.
• Server_Connections_created: total number of backend connections created.
• Server_Connections_delayed: number of backend connections that were delayed.
• Servers_table_version: internal version counter for the servers table.
• Backend_query_time_nsec: time spent making network calls to communicate with the backends.
• Queries_backends_bytes_recv: total bytes received from backends.
• Queries_backends_bytes_sent: total bytes sent to backends.
• Queries_frontends_bytes_recv: total bytes received from frontends (clients).
• Queries_frontends_bytes_sent: total bytes sent to frontends (clients).
• backend_lagging_during_query: number of queries where the backend was detected as lagging.
• backend_offline_during_query: number of queries where the backend went offline during execution.
• pgsql_backend_buffers_bytes: buffers related to backend connections.
• pgsql_frontend_buffers_bytes: buffers related to frontend connections (read/write buffers and other queues).
• pgsql_killed_backend_connections: number of backend connections that were killed.
• pgsql_killed_backend_queries: number of backend queries that were killed.
• client_host_error_killed_connections: number of connections killed due to client host error limits.
• max_connect_timeouts: number of connection attempts that timed out.
• new_req_conns_count: number of new connection requests made to backends.
• generated_error_packets: number of error packets generated by ProxySQL (not the backend).
• pgsql_session_internal_bytes: memory used by ProxySQL to handle PostgreSQL sessions.
• PgSQL_Thread_Workers: number of PostgreSQL thread workers.
• PgSQL_Monitor_Workers: number of monitor threads for PostgreSQL backends.
• PgSQL_Monitor_Workers_Aux: number of auxiliary monitor worker threads.
• PgSQL_Monitor_Workers_Started: total number of monitor worker threads that have been started.
• PgSQL_Monitor_connect_check_ERR: number of failed monitor connect checks.
• PgSQL_Monitor_connect_check_OK: number of successful monitor connect checks.
• PgSQL_Monitor_ping_check_ERR: number of failed monitor ping checks.
• PgSQL_Monitor_ping_check_OK: number of successful monitor ping checks.
• ConnPool_get_conn_success: number of requests where a connection was already available in the connection pool.
• ConnPool_get_conn_failure: number of requests where a connection was not available in the connection pool and either a new connection had to be created or no backend was available.
• ConnPool_get_conn_immediate: number of connections that a thread obtained from its own local connection pool cache.
• Questions: the total number of client requests and statements executed.
• Slow_queries: the total number of queries with an execution time greater than the configured slow query threshold in milliseconds.
• Access_Denied_Max_Connections: number of connections denied due to the max_connections limit.
• Access_Denied_Max_User_Connections: number of connections denied due to the per-user max_connections limit.
• Access_Denied_Wrong_Password: number of connections denied due to wrong password.
• Stmt_Client_Active_Total: the total number of prepared statements that are in use by clients.
• Stmt_Client_Active_Unique: the number of unique prepared statements currently in use by clients.
• Stmt_Server_Active_Total: the total number of prepared statements currently available across all backend connections.
• Stmt_Server_Active_Unique: the number of unique prepared statements currently available across all backend connections.
• Stmt_Cached: the number of global prepared statements for which ProxySQL has metadata.
• SQLite3_memory_bytes: memory used by SQLite for internal storage.
• ConnPool_memory_bytes: memory used by the connection pool to store connection metadata.
• Query_Processor_time_nsec: the time spent inside the Query Processor to determine what action needs to be taken with the query.

Related tables:

• stats_pgsql_connection_pool — per-backend connection statistics
• stats_pgsql_commands_counters — per-command-type execution statistics

stats_pgsql_prepared_statements_info

Because of multiplexing, a client may prepare a prepared statement (PS) in a backend connection but that connection may not be free when the same client wants to execute the PS. It is also possible that multiple clients prepare the same PS. ProxySQL addresses these issues by maintaining a global prepared statement cache:

• For every unique PS, a global global_stmt_id is generated and its metadata is stored internally.
• Each client preparing a PS gets a statement ID local to that client, mapped to the global statement ID.
• On every backend connection where a PS is prepared, the statement ID returned by the backend is mapped to the global statement ID.

CREATE TABLE stats_pgsql_prepared_statements_info (
    global_stmt_id INT NOT NULL,
    database VARCHAR NOT NULL,
    username VARCHAR NOT NULL,
    digest VARCHAR NOT NULL,
    ref_count_client INT NOT NULL,
    ref_count_server INT NOT NULL,
    num_param_types INT NOT NULL,
    query VARCHAR NOT NULL
)

The fields have the following semantics:

• global_stmt_id: the global statement ID used across client connections and backend connections.
• database: the database the prepared statement is associated with.
• username: the username associated with the prepared statement.
• digest: the digest hash of the query used for the prepared statement.
• ref_count_client: the number of client connections currently holding a reference to this prepared statement.
• ref_count_server: the number of backend connections currently holding a reference to this prepared statement.
• num_param_types: the number of parameter type OIDs declared in the prepared statement (PostgreSQL typed parameters).
• query: the full query text used for the prepared statement.

Admin> SELECT global_stmt_id, database, username, ref_count_client, ref_count_server, num_param_types, query FROM stats_pgsql_prepared_statements_info\G
*************************** 1. row ***************************
global_stmt_id: 1
      database: appdb
      username: app_user
ref_count_client: 5
ref_count_server: 3
num_param_types: 1
         query: SELECT c FROM sbtest1 WHERE id=$1
*************************** 2. row ***************************
global_stmt_id: 2
      database: appdb
      username: app_user
ref_count_client: 2
ref_count_server: 1
num_param_types: 2
         query: INSERT INTO events (type, data) VALUES ($1, $2)
2 rows in set (0.00 sec)

Related tables:

• stats_pgsql_global — Stmt_Cached, Stmt_Client_Active_Total, and related counters
• stats_pgsql_query_digest — query digests including prepared statement queries

stats_pgsql_processlist

The stats_pgsql_processlist table provides a real-time snapshot of what ProxySQL PostgreSQL sessions are currently doing. Each row represents an active client session, showing the routing decision, the backend server being used (if any), the current command, and detailed extended information. Because this is a snapshot, there is no guarantee that the same sessions will be active moments later.

CREATE TABLE stats_pgsql_processlist (
    ThreadID INT NOT NULL,
    SessionID INTEGER PRIMARY KEY,
    user VARCHAR,
    database VARCHAR,
    cli_host VARCHAR,
    cli_port INT,
    hostgroup INT,
    l_srv_host VARCHAR,
    l_srv_port INT,
    srv_host VARCHAR,
    srv_port INT,
    backend_pid INT,
    backend_state VARCHAR,
    command VARCHAR,
    time_ms INT NOT NULL,
    info VARCHAR,
    status_flags INT,
    extended_info VARCHAR
)

The fields have the following semantics:

• ThreadID: the internal ID of the thread within ProxySQL. This is a 0-based numbering of the threads.
• SessionID: the internal global identifier of the ProxySQL session (client frontend connection). It is useful to uniquely identify a session for monitoring or killing it.
• user: the PostgreSQL username with which the client connected to ProxySQL.
• database: the database currently selected for this session.
• cli_host, cli_port: the TCP endpoint of the connection between the client and ProxySQL.
• hostgroup: the current destination hostgroup. If a query is being processed, this is the hostgroup towards which the query was or will be routed, based on the pgsql_users default hostgroup or query rules.
• l_srv_host, l_srv_port: the local TCP endpoint of the connection between ProxySQL and the backend PostgreSQL server.
• srv_host, srv_port: the TCP endpoint on which the backend PostgreSQL server is listening for connections.
• backend_pid: the PostgreSQL server-side process ID (PID) of the backend connection, as assigned by PostgreSQL.
• backend_state: the current state of the backend PostgreSQL server process.
• command: the type of PostgreSQL protocol command being executed (e.g. Query, Parse, Execute, Bind).
• time_ms: the time in milliseconds for which the session has been in the specified command state.
• info: the actual query being executed.
• status_flags: internal status flags for the session.
• extended_info: a JSON object holding additional detailed information about the session and its backend connections.

Admin> SELECT ThreadID, SessionID, user, database, cli_host, cli_port, hostgroup, srv_host, srv_port, backend_pid, command, time_ms, info FROM stats_pgsql_processlist;
+----------+-----------+----------+--------+-----------+----------+-----------+----------------+----------+-------------+---------+---------+----------------------------------------------+
| ThreadID | SessionID | user     | database | cli_host | cli_port | hostgroup | srv_host       | srv_port | backend_pid | command | time_ms | info                                         |
+----------+-----------+----------+--------+-----------+----------+-----------+----------------+----------+-------------+---------+---------+----------------------------------------------+
| 2        | 1         | app_user | appdb  | 10.0.1.10 | 52411    | 10        | pg-primary-001 | 5432     | 18204       | Query   | 0       | SELECT c FROM sbtest1 WHERE id=198898        |
| 0        | 2         | app_user | appdb  | 10.0.1.11 | 52412    | 10        | pg-primary-001 | 5432     | 18205       | Query   | 0       | SELECT c FROM sbtest3 WHERE id=182586        |
| 3        | 3         | app_user | appdb  | 10.0.1.12 | 52413    | 11        | pg-replica-001 | 5432     | 9831        | Query   | 1       | SELECT id, val FROM orders WHERE status=$1   |
+----------+-----------+----------+--------+-----------+----------+-----------+----------------+----------+-------------+---------+---------+----------------------------------------------+
3 rows in set (0.02 sec)

Note: ProxySQL also supports the command SHOW PGSQL PROCESSLIST to return information related to current PostgreSQL sessions.

Related tables:

• stats_pgsql_query_digest — historical query statistics
• pgsql_users — defines the users that appear in this table

stats_pgsql_query_digest

The stats_pgsql_query_digest table provides aggregated statistics about queries that have been processed by ProxySQL for PostgreSQL backends. It is useful for identifying queries that can be routed to read replicas, rewritten, or cached, and for understanding which query patterns consume the most time or are executed most frequently. Queries are grouped by their normalized form (digest) per hostgroup, database, username, and client address.

CREATE TABLE stats_pgsql_query_digest (
    hostgroup INT,
    database VARCHAR NOT NULL,
    username VARCHAR NOT NULL,
    client_address VARCHAR NOT NULL,
    digest VARCHAR NOT NULL,
    digest_text VARCHAR NOT NULL,
    count_star INTEGER NOT NULL,
    first_seen INTEGER NOT NULL,
    last_seen INTEGER NOT NULL,
    sum_time INTEGER NOT NULL,
    min_time INTEGER NOT NULL,
    max_time INTEGER NOT NULL,
    sum_rows_affected INTEGER NOT NULL,
    sum_rows_sent INTEGER NOT NULL,
    PRIMARY KEY(hostgroup, database, username, client_address, digest)
)

Each row represents a class of queries sharing the same normalized pattern routed through ProxySQL. The fields have the following semantics:

• hostgroup: the hostgroup where the query was sent.
• database: the database that is currently being queried.
• username: the username with which the PostgreSQL client connected to ProxySQL.
• client_address: the address of the client, populated when query digest hostname tracking is enabled.
• digest: a hexadecimal hash that uniquely represents a query with its parameters stripped.
• digest_text: the actual normalized query text with parameters replaced by placeholders (e.g. $1, $2 in PostgreSQL parameterized queries, or ? for generic placeholders).
• count_star: the total number of times the query has been executed (with different parameter values).
• first_seen: Unix timestamp, the first moment when the query was routed through the proxy.
• last_seen: Unix timestamp, the last moment when the query was routed through the proxy.
• sum_time: the total time in microseconds spent executing queries of this type. This is useful for identifying where the most time is spent in the application workload.
• min_time, max_time: the range of observed execution durations. min_time is the minimum execution time and max_time is the maximum execution time, both in microseconds.
• sum_rows_affected: the total number of rows affected across all executions.
• sum_rows_sent: the total number of rows returned to clients.

Admin> SELECT hostgroup, database, username, digest, digest_text, count_star, sum_time, min_time, max_time FROM stats_pgsql_query_digest ORDER BY count_star DESC LIMIT 2\G
*************************** 1. row ***************************
   hostgroup: 11
    database: appdb
    username: app_user
      digest: 0x6A3D9B1F2C4E8710
 digest_text: SELECT c FROM sbtest1 WHERE id=?
  count_star: 6241803
    sum_time: 5914822031
    min_time: 812
    max_time: 98340
*************************** 2. row ***************************
   hostgroup: 11
    database: appdb
    username: app_user
      digest: 0x9C2E7F4A1B0D5382
 digest_text: SELECT c FROM sbtest3 WHERE id=?
  count_star: 6108274
    sum_time: 5741039482
    min_time: 904
    max_time: 112047
2 rows in set (0.01 sec)

Related tables:

• stats_pgsql_query_rules — shows which query rules matched traffic
• stats_pgsql_commands_counters — aggregated counters by SQL command type

stats_pgsql_query_digest_reset

Table stats_pgsql_query_digest_reset is identical to stats_pgsql_query_digest, but reading from stats_pgsql_query_digest_reset causes all statistics to be reset to 0 at the end of the SELECT.

stats_pgsql_query_rules

The stats_pgsql_query_rules table exports how many times PostgreSQL query rules have been matched by incoming traffic. Each row corresponds to a single rule defined in pgsql_query_rules, identified by its rule_id, and records the cumulative number of queries that matched that rule. This table is useful for verifying that query routing rules are working as intended and for identifying rules that are never matched.

CREATE TABLE stats_pgsql_query_rules (
    rule_id INTEGER PRIMARY KEY,
    hits INT NOT NULL
)

The fields have the following semantics:

• rule_id: the ID of the rule. Can be joined with the main.pgsql_query_rules table on the rule_id field.
• hits: the total number of hits for this rule. One hit is registered each time an incoming query matches the rule.

Note that the hits value is reset every time query rules are loaded to runtime, either through explicit LOAD PGSQL QUERY RULES TO RUNTIME or through implicit re-sync via ProxySQL Cluster.

Admin> SELECT * FROM stats_pgsql_query_rules;
+---------+-----------+
| rule_id | hits      |
+---------+-----------+
| 1       | 4102843   |
| 2       | 87        |
| 5       | 0         |
+---------+-----------+
3 rows in set (0.00 sec)

Related tables:

• pgsql_query_rules — defines the query rules tracked in this table
• stats_pgsql_query_digest — per-query-pattern statistics

stats_pgsql_users

The stats_pgsql_users table reports all configured PostgreSQL users, their current number of active frontend connections, and the maximum number of frontend connections they are allowed to hold (as defined in pgsql_users.max_connections). This table is useful for monitoring per-user connection consumption and identifying users that are approaching their connection limits.

CREATE TABLE stats_pgsql_users (
    username VARCHAR PRIMARY KEY,
    frontend_connections INT NOT NULL,
    frontend_max_connections INT NOT NULL
)

The fields have the following semantics:

• username: the username from the pgsql_users table.
• frontend_connections: the number of frontend connections currently used by this user.
• frontend_max_connections: the maximum number of frontend connections this user is allowed to use, as configured in the pgsql_users table.

Admin> SELECT username, frontend_connections conns, frontend_max_connections max_conns FROM stats_pgsql_users WHERE frontend_connections > 0;
+------------------+-------+-----------+
| username         | conns | max_conns |
+------------------+-------+-----------+
| app_rw           | 124   | 10000     |
| app_ro           | 318   | 10000     |
| reporting_user   | 12    | 2000      |
| analytics_ro     | 56    | 5000      |
+------------------+-------+-----------+
4 rows in set (0.00 sec)

Related tables:

• pgsql_users — defines the users and their max_connections limits tracked here
• stats_pgsql_global — Access_Denied_Max_User_Connections counter