ClickHouse / ClickHouse/ClickHouse
Metrics for Outgoing PostgreSQL Client Connections
- Dominant language
- C++
- Stars
- 49.9k
- Forks
- 9k
- Avg merge
- 21h 32m
- Merged PRs (30d)
- 515
Description
### Use case
ClickHouse today exposes metrics for **incoming** PostgreSQL connections when it acts as a server (e.g. `PostgreSQLConnection`, `PostgreSQLRejectedConnection`, `PostgreSQLThreads`).
However, there is *no telemetry* for the opposite direction—connections opened **by** ClickHouse to remote PostgreSQL instances when using the `PostgreSQL` table/database engines, `MaterializedPostgreSQL`, or the `postgresql()` table function. This leaves SREs blind when trying to monitor pool utilisation, diagnose saturation (`FATAL: too many connections` on the Pg side), or validate that idle‑timeout settings are behaving as intended.
### Describe the solution you'd like
Expose a small set of *CurrentMetrics* and/or *asynchronous metrics* that reflect ClickHouse’s role as a PostgreSQL client. Suggested counters:
| Metric | Type | Semantics |
|--------|------|-----------|
| `PostgreSQLClientConnections` | *CurrentMetrics* | Number of TCP sessions in the connection pool across all pg engines. |
| `PostgreSQLClientConnectionsIdle` | *CurrentMetrics* | Subset of the above that are idle (no query running). |
| `PostgreSQLClientConnectionsInUse` | *CurrentMetrics* | Subset currently executing a query. |
| `PostgreSQLClientConnectionsCreated` | *ProfileEvents* | Cumulative connections ever opened. Useful for spotting thrashing when idle time‑outs are too aggressive. |
All metrics should be collected per‑shard (same as existing metrics) and become available in:
- `system.metrics`
- `system.asynchronous_metrics`
- Prometheus `/metrics` endpoint (prefixed like `ClickHouse_PostgreSQLClientConnections`).
### Rationale / Use‑cases
* Capacity planning – alert when pool close to `postgresql_connection_pool_size`.
* Cost reduction – verify that `postgresql_connection_pool_auto_close_connection` actually reaps idle sessions.
* Debugging – correlate spikes in queries with spikes in Pg connection churn.
### Describe alternatives you've considered
SREs can scrape `pg_stat_activity` on every upstream PostgreSQL instance or parse ClickHouse logs. Both add operational complexity and miss per‑engine granularity.
### Additional context
* Existing server‑side metrics documented in [system.metrics](https://clickhouse.com/docs/operations/system-tables/metrics) only cover incoming connections.
* Similar *client* metrics already exist for Kafka (`KafkaConsumers`, `KafkaConsumersInUse`) and MySQL (`MySQLConnection`).
Contributor guide
Assessment
This issue has not been assessed yet.