How to pass very large ID sets (500K–5M) to ClickHouse queries using clickhouse-cs (.NET)? any industry guidlines?

[solve-ckulkarni]

I am using the official clickhouse-cs .NET driver with a ClickHouse cluster that has two nodes behind a network load balancer.

My use case requires filtering a query using a very large set of IDs (around 500K to 5M integers).

Using a large IN clause is not practical because the query is sent over HTTP and the request can become too large.

To maintain session affinity for operations like temporary tables, I am currently creating connections like this:

var sessionId = Guid.NewGuid().ToString();
var connectionStringWithSession = $"{_connectionString};UseSession=true;SessionId={sessionId}";
var connection = new ClickHouseConnection(connectionStringWithSession);
return connection;

However, since there is a load balancer in front of the ClickHouse nodes, relying on temporary tables may still be risky if queries land on different nodes.

I am trying to design a generic ClickHouse access library layer that multiple internal projects can use, so I am looking for a robust and scalable pattern that works well in production environments.

One approach I see often suggested is inserting IDs into a staging table and performing a JOIN, but before standardizing on that pattern, I wanted to ask:

• What approach do teams typically use in production ClickHouse deployments when they need to filter queries using very large ID sets (500K–5M)?
• Is the staging table + join pattern considered the best practice, or are there other patterns that work better for reusable library-level solutions?

Any insights from real-world deployments would be greatly appreciated.

[alex-clickhouse]

Hi,

For the session affinity issue there's work being done right now to create a mechanism that'll give users the ability to ensure that requests land on the same replica. Don't have an exact timeline for this atm though.

The IN approach is definitely not the way to go due to the limitations you noted, I think the staging table + join approach is the most appropriate here.

[sean-krail]

Is this session affinity work completed? Where can I track progress?

[solve-ckulkarni]

@alex-clickhouse ,
What about another approach where I can upload by data/input param file to S3 and use it in clickhouse using s3() table function. In that way, I am not utilizing any clickhouse resources to store params and also, I can apply data cleanup policies on s3 bucket.
I will checkout the latencies but what do you thinkl?

Also , I understand the staging table approach looks good, but for that I will check how good it supports concurrency insertions to staging table. Also, I have 2 nodes cluster then in how much time the data will be replicated as I am passing params to staging and immediately accesing them in subsequent query.

[alex-clickhouse]

Hi,

The S3 approach also seems viable, I think figuring out the optimal approach for your problem here (both for the S3 alternative and the cluster) will require some practical experimentation to see what works best for you.