ClickHouse DB::Exception: Too many databases

The "DB::Exception: Too many databases" error means you have reached the configured limit on the number of databases a ClickHouse server can host. The error code TOO_MANY_DATABASES serves as a protective measure against unbounded database creation, which can strain server resources and slow down operations that scan all databases.

Impact

No new databases can be created until existing ones are removed or the limit is increased. Existing databases and their tables continue to operate normally. Applications or multi-tenant systems that create databases dynamically will be unable to provision new tenants until the issue is addressed.

Common Causes

1. Per-tenant database architecture -- creating a separate database for each customer or project without cleaning up after decommissioning.
2. Automated test environments -- CI/CD pipelines that create databases for each test run without deleting them afterward.
3. Accumulation of abandoned databases -- developers creating databases for experiments that are never removed.
4. The configured limit being too low -- the default may not match your architecture's requirements.

Troubleshooting and Resolution Steps

1. Check the current database count:

   SELECT count() FROM system.databases;
   

2. List all databases with their engine and table counts:

   SELECT
       d.name,
       d.engine,
       count(t.name) AS table_count
   FROM system.databases d
   LEFT JOIN system.tables t ON d.name = t.database
   GROUP BY d.name, d.engine
   ORDER BY table_count DESC;
   

3. Identify databases that can be removed:

   -- Find empty databases
   SELECT d.name
   FROM system.databases d
   LEFT JOIN system.tables t ON d.name = t.database
   GROUP BY d.name
   HAVING count(t.name) = 0
   AND d.name NOT IN ('system', 'information_schema', 'INFORMATION_SCHEMA', 'default');
   

4. Drop unnecessary databases. Remember to drop all tables first:

   -- Check what is inside
   SHOW TABLES FROM old_database;
   -- Drop tables, then the database
   DROP DATABASE IF EXISTS old_database;
   

5. Increase the limit if needed. In config.xml or a config override file:

   <max_database_num_to_throw>500</max_database_num_to_throw>
   

   Restart ClickHouse to apply the change.

6. Consolidate databases. If you are using a per-tenant database model, consider switching to a single database with per-tenant tables or a multi-tenant table with a tenant_id column:

   CREATE TABLE all_tenants_data (
       tenant_id UInt32,
       event_date Date,
       data String
   ) ENGINE = MergeTree()
   PARTITION BY (tenant_id, toYYYYMM(event_date))
   ORDER BY (tenant_id, event_date);
   

Best Practices

• Favor multi-tenant table designs over per-tenant databases when possible -- it scales better and is easier to manage.
• Automate database cleanup in CI/CD pipelines so test databases are removed after the run completes.
• Monitor the database count as part of your ClickHouse operational metrics.
• Establish a naming convention and lifecycle policy for databases (e.g., databases prefixed with test_ are auto-deleted after 7 days).
• Review database counts during capacity planning and adjust limits proactively.

Frequently Asked Questions

Q: What is the default maximum number of databases in ClickHouse?
A: The default depends on your ClickHouse version and configuration. Many installations do not enforce a hard limit by default, but the max_database_num_to_throw setting allows you to set one.

Q: Do system databases count toward the limit?
A: System databases (system, information_schema, INFORMATION_SCHEMA, default) are typically present on every ClickHouse server but usually count toward the total. Factor them in when setting your limit.

Q: Is there a performance impact from having many databases?
A: Having many databases increases startup time and memory usage for metadata. Operations that iterate over all databases (like SHOW DATABASES or dictionary loading) also become slower.

Q: Can I merge two databases into one?
A: Not directly. You would need to create the target tables in the destination database, use INSERT INTO ... SELECT to migrate data, and then drop the source database. The RENAME TABLE command can also move tables between databases atomically.