ClickHouse CREATE DATABASE: Engines, Atomic, and Replicated

Q: What does the `Atomic` engine actually give me over `Ordinary`?

Non-blocking drop and rename, table swaps via EXCHANGE TABLES , UUID-based on-disk paths, and the ability to UNDROP a recently-dropped table. Ordinary is the legacy engine; new databases should always use Atomic (which is the default since ClickHouse 20.10).

Q: I dropped a table by mistake — can I get it back?

If it was on an Atomic database and you did not pass SYNC / NO DELAY , yes — for database_atomic_delay_before_drop_table_sec seconds after the drop (8 minutes by default). Run UNDROP TABLE (ClickHouse 23.3+). Check system.dropped_tables to confirm it is still recoverable and to grab its UUID if the name is ambiguous. Once the delay elapses the data is gone.

Q: Can I rename a database?

You can rename individual tables across databases ( RENAME TABLE a.x TO b.x ), and on Atomic databases this is atomic. Renaming the database itself is not directly supported; the workaround is to create a new database, move every table with RENAME , and drop the old database.

Q: How do I delete a database?

DROP DATABASE name . The default behavior is asynchronous on Atomic databases; pass SYNC to wait for completion. The database must be empty in the strict sense only if it has no tables you cannot drop.

To create a database in ClickHouse, run CREATE DATABASE <name>. The default engine is Atomic, which gives you UUID-based table storage, safe drop and rename semantics, and the ability to recover a recently-dropped table with UNDROP TABLE. Most installations need nothing more, but ClickHouse also ships several specialized database engines for replicated DDL across a cluster, for bridging to MySQL or PostgreSQL, and for lazy table loading. This guide covers the full syntax, the engines that matter in practice, and the patterns that hold up in production.

The Quick Answer

CREATE DATABASE analytics;

That creates an Atomic database called analytics. New tables live in analytics.* and inherit the engine's safer drop and rename behavior. You can verify with:

SELECT name, engine FROM system.databases;

Full CREATE DATABASE Syntax

CREATE DATABASE [IF NOT EXISTS] db_name
    [ON CLUSTER cluster]
    [ENGINE = engine(...)]
    [SETTINGS name = value, ...]
    [COMMENT 'description'];

Only the name is required. Everything else has reasonable defaults.

Database Engines

ClickHouse ships several database engines, each suited to a particular pattern.

Atomic (Default)

CREATE DATABASE analytics ENGINE = Atomic;
-- or equivalently:
CREATE DATABASE analytics;

The Atomic engine stores each table under a UUID-derived path on disk, which gives you:

Non-blocking DROP TABLE and RENAME TABLE.
EXCHANGE TABLES a AND b for atomic table swaps.
UNDROP TABLE for a configurable window after a drop.

For nearly all new databases, leave this as the default. The only reason to pick anything else is if you have a specific reason described below.

Replicated

CREATE DATABASE analytics ON CLUSTER my_cluster
ENGINE = Replicated(
    '/clickhouse/databases/{shard}/analytics',
    '{shard}',
    '{replica}'
);

The Replicated database engine replicates DDL statements through ClickHouse Keeper. Any CREATE TABLE, ALTER, DROP, or RENAME inside the database runs on every replica automatically; you no longer need ON CLUSTER on individual table statements. This is the cleanest way to keep cluster schemas in sync.

The three parameters are the Keeper path (where DDL coordination state lives), the shard name, and the replica name. The {shard} and {replica} macros are resolved per node. On ClickHouse Cloud the platform handles this transparently and you typically use the default database setup.

MySQL and PostgreSQL

CREATE DATABASE remote_app
ENGINE = MySQL('mysql-host:3306', 'app_db', 'reader', 'secret');

CREATE DATABASE remote_pg
ENGINE = PostgreSQL('pg-host:5432', 'app_db', 'reader', 'secret');

These engines expose every table in the remote database as a virtual ClickHouse table. You can SELECT from them as if they were local; ClickHouse pushes filters down where possible. Useful for ad-hoc joins between operational stores and ClickHouse analytics, but expect the latency of the upstream database for every query.

MaterializedPostgreSQL

CREATE DATABASE pg_mirror
ENGINE = MaterializedPostgreSQL('pg-host:5432', 'app_db', 'reader', 'secret')
SETTINGS materialized_postgresql_tables_list = 'users,orders';

MaterializedPostgreSQL streams logical replication from PostgreSQL into ClickHouse, materializing the upstream tables as ReplacingMergeTree. This gives you near-real-time replication without an external CDC tool.

