How to TRUNCATE TABLE in ClickHouse

Galaxy Glossary

How do I truncate (empty) a table in ClickHouse?

TRUNCATE TABLE instantly deletes all rows from a ClickHouse table while keeping its schema intact.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

Example H2

Example H3

When should I use TRUNCATE TABLE instead of DROP?

Use TRUNCATE TABLE when you need to empty data quickly but still need the table definition, privileges, and metadata. DROP TABLE removes both data and structure, forcing you to recreate the table later.

How does TRUNCATE TABLE work internally?

ClickHouse removes entire data parts from disk in a metadata-only operation, so the command is nearly instantaneous and does not log individual row deletions. Replicated tables broadcast the action to replicas.

What is the basic syntax?

Use TRUNCATE TABLE [db.]table. Optional clauses include IF EXISTS, ON CLUSTER, and SYNC/ASYNC/NO DELAY to control cluster propagation speed.

How do I truncate an ecommerce table?

To clear all orders before a bulk reload, run:
TRUNCATE TABLE Orders;

Can I truncate on a cluster?

Yes. Example:
TRUNCATE TABLE Orders ON CLUSTER analytics_cluster SYNC; guarantees all nodes finish before the statement returns.

What happens to table metadata?

Columns, codecs, engine settings, and permissions remain unchanged. Only data parts are removed.

Is TRUNCATE TABLE transaction-safe?

ClickHouse lacks full ACID transactions; TRUNCATE is atomic at the table level but cannot be rolled back after execution.

Best practices for TRUNCATE TABLE

1. Wrap in a BEGIN/COMMIT block only if your client library requires explicit statement grouping.2. Use IF EXISTS in automation scripts to avoid errors.
3. Add SYNC on clusters to ensure downstream jobs read a consistent state.

Common pitfalls and how to fix them

Missing ON CLUSTER clause: The command runs only on the local replica, leaving other nodes with stale data. Always specify ON CLUSTER when working in distributed setups.

Accidental production truncation: Lack of a WHERE clause means all rows are dropped. Protect critical tables with separate user roles that do not have TRUNCATE privileges.

Performance considerations

Because ClickHouse drops parts instead of rows, TRUNCATE is O(1) relative to table size. The primary cost is metadata update and, on replicated tables, background part removal.

Can I truncate only one partition?

Yes. Use ALTER TABLE [db.]table DROP PARTITION id instead of TRUNCATE when you need targeted cleanup while keeping other partitions intact.

Why How to TRUNCATE TABLE in ClickHouse is important

How to TRUNCATE TABLE in ClickHouse Example Usage


-- Empty the OrderItems table before re-importing
TRUNCATE TABLE OrderItems;
-- Clear Products table on all nodes without waiting for confirmation
TRUNCATE TABLE Products ON CLUSTER analytics_cluster ASYNC;

How to TRUNCATE TABLE in ClickHouse Syntax


TRUNCATE [TABLE] [IF EXISTS] [db.]table
    [ON CLUSTER <cluster_name>]
    [SYNC | ASYNC | NO DELAY];
-- Ecommerce example on a cluster
TRUNCATE TABLE IF EXISTS Orders ON CLUSTER analytics_cluster SYNC;

Common Mistakes

Running TRUNCATE TABLE on one replica without ON CLUSTER, leaving other nodes untouched. Always include ON CLUSTER for distributed tables.
Using TRUNCATE TABLE instead of partition drops when only a subset of data should be removed, leading to unintended full-table wipes.