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.5).

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 ReplicatedReplacingMergeTree. 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';

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.5).

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).