SQLite

CREATE DATABASE analytics_lite
ENGINE = SQLite('/var/lib/clickhouse/sqlite/app.db');

Useful in test environments and for single-file analytical workloads. Rare in production.

Lazy

CREATE DATABASE old_logs ENGINE = Lazy(3600);

The Lazy engine keeps Log-family tables in memory only when they are queried, evicting them after the configured idle timeout (in seconds). Niche, but useful for databases with thousands of rarely-queried log tables.

SETTINGS

The Atomic engine accepts a couple of useful settings:

CREATE DATABASE huge_db
ENGINE = Atomic
SETTINGS lazy_load_tables = 1;

lazy_load_tables (default 0) defers loading each table's engine state until first access. For a database with thousands of tables of which only a handful get queried regularly, this cuts startup time and memory dramatically. The trade-off: system.tables reports TableProxy and NULL for sizes until each table is actually opened.

ON CLUSTER and Replication

CREATE DATABASE analytics ON CLUSTER my_cluster ENGINE = Atomic;

ON CLUSTER runs the statement on every node defined in the cluster, so the database exists everywhere. The Replicated database engine is a stronger guarantee: it not only fans out the create, but also keeps subsequent DDL synchronized.

Rule of thumb: use a Replicated database when you have lots of DDL and want it to "just work"; use plain Atomic databases with ON CLUSTER on individual statements when DDL is rare and version-controlled.

COMMENT and IF NOT EXISTS

CREATE DATABASE IF NOT EXISTS analytics
COMMENT 'Production analytics workload, owned by the data platform team';

COMMENT is supported on all engine types and surfaces in system.databases.comment. Use it. Six months from now, "who owns this database" is the first question someone will ask.

Switching Default Database

USE analytics;

The current session's default database controls unqualified table references. To set a per-user default, attach DEFAULT DATABASE analytics when creating the user.

Inspecting Databases

-- List
SELECT name, engine, comment FROM system.databases;

-- Reproducible definition
SHOW CREATE DATABASE analytics;

-- Tables in a database
SHOW TABLES FROM analytics;
SELECT count() FROM system.tables WHERE database = 'analytics';

Migrating From Ordinary to Atomic

Atomic has been the default database engine since ClickHouse 20.10. The legacy Ordinary engine was deprecated in 22.8, and since then ClickHouse refuses to create new Ordinary databases unless you explicitly set allow_deprecated_database_ordinary = 1. That setting is a stopgap and may be removed in a future release, so any database still running on Ordinary (typically the default database on clusters first installed years ago) should be migrated.

The supported path is the automatic on-startup conversion, available since 22.8. Drop a flag file into ClickHouse's flags directory and restart the server; every Ordinary database is converted to Atomic during startup:

sudo touch /var/lib/clickhouse/flags/convert_ordinary_to_atomic
sudo chmod 666 /var/lib/clickhouse/flags/convert_ordinary_to_atomic
sudo systemctl restart clickhouse-server

The conversion is fast because it renames metadata and assigns UUID-based paths rather than rewriting table data. A few things to check first:

Remove detached parts from every table in the Ordinary databases beforehand. Leftover detached/ directories can cause "directory not empty" errors mid-conversion.
Run the flag on every node of a cluster if you want all hosts converted; the flag is local to each server.
Verify afterward with SELECT name, engine FROM system.databases that nothing still reports Ordinary.

If you would rather migrate one database at a time without touching server config, do it manually: create a fresh Atomic database, move each table into it with RENAME TABLE, then drop the old database.

CREATE DATABASE analytics_new ENGINE = Atomic;
RENAME TABLE analytics_old.events TO analytics_new.events;
RENAME TABLE analytics_old.users  TO analytics_new.users;
-- repeat for every table, then:
DROP DATABASE analytics_old;

The manual route lets you rename the result back into place afterward and gives you a clean rollback point, but it does move the database name. For replicated tables, confirm the Keeper paths still resolve after the move, and review any ALTER automation that hard-codes the old database name.

UNDROP and the Drop Delay

On an Atomic database, DROP TABLE does not remove data immediately. The table's metadata is moved aside and the data sits untouched until a background task reclaims it after database_atomic_delay_before_drop_table_sec seconds (default 480, i.e. 8 minutes). Within that window the drop is reversible. UNDROP TABLE (added in ClickHouse 23.3) restores the table:

DROP TABLE analytics.events;

-- changed your mind, within the delay window:
UNDROP TABLE analytics.events;

