ClickHouse Cloud Cost: How Expensive Queries Become a Very Large Bill, Measured and Fixed

What we measured

Expensive ClickHouse queries, measured against their rewrites

The lab is a single node with 2 vCPU and 7 GiB of RAM, just under one ClickHouse Cloud compute unit, running ClickHouse 26.8.11.7 LTS. The table holds 1,000,000,000 rows across six monthly partitions, ordered by (tenant_id, event_time); every query reads one month, about 165 million rows. Each query ran twice and we kept the second run, with the query condition cache disabled where it would have hidden the cost of the first pattern.

CPU time comes from ProfileEvents['OSCPUVirtualTimeMicroseconds'] in system.query_log. To turn it into money we divide by 7,200 vCPU-seconds per compute-unit hour and multiply by the Scale list price. That gives a floor: the CPU-equivalent cost of one million executions if the service ran at 100% utilisation and billed nothing else. Real ClickHouse Cloud cost is higher because capacity is provisioned, but the ratios carry straight through.

The last row is the one we would rather not have, and it stays in. Swapping an exact distinct count for an approximate one is standard advice, and at this cardinality it made the query slower. Advice that is not measured on your own data is how teams end up paying for rewrites that do nothing.

Pattern 2

Rollups recomputed from raw rows: ClickHouse Cloud cost paid on every refresh

A dashboard that shows events per tenant per day does not need 165 million rows each time it loads. A materialised view maintains the aggregate as data arrives, and the dashboard reads about ten thousand pre-aggregated rows instead. In the lab that cut CPU per execution from 0.800 to 0.006 seconds.

-- Target table holds partial aggregate states, merged in the background.
CREATE TABLE IF NOT EXISTS analytics.events_daily
(
    tenant_id  UInt16,
    day        Date,
    events     AggregateFunction(count),
    errors     AggregateFunction(countIf, UInt8),
    users      AggregateFunction(uniqCombined(12), UInt32)
)
ENGINE = AggregatingMergeTree()
PARTITION BY toYYYYMM(day)
ORDER BY (tenant_id, day)
SETTINGS index_granularity = 8192;

-- Fires on every INSERT into analytics.events; existing rows need a one-off backfill.
CREATE MATERIALIZED VIEW IF NOT EXISTS analytics.mv_events_daily
TO analytics.events_daily
AS
SELECT
    tenant_id,
    toDate(event_time)                AS day,
    countState()                      AS events,
    countIfState(http_status >= 500)  AS errors,
    uniqCombinedState(12)(user_id)    AS users
FROM analytics.events
GROUP BY tenant_id, day;

-- The dashboard query: merge the states, never touch raw rows.
SELECT tenant_id, day, countMerge(events) AS events
FROM analytics.events_daily
WHERE day >= '2026-06-01'
GROUP BY tenant_id, day
ORDER BY tenant_id, day;

The view moves work from read time to insert time, so it is not free. Every insert now does a small aggregation, and on ClickHouse Cloud that runs on the same billed compute. For a table that is written once and read thousands of times a day, the trade is overwhelmingly in favour of the view, and the ClickHouse Cloud cost of the dashboard drops with it.

Find them first

Attributing ClickHouse Cloud cost to query fingerprints

ClickHouse Cloud writes system.query_log on each replica, so attribution has to read all of them through clusterAllReplicas('default', …). The query below ranks fingerprints by CPU over seven days and converts CPU-seconds into compute-unit hours at whatever price you pass in. It is the first query we run on any ClickHouse Cloud cost engagement.

