clickhouse-pool

clickhouse-pool is a lightweight, thread-safe connection pool for ClickHouse, built on top of clickhouse-driver. It maintains a warm set of reusable Client connections behind a simple pull/push API, so multi-threaded Python applications avoid the overhead of establishing a new ClickHouse connection on every query.

Modeled closely on psycopg2’s connection pool, it exposes a get_client() context manager for exception-safe checkout, enforces a configurable maximum connection count, and cleans up all open connections on shutdown, making it a minimal, focused utility rather than a full ORM or query builder.

What You Get

• A thread-safe ChPool class that wraps clickhouse_driver.Client instances in a reusable pool
• A get_client() context manager for exception-safe connection checkout and release
• Configurable connections_min/connections_max with a TooManyConnections error when the pool is exhausted
• A cleanup() method that disconnects every open connection and closes the pool

Common Use Cases

• Multi-threaded ingestion workers pulling a pooled client per task instead of opening a fresh connection per thread
• Web APIs backed by ClickHouse checking out a pooled client per request via get_client()
• Batch analytics jobs capping connections_max to stay under the ClickHouse server’s max_connections limit
• Long-running daemons calling pool.cleanup() on shutdown to deterministically close every connection

Under The Hood

Architecture The library is a single-module implementation (clickhouse_pool/pool.py) exposing one class, ChPool, which manages a pool of clickhouse_driver.Client instances behind a threading.Lock. Internal state is split into _pool (idle clients), _used (a dict mapping opaque integer keys to checked-out clients), and _rused (a reverse map from client id() back to key) — a design directly mirrored from psycopg2’s connection-pool implementation. pull()/push() form the core check-out/check-in cycle, get_client() wraps them in a contextmanager for exception-safe checkout inside a with block, and cleanup() disconnects every client and marks the pool closed. The whole surface fits in one file, with connections_min pre-warmed at construction and connections_max enforced by raising TooManyConnections when exhausted.

Tech Stack Pure Python (3.9+), packaged with Poetry (pyproject.toml, poetry-core build backend). The single runtime dependency is clickhouse-driver, the official pure-Python ClickHouse client, which pool.py wraps directly via Client(**self.connection_args). Dev dependencies cover pytest for testing, pylint/black for linting and formatting, and sphinx plus sphinx-rtd-theme for documentation builds. There is no web framework, ORM, or CLI involved — it is a narrowly scoped utility library distributed on PyPI.

Code Quality tests/test_pool.py covers the core behaviors: context-manager checkout/release, connections_min pre-warming, connections_max exhaustion raising TooManyConnections, and connection release on error. Tests run against a live ClickHouse instance rather than mocks, so they require a running server locally. No CI workflow or type-checker configuration was found in the clone, and type hints are present but partial. Naming is clear and consistent, and error handling uses a small custom exception hierarchy (ChPoolError, TooManyConnections) rather than bare exceptions, though cleanup() does swallow disconnect() failures with a broad except clause.

API Design The API is deliberately minimal and modeled on psycopg2’s connection pool, which will feel familiar to Python developers coming from Postgres. get_client() as a context manager is the primary entry point and requires essentially no boilerplate beyond a with block around client.execute(), mirroring the underlying clickhouse_driver.Client API directly so pooling is added transparently. It isn’t architecturally novel — it’s a straightforward pooling wrapper — but it fills a real gap, since clickhouse-driver itself ships with no built-in pooling.