YSQL Connection Manager: Why Active Queries Still Need Backend Connections

A customer recently asked a great question about YSQL Connection Manager (YCM):

“I thought Connection Manager would pool my connections so the database would not be overloaded. But during a connection-heavy workload, my client connections and server connections looked almost the same. Why didn’t YCM reduce the backend connection count?”

That question gets right to the heart of how YSQL Connection Manager works.

YCM is very helpful when an application has many client sessions, but only a subset of those sessions are actively doing work at any given moment.

That is the classic connection pooling problem:

● Many client sessions.
● Many are idle.
● A smaller number are actively running SQL.
● Backend server connections can be reused.

But if every client session is actively running a query at the same time, YCM has much less opportunity to multiplex. Active work still needs backend execution capacity.

In other words:

● YCM can reduce the cost of idle or intermittently active connections.
● YCM does not make unlimited active query concurrency free.

That distinction is important.

Related YugabyteDB Tips

For more background on YSQL Connection Manager sizing and validation, see these earlier tips:

👉 Right-sizing connections in YugabyteDB with YSQL Connection Manager (YCM)

👉 Am I actually using YSQL Connection Manager? (YCM)

The Customer Question

Let’s make the question generic:

“We are using YSQL Connection Manager and ran a connection-heavy workload. We expected the database to pool client connections and avoid backend connection pressure. But when the workload ran, we still saw backend/server connections climb close to the client connection count. Why?”

The short answer:

Because those were not mostly idle client sessions. They were active sessions running queries.

YCM can multiplex idle or waiting client sessions across fewer backend server connections. But when a client is actively running SQL inside a transaction, YCM needs to attach that client session to a backend server connection.

If many client sessions are all active at the same time, the backend pool can still fill up.

That does not mean YCM is broken.

It means the workload is creating active query concurrency, not just client connection concurrency.

Why This Matters

Applications often have a large theoretical connection count.

For example:

● A service has 50 application pods.
● Each pod has a connection pool size of 20.
● The theoretical maximum is 1,000 client connections.

But that does not mean all 1,000 sessions are active at the same time.

In a normal application workload, many sessions are idle between requests. YCM can help by allowing many logical client connections while keeping the number of backend server connections lower.

But load tests can behave differently.

A test harness may open many sessions and immediately run queries on all of them at once. That creates a burst of active concurrency.

That is a very different pattern.

Important Distinction

YCM helps reduce backend connection pressure when many client sessions are idle or intermittently active. If many clients are actively running SQL at the same time, those active requests still need backend execution capacity.

A Simple Mental Model

Think of YCM as managing two sides:

● Client connections:
- The logical connections coming from the application.
● Backend/server connections:
- The physical PostgreSQL/YSQL backend connections used to execute SQL.

When a client is idle, it does not need to hold a backend connection.

When a client starts active work, YCM attaches it to a backend connection.

When the transaction completes, that backend connection can return to the pool and be reused.

Here is the simplified behavior:

Client State	What YCM Can Do	Backend Connection Impact
Idle	Detach from backend connection.	Low
Waiting between requests	Reuse backend connections for other clients.	Low to moderate
Actively running SQL	Attach to a backend connection.	Higher
Many active queries at once	Queue or wait for backend capacity.	Can reach configured limits

This is why connection count alone is not enough.

You also need to understand connection behavior.

1. Are the connections idle?
2. Are they active?
3. Are they in transactions?
4. Are they running short queries?
5. Are they holding backend connections for a long time?

The Smart Driver Question

Another common question is:

“Do we need the YugabyteDB Smart Driver for YCM to work?”

No, the smart driver is not required for YCM itself.

YCM runs on the YugabyteDB node and manages client/backend connection pooling on that node.

But the smart driver is still strongly recommended because it helps distribute application connections across the cluster.

That means:

● The smart driver helps spread client connections across nodes.
● YCM helps pool and manage connections on each node.

They solve different parts of the problem, and they work well together.

Key Insight

