ClickHouse Schema Limits: Tables, Columns, Parts, and Constraints

ClickHouse has very few hard schema limits — you can create thousands of tables and hundreds of columns without hitting a wall. The practical limits come from performance and operational degradation: slow startup, slow merges, heavy RAM use, and inserts that get throttled or rejected. This guide collects the numbers that matter — tables, databases, columns, partitions, and parts — explains the settings that enforce them, and gives the thresholds where "it works" turns into "it hurts."

Most of these are guidelines, not enforced ceilings. Where ClickHouse does enforce a limit (most notably with parts), the relevant setting name is called out so you can find it in system.merge_tree_settings.

The Numbers at a Glance

These are the widely recommended ceilings for a single ClickHouse service or cluster. Treat them as "raise an eyebrow above this" thresholds, not absolute maximums.

ClickHouse emits a warning (visible in system.warnings) when the server-wide database/table/partition/parts thresholds are crossed.

Tables and Databases

Databases in ClickHouse are essentially namespaces and carry almost no runtime cost, so the ~1,000 figure is a loose guideline. Tables are different: every table's metadata is loaded at startup, so a service with tens of thousands of tables can take a long time to come up.

The table ceiling also depends on the engine family:

• Non-replicated MergeTree — a few thousand tables is fine.
• ReplicatedMergeTree — keep it lower (a few hundred is a safer guideline), because each replicated table maintains state in ClickHouse Keeper / ZooKeeper, and that coordination overhead grows with table count.
• Log-family tables — being far simpler, even tens of thousands can be acceptable.

If you find yourself creating one table per customer or per day and the count is climbing into the thousands, reconsider the design — a single partitioned table is almost always better than many small tables. See CREATE TABLE in ClickHouse for engine selection, and clickhouse-error-too-many-tables and clickhouse-error-too-many-databases for the specific limit-exceeded errors.

Columns Per Table

ClickHouse is columnar, so a wide table does not slow down queries that read only a few columns — query cost scales with the columns you SELECT, not the total column count. Wide tables are penalized on the write side instead:

• Up to a few hundred columns is generally fine out of the box.
• Around a thousand columns works but needs more RAM for inserts, merges, and mutations.
• Ten thousand columns is demanding — inserts and merges slow down substantially and require careful tuning.

The cost comes from the fact that each column is a separate set of files per part, so background merges and mutations have to touch many more streams. If you genuinely need thousands of attributes, consider a Map column or a key/value layout instead of thousands of physical columns. For the hard error path, see clickhouse-error-too-many-columns, and for measuring what your columns actually cost on disk, see database, table, and column size queries.

-- Count columns per table to find unusually wide tables
SELECT
    database,
    table,
    count() AS num_columns
FROM system.columns
GROUP BY database, table
ORDER BY num_columns DESC
LIMIT 20;

Partitions

Partitioning is a data-management tool (for dropping old data, for example), not a query-optimization tool — ClickHouse prunes by the primary key, not primarily by partition. Over-partitioning is one of the most common schema mistakes.

The guideline is to keep server-wide partitions under ~50,000, but the more useful rule is local: a PARTITION BY expression should produce a small number of partitions per table. Partitioning by month (toYYYYMM(date)) is typical; partitioning by day, hour, or by a high-cardinality column like user ID creates thousands of tiny partitions, which directly produces too many parts.

-- Partition count per table
SELECT
    database,
    table,
    uniqExact(partition) AS num_partitions
FROM system.parts
WHERE active
GROUP BY database, table
ORDER BY num_partitions DESC
LIMIT 20;

If you have already over-partitioned, see repartitioning a ClickHouse table.

Parts: The Limit You Will Actually Hit

Parts are where ClickHouse enforces a real, hard limit. Every insert creates at least one new part per partition it touches, and a background process continuously merges small parts into larger ones. When inserts arrive faster than merges can keep up, the part count climbs and ClickHouse pushes back.

Three settings (all in system.merge_tree_settings) govern this, with current defaults:

The first two are per partition. (The parts_to_throw_insert default was raised in ClickHouse 23.6 — it was 300 before that — so older clusters throw far sooner.) The Too many parts exception that results looks like:

DB::Exception: Too many parts (N). Merges are processing significantly slower than inserts.

-- See current part-throttling thresholds
SELECT name, value
FROM system.merge_tree_settings
WHERE name IN ('parts_to_delay_insert', 'parts_to_throw_insert', 'max_parts_in_total');

-- Find partitions approaching the limit
SELECT
    database,
    table,
    partition,
    count() AS parts
FROM system.parts
WHERE active
GROUP BY database, table, partition
ORDER BY parts DESC
LIMIT 20;

Even below the throw threshold, a single table with more than ~5,000 parts tends to slow queries and, for ReplicatedMergeTree, can make ALTERs slow or problematic because of coordination state in Keeper/ZooKeeper.

The fix is almost never raising the limit

Raising parts_to_throw_insert only postpones the problem and degrades query performance, because more parts mean more files to open and merge per query. The real fix is on the insert side: insert in large batches (10,000–100,000 rows) at a modest rate (on the order of one insert per second), rather than many tiny inserts. Where the client can't batch, use asynchronous inserts or a Buffer table. For deeper diagnosis and remediation, see clickhouse-error-too-many-parts, too many parts with merge lagging, and the ingestion-side merge backlog guide. For how parts, granules, and merges fit together, see the MergeTree guide.

