Manage Hologres instance connections - query, terminate, connection pool - Hologres

Connection and query management

Hologres is PostgreSQL-compatible. Query the pg_stat_activity view to inspect connections and diagnose running queries. Key operations:

Query the default maximum number of connections: Each instance specification has a default connection limit. Run a command to query yours.
Manage connections in HoloWeb: View, manage, and terminate active connections in the HoloWeb console.
Query connections with SQL: Query connection counts, check states, and terminate idle connections.
Terminate connections: Use SQL functions to release resources held by specific connections.
Reserved connections for Superusers: Use reserved connections for management when the connection limit is reached.
Limit connections for a single user: Set a per-user connection limit to prevent resource overuse.
Auto-terminate idle connections: Automatically close connections idle beyond a specified duration.
Best practices for using connections: Best practices for Hologres connection management.

Query the pg_stat_activity view

The pg_stat_activity view provides runtime information about instance connections and queries. Run the following command to query it.

select * from pg_stat_activity ;

The following table describes the pg_stat_activity fields.

Field	Description
datid	The object ID (OID) of the database that the backend is connected to.
datname	The name of the database that the backend is connected to.
pid	The process ID (PID) of the Hologres backend.
usesysid	The OID of the user logged in to the Hologres backend.
usename	The username for the current connection.
application_name	The type of client application. Common application types include: Realtime Compute for Apache Flink: `{client_version}_ververica-connector-hologres`. Open-source Flink: `{client_version}_hologres-connector-flink`. DataWorks Data Integration for batch synchronization (read from Hologres): `datax_{jobId}`. DataWorks Data Integration for batch synchronization (write to Hologres): `{client_version}_datax_{jobId}`. DataWorks Data Integration for real-time synchronization: `{client_version}_streamx_{jobId}`. HoloWeb: `holoweb`. Accessing Hologres using an external table in MaxCompute: `MaxCompute`. A process started by a Holo Client to read the Hologres binlog: `holo_client_replication`. Query content is not displayed for this type of task. For other applications, specify the `application_name` in the connection string.
client_addr	The IP address of the client. This may be a resolved address, not the original source IP.
client_hostname	The hostname of the client.
client_port	The port of the client.
backend_start	Time the backend process started.
xact_start	Start time of the process's current transaction. This field is null if no transaction is active. If the current query is the first transaction in this process, this value is the same as `query_start`.
query_start	Start time of the currently active query. If the connection state is not `active`, this is the start time of the previous query.
state_change	Time the connection's state was last changed.
wait_event_type	The type of event for which the backend is waiting, or NULL if none. Possible values include: LWLock: The backend is waiting for a lightweight lock. Lock: The backend is waiting for a heavyweight lock. The `wait_event` field identifies the type of lock being awaited. BufferPin: The server process is waiting to access a data buffer while no other process is examining that buffer. Activity: The server process is idle. This is used for system processes that are waiting for activity in their main processing loop. Extension: The server process is waiting for activity within an extension module. Client: The server process is waiting for a query from a client application and expects something to happen that is not related to its internal processing. IPC: The server process is waiting for an activity from another process on the server. Timeout: The server process is waiting for a timeout to occur. IO: The server process is waiting for an I/O operation to complete.
wait_event	The name of the wait event if the backend is currently waiting, otherwise NULL.
state	The current state of the connection. Common states include: active: The connection is executing a query. idle: The connection is waiting for a new client command. idle in transaction: The connection is in a transaction but is not currently executing a query. idle in transaction (Aborted): The connection is in a failed transaction and is not currently executing a query. \N (Empty): Indicates a non-user process, typically a system background maintenance process that can be ignored.
backend_xid	The top-level transaction identifier of the Hologres backend.
backend_xmin	The current backend's xmin horizon.
query	The most recent query on the backend. Shows the running query if state is `active`; otherwise shows the last executed query.
backend_type	The backend type. Possible values: autovacuum launcher, autovacuum worker, logical replication launcher, logical replication worker, parallel worker, background writer, client backend, checkpointer, startup, walreceiver, walsender, walwriter, and backend execution components such as PQE. Note The `client backend` type represents application connections.