The smart driver distributes connections across YugabyteDB nodes. YSQL Connection Manager manages pooling on the node that receives those connections. For best results, use both together.

Demo: Showing the Difference Between Idle Connections and Active Queries

Let’s create a small demo using yugabyted.

We do not need hundreds of connections to make the point. In fact, a smaller demo is easier to understand.

The demo will show:

1. YCM is enabled.
2. Multiple idle client sessions can share a smaller number of backend connections.
3. Multiple active queries can still consume backend connection capacity.

Step 1: Start YugabyteDB with YCM Enabled

For demo purposes, we will enable YSQL Connection Manager and use a few YCM-related settings. The exact effect of individual pool-sizing flags can vary by version and configuration, so this demo focuses on the observable behavior: idle logical connections versus active SQL execution.

				
					yugabyted destroy --base_dir=/tmp/yb-ycm-demo 2>/dev/null

yugabyted start \
  --base_dir=/tmp/yb-ycm-demo \
  --ui false \
  --tserver_flags="enable_ysql_conn_mgr=true,ysql_conn_mgr_max_client_connections=50,ysql_conn_mgr_idle_time=15"

What these settings do:

Flag	Demo Value	Purpose
`enable_ysql_conn_mgr`	`true`	Enables YSQL Connection Manager.
`ysql_conn_mgr_max_client_connections`	`50`	Allows up to 50 client connections into YCM for this demo.
`ysql_conn_mgr_idle_time`	`15`	Closes idle backend/server connections after 15 seconds.

Demo Setting, Not a Recommendation

The value ysql_conn_mgr_max_conns_per_db=3 is intentionally tiny so the demo is easy to see. Do not treat this as a production sizing recommendation.

Step 2: Confirm We Are Using YCM

From the command line:

				
					ysqlsh -h 127.0.0.1 -p 5433 -c "SHOW yb_is_client_ysqlconnmgr;"

Expected result:

				
					.yb_is_client_ysqlconnmgr
--------------------------
 on

If this returns on, the client connection is going through YSQL Connection Manager.

Step 3: Create a Demo User and Demo Table

For this demo, create a normal application user instead of using the yugabyte superuser for the client connection tests.

This is important because some connection/session behaviors can cause YCM sessions to become sticky. A sticky connection stays pinned to a physical backend connection, which prevents YCM from multiplexing that session.

Using a normal demo user keeps the test closer to a typical application connection pattern.

First, create the demo user:

				
					ysqlsh -h 127.0.0.1 -p 5433 -U yugabyte -c "
DROP USER IF EXISTS ycm_user;
CREATE USER ycm_user;
GRANT ALL ON DATABASE yugabyte TO ycm_user;
"

Now create a small demo table:

				
					ysqlsh -h 127.0.0.1 -p 5433 -U yugabyte -c "
DROP TABLE IF EXISTS ycm_demo;

CREATE TABLE ycm_demo (
  id int PRIMARY KEY,
  payload text
);

INSERT INTO ycm_demo
SELECT i, repeat('x', 100)
FROM generate_series(1, 1000) AS i;

GRANT SELECT ON ycm_demo TO ycm_user;
"

Step 4: Demo A – Many Idle Client Connections

Now open 20 client sessions using the non-superuser demo account.

Each session connects through YCM, runs a tiny statement, and then stays connected but idle.

This distinction matters. If you run this test as the yugabyte superuser, the sessions may become sticky and hold physical backend connections, which hides the pooling behavior we are trying to demonstrate.

				
					for i in $(seq 1 20); do
  {
    echo "SELECT 'client ${i} connected through YCM' AS msg;"
    sleep 60
  } | ysqlsh -h 127.0.0.1 -p 5433 -U ycm_user -d yugabyte > /tmp/ycm_idle_${i}.log 2>&1 &
done

Now check the YCM connection metrics:

				
					curl -s http://127.0.0.1:13000/connections

