Agnostic AGP Tiers for Routing Query Workloads

Different Settings or Separate ClickHouse Clusters

Shared-cluster routing fits workloads needing the same data environment with different resource allowances. Separate-cluster routing fits workloads needing different compute destinations. Choose the boundary around the actual workload: changing query settings may address resource pressure, while changing the destination introduces another database environment. A tier policy needs to account for both the resources a query consumes and the data it must reach.

Time-sensitive workloads need available workers and sufficient database capacity. A more generous processing allowance can help a demanding query, although concurrent work still consumes shared resources. Where workloads need separate compute capacity, workers pointing to different clusters provide another configuration path. The destination must already offer the tables, functions, and permissions the query requires.

Execution Tiers, PostgreSQL, and Workers

An AGP execution carries its tier as a string assigned at creation. PostgreSQL stores the queued execution records the API creates, while workers poll that orchestration database for work, run SQL against ClickHouse, update execution status, and store successful results for retrieval. Tier-specific workers connect these layers through their configured settings and cluster destinations. Keep the tier name associated with an explicit worker policy so the routing label has a clear operational meaning.

Queue Delay and Query Runtime

Queue delay sits outside ClickHouse query execution time: a request can remain PENDING before a worker starts the database work.

A query execution limit governs database processing after that work begins. An application deadline also needs allowance for queueing and result delivery. Raising the runtime allowance addresses a query needing more execution time; a request waiting for a worker needs attention to worker availability and routing. Record these delays separately when assessing a tier, because a longer database timeout cannot establish a shorter wait in the queue.

Configure Query Processing, Memory, and Runtime

A query resource policy needs separate controls for parallel processing, memory, and runtime. Increasing one allowance does not establish extra capacity elsewhere.

Query Processing Threads

ClickHouse's max_threads limits parallel query-processing threads, excluding threads retrieving data from remote servers. The setting describes permitted processing parallelism, without reserving a fixed number of CPU cores. Actual use depends on the query pipeline and available work. A larger limit can increase resource demand, so judge it alongside the concurrent queries the cluster must support.

Query Memory

A nonzero max_memory_usage limits tracked query memory on a single server; zero leaves this setting unrestricted. It does not cover every allocation: ClickHouse does not fully track some aggregate states involving String and Array arguments. Distributed execution applies relevant restrictions on individual servers. Account for concurrent queries and other server work when setting an allowance; a per-query limit does not describe the machine's total memory budget.

Execution Limits

ClickHouse expresses max_execution_time in seconds. Its enforcement can use projected execution time, depending on timeout_before_checking_execution_speed; a zero value for that setting makes the execution limit use clock time. ClickHouse checks timeouts at designated processing points, so actual runtime can exceed the configured limit. Set the timeout policy alongside the duration allowance, especially when the application requires a complete result.

Constrain Settings at the Database

ClickHouse settings constraints can preserve an execution policy when a database user attempts to change its resource settings.

Allowed Values

Settings profiles can define minimum and maximum values or make a setting unchangeable. ClickHouse rejects a change violating an active constraint and leaves the setting unchanged. A worker's preferred query settings must fit those constraints. Keep deployment values explicit when a resource boundary matters, because inherited defaults and database policies also affect the active configuration.

Worker Session Settings

The system.settings table exposes the current session's settings. Inspect the context the worker actually uses, including its database identity and active profiles. A session opened under another user can show different values. This distinction matters when the intended tier allowance differs from the settings the database permits its worker to use.

Can a Tier Change Which Blockchain Data a Query Uses?

A tier can change the ClickHouse cluster handling a query when its assigned workers connect to another cluster. That destination determines which datasets the query can reach under its database permissions. SQL queries still require the tables and columns their destination exposes. Data decoding also requires the appropriate functions and input data in that environment. Equivalent schema names across clusters do not establish equivalent records or freshness, so moving workers requires attention to dataset coverage and decoding capabilities, particularly when an analysis depends on recently indexed records.

Measure Resource Use on the Target Cluster

ClickHouse's system.query_log records query execution events according to its logging configuration. Its measurements include processing duration, memory usage, and errors. The query_duration_ms field uses milliseconds. Changed query settings appear in its Settings field when log_query_settings enables that recording. These values help distinguish an insufficient allowance from a query consuming resources for another reason; recorded changes do not replace the session's complete effective configuration.

The system.processes table exposes running queries and their elapsed time in seconds, alongside memory measurements. Some dedicated memory may fall outside its reported usage. Inspect the servers the distributed query actually uses. AGP execution records and ClickHouse query logs describe different layers, so correlating them requires the identifiers the integration actually records. A database measurement alone does not describe how long a request waited in the orchestration queue.

How Do Query Limits Affect Result Completeness?

ClickHouse can throw an exception or stop with a partial result when a query limit uses its corresponding overflow policy. For limits supporting these modes, throw reports an exception, while break stops processing and can return the rows processed so far. Some operations cannot produce a safe partial result after a timeout and stop without producing one. Complete-result applications need a policy matching that requirement. A successful execution status alone does not establish an untruncated answer when the database permits partial output. Preserve any truncation condition when interpreting a tier's results.

Shared-Cluster and Separate-Cluster Configurations

Worker policies can vary without giving every execution tier its own database environment. Different AGP workers can use distinct ClickHouse settings or separate clusters. These configuration choices describe resource routing; dataset availability follows the destination and its permissions.

Shared-Cluster and Separate-Cluster Configurations

Worker policies can vary without giving every execution tier its own database environment. Different AGP workers can use distinct ClickHouse settings or separate clusters. These configuration choices describe resource routing; dataset availability follows the destination and its permissions.

• Resource Policy (Worker Configuration) Shared cluster with common settings: Workers use the same configured query allowances. Shared cluster with different settings: Tier-specific workers use distinct query allowances. Separate cluster destinations: Tier-specific workers connect to different ClickHouse clusters.
• Dataset Access (Worker Configuration) Shared cluster with common settings: Queries reach data accessible on the shared cluster. Shared cluster with different settings: Data access follows the shared cluster and database permissions. Separate cluster destinations: Each destination must expose the query's required datasets.

View image file

Tier Changes and Ongoing Maintenance

Tier maintenance means keeping the execution labels, worker policies, and database environments consistent. Record the settings and destinations associated with each tier before changing capacity. Preserve a useful record of the active configuration alongside measurements, so a change in performance has an identifiable context.

Database upgrades and profile changes deserve the same attention as worker changes. Review the effective settings and overflow policies after either change. Revisit dataset access when a worker moves to another cluster, and keep query logging available where operational measurements depend on it. A routing change can otherwise obscure whether a new behavior comes from resource policy or the data environment.

AGP's Bookkeeper detects dead workers and recovers stuck executions. Worker retirement therefore needs to account for recovery as well as replacement capacity. Recovery handles interrupted orchestration; sufficient workers and database resources still determine whether the remaining workload can proceed. Keep stored-result expiration aligned with the period applications need those results.

Capacity, Storage, and Operational Cost

A tier label does not establish the cost of running its assigned workload. Compute demand follows query execution and concurrency, while stored-result volume and retention affect storage needs. PostgreSQL orchestration and each ClickHouse environment also require operational capacity. Separate cluster destinations add environments to maintain, even when they improve workload separation. Base capacity changes on queue delay and database measurements together. Keep the result-retention budget separate from the query timeout. An application may need a completed output after database execution has ended, so its storage policy must cover that access period.