Usage notes

Only Superusers can view all connections. Standard users see only their own.

Default maximum connections

The default connection limit varies by instance specification. Run the following commands to query it. Each command returns the per-FE-node limit. Multiply by the number of FE nodes to get the total instance limit. FE node counts are listed in Instance management.

Query the maximum connections for the entire instance (Hologres V1.3.23 and later).
```
select instance_max_connections();
```
Query the maximum connections for a single FE node. Multiply by the number of FE nodes to get the total instance limit.
```
show max_connections;
```

Manage connections in HoloWeb

View and manage active connections in the HoloWeb console.

Log on to the HoloWeb console. Connect to HoloWeb and run SQL queries.
In the top navigation bar, click Diagnostics and Optimization.
In the left-side navigation pane, click Connections.

On the Connections page, configure the parameters to view connections and related information for your instance.

Note

Only Superusers can view all connections. Standard users see only their own.

The following table describes the parameters.

Parameter	Description
Instance name	The Hologres instance name.
Database	The Hologres database. Leave blank to display connections to all databases.
Database	The name of the connected database. Note If the database name is Postgres, it indicates a backend maintenance connection, which can be ignored.
User Name	The user account for the connection.
Client Address	This may be a routed outbound IP address, not the original source IP.
Application Name	The name of the application that created the connection.
State	The state of the connection. Common states include: active: The connection is active. idle: The connection is idle. idle in transaction: The connection is idle within a long-running transaction. idle in transaction (Aborted): The connection is idle within a failed transaction.
Query Start	The start time of the query.
Query	The executed query. Note Long queries might be truncated.
PID	The process ID (PID) of the query.
Operation	Kill: Terminate unexpected connections individually or in batches. Details: Click to view detailed information about the connection.

Query connections with SQL

Use the following SQL statements to query connection information.

Query the number of connections to the current database.

Run the following commands to view the connection count for the current database. Field descriptions are in Query the pg_stat_activity view.

For Hologres V1.1 and later:

SELECT  datname::TEXT
        ,COUNT(1) AS COUNT
FROM    pg_stat_activity
WHERE   backend_type = 'client backend'
AND     application_name != 'hologres'
GROUP BY datname::TEXT;

For Hologres V0.10 and earlier:

SELECT  datname
        ,COUNT(1) AS COUNT
FROM    pg_stat_activity
WHERE   backend_type = 'client backend'
AND     application_name != 'hologres'
GROUP BY datname;

View the state of each connection.
Use HoloWeb in the Hologres console to view connection status, or run the following statement to query all JDBC or PSQL connection states.
```
select * from pg_stat_activity where backend_type = 'client backend' and state = '<statename>';
```
Replace <statename> with one of the following states:
- idle: The process is waiting for new client commands.
- active: An active connection, where the process is executing a query.
- idle in transaction: The process is in a transaction but is not currently executing a query.
- idle in transaction (aborted): The process is in a failed transaction, and the process is not currently executing a query.
- fastpath function call: Indicates that a process is executing a fast-path function.
- disabled: Indicates that track activities for the process is disabled.
For example, query idle connections:
```
select * from pg_stat_activity where backend_type = 'client backend' and state = 'idle';
```
Hologres components such as HoloWeb use JDBC and consume connections. If the count consistently approaches max_connections, check your application for connection leaks and configure an appropriate connection pool. Release connections.
View the number of connections on each access node
Query the connection count on each FE node (Hologres V1.3.23 and later). FE nodes without active connections are omitted from results.
```
select * from hologres.hg_connections;
```
The following table describes the fields in the query result.
- fe_id: The ID of the FE node.
- used_connections: The number of connections currently in use on the FE node.
- max_connections: The maximum number of connections for an FE node, which is the same as the return value of the show max_connections command.

Terminate connections

The following symptoms indicate the connection limit has been reached:

