Asking ClickHouse for REPLACE TABLE

In August 2017 there was no way to delete rows from a MergeTree table in place. If you wanted rid of some rows you rebuilt the table: create a twin, fill it with what you want to keep, drop the original, rename the twin. I filed #1102 asking for the whole dance to be one statement. It was implemented three years and four months later, and the delay is the interesting part: the feature was not hard to specify, it was impossible to do safely until something else existed.

Four statements, one gap

CREATE TABLE events_new AS events;
INSERT INTO events_new SELECT * FROM events WHERE event_type != 'bot';
DROP TABLE events;
RENAME TABLE events_new TO events;

Between the DROP and the RENAME there is a window in which events does not exist, and every reader or writer that arrives in it gets UNKNOWN_TABLE. If the script dies in the middle you must work out by hand which of the two tables holds the truth. A second, quieter problem: any rows inserted into events while the INSERT ... SELECT is running are lost when the original is dropped. This is the shape of the request:

REPLACE TABLE myOldTable SELECT * FROM myOldTable WHERE CounterID < 12345;

“Behind the scenes, CH can do the creating, dropping and renaming” was how I put it.

What the maintainers said

The reply came the next day from alexey-milovidov, and it was a plan for a family of statements rather than one: TRUNCATE TABLE, CREATE OR REPLACE TABLE and REPLACE TABLE, with this caveat: “The main downside is that in first implementation they will be supported for all table engines except Replicated- tables. This is because for Replicated tables, CREATE and DROP just add or remove single replica for table.”

That last sentence is the design tension in miniature. In a replicated setup DROP TABLE is local: it removes this replica, not the table. A statement defined as “create temporary, fill, drop old, rename” therefore has no meaning for the cluster-wide table unless something coordinates it.

I offered to help and then admitted I was not good enough at C++. The issue sat. Other users came with their own reasons. tetafro: “The usecase is switching application to another table by re-creating view … and application (which queries my_table) seamlessly switches from one table to another.” Someone wanted multi-table RENAME in a transaction; filimonov pointed out that RENAME TABLE a1 TO b1, a2 TO b2 already renames several tables at once but quoted the source comment that it is atomic only “in cases when there was no exceptions during this process and server does not fall”. So in 2018 the atomicity of a rename was conditional on nothing going wrong.

On 2020-05-26 the maintainer wrote: “Now this feature can be easily implemented on top of DatabaseAtomic. @tavplubix said that he is ready to take.” The Atomic database engine gives every table a UUID, stores its data under the UUID rather than the name, and makes RENAME and EXCHANGE TABLES a metadata swap that is safe against a crash in the middle. With that underneath, REPLACE is no longer a dance; it is an exchange.

PR #18521, “Implement [CREATE OR] REPLACE TABLE”, by tavplubix, was opened on 2020-12-25 and merged on 2020-12-27 (the issue had picked up a gift label four days earlier). The review had one note, from alexey-milovidov: a concurrent test “when one thread is doing CREATE OR REPLACE and another is doing CREATE/DROP will be also useful (I’m not sure that there is no logical race conditions when CREATE OR REPLACE can throw exception)”. The merge commit is in the 21.1 prestable tag and not in 20.12, so 21.1 is the first release.

How it works

The current doCreateOrReplaceTable in InterpreterCreateQuery.cpp does what the old script did, in a safer order:

1. Create a hidden table named _tmp_replace_<hash>_<random> in the same database, from the new definition.
2. Run the SELECT into it. The original table is untouched and fully available.
3. EXCHANGE the two names (for REPLACE TABLE, which fails if the target does not exist). For CREATE OR REPLACE TABLE it uses a plain rename when the target does not exist yet.
4. Drop the temporary name, which now holds the old table.

If anything fails before step 3, the temporary table is dropped and the original is where you left it. That is the difference from the script. On 26.4, a failing select:

REPLACE TABLE events ENGINE = MergeTree ORDER BY (event_type, event_time)
AS SELECT * FROM events WHERE throwIf(user_id = 3, 'boom') = 0;   -- Code: 395, boom

SELECT count() FROM events;                                       -- 9: nothing lost
SELECT name FROM system.tables WHERE database = currentDatabase();   -- events only, no stranded temp

And the swap is a real swap, not an in-place rewrite. In an Atomic database the table’s UUID changes:

