--- title: "Set up YSQL Connection Manager" url: "https://docs.yugabyte.com/stable/additional-features/connection-manager-ysql/ycm-setup/" --- # Set up YSQL Connection Manager YSQL Connection Manager flags and settings ## Start YSQL Connection Manager - [![Server Icon](/icons/database.svg) Local](#yugabyted) - [![Server Icon](/icons/server.svg) YugabyteDB Anywhere](#platform) - [![Cloud Icon](/icons/cloud.svg) YugabyteDB Aeon](#aeon) To start a YugabyteDB cluster with YSQL Connection Manager, set the [yb-tserver](/stable/reference/configuration/yb-tserver/ "yb-tserver") flag `enable_ysql_conn_mgr` to true. For example, to create a single-node cluster with YSQL Connection Manager using [yugabyted](/stable/reference/configuration/yugabyted/ "yugabyted"), use the following command: ```sh ./bin/yugabyted start --tserver_flags "enable_ysql_conn_mgr=true" --ui false ``` When `enable_ysql_conn_mgr` is set, each YB-TServer starts the YSQL Connection Manager process along with the PostgreSQL process. You should see one YSQL Connection Manager process per YB-TServer. To create a large number of client connections, ensure that "SHMMNI" (the maximum number of concurrent shared memory segments an OS allows) as well as [ulimit](/stable/deploy/manual-deployment/system-config/#set-ulimits "ulimit") is set correctly as follows: 1. Open the file `/etc/sysctl.conf`. 2. Add `kernel.shmmni = 32768` (support for 30000 clients) at the end of the file. 3. To refresh the settings, use `sudo sysctl -p`. To enable built-in connection pooling for universes deployed using YugabyteDB Anywhere: - Turn on the **Connection pooling** option when creating a universe. Refer to [Create a multi-zone universe](/stable/yugabyte-platform/create-deployments/create-universe-multi-zone/#advanced-configuration "Create a multi-zone universe"). - Edit connection pooling on an existing universe. Refer to [Edit connection pooling](/stable/yugabyte-platform/scale-deployments/edit-config-flags/#connection-pooling "Edit connection pooling"). Note that when managing universes using YugabyteDB Anywhere, do not set the following flags manually: `enable_ysql_conn_mgr`, `ysql_conn_mgr_port`, or `pgsql_proxy_bind_address`. To customize other [Connection Manager settings](#configure "Connection Manager settings"), use [Edit configuration flags](/stable/yugabyte-platform/manage-deployments/edit-config-flags/#modify-configuration-flags "Edit configuration flags") (**Actions > Edit Flags**). **Connect** To connect to YSQL Connection Manager, use the [ysqlsh](/stable/api/ysqlsh/ "ysqlsh") command with the [`-h `](/stable/api/ysqlsh/#h-hostname-host-hostname "-h ") flag, instead of specifying the Unix-domain socket directory. Using the socket directory along with [`-p`](/stable/api/ysqlsh/#p-port-port-port "-p") (custom PostgreSQL port or default 6433) will connect you to the PostgreSQL process, not the YSQL Connection Manager process. You can enable built-in connection pooling on YugabyteDB Aeon clusters in the following ways: - When [creating a cluster](/stable/yugabyte-cloud/cloud-basics/create-clusters/ "creating a cluster"), turn on the **Connection Pooling** option. (Connection Pooling is enabled by default for [Sandbox clusters](/stable/yugabyte-cloud/cloud-basics/create-clusters/create-clusters-free/ "Sandbox clusters").) - For clusters that are already created, navigate to the cluster **Settings>Connection Pooling** tab. Enabling connection pooling on an Aeon cluster gives 10 client connections for every server connection by default. ## Configure By default, when YSQL Connection Manager is enabled, it uses the port 5433, and the backend database is assigned a random free port. To explicitly set a port for YSQL, you should specify ports for the flags `ysql_conn_mgr_port` and [ysql\_port](/stable/reference/configuration/yugabyted/#advanced-flags "ysql_port"). The following table describes YB-TServer flags related to YSQL Connection Manager: Flag Description enable\_ysql\_conn\_mgr Enables YSQL Connection Manager for the cluster. YB-TServer starts a YSQL Connection Manager process as a child process. Default: false enable\_ysql\_conn\_mgr\_stats Enable statistics collection from YSQL Connection Manager. These statistics are displayed at the endpoint `:13000/connections`. Default: true ysql\_conn\_mgr\_idle\_time Specifies the maximum idle time (in seconds) allowed for database connections created by YSQL Connection Manager. If a database connection remains idle without serving a client connection for a duration equal to, or exceeding this value, it is automatically closed by YSQL Connection Manager. Default: 180 ysql\_conn\_mgr\_jitter\_time Jitter duration (in seconds) up to which an idle server connection may be kept open beyond its idle timeout (`ysql_conn_mgr_idle_time`). This avoids sudden bursts of connections closing simultaneously during connection bursts. Default: 120 ysql\_conn\_mgr\_max\_client\_connections Maximum number of concurrent client connections allowed. Default: 10000 ysql\_conn\_mgr\_min\_conns\_per\_db Minimum number of server connections that is present in the pool. This limit is not considered while closing a broken server connection. Default: 1 ysql\_conn\_mgr\_max\_pools Maximum total number of pools supported by YSQL Connection Manager. This includes pools corresponding to dropped users and databases. Default: 10000 ysql\_conn\_mgr\_control\_connection\_pool\_size Maximum number of concurrent control connections in YSQL Connection Manager. If set to 0, the limit is 0.1 * `ysql_max_connections`. Default: 0 ysql\_conn\_mgr\_num\_workers Number of worker threads used by YSQL Connection Manager. If set to 0, the number of worker threads will be half of the number of CPU cores. Default: 0 ysql\_conn\_mgr\_stats\_interval Interval (in seconds) for updating YSQL Connection Manager statistics. Default: 1 ysql\_conn\_mgr\_superuser\_sticky Make superuser connections sticky. Default: true ysql\_conn\_mgr\_port YSQL Connection Manager port to which clients can connect. This must be different from the PostgreSQL port set via `pgsql_proxy_bind_address`. Default: 5433 ysql\_conn\_mgr\_server\_lifetime The maximum duration (in seconds) that a backend PostgreSQL connection managed by YSQL Connection Manager can remain open after creation. Default: 3600 ysql\_conn\_mgr\_log\_settings Comma-separated list of log settings for YSQL Connection Manager, which may include `log_debug`, `log_session`, `log_query`, and `log_stats`. Only the log settings present in this string are enabled; omitted settings remain disabled. `log_config` is accepted for backward compatibility but has no effect, as config logging is always on. Default: "" ysql\_conn\_mgr\_log\_max\_size Maximum YSQL Connection Manager log size (in bytes) after which the log file is rolled over. Set to 0 to disable size-based rollover. Default: 0 ysql\_conn\_mgr\_log\_rotate\_interval Duration (in seconds) after which the YSQL Connection Manager log file is rolled over. Set to 0 to disable time-based rollover. Default: 0 ysql\_conn\_mgr\_use\_auth\_backend When set to true ("auth backend mode"), each incoming authentication request is handled by a freshly spawned postgres backend. Set this parameter to false to use an authentication passthrough mechanism, where backends from a pool of "control backends" are reused for all authentication requests; this can potentially reduce new connection acquisition latency. Contact [Yugabyte Support](https://support.yugabyte.com/ "Yugabyte Support") about whether setting this parameter to false is recommended for your workload. Default: true ysql\_enable\_read\_request\_cache\_for\_connection\_auth Uses the YB-TServer response cache during connection authentication to prefetch authorization catalog metadata (for example, role and database ACL information), reducing YB-Master RPC overhead and improving connection acquisition latency when YSQL Connection Manager is enabled. Requires [ysql\_enable\_read\_request\_caching](/stable/reference/configuration/yb-tserver/#ysql-enable-read-request-caching "ysql_enable_read_request_caching") which is enabled by default. Not supported when login profiles are enabled ([ysql\_enable\_profile](/stable/reference/configuration/yb-tserver/#ysql-enable-profile "ysql_enable_profile")). Auth cache entries are refreshed according to `pg_cache_response_trust_auth_lifetime_limit_ms` (default is 60000 ms) and when the catalog version increments. Default: false. pg\_cache\_response\_trust\_auth\_lifetime\_limit\_ms Maximum age (in milliseconds) for reusing auth cache entries when ysql\_enable\_read\_request\_cache\_for\_connection\_auth is enabled. After this limit, the next authentication request refreshes the cache instead of serving stale data. Auth changes usually take effect sooner via catalog invalidation. Default: 60000. ysql\_conn\_mgr\_auth\_msg\_timeout Maximum time (in milliseconds) to wait for each startup and authentication message from a client. Applied individually to the TLS handshake, startup packet, and each auth packet; the connection is closed if exceeded. Default: 15000. ysql\_conn\_mgr\_readahead\_buffer\_size Size of the per-connection buffer (in bytes) used for IO read-ahead operations in YSQL Connection Manager. Default: 8192 ysql\_conn\_mgr\_tcp\_keepalive TCP keepalive time (in seconds) in YSQL Connection Manager. Set to zero to disable keepalive. Default: 15 ysql\_conn\_mgr\_tcp\_keepalive\_keep\_interval TCP keepalive interval (in seconds) in YSQL Connection Manager. Only applicable if 'ysql\_conn\_mgr\_tcp\_keepalive' is enabled. Default: 75 ysql\_conn\_mgr\_tcp\_keepalive\_probes Number of TCP keepalive probes in YSQL Connection Manager. Only applicable if `ysql_conn_mgr_tcp_keepalive` is enabled. Default: 9 ysql\_conn\_mgr\_tcp\_keepalive\_usr\_timeout TCP user timeout (in milliseconds) in YSQL Connection Manager. Only applicable if 'ysql\_conn\_mgr\_tcp\_keepalive' is enabled. When set to a value greater than 0, transmitted data may remain unacknowledged for at most this duration before the connection is forcibly closed; 0 uses the operating system default. Default: 0 ysql\_conn\_mgr\_pool\_timeout Server pool wait timeout (in milliseconds) in YSQL Connection Manager. This is the time clients wait for an available server, after which they are disconnected. If set to zero, clients wait for server connections indefinitely. Default: 0 ysql\_conn\_mgr\_sequence\_support\_mode Sequence support mode when YSQL Connection Manager is enabled. When set to `pooled_without_curval_lastval`, the currval() and lastval() functions are not supported. When set to `pooled_with_curval_lastval`, the currval() and lastval() functions are supported. For both settings, monotonic sequence order is not guaranteed if `ysql_sequence_cache_method` is set to `connection`. To also support monotonic order, set this flag to `session`. Default: pooled\_without\_curval\_lastval ysql\_conn\_mgr\_optimized\_extended\_query\_protocol Enables optimization of [extended-query protocol](https://www.postgresql.org/docs/current/protocol-overview.html#PROTOCOL-QUERY-CONCEPTS "extended-query protocol") to provide better performance; note that while optimization is enabled, you may have correctness issues if you alter the schema of objects used in prepared statements. If set to false, extended-query protocol handling is always fully correct but unoptimized. Default: true ysql\_conn\_mgr\_optimized\_session\_parameters Optimize usage of session parameters in YSQL Connection Manager. If set to false, all applied session parameters are replayed at transaction boundaries for each client connection. Default: true ysql\_conn\_mgr\_max\_prepared\_statements Soft limit on prepared statements per server connection. When exceeded, the least recently used statements are closed on the backend (enforced periodically, so the count can temporarily exceed this value). Set to 0 to disable eviction. Default: 100. ysql\_conn\_mgr\_reserve\_internal\_conns The number of physical connections to reserve for internal operations, out of the total number of connections (per node) as set using [ysql\_max\_connections](/stable/reference/configuration/yb-tserver/#ysql-max-connections "ysql_max_connections"). The reserved connections bypass YSQL Connection Manager; the remaining connections are available for the Connection Manager pool. For example, if `ysql_max_connections` is 300 and this flag is set to 15, YSQL Connection Manager will have a physical connection limit of 285 (300 - 15) per node. Default: 15. ysql\_conn\_mgr\_alter\_guc\_adoption\_strategy Strategy used by YSQL Connection Manager to adopt configuration parameter (GUC) settings changed by ALTER ROLE and ALTER DATABASE statements. One of `fluctuating`, `gradual`, or `connection_static`. See [ALTER ROLE SET and ALTER DATABASE SET commands](#alter-role-set-and-alter-database-set-commands "ALTER ROLE SET and ALTER DATABASE SET commands"). Default: gradual. ysql\_conn\_mgr\_alter\_guc\_stale\_backend\_ttl\_ms Maximum time (in milliseconds) that a backend which doesn't have the latest ALTER GUC settings is allowed to stay alive. When set to a positive value, stale backends are terminated uniformly within the TTL period after the ALTER is executed. `-1` means no action is taken on stale backends; `0` means stale idle backends are destroyed immediately and stale active backends are destroyed when they next become idle. Applies regardless of the `ysql_conn_mgr_alter_guc_adoption_strategy` setting. Default: 60000. v2025.2.4.0 and later only. ysql\_conn\_mgr\_enable\_prep\_stmt\_close Applies only to named prepared statements. When enabled, YSQL Connection Manager forwards Close messages to the backend, which drops the prepared statement only if its cached plan is invalid or the connection is sticky; valid plans on non-sticky connections are retained for reuse across logical connections. Unnamed prepared statements are not covered; see [#30481](https://github.com/yugabyte/yugabyte-db/issues/30481). When disabled, Close messages are handled as a no-op by the connection manager and never reach the backend, which can cause errors. Requires `ysql_conn_mgr_optimized_extended_query_protocol` to be enabled. Default: true. ysql\_yb\_conn\_mgr\_selective\_deallocate When enabled, DEALLOCATE commands sent via YSQL Connection Manager only drop prepared statements whose cached plans are invalid (for example, stale due to schema changes or `search_path` drift), while preserving valid statements that may be shared across logical connections on the same backend. SQL-level prepared statements (from PREPARE) are always dropped because they make the connection sticky. When disabled, standard PostgreSQL DEALLOCATE behavior applies: `DEALLOCATE ALL` unconditionally drops all statements, and `DEALLOCATE ` fails for protocol-level prepared statements. Default: true. ysql\_conn\_mgr\_internal\_conn\_db Database to which YSQL Connection Manager connects when creating internal control connections. Default: yugabyte. ysql\_conn\_mgr\_internal\_conn\_user Username used by YSQL Connection Manager when creating internal control connections (for example, for authentication passthrough and internal queries). Replaces the deprecated `ysql_conn_mgr_username` flag. Default: yugabyte. ysql\_conn\_mgr\_socket\_listen\_backlog Maximum number of pending TCP connections queued by the kernel on Connection Manager's listening socket (the backlog argument to `listen(2)`). Incoming connections beyond this limit may be refused or dropped during heavy connection bursts. Default: 128. ysql\_conn\_mgr\_wait\_timeout\_ms Wait time (in milliseconds) before timing out while sending or receiving packets on the Connection Manager socket. Default: 10000 ysql\_conn\_mgr\_backend\_drain\_timeout\_ms Upper bound (in milliseconds) that Connection Manager synchronously waits for a backend's socket to close when closing the backend connection. 0 skips the wait (may cause "sorry, too many clients already"); too high can reduce throughput. Default: 100. ysql\_conn\_mgr\_enable\_parse\_queue\_tracking Track in-flight Parse operations so prepared-statement state can be reconciled with the backend after errors. Disabling can cause errors such as "prepared statement does not exist". Default: true. ysql\_conn\_mgr\_wait\_for\_rfq\_on\_sync When enabled, stop reading further client packets after forwarding a Sync message until the matching ReadyForQuery is received, preventing cross-Sync pipelining. Disabling can cause correctness issues with pipelining. Default: true. ysql\_conn\_mgr\_tcmalloc\_gc\_interval Interval (in seconds) between TCMalloc page-heap garbage collection (GC) checks in YSQL Connection Manager. Set to 0 to disable periodic GC. Default: 300. ysql\_conn\_mgr\_tcmalloc\_sample\_period Interval (in bytes) at which TCMalloc samples allocations for Connection Manager. Only in effect when `ysql_conn_mgr_dump_heap_snapshot_interval` is greater than 0. Default: 1048576 ysql\_conn\_mgr\_dump\_heap\_snapshot\_interval If greater than 0, dumps the TCMalloc current heap snapshot of the Connection Manager process to the logs every N seconds. Default: 0 ### Deprecated flags Deprecated Use instead ysql\_conn\_mgr\_username ysql\_conn\_mgr\_internal\_conn\_user v2025.2.4.0 and later ysql\_conn\_mgr\_password Not used; internal connections authenticate with the TServer key in v2024.1.0.0 and later ysql\_conn\_mgr\_max\_phy\_conn\_percent ysql\_conn\_mgr\_reserve\_internal\_conns v2025.2.1.0 and later ysql\_conn\_mgr\_deallocate\_if\_invalid\_prep\_stmt (only available in v2025.2.3.0) ysql\_conn\_mgr\_enable\_prep\_stmt\_close v2025.2.4.0 and later ## Authentication methods The following table outlines the various authentication methods supported by YugabyteDB and their compatibility with YSQL Connection Manager when a connection matches an HBA ([Host-Based Authentication](/stable/secure/authentication/host-based-authentication/ "Host-Based Authentication")) record. Auth Method Description Ident Authentication Server contacts client's OS to verify username that initiated connection, trusting OS-level identity. Peer Authentication For local/Unix socket connections, server checks that the connecting UNIX user matches the requested database user, relying on OS user identity. Plain/Clear Text Password Standard password-based authentication, though storing passwords in plain text is not recommended. JWT Authentication (OIDC) Uses JSON Web Tokens (JWT) from an external Identity Provider (IDP) to securely transmit authentication and authorization information. LDAP Authentication Verifies users against a centralized directory service using Lightweight Directory Access Protocol (LDAP). GSS API or Kerberos Enables Kerberos-based authentication through a standardized API, allowing secure, enterprise-grade Single Sign-On (SSO) logins without passwords. **Note**: Testing of this feature with YugabyteDB is currently limited. SCRAM-SHA-256 A secure password-based authentication that protects credentials using hashing, salting, and challenge-response. SCRAM-SHA-256-PLUS A variant of SCRAM-SHA-256 over TLS channels that performs TLS channel-binding as part of authentication. MD5 Password-based authentication where the user's password is by default stored in MD5 encryption format in the database. Cert Currently, Connection Manager does not support the HBA [auth-method](/stable/secure/authentication/host-based-authentication/#auth-method "auth-method") `cert`, where the server authenticates users via the client certificate (for example, CN/DN mapping). Client-side `sslmode` (such as verify-ca, verify-full), where the client verifies the *server* certificate, is a different layer and is supported (see [SSL modes and encryption in transit](#ssl-modes-and-encryption-in-transit "SSL modes and encryption in transit")). ## SSL modes and encryption in transit Client connection behavior and server-side policy are handled separately as follows: - **SSL mode** (client-side connection behavior): controls whether the client uses TLS and how it verifies the server (disable, allow, prefer, require, verify-ca, verify-full). Connection Manager supports all of these client SSL modes. - **ysql\_hba.conf** ([host-based authentication](/stable/secure/authentication/host-based-authentication/ "host-based authentication")): controls client authentication for YSQL connections, including whether connections must use SSL/TLS, whether the client must present a certificate, and whether the server verifies the client's identity using that certificate. Currently, Connection Manager does not support HBA certificate authentication. ### Encryption in transit YSQL Connection Manager can be enabled on universes with or without encryption in transit enabled. Connection Manager automatically determines which mode is required and updates the configuration file accordingly. You can review the default settings in the [template configuration file](https://github.com/yugabyte/yugabyte-db/blob/2025.2.2/src/yb/yql/ysql_conn_mgr_wrapper/ysql_conn_mgr.template.conf "template configuration file"). To enable encrypted connections (TLS/SSL) between your client application and YugabyteDB via YSQL Connection Manager, enable client-to-server encryption in transit on your universe by configuring the following [yb-tserver](/stable/reference/configuration/yb-tserver/ "yb-tserver") flags: - Set [use\_client\_to\_server\_encryption](/stable/reference/configuration/yb-tserver/#use-client-to-server-encryption "use_client_to_server_encryption") to true. - Set [certs\_for\_client\_dir](/stable/reference/configuration/yb-tserver/#certs-for-client-dir "certs_for_client_dir") to the path containing the server TLS certificates. For more information on encryption in transit in YugabyteDB, refer to [Encryption in transit](/stable/secure/tls-encryption/ "Encryption in transit"). #### Disabled When encryption in transit is not enabled, TLS is not enabled in the Connection Manager configuration file either. The default HBA configuration allows unencrypted connections as follows: ```text # This is an autogenerated file, do not edit manually! # Internal configuration: # local all postgres yb-tserver-key host all all all trust local all yugabyte trust ``` The following table describes how each client SSL mode behaves when connecting via YSQL Connection Manager with the default HBA configuration and encryption in transit disabled. | Client SSL mode | Description | Same as PostgreSQL? | Connection succeeds? | |--------------------------------|-------------------------------------------------------------------------------------------------------------------------------|---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|-----------------------------------------------------------------------------------------------------------------------------------| | disable | Client attempts a non-SSL connection | Yes. Unencrypted connection is established. | Yes | | allow | Tries unencrypted connection first, then secure (encrypted) | Yes. Unencrypted connection is established. | Yes (first non-SSL attempt succeeds) | | prefer (default in PostgreSQL) | Tries secure connection first, then unencrypted | Yes. Unencrypted connection is established. | Partially. First SSL attempt is rejected by Connection Manager, second non-SSL attempt follows Connection Manager authentication. | | require | Uses secure connection, fails if not available | Yes. If connection fails, PostgreSQL or Connection Manager sends 'N' (not supported) message to the client; client reports: "server does not support SSL, but SSL was required" | No. Client's SSL attempt is rejected by Connection Manager. | | verify-ca | Behaves like require. Additionally, verifies server cert against CA, or fails if no valid matching CA certificates are found. | Yes. If connection fails, PostgreSQL or Connection Manager sends 'N' (not supported) message to the client; client reports: "server does not support SSL, but SSL was required" | No. Client's SSL attempt is rejected by Connection Manager. | | verify-full | Behaves like verify-ca. Additionally, verifies that the server cert matches the host. | Yes. If connection fails, PostgreSQL or Connection Manager sends 'N' (not supported) message to the client; client reports: "server does not support SSL, but SSL was required" | No. Client's SSL attempt is rejected by Connection Manager. | Adding entries to the default configuration does not change behavior for any of these client SSL modes. #### Enabled When encryption in transit is enabled for the cluster (by setting the `use_client_to_server_encryption` and `certs_for_client_dir` flags), Connection Manager automatically switches to require mode (that is, it starts with Odyssey `tls "require"` by reading the TLS-related flags and overwriting the Connection Manager configuration file). The default HBA file in this mode is as follows: ```text # This is an autogenerated file, do not edit manually! # Internal configuration: # local all postgres yb-tserver-key hostssl all all all trust local all yugabyte trust ``` The following table describes how each client SSL mode behaves when connecting via YSQL Connection Manager with the default HBA configuration and encryption in transit enabled. Client SSL mode Description Same as PostgreSQL? Connection succeeds? disable Client attempts a non-SSL connection. Mostly. Error messages differ. If you add `host all all all trust` to the HBA file, connection fails with Connection Manager, but succeeds with PostgreSQL. No. Non-SSL attempt is rejected by Connection Manager. allow Tries unencrypted connection first, then secure Mostly. Encrypted connection is established. But if you add `host all all all trust` to the HBA file, an encrypted connection is created with Connection Manager, and unencrypted with PostgreSQL. Partially. First non-SSL attempt is rejected by Connection Manager; second SSL attempt follows Connection Manager authentication. prefer (default in PostgreSQL) Tries secure connection first, then unencrypted. Yes. Encrypted connection is established. Yes. First SSL attempt establishes connection with Connection Manager. require Uses secure connection, fails if not available. Yes. Encrypted connection is established. Yes. An SSL attempt establishes connection with Connection Manager. verify-ca Behaves like require. Additionally, verifies server cert against CA, or fails if no valid matching CA certificates are found. Yes. Encrypted connection is established and client verifies the TLS certificate. Yes. An SSL attempt establishes connection with Connection Manager. verify-full Behaves like verify-ca. Additionally, verifies that the server cert matches the host. Yes. Encrypted connection is established and client verifies the TLS certificate and hostname. The handshake happens as follows: - Client-side validation: The client checks if the server's certificate is signed by a trusted CA. - Identity match: The client ensures the certificate's hostname matches the actual server address. - Connection failure on mismatch: If either check fails, the connection is rejected (for example, JDBC and ysqlsh perform this verification). Yes. An SSL attempt establishes connection with Connection Manager. ## Sticky connections YSQL Connection Manager enables a larger number of client connections to efficiently share a smaller pool of backend processes using a many-to-one multiplexing model. However, in certain cases, a backend process may enter a state that prevents connection multiplexing between transactions. When this occurs, the backend process remains dedicated to a single client connection (hence the term "sticky connection") for the entire session rather than just a single transaction. This behavior deviates from the typical use case, where backend processes are reassigned after each transaction. Currently, once formed, sticky connections remain sticky until the end of the session. At the end of the session, the backend process corresponding to a sticky connection is destroyed along with the connection, and the connection does not return to the pool. When using YSQL Connection Manager, sticky connections can form in the following circumstances: - Creating TEMP tables. - Declaring a CURSOR using the WITH HOLD attribute. - Using a PREPARE query (not to be confused with protocol-level preparation of statements). - Superuser connections; if you want superuser connections to not be sticky, set the `ysql_conn_mgr_superuser_sticky` flag to false. - Using a SEQUENCE with `ysql_conn_mgr_sequence_support_mode` set to `session`. (Other values for this flag provide lesser support without stickiness.) - Replication connections. - Setting the following configuration parameters during the session: - `session_authorization` - `role` - `default_tablespace` - `temp_tablespaces` - Any string-type variables of extensions - `yb_read_after_commit_visibility` ## ALTER ROLE SET and ALTER DATABASE SET commands `ALTER ROLE ... SET` and `ALTER DATABASE ... SET` statements change the default values of configuration parameters (GUCs) for a role or database. Because YSQL Connection Manager multiplexes many client sessions over a smaller pool of backend processes, a backend that was created before such an ALTER may not have the new settings. The `ysql_conn_mgr_alter_guc_adoption_strategy` flag controls how existing sessions behave after an ALTER. | Setting | Description | |--------------------|-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------| | fluctuating | No special handling is done. Successive transactions in the same session may run on backends with or without the new settings, so the configuration parameter values observed by the session may fluctuate. | | gradual | Default. Existing sessions gradually adopt the new settings. Subsequent transactions are routed to backends that have the latest ALTER settings. | | connection\_static | Existing sessions retain the configuration parameter settings they had at the time of connection and never observe later ALTER statements. This is closest to the behavior of vanilla PostgreSQL. | In both the `gradual` and `connection_static` modes, new sessions always observe the effects of ALTER statements that were issued before they were created. Independent of the adoption strategy, the `ysql_conn_mgr_alter_guc_stale_backend_ttl_ms` flag bounds how long backends that don't have the latest ALTER settings ("stale" backends) are allowed to stay alive, so that they are eventually recycled. Only *idle* stale backends are terminated: a stale backend that is currently running a transaction is never interrupted, and is recycled only after it becomes idle. As a result, the effective lifetime of a stale backend can exceed the configured TTL if it stays busy. ## Limitations - YSQL Connection Manager can route up to 10,000 connection pools. This includes pools corresponding to dropped users and databases. - Prepared statements may be visible to other sessions in the same connection pool. [#24652](https://github.com/yugabyte/yugabyte-db/issues/24652) - With `ysql_conn_mgr_enable_prep_stmt_close` disabled (not recommended), DEALLOCATE/DEALLOCATE ALL and protocol-level Close handling can result in unexpected behavior. [#24653](https://github.com/yugabyte/yugabyte-db/issues/24653) - Currently, you can't apply custom configurations to individual pools. The YSQL Connection Manager configuration applies to all pools. - When YSQL Connection Manager is enabled, the backend PID stored using JDBC drivers may not be accurate. This does not affect backend-specific functionalities (for example, cancel queries), but this PID should not be used to identify the backend process. - By default, `currval` and `lastval` functions do not work when YSQL Connection Manager is enabled. They can be supported with the help of the `ysql_conn_mgr_sequence_support_mode` flag. - YSQL Connection Manager does not yet support IPv6 connections. [#24765](https://github.com/yugabyte/yugabyte-db/issues/24765) - Currently, [auth-method](/stable/secure/authentication/host-based-authentication/#auth-method "auth-method") `cert` is not supported for host-based authentication. [#20658](https://github.com/yugabyte/yugabyte-db/issues/20658) - The use of auth-backends (`ysql_conn_mgr_use_auth_backend=true`) to authenticate client connections can result in higher connection acquisition latencies. Contact [Yugabyte Support](https://support.yugabyte.com/ "Yugabyte Support") about whether setting `ysql_conn_mgr_use_auth_backend` to false is the recommended setting for your workload. [#25313](https://github.com/yugabyte/yugabyte-db/issues/25313) - Unix socket connections to YSQL Connection Manager are not supported. [#20048](https://github.com/yugabyte/yugabyte-db/issues/20048) - Connection manager is not supported or included in the MacOS YugabyteDB releases, and there are currently no plans to support it. - The logical client version increment caused by an ALTER ROLE SET or ALTER DATABASE SET query is only observed by the YSQL Connection Manager instance on the node where the ALTER was executed; it is not yet propagated to Connection Manager instances on other nodes. As a result, the stale-backend TTL (`ysql_conn_mgr_alter_guc_stale_backend_ttl_ms`) takes effect only on that node, and stale backends on other nodes do not respect the TTL. New connections on all nodes continue to observe the settings of the latest ALTER. For more information on how ALTER statements are handled, see [ALTER ROLE SET and ALTER DATABASE SET commands](#alter-role-set-and-alter-database-set-commands "ALTER ROLE SET and ALTER DATABASE SET commands"). [#30425](https://github.com/yugabyte/yugabyte-db/issues/30425)