ClickHouse DB::Exception: Experimental feature requires allow_experimental setting

The "DB::Exception: Experimental feature" error in ClickHouse appears when you try to use a feature that is still marked as experimental without first enabling the corresponding allow_experimental_* setting. ClickHouse gates these features behind explicit opt-in flags to protect users from potential instability. The error code is EXPERIMENTAL_FEATURE_ERROR.

Impact

The query or DDL statement fails immediately. No data is read or written. This is by design — experimental features may change behavior between releases or have known limitations, so ClickHouse wants confirmation that you accept the risks.

Common Causes

1. Using an experimental table engine (e.g., MaterializedPostgreSQL, TimeSeries) without enabling its setting.
2. Calling an experimental function or using experimental syntax that requires an opt-in flag.
3. Upgrading ClickHouse to a version where a previously stable feature has been reclassified or a new experimental dependency was introduced.
4. Following documentation or blog posts that omit the required setting because the author had it enabled globally.

Troubleshooting and Resolution Steps

1. Read the error message carefully: The exception text typically names the exact setting you need to enable. For example:

   DB::Exception: MaterializedPostgreSQL is an experimental feature.
   Set allow_experimental_materialized_postgresql_table to enable it.
   

2. Enable the setting for the current session:

   SET allow_experimental_materialized_postgresql_table = 1;
   

3. Enable the setting per-query using the SETTINGS clause:

   CREATE TABLE pg_replica
   ENGINE = MaterializedPostgreSQL(...)
   SETTINGS allow_experimental_materialized_postgresql_table = 1;
   

4. Enable the setting in user profiles for broader adoption. In users.xml or via SQL-based access control:

   ALTER USER my_user SETTINGS allow_experimental_lightweight_delete = 1;
   

5. Verify the feature's stability status: Check the ClickHouse changelog and GitHub issues for the feature. Some experimental features are mature and widely used (like allow_experimental_object_type for JSON), while others may have significant caveats.

6. Plan for graduation: Experimental features can change or be removed. Pin your ClickHouse version in production and test experimental features in staging before relying on them.

Best Practices

• Enable experimental settings at the narrowest scope possible — prefer per-query or per-session over server-wide.
• Document which experimental features your application depends on so the team is aware of the risk.
• Monitor ClickHouse release notes for changes to experimental feature status before upgrading.
• Test experimental features thoroughly in non-production environments before deploying.

Frequently Asked Questions

Q: Is it safe to use experimental features in production?
A: It depends on the feature. Some experimental features have been stable for years and are widely adopted. Others are genuinely early-stage. Check the ClickHouse GitHub repository and community forums for real-world experience reports.

Q: Will my data be at risk if I enable an experimental feature?
A: Potentially. Experimental table engines or storage changes could have bugs that affect data integrity. Always maintain backups and test in a staging environment first.

Q: How do I find all available allow_experimental settings?
A: Query the system settings table:

SELECT name, value, description
FROM system.settings
WHERE name LIKE 'allow_experimental%';

Q: Do experimental settings persist across server restarts?
A: Session-level SET commands do not persist. To make them permanent, add them to user profiles in users.xml or use ALTER USER/ALTER PROFILE with SQL-based access control.