Hologres:Manage connections

Connection and query management

Connection architecture

Connections to a Hologres instance pass through an access layer and then an execution layer. Each layer enforces its own connection limit.

The components are as follows:

• Gateway: the single access layer of the instance. Every client connection lands on a Gateway node first. The Gateway authenticates the connection, checks it against the IP whitelist, and routes it to an FE or FixedFE node based on the connection attributes. An instance has at least two Gateway nodes for high availability. On virtual warehouse instances, the Gateway also routes connections to the FE of the target virtual warehouse.

• FE (Frontend): the PostgreSQL-compatible frontend node that serves standard SQL connections, such as DDL statements, queries, and writes. Fixed Plan point queries and point writes can also run over FE connections, in which case they consume the FE max_connections quota. Whether such requests use FixedFE instead depends on the client and the instance version. show max_connections returns the limit of a single FE node.

• FixedFE (Fixed Frontend): a high-throughput connection service that runs alongside each FE node and is optimized primarily for Fixed Plan point queries and point writes, such as holo-client and the real-time ingestion paths of Flink and DataWorks. Starting from Hologres V2.1, binary log consumption also uses lightweight FixedFE connections by default. FixedFE connections are lightweight, do not count against the FE max_connections quota, and have a separate limit that is much higher than the FE limit.

• Walsender: the process inside FE that serves logical replication connections (replication=database). Because binary log consumption and real-time writes use lightweight FixedFE connections by default starting from Hologres V2.1, Walsender now mainly serves clients that still use the traditional replication protocol. Walsender connections are independent of max_connections and are governed by max_wal_senders.

Connection limits by layer

too many client tcp connections (global client_max)

-- Maximum connections of a single FE node.
show max_connections;

-- Maximum connections of the whole instance (FE node count multiplied by max_connections). Supported in Hologres V1.3.23 and later.
select instance_max_connections();

FATAL: sorry, too many clients already connection limit exceeded for superusers
FATAL: remaining connection slots are reserved for non-replication superuser connections

FATAL: too many fixed frontend connections

FATAL: number of requested standby connections exceeds max_wal_senders (currently 600)

Error message reference

Query the pg_stat_activity view

select * from pg_stat_activity ;

Usage notes

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

Default maximum connections

Manage connections in HoloWeb

Query connections with SQL

Use the following SQL statements to query connection information.

1. 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;

2. 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.

3. 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.

Query FixedFE connections

FixedFE connections, which serve Fixed Plan writes and point queries, do not appear in pg_stat_activity. Query the dedicated hg_fixed_fe_stat_activity view instead.

-- List all FixedFE connections on the current FE node.
select * from hg_fixed_fe_stat_activity;

-- Count FixedFE connections to compare against the limit of 6,400.
select count(*) from hg_fixed_fe_stat_activity;

-- Group FixedFE connections by database, user, and application.
SELECT  datname
        ,usename
        ,application_name
        ,COUNT(1)
FROM    hg_fixed_fe_stat_activity
GROUP BY datname
         ,usename
         ,application_name;

Key fields include datname, conn_id (the FixedFE connection ID), usename, application_name, client_addr, client_port, backend_start, state, query_start, and query. The backend_type field is always fixed frontend connection.

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.

• The FATAL: too many fixed frontend connections error is reported, which means the FixedFE layer hit its limit.

• The too many client tcp connections error is reported, which means the Gateway layer hit its limit.

First identify which layer hit its limit. For more information, see Error message reference. If the FE layer is full, connect with a Superuser account, which uses the reserved connections, 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

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

       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

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.

For more connection-related errors and how to handle them, see Error message reference.