ClickHouse DB::Exception: No free connection (Code: 203)

The "DB::Exception: No free connection" error in ClickHouse occurs when all connections in a connection pool have been consumed and no free connection is available to serve a new request. This error corresponds to ClickHouse error code NO_FREE_CONNECTION (error code 203) and is raised by the connection pool used to reach remote ClickHouse replicas, most commonly when querying a Distributed table that fans out to many shards and replicas. The same pool exhaustion can also surface for other pooled connections, such as those to external dictionary or table-function data sources.

Impact

New queries that require a connection from the exhausted pool will fail until existing connections are released. This can create a cascading effect where a backlog of queries builds up, leading to timeouts and widespread query failures across applications that depend on the affected connection pool.

Common Causes

1. Too many concurrent queries — a sudden spike in concurrent queries that all need connections to the same external resource or remote shard.
2. Slow remote server — the target server is responding slowly, causing connections to be held longer than usual and preventing them from returning to the pool.
3. Connection pool size too small — the configured connection pool limit is insufficient for the workload.
4. Connection leaks — a bug or misconfiguration causes connections to not be properly returned to the pool.
5. Network issues — connectivity problems that cause connections to hang without timing out.
6. Distributed queries across many shards — queries that fan out to many shards simultaneously can exhaust the pool.

Troubleshooting and Resolution Steps

1. Check current connection usage in ClickHouse:

   SELECT * FROM system.metrics WHERE metric LIKE '%Connection%';
   

2. Identify the queries consuming connections:

   SELECT query_id, user, elapsed, query
   FROM system.processes
   ORDER BY elapsed DESC;
   

3. Check the distributed connection pool size. The pool used for remote shards/replicas is controlled by the distributed_connections_pool_size profile setting (default 1024), not by a per-replica element in remote_servers:

   SELECT name, value FROM system.settings
   WHERE name = 'distributed_connections_pool_size';
   

4. Increase the connection pool size if the current limit is too low. Raise distributed_connections_pool_size in the user/profile configuration (users.xml or a profile drop-in):

   <profiles>
       <default>
           <distributed_connections_pool_size>2048</distributed_connections_pool_size>
       </default>
   </profiles>
   

   This is a per-server profile setting; new sessions pick up the change.

5. Kill long-running queries that may be holding connections:

   KILL QUERY WHERE elapsed > 300 AND query LIKE '%remote%';
   

6. Check the remote server's health to ensure it is responding normally:

   clickhouse-client -h remote-host -q "SELECT 1"
   

7. Review connection-related settings:

   SELECT name, value FROM system.settings
   WHERE name LIKE '%connection%' OR name LIKE '%pool%';
   

Best Practices

• Size connection pools based on expected peak concurrent query load with a reasonable buffer.
• Set appropriate query timeouts to prevent long-running queries from monopolizing connections.
• Monitor connection pool utilization and set up alerts before the pool is fully exhausted.
• Use connection pooling middleware or load balancers for external database connections where possible.
• Implement query queuing or rate limiting at the application level to prevent connection storms.
• Regularly review and optimize slow queries that hold connections for extended periods.

Frequently Asked Questions

Q: How do I determine the right connection pool size?
A: Monitor your peak concurrent query count over time using system.metrics and system.processes. Set the pool size to at least 1.5 to 2 times your observed peak to handle spikes. Adjust based on monitoring data.

Q: Does this error affect only distributed queries?
A: No. While it is most common with distributed queries and remote table engines, any connection pool in ClickHouse can be exhausted, including pools for external dictionary sources and table functions that connect to external databases.

Q: Will increasing the pool size cause memory issues?
A: Each connection consumes some memory, but the overhead is typically small (a few KB per connection). However, a very large pool combined with high concurrency could lead to increased memory usage on both the ClickHouse server and the remote target. Monitor memory after changes.

Q: Can connection pool exhaustion cause data loss?
A: No. This error only prevents new queries from establishing connections. It does not affect data integrity. Queries that are already running will continue, and failed queries can be retried once connections become available.