Replica-aware routing - ClickHouse Documentation

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.

Requires the HTTP interfaceReplica-aware routing is applied at the proxy layer over the HTTP/HTTPS interface. ClickHouse Cloud is transitioning replica-aware routing from session_id to the X-ClickHouse-Replica-Tag header. The tabs below describe both methods during the rollout.Replica-aware routing is currently unavailable over the native protocol (native port, e.g. the clickhouse-go driver in its default native mode). Native-protocol clients must switch to HTTP and send the routing value on every request.

Prerequisites

Your service needs 2 or more replicas. On a single-replica service, there’s nothing to pin to.
Available on Enterprise by default when the feature is GA.
Supported on standard ClickHouse Cloud services. BYOC isn’t supported yet.

Configuring replica-aware routing

Open a support ticket and ask to enable HTTP-based sticky replica routing. Include your service ID and why you need it (temporary tables, session state, cache reuse, or read-after-write consistency). Before migrating an existing service, ask Support to confirm that header-based routing is enabled for it. Continue using session_id until you receive confirmation; X-ClickHouse-Replica-Tag won’t provide sticky routing until the rollout reaches your service. No restart is required.

HTTP-based routing

X-ClickHouse-Replica-Tag (preferred)
session_id (legacy)

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.

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 an X-ClickHouse-Replica-Tag header, then reuse the same header 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.For broader guarantees across all replicas, you can also set select_sequential_consistency to 1 on ClickHouse Cloud.

Check which replica you hit

Run the SELECT hostName() example again with the same X-ClickHouse-Replica-Tag value. You should get the same hostname while the number of replicas remains unchanged. A different header value may map to a different replica.

X-ClickHouse-Replica-Tag is replacing session_id for replica-aware routing. Continue using session_id until Support confirms that header-based routing is enabled for your service.

Concurrent requests fail with SESSION_IS_LOCKED

Because session_id creates a ClickHouse HTTP session, only one query can run within a given session at a time.
After header-based routing is enabled for your service, workloads that only need replica affinity can switch to X-ClickHouse-Replica-Tag. Concurrent requests can share the same replica tag.
If you need ClickHouse HTTP session state, serialize requests sharing a session_id.

To pin a workload to a replica, send a session_id query parameter on the HTTPS interface. The proxy uses consistent hashing on the parameter 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 session_id can be any string you choose, such as an application name, user ID, or workload label. Requests without session_id keep normal load balancing.Set the session_id query parameter on each request:

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

For clickhouse-go (v2), set Protocol: clickhouse.HTTP and pass session_id as a setting. The driver sends it as a URL query parameter.

Read-after-write consistency with `session_id`

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 session_id, then reuse the same session_id 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.For broader guarantees across all replicas, you can also set select_sequential_consistency to 1 on ClickHouse Cloud.

Check which replica you hit with `session_id`

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

Legacy subdomain-based routing

Subdomain-based routing is no longer enabled on new services. If you already use sticky subdomains, contact Support to migrate to the HTTP header method.

How legacy subdomain-based routing works

Previously, enabling replica-aware routing allowed a wildcard subdomain on top of the service hostname. For a service with the host name abcxyz123.us-west-2.aws.clickhouse.cloud, any hostname matching *.sticky.abcxyz123.us-west-2.aws.clickhouse.cloud (e.g. aaa.sticky.abcxyz123.us-west-2.aws.clickhouse.cloud) was hashed by Envoy to a consistent replica. The original hostname continued to use LEAST_CONNECTION load balancing, the default routing algorithm.

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.

Replica-aware routing isn’t workload isolation

Sticky routing only controls which replica handles a request. That replica may still serve other traffic. For dedicated compute, use compute-compute separation.

Private Link and the legacy subdomain method

HTTP-based routing works with private networking on your normal service hostname. No extra DNS entries are required. The legacy subdomain method doesn’t: you must add DNS for the *.sticky.* hostname pattern, and incorrect setup can imbalance load across replicas.

Replica-aware routing requires the HTTP protocol

Sticky routing is keyed on an HTTP header or query parameter, depending on the routing method available for your service. The native binary protocol doesn’t carry either value for the HTTP proxy to hash on, so replica-aware routing isn’t available over the native protocol. Native-protocol clients must move the relevant workload to the HTTP interface to use this feature.

Troubleshooting

Queries still land on different replicas with the same routing value

Confirm that you’re using the routing method available for your service: the X-ClickHouse-Replica-Tag header or the legacy session_id URL query parameter.
Confirm that every request uses exactly the same routing value.
Wait briefly after enablement. It can take under a minute to take effect.
Check whether the number of replicas recently changed; remapping is expected after scaling. Use SELECT hostName() to discover the new mapping.

​Prerequisites

​Configuring replica-aware routing

​HTTP-based routing

​Read-after-write consistency

​Check which replica you hit

​Read-after-write consistency with session_id

​Check which replica you hit with session_id

​Legacy subdomain-based routing

​Limitations of replica-aware routing

​Stickiness changes when the replica count changes

​Replica-aware routing isn’t workload isolation

​Private Link and the legacy subdomain method

​Replica-aware routing requires the HTTP protocol

​Troubleshooting

Prerequisites

Configuring replica-aware routing

HTTP-based routing

Read-after-write consistency

Check which replica you hit

Read-after-write consistency with `session_id`

Check which replica you hit with `session_id`

Legacy subdomain-based routing

Limitations of replica-aware routing

Stickiness changes when the replica count changes

Replica-aware routing isn’t workload isolation

Private Link and the legacy subdomain method

Replica-aware routing requires the HTTP protocol

Troubleshooting