Replica-aware routing

Replica-aware routing (also known as sticky sessions, sticky routing, or session affinity) routes related requests to the same ClickHouse replica. Use it when you need temporary tables or named session state to stay reachable across queries, when you want related queries to reuse the same replica’s local caches, or when you need read-after-write consistency across a write and its follow-up reads. It’s best-effort and doesn’t guarantee isolation. The proxy maps each routing value to one replica. The mapping remains stable while the number of replicas remains unchanged; scaling the service can map the value to a different replica. Replica-aware routing is available over both interfaces:

Over HTTP/HTTPS, using the X-ClickHouse-Replica-Tag header.
Over the native protocol, using a TLS Server Name Indication (SNI) override.

Both are enabled separately and use the same consistent hashing behind the proxy.

Prerequisites

Your service needs 2 or more replicas. On a single-replica service, there’s nothing to pin to.
An Enterprise tier service.
Supported on standard ClickHouse Cloud services and BYOC

Configuring replica-aware routing

Enterprise customers enable replica-aware routing from the service settings page in the ClickHouse Cloud console. Open your service, go to Settings, and turn on the toggle for the interface you want:

One toggle enables HTTP-based routing on the X-ClickHouse-Replica-Tag header.
A separate toggle enables native-protocol routing on the SNI override.

Enable either or both. No restart is required, and it can take under a minute to take effect. The toggles are rolling out across Enterprise tier plans. If they aren’t on your service yet, open a support ticket with your service ID to have the feature turned on earlier.

HTTP-based routing

To pin a workload to a replica, send an X-ClickHouse-Replica-Tag header on the HTTPS interface. The proxy uses consistent hashing on the header value, so requests sharing it go to the same replica while the number of replicas remains unchanged. A different value hashes independently and may land on the same or a different replica, but you don’t choose which replica a value maps to. Use your existing service hostname. No special sticky hostnames or DNS changes are required. The header value can be any string you choose, such as an application name, user ID, or workload label. Requests without the header keep normal load balancing. Set the X-ClickHouse-Replica-Tag header on each request:

echo 'SELECT hostName()' | curl \
  -H 'X-ClickHouse-Replica-Tag: my-workload-1' \
  -H 'X-ClickHouse-User: default' \
  -H 'X-ClickHouse-Key: <password>' \
  'https://<host>:8443/' -d @-

For clickhouse-go (v2), set Protocol: clickhouse.HTTP and pass the header with the HttpHeaders connection option.

X-ClickHouse-Replica-Tag provides replica affinity without creating a ClickHouse HTTP session. Concurrent requests can reuse the same tag without encountering SESSION_IS_LOCKED.

Native-protocol routing

Over the native protocol, pass the routing value as a TLS server name of the form <routing-value>.sticky.<host>. Connect to your regular service hostname as usual. ClickHouse Client takes the routing value through --tls-sni-override:

clickhouse client \
  --host <host> \
  --secure \
  --tls-sni-override my-workload-1.sticky.<host> \
  --query 'SELECT hostName()'

--host is your regular service hostname, --secure turns on TLS, and --tls-sni-override carries the routing value. TLS is required. No extra certificate or DNS entry is needed.

Read-after-write consistency

On a multi-replica service, a write on one replica may not be visible on the others until replication catches up. Send your write with a routing value, then reuse that same value on follow-up reads. The proxy routes both to the same replica, so you read your own write even while other replicas are still behind. This pattern works for workloads that write and then immediately read back the same data, such as interactive applications or ETL jobs that validate inserts before moving on. It also helps after a schema change that hasn’t yet replicated, since reusing the routing value keeps inserts on a replica that already has the new schema. Over HTTP, reuse the header value:

# Write, tagged with a routing value
echo "INSERT INTO events VALUES (now(), 'signup')" | curl \
  -H 'X-ClickHouse-Replica-Tag: my-workload-1' \
  -H 'X-ClickHouse-User: default' \
  -H 'X-ClickHouse-Key: <password>' \
  'https://<host>:8443/' -d @-

# Read it back on the same replica, using the same value
echo 'SELECT count() FROM events' | curl \
  -H 'X-ClickHouse-Replica-Tag: my-workload-1' \
  -H 'X-ClickHouse-User: default' \
  -H 'X-ClickHouse-Key: <password>' \
  'https://<host>:8443/' -d @-

Over the native protocol, reuse the SNI override:

# Write with a routing value
clickhouse client \
  --host <host> \
  --secure \
  --tls-sni-override my-workload-1.sticky.<host> \
  --query "INSERT INTO events VALUES (now(), 'signup')"

# Read it back on the same replica, using the same value
clickhouse client \
  --host <host> \
  --secure \
  --tls-sni-override my-workload-1.sticky.<host> \
  --query 'SELECT count() FROM events'

For broader guarantees across all replicas, you can also set select_sequential_consistency to 1 on ClickHouse Cloud.

Check which replica you hit

Run one of the SELECT hostName() examples again with the same routing value. You should get the same hostname while the number of replicas remains unchanged. A different routing value may map to a different replica.

Limitations of replica-aware routing

Stickiness changes when the replica count changes

Scaling out or in changes the routing hash ring. Requests sharing the same routing value may then land on a different replica. If you rely on temporary tables or session-level settings, be ready to recreate them after a remap. SELECT hostName() always tells you which replica you’re on.