Tables waiting out their delay are visible in system.dropped_tables, including the UUID and the time they were dropped:

SELECT database, table, uuid, table_dropped_time
FROM system.dropped_tables;

If two tables of the same name were dropped, disambiguate with the UUID from that view:

UNDROP TABLE analytics.events UUID '6f2a...-...';

Two things to keep in mind:

Passing SYNC (or the deprecated NO DELAY) to DROP TABLE bypasses the delay entirely and removes the data immediately, so an UNDROP is no longer possible. The same is true once the delay has elapsed.
Recovering a table backed by a materialized view without a TO clause means recovering its hidden inner table (.inner.<uuid>) separately; UNDROP the inner table as well.

You can shorten or lengthen the recovery window globally in the server config — set a small value for aggressive cleanup, a larger one for more safety margin:

<clickhouse>
    <database_atomic_delay_before_drop_table_sec>480</database_atomic_delay_before_drop_table_sec>
</clickhouse>

Common Errors

Database already exists: pick a new name or use IF NOT EXISTS. The OR REPLACE form is not supported for CREATE DATABASE; drop and recreate if you really mean it.
Access denied: the user lacks CREATE DATABASE. See database-access-denied and grant via GRANT CREATE DATABASE ON *.* TO ....
Database engine X is not supported: the engine name is misspelled or the build does not include it (some engines depend on optional features).
Cannot create database on cluster: usually a stale Keeper path or a replica that is unreachable; check system.zookeeper and system.clusters.

How Pulse Helps With Database Hygiene

Most ClickHouse incidents that look like "the cluster slowed down overnight" trace back to two database-level things: too many tables in a single database (with the resulting metadata cost), and DDL drift across replicas (where one node thinks the schema is one way and another disagrees). Pulse continuously inventories databases, tables, and engines across ClickHouse clusters and surfaces metadata pressure, replicated databases that have fallen behind on DDL, tables created outside the expected database conventions, and orphan databases nobody has queried in months. Connect your ClickHouse cluster to Pulse and let it watch the database-level shape of the cluster so you don't have to.

Frequently Asked Questions

Q: What does the Atomic engine actually give me over Ordinary?

Non-blocking drop and rename, table swaps via EXCHANGE TABLES, UUID-based on-disk paths, and the ability to UNDROP a recently-dropped table. Ordinary is the legacy engine; new databases should always use Atomic (which is the default since ClickHouse 20.10).

Q: How do I convert an existing Ordinary database to Atomic?

The supported way is the automatic on-startup conversion: create a convert_ordinary_to_atomic flag file in ClickHouse's flags directory (available since 22.8) and restart the server. Every Ordinary database is converted in place. Remove any detached parts first, and run the flag on each node of a cluster. To migrate one database at a time without a restart, create a new Atomic database and move tables into it with RENAME TABLE. See Migrating From Ordinary to Atomic above.

Q: I dropped a table by mistake — can I get it back?

If it was on an Atomic database and you did not pass SYNC/NO DELAY, yes — for database_atomic_delay_before_drop_table_sec seconds after the drop (8 minutes by default). Run UNDROP TABLE <name> (ClickHouse 23.3+). Check system.dropped_tables to confirm it is still recoverable and to grab its UUID if the name is ambiguous. Once the delay elapses the data is gone.

Q: Should I use a Replicated database engine for everything?

It is a strong default for a multi-node self-managed cluster, especially if you have a lot of DDL. The trade-off is that DDL becomes a Keeper-coordinated operation, so it is slower than a single-node create. For a database with stable schema and rare DDL, plain Atomic with ON CLUSTER is fine.

Q: Can I rename a database?

You can rename individual tables across databases (RENAME TABLE a.x TO b.x), and on Atomic databases this is atomic. Renaming the database itself is not directly supported; the workaround is to create a new database, move every table with RENAME, and drop the old database.

Q: How do I delete a database?

DROP DATABASE name. The default behavior is asynchronous on Atomic databases; pass SYNC to wait for completion. The database must be empty in the strict sense only if it has no tables you cannot drop.

Q: Is the MaterializedPostgreSQL engine production-ready?

It is marked as experimental in current ClickHouse releases. Real production CDC pipelines usually use a dedicated tool (Debezium feeding Kafka feeding a Kafka engine table, or a managed transfer service) for stronger guarantees and visibility.

Q: Does CREATE DATABASE affect existing tables?

No. Tables are scoped to a database; creating a new one has no impact on others. The two areas where database-level settings matter are lazy_load_tables (affects startup of the new database) and the choice of engine (affects every table created inside it).