Reducing storage & cost

Most ClickHouse bills grow from data nobody is reading: tables abandoned months ago, raw events that should have aged into a colder tier, and columns compressing far below their potential. This playbook shows how to find the reclaimable bytes and who is spending them.

Symptoms

• Disk usage climbs every week but nobody added a new workload.
• A few tables dominate total size, and some have not been queried in months.
• Storage cost is impossible to attribute to a team or product.
• Old partitions sit on expensive hot disks instead of cold object storage.

Step 1 — Find the biggest tables

`system.parts` holds the on-disk size of every active part. Aggregate it per table to see exactly where your bytes live.

SELECT
    database,
    table,
    formatReadableSize(sum(bytes_on_disk)) AS size,
    sum(rows)                              AS rows,
    count()                                AS parts
FROM system.parts
WHERE active
GROUP BY database, table
ORDER BY sum(bytes_on_disk) DESC
LIMIT 20;

Step 2 — Find tables nobody reads

A large table is only waste if no one queries it. Cross-reference the table list against the tables touched in query_log over the last 90 days — anything large that never appears is a drop or archive candidate.

WITH read_tables AS (
    SELECT DISTINCT arrayJoin(tables) AS t
    FROM system.query_log
    WHERE type = 'QueryFinish'
      AND event_time > now() - INTERVAL 90 DAY
)
SELECT
    p.database,
    p.table,
    formatReadableSize(sum(p.bytes_on_disk)) AS size
FROM system.parts AS p
WHERE p.active
  AND concat(p.database, '.', p.table) NOT IN (SELECT t FROM read_tables)
  AND p.database NOT IN ('system', 'information_schema')
GROUP BY p.database, p.table
ORDER BY sum(p.bytes_on_disk) DESC;

Step 3 — Age cold data into a cheaper tier

You rarely need to delete data — you need to stop paying hot-disk prices for it. A TTL TO VOLUME rule moves old partitions to cold object storage automatically while keeping them queryable.

-- Move partitions older than 90 days to a 'cold' volume
ALTER TABLE events
MODIFY TTL ts + INTERVAL 90 DAY TO VOLUME 'cold';

-- Or delete data older than a year outright
ALTER TABLE events
MODIFY TTL ts + INTERVAL 365 DAY DELETE;

Step 4 — Attribute the cost

To control spend you need to know whose workload drives it. query_log carries the user and can be grouped to build a simple chargeback of read volume per team or service.

SELECT
    user,
    formatReadableSize(sum(read_bytes)) AS bytes_read,
    count()                             AS queries
FROM system.query_log
WHERE type = 'QueryFinish'
  AND event_time > now() - INTERVAL 30 DAY
GROUP BY user
ORDER BY sum(read_bytes) DESC;

How OptiHouse automates this