Materialized Views, Projections, and Indexes

These per-table features all add insert-time cost, so the recommended counts are small:

• Materialized views — keep to a few per source table. Each insert into the source table also writes to every attached MV, multiplying write cost and the chance of inconsistency. See clickhouse-error-too-many-materialized-views and ClickHouse materialized views.
• Projections — also a few per table; each is rebuilt on insert and on merge. See the projections guide.
• Data-skipping indexes — one to roughly a dozen is reasonable, but heavier index types (such as bloom filters) cost more than lightweight minmax indexes.

Constraints in CREATE TABLE

Separate from these capacity limits, ClickHouse supports a CONSTRAINT ... CHECK clause that validates rows at insert time. It is a correctness guardrail, not a performance limit, and a failed check raises Violated constraint:

CREATE TABLE events
(
    id UInt64,
    amount Decimal(10, 2),
    status String,
    CONSTRAINT positive_amount CHECK amount >= 0,
    CONSTRAINT valid_status CHECK status IN ('new', 'paid', 'cancelled')
)
ENGINE = MergeTree
ORDER BY id;

Constraints are checked on every insert, so they add a small per-row cost. You can add or drop them later with ALTER TABLE ... ADD CONSTRAINT / DROP CONSTRAINT — note that adding a constraint does not retroactively validate existing rows. See ALTER TABLE in ClickHouse for the full syntax.

Best Practices

1. Partition coarsely. Aim for partitions measured in months, not days or hours. Over-partitioning is the most common root cause of "too many parts".
2. Batch your inserts. Large, infrequent inserts keep the part count low far more effectively than any setting change.
3. Prefer one partitioned table over many small tables. Thousands of tables slow startup; a single table with good partitioning and ordering scales better.
4. Don't raise parts_to_throw_insert as a first response. Treat the error as a signal that your insert pattern needs fixing.
5. Keep wide tables in check. A few hundred columns is fine; thousands need RAM tuning. Consider Map columns for sparse, high-cardinality attributes.
6. Limit per-table fan-out. A handful of materialized views and projections per table; more multiplies insert cost.
7. Watch system.warnings. ClickHouse proactively warns when you cross server-wide table/partition/part thresholds.

Common Issues

• Server slow to start. Usually too many tables and/or partitions — metadata loads at startup. Consolidate tables and coarsen partitioning.
• "Too many parts" on insert. Inserts are too small/frequent or partitioning is too fine. Batch inserts and reduce partition granularity rather than raising limits.
• High RAM during merges/mutations. Often a very wide table; reduce column count or schedule heavy mutations carefully.
• Slow ALTER on Replicated tables. Often correlated with a high part count interacting with Keeper/ZooKeeper coordination.

How Pulse Helps

Pulse continuously tracks the schema-capacity signals that turn into outages — part counts per partition relative to parts_to_delay_insert / parts_to_throw_insert, partition and table growth over time, merge backlog, and unusually wide tables — and alerts before an insert pipeline starts getting throttled or rejected. Instead of discovering a "too many parts" error in production, you get an early warning with the offending table and partition identified, plus context on whether the cause is insert batching, partitioning granularity, or a stalled merge. Learn more at pulse.support.

Frequently Asked Questions

Q: What is the maximum number of tables in ClickHouse?

There is no hard maximum, but ~5,000 tables server-wide is the recommended ceiling because table metadata is loaded at startup. ReplicatedMergeTree tables are more expensive due to Keeper/ZooKeeper coordination, so keep those lower (a few hundred). Log-family tables are cheap enough that even tens of thousands can be acceptable.

Q: How many columns can a ClickHouse table have?

A few hundred columns is comfortable. Around a thousand works but needs more RAM for inserts and merges; ten thousand is demanding and requires tuning. Because ClickHouse is columnar, wide tables don't slow queries that read only a few columns — the cost is on the write/merge side.

Q: What is the maximum number of parts in ClickHouse?

parts_to_throw_insert (default 3000) caps active parts per partition before inserts are rejected, and parts_to_delay_insert (default 1000) starts throttling earlier. Server-wide, max_parts_in_total defaults to 100000. A single table is best kept under a few thousand active parts.

Q: Should I raise parts_to_throw_insert to fix "too many parts"?

Usually no. Raising it postpones the error and slows queries (more parts = more files per query). Fix the insert pattern instead: insert larger batches less frequently, or use asynchronous inserts / a Buffer table. See clickhouse-error-too-many-parts.

Q: How many partitions should a ClickHouse table have?

As few as practical — partition by month for most time-series data. Server-wide, keep partitions under ~50,000. Fine-grained partitioning (by day, hour, or a high-cardinality column) is a leading cause of too many parts and slow startup.

Q: Are these limits enforced or just recommendations?

Parts limits (parts_to_*_insert, max_parts_in_total) and CONSTRAINT checks are enforced and will reject inserts. The table, column, database, and partition counts are guidelines — ClickHouse warns via system.warnings but won't stop you from exceeding them.