ClickHouse / ClickHouse/ClickHouse

Metrics for Outgoing PostgreSQL Client Connections

Open
#85,031 1 comment 2 reactions 0 assignees View on GitHub
comp-postgresql easy task external feature
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

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.