-- Top query fingerprints by CPU across every replica, last 7 days.
-- Run with: clickhouse client --param_unit_price=0.2985   (your tier's $ per unit-hour)
SELECT
    normalizedQueryHash(query)                                          AS fingerprint,
    any(user)                                                           AS sample_user,
    count()                                                             AS executions,
    round(sum(ProfileEvents['OSCPUVirtualTimeMicroseconds']) / 1e6, 1)  AS cpu_seconds,
    round(cpu_seconds / 7200, 3)                                        AS unit_hours,   -- 1 unit = 2 vCPU
    round(unit_hours * {unit_price:Float64}, 2)                         AS cpu_equivalent_usd,
    formatReadableSize(sum(read_bytes))                                 AS bytes_read,
    formatReadableSize(sum(result_bytes))                               AS bytes_returned,
    formatReadableSize(max(memory_usage))                               AS peak_memory,
    substring(any(query), 1, 100)                                       AS example
FROM clusterAllReplicas('default', system.query_log)
WHERE type = 'QueryFinish'
  AND event_date >= today() - 7
  AND query_kind = 'Select'
GROUP BY fingerprint
ORDER BY cpu_seconds DESC
LIMIT 20;

The column to argue about is cpu_equivalent_usd. It is a floor, not the invoice: it ignores idle capacity, the second replica and storage. Its value is ranking. When a handful of fingerprints dominate the list, most of the avoidable ClickHouse Cloud cost sits in queries someone can name and fix this week.

Two more queries explain the service-level multipliers. The first shows CPU demand per five-minute window, which is what vertical autoscaling reacts to. The second lists who is querying outside working hours, which is what stops a service from idling.

-- CPU demand per 5-minute window: the signal behind vertical autoscaling.
SELECT
    toStartOfFiveMinutes(event_time)                                          AS window_start,
    count()                                                                   AS queries,
    round(sum(ProfileEvents['OSCPUVirtualTimeMicroseconds']) / 1e6 / 300, 2) AS avg_busy_vcpus,
    formatReadableSize(max(memory_usage))                                     AS peak_query_memory
FROM clusterAllReplicas('default', system.query_log)
WHERE type = 'QueryFinish'
  AND event_date >= today() - 1
GROUP BY window_start
ORDER BY avg_busy_vcpus DESC
LIMIT 20;

-- Who keeps the service awake overnight: every user query resets the idle timer.
SELECT
    toHour(event_time)             AS hour_utc,
    user,
    client_name,
    http_user_agent,
    count()                        AS queries,
    substring(any(query), 1, 60)   AS example
FROM clusterAllReplicas('default', system.query_log)
WHERE type = 'QueryStart'
  AND event_date >= today() - 7
  AND (toHour(event_time) < 6 OR toHour(event_time) >= 20)
GROUP BY hour_utc, user, client_name, http_user_agent
ORDER BY queries DESC
LIMIT 20;

The overnight list is usually short and embarrassing: a health check sending SELECT 1 every minute, a BI tool refreshing an unused dashboard, an exporter on a cron. Each one costs the difference between 730 billed hours and the hours anyone actually works, which on an idle-capable service is often the largest single ClickHouse Cloud cost saving available.

-- Result bytes by user and interface: the egress side of ClickHouse Cloud pricing.
-- Run with: --param_egress_per_gb=0.1152   (public-internet rate; inter-region is lower)
SELECT
    user,
    interface,
    count()                                                       AS queries,
    formatReadableSize(sum(result_bytes))                         AS returned,
    round(sum(result_bytes) / 1e9 * {egress_per_gb:Float64}, 2)   AS egress_usd_if_public
FROM clusterAllReplicas('default', system.query_log)
WHERE type = 'QueryFinish'
  AND event_date >= today() - 30
GROUP BY user, interface
ORDER BY sum(result_bytes) DESC
LIMIT 20;

Then configure the service

Idling, replica limits and separate compute: service-level ClickHouse Cloud cost controls

Once queries are cheap, service settings start to pay. Idling lets a service stop billing compute when no user queries arrive; the timeout can be as low as five minutes, though services with long start-up times get a longer adaptive minimum. Vertical autoscaling limits stop a bad week from resizing every replica. Both are set per service in the console or through the ClickHouse Cloud API.

# Cap vertical autoscaling and enable idling for one service.
# Credentials are placeholders; the API key needs control-plane:service:manage-scaling-config.
curl -sS -X PATCH \
  --user "${CH_CLOUD_KEY_ID}:${CH_CLOUD_KEY_SECRET}" \
  -H 'Content-Type: application/json' \
  "https://api.clickhouse.cloud/v1/organizations/${CH_ORG_ID}/services/${CH_SERVICE_ID}/replicaScaling" \
  -d '{
        "minReplicaMemoryGb": 16,
        "maxReplicaMemoryGb": 64,
        "idleScaling": true,
        "idleTimeoutMinutes": 15
      }'
# Rollback: send the previous values, recorded from the console before the change.

Order matters. Lowering the maximum replica memory while expensive ClickHouse queries are still running does not reduce cost; it converts cost into slow dashboards and memory-limit errors. Idling also has a price: connections can time out while the service wakes, so applications need retries. Background merges, high part counts and inserts on another read-write service in the same warehouse can keep a service awake.

For mixed workloads, compute-compute separation lets a read-only service serve dashboards from the same storage while ingestion runs elsewhere. Compute is priced the same for every service in the warehouse and storage is billed once. The read-only service skips background merges, can idle on its own schedule, and can be sized for the dashboard peak instead of the ingest peak, which keeps ClickHouse Cloud cost proportional to each workload.

Checklist

The ClickHouse Cloud cost checklist

None of this argues against ClickHouse Cloud. For many teams a managed service with idling and per-minute billing is cheaper than running clusters themselves, and we support customers on both. The point is narrower: expensive ClickHouse queries are a ClickHouse Cloud cost problem first, and the fix is in the SQL. Test every change on a staging copy of your own workload, and keep the rollback for every service setting before you apply it.

Related reading: our guides to advanced ClickHouse troubleshooting, how ClickHouse query execution works and why ClickHouse is so fast, plus the ClickHouse documentation on billing and automatic scaling.

Prices: ClickHouse Cloud list prices for AWS us-east-1 as shown on clickhouse.com/pricing on 2 October 2026; regions, contracts and committed-spend discounts differ. Measurements: ChistaDATA lab, 2 vCPU, ClickHouse 26.8.11.7 LTS, 1,000,000,000-row MergeTree table, second of two runs, server-side CPU from system.query_log. Worked examples are illustrative arithmetic, not customer data.