The problem
Queries on a ClickHouse Distributed table can be slow, fail with errors, or return incomplete results.
- Slow reads: A query may scan every shard even when the filter matches data on only one. It can also route to a slow replica or stall on an unavailable shard.
- Subquery errors: Queries with an IN or JOIN subquery reading from another Distributed table often fail with errors like
DISTRIBUTED_IN_JOIN_SUBQUERY_DENIED. - Silent insert delays: By default, distributed inserts send data to remote shards in the background. The client can receive a success response before remote delivery finishes.
Each issue requires a specific setting. Changing all distributed settings at once hides the root cause.
This article explains how to inspect distributed queries and tune the settings that control shard selection, subqueries, inserts, and parallel reads.
Why it happens
A Distributed table does not store data itself. It acts as a unified view over data stored across multiple shards.
When you query a Distributed table, the receiving node acts as a coordinator. It forwards the query to the target shards, collects their partial results, merges them, and returns the final response.
This architecture introduces four common bottlenecks:
Shard coordination: Every contacted shard adds network and coordination overhead. If a single shard responds slowly, the entire query waits for it.Replica routing: Each shard can have multiple replicas. ClickHouse chooses which replica to query based on load_balancing and locality rules, which may route traffic to a busy or distant node.Double-distributed subqueries: When a distributed query uses another Distributed table inside an IN or JOIN subquery, ClickHouse must decide how to execute it across the cluster. By default,distributed_product_modeis set to deny and blocks these queries to prevent excessive cluster load.Asynchronous inserts: Distributed inserts write to local disk first and forward data to remote shards in the background. Clients receive a success response immediately, even if remote delivery later stalls or fails.
How to confirm
Start by checking the cluster configuration. The system.clusters table shows the shards and replicas known to the server.
SELECT
cluster,
shard_num,
replica_num,
host_name,
port,
is_local
FROM system.clusters
WHERE cluster = 'cluster_name'
ORDER BY
shard_num,
replica_num;Replace 'cluster_name' with your actual cluster name.
Next, check the current values of the distributed query settings.
SELECT
name,
value,
changed
FROM system.settings
WHERE name IN
(
'prefer_localhost_replica',
'load_balancing',
'skip_unavailable_shards',
'optimize_skip_unused_shards',
'distributed_product_mode',
'distributed_foreground_insert',
'enable_parallel_replicas',
'max_parallel_replicas'
)
ORDER BY name;The value column shows the current setting for your session. The changed column shows whether the session value differs from its default.
Check the query log for slow queries and errors. Filtering out QueryStart captures both completed queries (QueryFinish) and failed queries (ExceptionBeforeStart and ExceptionWhileProcessing).
SELECT
event_time,
query_duration_ms,
read_rows,
result_rows,
exception_code,
exception,
query
FROM system.query_log
WHERE type != 'QueryS'
AND event_time >= now() - INTERVAL 1 HOUR
AND query_kind = 'Select'
ORDER BY event_time DESC
LIMIT 20;Look for high query durations, large numbers of read rows, and distributed query errors. Check the remote servers as well when the coordinator log does not show enough detail.
The settings
The following defaults can differ between releases. Verify them on your own server.
| Setting | Default | What it controls | Restart needed |
|---|---|---|---|
prefer_localhost_replica | true | Prefers a local replica when it is available | No |
load_balancing | random | Selects a replica for distributed queries | No |
skip_unavailable_shards | false | Allows queries to continue when a shard is unavailable | No |
optimize_skip_unused_shards | false | Skips shards that cannot contain matching data | No |
distributed_product_mode | deny | Controls distributed IN and JOIN subqueries | No |
distributed_foreground_insert | false | Sends distributed inserts synchronously | No |
enable_parallel_replicas | 0 | Enables parallel query execution across replicas; alias of allow_experimental_parallel_reading_from_replicas | No |
max_parallel_replicas | 1000 | Sets the maximum number of replicas for parallel query execution | No |
How to fix it
Choose a replica selection strategy
The load_balancing setting controls how ClickHouse selects a replica for a distributed query.The default value is random. This can work well when replicas have similar resources and performance.
Other available modes include nearest_hostname and in_order. Check the supported values on your target release before changing this setting.
For example, use nearest_hostname when network locality is important and replica hostnames reflect their network locations.
Set the value for one session:
SET load_balancing = 'nearest_hostname';You can also use prefer_localhost_replica to prefer a local replica when one is available.
SET prefer_localhost_replica = 1;Do not assume that a local replica is always the fastest. Check its resource usage and replication state before enabling this behavior.
Handle distributed subquery errors
The distributed_product_mode setting controls how ClickHouse handles certain subqueries on Distributed tables.
It supports four values:
deny: Rejects the double-distributed subquery. This is the default.local: Replaces the distributed table in the subquery with its local table.global: Rewrites the subquery as aGLOBAL INorGLOBAL JOIN.allow: Allows the distributed subquery to execute on the cluster.
Use local only when the data placement makes local execution correct. The relevant rows must be available on the corresponding local shards.
Use global when the subquery result must be calculated and distributed to the remote servers. This can increase network traffic and memory usage.
Use allow only when you understand the execution cost. It can cause repeated distributed work across shards.
For a single query, use:
SELECT
customer_id,
count()
FROM company.orders_distributed
WHERE customer_id IN
(
SELECT customer_id
FROM company.customers_distributed
WHERE status = 'active'
)
GROUP BY customer_id
SETTINGS
distributed_product_mode = 'global';Choose the mode based on the data layout and query logic. Do not change deny to allow just to remove the error.
Skip unused shards
A distributed query can contact every shard even when its filter matches data on only a few shards.
The optimize_skip_unused_shards setting allows ClickHouse to skip shards that cannot contain matching data. This optimization requires a suitable sharding key and a filter that ClickHouse can use to identify the relevant shards.
For example, assume the table uses cityHash64(customer_id) as its sharding key. A filter on customer_id can help ClickHouse identify the required shards.
Enable the optimization for one query:
SELECT
customer_id,
count()
FROM company.orders_distributed
WHERE customer_id = 1001
GROUP BY customer_id
SETTINGS
optimize_skip_unused_shards = 1;Check the table's actual sharding key before using this example. A filter on an unrelated column may not allow shard pruning.
Skip unavailable shards only when partial results are acceptable
The skip_unavailable_shards setting allows a distributed query to continue when a shard is unavailable.
SET skip_unavailable_shards = 1;Use this setting only when the application can accept incomplete results. It does not recover the missing data. It allows the query to return results from the available shards.
For financial reports, reconciliation, and other workloads that require complete results, keep this setting disabled.
Return distributed insert errors to the client
By default, ClickHouse can send data to remote shards in the background when inserting into a Distributed table.
The distributed_foreground_insert setting changes this behavior. When enabled, ClickHouse sends the data synchronously and waits for the remote insert operations.
Enable it for a session:
SET distributed_foreground_insert = 1;You can also enable it for one insert:
INSERT INTO company.orders_distributed
SELECT *
FROM company.orders_staging
SETTINGS
distributed_foreground_insert = 1;Use this option when the client must receive remote insert errors before the query returns. It can increase insert latency because the client waits for remote delivery.
This setting does not replace a retry strategy. Review the insert deduplication behavior before retrying a failed request.
Use parallel replicas for large scans
A distributed query normally uses one replica from each shard. Other replicas remain available for different queries.
Parallel replicas allow one query to use multiple replicas to process a large scan. ClickHouse divides the work and merges the results at the coordinator.
Enable parallel replicas for a large query:
SELECT
event_type,
count()
FROM company.events_distributed
GROUP BY event_type
SETTINGS
enable_parallel_replicas = 1,
max_parallel_replicas = 4;The example allows ClickHouse to use up to four replicas for parallel execution. Adjust this value to match the available replicas and workload.
Parallel replicas are useful for large scans. They may not improve short queries because coordination adds overhead. Check table replication, cluster configuration, and analyzer support before enabling this feature in production.
Apply settings through a profile
For a workload that needs consistent settings, create a settings profile in users.xml or a file under users.d.
The following example applies selected settings to a profile:
<clickhouse>
<profiles>
<distributed_read_profile>
<load_balancing>nearest_hostname</load_balancing>
<optimize_skip_unused_shards>1</optimize_skip_unused_shards>
<distributed_product_mode>global</distributed_product_mode>
<enable_parallel_replicas>1</enable_parallel_replicas>
<max_parallel_replicas>4</max_parallel_replicas>
</distributed_read_profile>
</profiles>
</clickhouse>Assign the profile to the required user through the existing user configuration. Do not replace other user settings when making this change.
These values are examples, not universal production defaults. In particular, use global only when it matches the subquery logic. Do not enable shard skipping unless the table has a compatible sharding key.
These settings do not require a server restart. Reload the configuration if you change the XML file, then reconnect the client to apply the profile.
How to verify the fix
Run the same query before and after the change. Keep the date range, filters, and data volume the same.
Compare the following values:
- Query duration.
- Rows read.
- Number of shards contacted.
- Replica response times.
- Query errors.
- Result row counts and aggregates.
Check the query log after each test.
SELECT
event_time,
query_duration_ms,
read_rows,
result_rows,
exception_code,
exception
FROM system.query_log
WHERE type = 'QueryFinish'
AND event_time >= now() - INTERVAL 30 MINUTE
ORDER BY event_time DESC
LIMIT 20;For shard pruning, compare the number of shards contacted before and after enabling optimize_skip_unused_shards.
For distributed subqueries, confirm that the query returns the expected rows. Compare its result with a known correct query or a controlled test dataset.
For foreground inserts, test an insert with an available shard and a controlled failure case. Confirm that the client receives the expected response.
Do not use query duration alone to confirm correctness. A faster query can still return incomplete data.
Risks and trade-offs
Distributed query settings affect data completeness, network traffic, and execution time.
load_balancingcan select replicas with different resource usage or replication states.distributed_product_mode = 'local'can return incorrect results when related data is not placed on corresponding shards.distributed_product_mode = 'global'can increase network traffic and memory usage.distributed_product_mode = 'allow'can cause repeated distributed work.skip_unavailable_shards = 1can return partial data without an error when some shards are unavailable.optimize_skip_unused_shardsneeds a compatible sharding key and filter.distributed_foreground_insert = 1can increase insert latency.Parallel replicasadd coordination and network overhead. They may not help small queries.
Test each change with production-like data before applying it to all users.
Summary
- A
Distributedtable sends work to shards and merges their results. - Use
load_balancingto control replica selection. - Use
distributed_product_modeto handle distributed subqueries correctly. - Enable shard skipping only when the sharding key supports the filter.
- Use foreground inserts when the client must receive remote insert errors.
- Use parallel replicas for large scans, and avoid skipping unavailable shards when complete results are required.