Example output:

				
					{
    "database_name": "yugabyte",
    "user_name": "ycm_user",
    "active_logical_connections": 0,
    "queued_logical_connections": 0,
    "waiting_logical_connections": 20,
    "active_physical_connections": 0,
    "idle_physical_connections": 6,
    "sticky_connections": 0
}

This is the behavior we wanted to demonstrate.

There are 20 logical client connections, but they are idle from YCM’s perspective. Since those clients are not actively running SQL, YCM does not need 20 physical backend connections.

Instead, the pool has only a smaller number of idle physical connections available for reuse.

The important values are:

Metric	Demo Value	What It Means
`waiting_logical_connections`	`20`	Twenty client sessions are connected through YCM and waiting for work.
`active_physical_connections`	`0`	No backend connections are actively executing SQL at this moment.
`idle_physical_connections`	`6`	YCM is keeping a smaller number of backend connections open for reuse.
`sticky_connections`	`0`	The client sessions are not pinned to dedicated backend connections.

This is where YCM shines.

The number of logical client connections is higher than the number of physical backend connections because the clients are connected but idle.

That is the pooling behavior we wanted to show.

Watch Out for Sticky Connections

For this demo, use a normal application user instead of the yugabyte superuser.

Some session behaviors can cause YCM connections to become sticky, meaning the logical client session stays pinned to a physical backend connection. If sticky_connections rises with your client count, YCM cannot multiplex those sessions the way this demo is intended to show.

Step 5: Demo B – Active Query Burst

Now run 10 active queries at the same time using the non-superuser demo account.

Each query calls pg_sleep(30), which keeps the query active long enough for us to inspect YCM metrics while the workload is running.

This test is intentionally different from the idle connection test in Step 4.

In Step 4, the clients were connected but idle. YCM could keep those sessions as logical connections without needing one physical backend connection per client.

In this step, the clients are actively running SQL at the same time.

Active SQL execution requires backend connection capacity.

				
					for i in $(seq 1 10); do
  ysqlsh -h 127.0.0.1 -p 5433 -U ycm_user -d yugabyte -c "
    SELECT ${i} AS client_id, pg_sleep(30);
  " > /tmp/ycm_active_${i}.log 2>&1 &
done

While the queries are still running, check the YCM connection metrics from another terminal:

				
					curl -s http://127.0.0.1:13000/connections

Example output from the yugabyte / ycm_user pool:

				
					{
    "database_name": "yugabyte",
    "user_name": "ycm_user",
    "active_logical_connections": 10,
    "queued_logical_connections": 0,
    "waiting_logical_connections": 0,
    "active_physical_connections": 10,
    "idle_physical_connections": 0,
    "sticky_connections": 0
}

This is the behavior we wanted to demonstrate.

In Step 4, we had 20 logical client connections sitting idle behind YCM, but YCM only needed a smaller number of physical/backend connections.

In Step 5, we have 10 active logical client connections and 10 active physical/backend connections.

Why?

Because these clients are not idle anymore. They are actively running SQL.

YCM can multiplex idle or waiting clients over fewer backend connections, but active SQL execution still needs backend execution capacity.

The important values are:

Metric	Demo Value	What It Means
`active_logical_connections`	`10`	Ten client sessions are actively running SQL through YCM.
`active_physical_connections`	`10`	Ten backend/server connections are actively executing SQL.
`queued_logical_connections`	`0`	No client sessions are currently waiting for an available backend connection.
`sticky_connections`	`0`	The sessions are not pinned. They are simply active, so they need backend execution capacity.

Notice that sticky_connections remained 0. That matters.

These sessions were not pinned to backend connections because of sticky session behavior. They needed backend connections because they were actively running SQL.

That is the distinction this demo is meant to show.

Now wait for the queries to finish, then check the query results:

				
					grep -H "client_id\|ERROR\|FATAL\|timeout" /tmp/ycm_active_*.log

Example output:

				
					/tmp/ycm_active_10.log: client_id | pg_sleep
