The old trick

In my childhood we once had to move flats. We had a piano. It was HEAVY. Nobody could carry it. We tried three times, and on the third try the piano won.

Years ago in SQL Server, changing a partitioned table was painful. So we cheated. We created a new table with the right structure, left the old one alone, and put a UNION ALL view on top of both. The change took seconds and no data moved. Everyone was happy, the DBA most of all.


The new problem

This week I had to change the table engine type and sort key on a ClickHouse table. ClickHouse doesn't let you do that on an existing table. You can add columns to the ORDER BY, but you can't reorder or replace them.

So: new table, correct structure, move the data. Simple.

The table was 4 TB.

The first attempt failed. The second failed too. After the third I started to take it personally.


The rediscovery

Then I found the Merge engine. It's my old UNION ALL view, in ClickHouse. It stores no data. You give it a list of tables (or a regex), and it reads from all of them as one table.


-- 1. Move the old table out of the way
RENAME TABLE events TO events_old;

-- 2. New table with the correct sort key
CREATE TABLE events_v2 ( ... )
ENGINE = MergeTree
ORDER BY (new, sort, key)
TTL event_time + INTERVAL 7 DAY;

-- 3. "View" with the original name, so readers change nothing
CREATE TABLE events AS events_v2
ENGINE = Merge(currentDatabase(), '^events_(old|v2)$');

Writers switch to events_v2. Readers keep querying events and see both tables.


Things worth knowing


Reads only. You can't insert into a Merge table. Point writers ( usually your Materialized View) at the new table.


Each table keeps its own indexes. Queries on events_old use the old sort key, queries on events_v2 use the new one. ClickHouse reads both in parallel.


Columns must be compatible. Same names and types, or at least ones that can be cast.


_table virtual column tells you which table a row came from. Useful for debugging and for filtering: WHERE _table = 'events_v2'.


The best part


The table has a 1-week TTL. In seven days events_old empties itself. Then I drop it, rename events_v2 to events, drop the Merge table, and nobody remembers any of this happened.


Zero bytes moved. Four terabytes, and I didn't carry a single one.