ClickHouse DB::Exception: Unknown storage policy (Code: 478)

The "DB::Exception: Unknown policy" error in ClickHouse occurs when a table or operation references a storage policy name that does not exist in the server's configuration. The UNKNOWN_POLICY error code is raised when ClickHouse looks up a storage policy by name and cannot find it in the <policies> section of the storage configuration. This corresponds to the UNKNOWN_POLICY error code (code 478).

Impact

The table creation, ALTER statement, or data move operation that references the unknown policy fails. Existing tables that relied on a policy that was subsequently removed from the configuration will fail to load after a server restart, making their data inaccessible until the policy is restored.

Common Causes

1. A typo in the storage policy name in the CREATE TABLE or ALTER TABLE statement
2. The policy was not defined in the ClickHouse storage configuration
3. The configuration file containing the policy definition has a syntax error and was not loaded
4. The policy was removed from the configuration while tables still reference it
5. A table definition was copied from another environment with different storage policies
6. The configuration file has incorrect permissions and ClickHouse cannot read it

Troubleshooting and Resolution Steps

1. List all currently defined storage policies:

   SELECT policy_name, volume_name, disks
   FROM system.storage_policies;
   

2. Verify the policy name in the table definition matches one of the available policies:

   SHOW CREATE TABLE your_table;
   

   Look for the storage_policy in the SETTINGS clause.

3. Check the storage configuration files for the policy definition:

   grep -r '<policies>' /etc/clickhouse-server/ --include="*.xml" -A 20
   

4. A correct policy configuration looks like this:

   <storage_configuration>
       <disks>
           <fast_disk>
               <path>/mnt/ssd/clickhouse/</path>
           </fast_disk>
           <slow_disk>
               <path>/mnt/hdd/clickhouse/</path>
           </slow_disk>
       </disks>
       <policies>
           <tiered>
               <volumes>
                   <hot>
                       <disk>fast_disk</disk>
                   </hot>
                   <cold>
                       <disk>slow_disk</disk>
                   </cold>
               </volumes>
           </tiered>
       </policies>
   </storage_configuration>
   

5. After adding or fixing the policy configuration, reload the config:

   SYSTEM RELOAD CONFIG;
   

6. Verify the policy is now visible:

   SELECT policy_name, volume_name, disks
   FROM system.storage_policies
   WHERE policy_name = 'tiered';
   

7. If a table references a removed policy, the simplest fix is to restore the policy definition in the configuration. Switching a table to a different policy is possible only when the new policy contains all the volumes and disks the table currently uses:

   ALTER TABLE your_table MODIFY SETTING storage_policy = 'another_policy';
   

Best Practices

• Define storage policies in a dedicated configuration file (e.g., config.d/storage_policies.xml) for clarity.
• Never remove a storage policy from the configuration while any table references it.
• Use system.storage_policies and SHOW CREATE TABLE to audit which policies are in use before making changes.
• Keep policy names descriptive and consistent across environments (e.g., hot_cold, tiered_s3).
• Test storage policy changes in a staging environment before applying to production.

Frequently Asked Questions

Q: What is the default storage policy?
A: Every ClickHouse instance has a default policy that uses the default disk (the main data directory). Tables created without an explicit storage_policy setting use this default.

Q: Can I change a table's storage policy?
A: Yes. Use ALTER TABLE your_table MODIFY SETTING storage_policy = 'new_policy'. The new policy must include all disks where the table currently has data, or you must move the data first.

Q: Will ClickHouse start if a policy referenced by a table is missing?
A: ClickHouse will start, but the affected tables will fail to load and queries against them will return errors. Restore the policy configuration to make those tables accessible again.