The connection count reaches or exceeds max_connections, as shown on the Monitoring and Alarms page in the Hologres console.
The FATAL: sorry, too many clients already connection limit exceeded for superusers error occurs.
The FATAL: remaining connection slots are reserved for non-replication superuser connections error is reported.

To resolve these issues, connect with a Superuser account and run the following statement to check for idle connections.

select * from pg_stat_activity where backend_type = 'client backend' and state = 'idle';

If results show excessive idle processes, use the pid from the results to release connections with the following statements. Field descriptions are in Query the pg_stat_activity view.

select pg_cancel_backend(<pid>);     -- Cancels the query on the connection.
select pg_terminate_backend(<pid>);  -- Terminates the corresponding backend connection process.

-- Terminate backend IDLE connection processes in a batch to release connections.
SELECT pg_terminate_backend(pid)
        ,query
        ,datname
        ,usename
        ,application_name
        ,client_addr
        ,client_port
        ,backend_start
        ,state
FROM    pg_stat_activity
WHERE   length(query) > 0
AND     pid != pg_backend_pid()
AND     backend_type = 'client backend'
AND     state = 'idle'
AND     application_name != 'hologres'
AND     query not like '%pg_cancel_backend%';

Reserved connections for Superusers

Hologres reserves connections for Superusers; the count varies by instance specification (Instance management). These connections enable management tasks such as terminating idle connections when the limit is reached. Standard users can use max_connections minus the reserved count. Do not use Superuser accounts for regular operations — this can exhaust all connections and block administrative access.

User connection limits

Set a user connection limit

Set a per-user connection limit to prevent excessive resource consumption.

Run the following statement to limit connections for a user on a single access node. For multi-node instances, the total limit is (connections per node) * (number of nodes).

Syntax

ALTER ROLE "Alibaba Cloud account ID" CONNECTION LIMIT <number>;

Parameters

Parameter

Description

Alibaba Cloud account ID

The ID of the account to limit. For a RAM user, prefix the account UID with p4_.

Account overview.

number

The connection limit.

The value must be in the range of [-1, N]. A value of -1 removes the user's connection limit.

Example
This example limits RAM user 283813xxxx to one connection per node.
```
ALTER ROLE "p4_283813xxxx" CONNECTION LIMIT 1; 
```

View user connection limits

Run the following statement to view per-node connection limits for instance users.

SELECT rolname, rolconnlimit
FROM pg_roles
WHERE rolconnlimit <> -1;

Sample query result:

       rolname | rolconnlimit 
---------------+--------------
 p4_283813xxxx |      1
(1 row)

Auto-terminate idle connections

If instance connections consistently approach the limit, your application may have connection leaks. Enable automatic idle connection termination to release resources. Connections idle beyond the specified duration are automatically disconnected.

Limitations

Supported in Hologres V0.10.25 and later. For earlier versions, see Common errors that occur during an upgrade or join the Hologres DingTalk group to request an upgrade. How do I get more online support?

Syntax

Session level

-- Automatically disconnect a connection if it has been idle for 10 minutes (600,000 milliseconds).
SET idle_session_timeout = 600000;

Database level

-- Automatically disconnect connections to this database if they have been idle for 10 minutes (600,000 milliseconds).
ALTER DATABASE  <db_name> SET idle_session_timeout = 600000;

Replace <db_name> with the name of the database for which you want to enable automatic termination of idle connections.

Connection best practices

Follow these best practices for using Hologres connections.

Use the Superuser account wisely
- Do not use Superuser accounts for application connections or regular operations. If connections exceed the limit, even Superusers may be unable to connect.
- Create a dedicated Superuser account for emergencies such as connection limit exhaustion or hung queries.
Configure a proper connection pool
- Hologres does not automatically terminate idle connections by default. Use a connection pool to release idle connections promptly.
- Periodically clean up idle connections to free resources.

Common errors

An error message is returned during SQL execution: terminating connection due to idle state timeout.
Cause: The connection was automatically terminated because it exceeded the configured idle timeout.
Solution: Reconnect to the instance or increase the idle connection timeout. For information about how to modify the idle connection timeout, see Auto-terminate idle connections.