CREATE DATABASE shop ENGINE = Atomic;
-- create shop.events and insert 9 rows (3 of them 'bot') ...
SELECT 'before', substring(toString(uuid), 1, 8), total_rows FROM system.tables WHERE database = 'shop' AND name = 'events';
REPLACE TABLE shop.events ENGINE = MergeTree ORDER BY (event_type, event_time)
AS SELECT * FROM shop.events WHERE event_type != 'bot';
SELECT 'after', substring(toString(uuid), 1, 8), total_rows FROM system.tables WHERE database = 'shop' AND name = 'events';

before  ea9af9c5  9
after   edd73658  6

The dependency on Atomic is explicit. In a database with a nil UUID (for example the Memory engine, or the deprecated Ordinary), the statement refuses:

CREATE DATABASE m ENGINE = Memory;
CREATE OR REPLACE TABLE m.t (x UInt8) ENGINE = Memory;
Code: 80. DB::Exception: CREATE OR REPLACE TABLE query is supported only for Atomic databases. (INCORRECT_QUERY)

A database created with a plain CREATE DATABASE is Atomic on 26.4, so most people never see this.

Traps in what shipped

The engine is not inherited. I assumed that REPLACE TABLE events AS SELECT ... would keep the table’s definition, since the point was to filter rows. It does not: the new table is a new CREATE, so everything not written is a default. Without an ENGINE clause:

REPLACE TABLE events AS SELECT * FROM events WHERE event_type != 'bot';
SHOW CREATE TABLE events;

ENGINE = MergeTree
ORDER BY tuple()

The sorting key (event_type, event_time) is gone; you now have a table with no primary index and every query scans everything. Spell out the full definition, or use the EXCHANGE route below.

Concurrent writes are lost. Rows inserted into the old table after the SELECT starts and before the swap are not in the new table, and the old table is dropped. This follows from the mechanism rather than from a test I ran: REPLACE is not a lock. For a table under continuous ingestion you need to pause writers, or accept that the window exists.

A new table, not the same one. Anything attached to the old table’s identity needs a check. Materialized views that read the replaced table are the example: the 26.5 changelog fixes silent data loss after EXCHANGE TABLES or CREATE OR REPLACE TABLE of a materialized view’s source table (#105029), a regression from an earlier change.

Cleanup after a crash, and platform edges. Two open reports show the edge cases of a temp-table-then-swap design: #124833, a server killed inside the exchange window on a plain_rewritable database disk cannot restart until a leftover _tmp_replace_* file is removed by hand; and #83524, a report of REPLACE TABLE failing with a file-system rename error in the Docker image with a bind-mounted data directory on macOS (a comment says it works on Linux). I did not reproduce either.

Privileges. The temp name is random, so no grant can cover it; until #117967 (26.9) a non-wildcard user got ACCESS_DENIED. Since that fix, the check is against the user-visible name: CREATE and DROP of its own kind, plus INSERT when the statement populates.

Alternatives, and when each wins

Want	Use	Trade-off
Drop some rows, keep the table	DELETE FROM / ALTER TABLE ... DELETE (mutations and lightweight deletes)	No new table, no gap; masks or rewrites parts in the background. Right for a small fraction of rows.
Change engine, ORDER BY, codecs, keep the name	REPLACE TABLE with a full definition	Rewrites everything once; atomic swap; loses concurrent writes.
Same, but control the definition separately	CREATE TABLE new ...; INSERT ...; EXCHANGE TABLES events AND events_new; DROP TABLE events_new	Same atomic swap, you own every step and the ability to catch up writes before the exchange.
Empty it	TRUNCATE TABLE	Removes parts; keeps definition.
Make a definition match a script	CREATE OR REPLACE TABLE	Silent: replaces whatever is there, data included.

EXCHANGE TABLES is the primitive; REPLACE TABLE is the convenience. Run on 26.4 (the default database of clickhouse local), the manual route (CREATE TABLE events_new AS events, insert the filtered rows, exchange) leaves events with click 3, view 3 and events_new with the old nine rows, to be dropped.

Lightweight deletes mean the original motivation, “I only want to remove some rows”, mostly has a better answer today. What REPLACE TABLE kept is the other half: changing a table’s shape while its name stays continuously available. For the materialized-view sibling see CREATE OR REPLACE for materialized views, which landed in 26.5 on the same machinery.

What it teaches

A feature can be specified in an afternoon and still be blocked for years by a missing primitive. REPLACE TABLE needed names to be separate from storage, and that is what the Atomic engine did. The first replies already named the second half of the problem: replication, where “drop” does not mean what a user means by it. The statement that shipped is honest about both: it is an atomic metadata swap, available where there is a UUID to swap, and it does nothing about the writes that arrive while it is working.