/tmp/ycm_active_1.log: client_id | pg_sleep
/tmp/ycm_active_2.log: client_id | pg_sleep
/tmp/ycm_active_3.log: client_id | pg_sleep
/tmp/ycm_active_4.log: client_id | pg_sleep
/tmp/ycm_active_5.log: client_id | pg_sleep
/tmp/ycm_active_6.log: client_id | pg_sleep
/tmp/ycm_active_7.log: client_id | pg_sleep
/tmp/ycm_active_8.log: client_id | pg_sleep
/tmp/ycm_active_9.log: client_id | pg_sleep

This output shows that all 10 active queries completed successfully.

That is expected.

The point of this demo is not that YCM should fail the queries.

The point is that while the queries were active, the YCM metrics showed:

				
					active_logical_connections: 10
active_physical_connections: 10
sticky_connections: 0

That tells us the active query burst was not idle connection pressure. It was active SQL execution.

YCM did not mark the sessions as sticky, but while the queries were running, each active logical connection needed a physical/backend connection to execute SQL.

That is the key contrast with Step 4:

● Idle logical clients do not need to hold physical backend connections.
● Active SQL queries do.

Step 6: Interpreting the Demo

The two tests show two very different workload shapes.

In Step 4, we opened 20 client sessions, but those sessions were idle after running a simple statement.

YCM showed:

				
					waiting_logical_connections: 20
active_physical_connections: 0
idle_physical_connections: 6
sticky_connections: 0

That means YCM had 20 logical client sessions connected, but it did not need 20 active backend/server connections because those sessions were not actively running SQL.

In Step 5, we ran 10 active queries at the same time.

YCM showed:

				
					active_logical_connections: 10
active_physical_connections: 10
sticky_connections: 0

That means the sessions were not sticky, but they were active. Since each client was actively running SQL, each one needed backend execution capacity.

That is the key lesson.

YCM can reduce backend connection pressure when client sessions are idle, waiting, or intermittently active.

YCM cannot make active SQL execution free. If 10 clients are actively running queries at the same time, YugabyteDB still needs backend/server connections to execute those queries.

Demo Step	Workload Shape	Observed YCM Behavior	What It Means
Step 4	20 connected but idle client sessions	20 waiting logical connections, 6 idle physical connections, 0 active physical connections	YCM can keep many idle logical clients connected without needing one active backend connection per client.
Step 5	10 active client sessions running SQL	10 active logical connections, 10 active physical connections, 0 sticky connections	Active SQL execution still needs backend/server connections.

This is why a workload with many mostly idle client connections behaves very differently from a workload with many actively running query sessions.

The first case is mostly a connection management problem. The second case is an active concurrency problem.

For troubleshooting, the important question is not just:

“How many connections do we have?”

The better question is:

“What are those connections doing?”

Useful follow-up questions include:

● How many client sessions are open at peak?
● How many are actively running SQL at the same time?
● Are connections reused by an application pool?
● Are connections opened in large bursts?
● Are queries short-lived or long-running?
● Are transactions being left open?
● Are sessions becoming sticky?
● Is traffic evenly distributed across nodes?
● Is the YugabyteDB Smart Driver being used?
● Is the application connecting to one node, one endpoint, or multiple nodes?

Those details determine how much YCM can reduce backend connection pressure.

Key Insight

Connection count alone does not tell the full story. The important question is whether those connections are idle, waiting, sticky, or actively running SQL.

Final Takeaway

YSQL Connection Manager is very useful, but it is important to understand what problem it is solving.

YCM helps reduce backend connection pressure when client sessions are idle, waiting, or intermittently active. But when many clients are actively running SQL at the same time, those queries still need backend/server connections to execute.

That is why the customer’s observation makes sense.

If the workload opens many connections but most are idle, YCM can multiplex those logical sessions over fewer physical connections.

If the workload opens many connections and all of them are actively running queries, the database still needs backend execution capacity for that active work.

So when troubleshooting connection pressure, do not stop at the connection count.

Ask what the connections are doing.

Have Fun!