---
alias: system-requirements-clickhouse-data-platform
tags:
- ClickHouse
- system requirements
description: "ClickHouse requires specific hardware and software configurations for optimal performance and operation"
---

# ClickHouse Requirements

## System Requirements

| Item       | Minimum | Recommended |
| :--------- | :------ | :---------- |
| Version    | 26.3    | 26.3        |
| Memory     | 16GB    | 32GB        |
| CPU        | 4vCPUs  | 8vCPUs      |
| Disk Space | 1TB     | 3TB         |

Table: Minimum and recommended system requirements for ClickHouse

!!! info "Recommended Container Image"
    Use an Altinity Stable image when deploying ClickHouse. Altinity Stable images are recommended because they provide a longer support period.

## Server-Level Settings

The following settings must be configured at the ClickHouse server level before proceeding with user provisioning.

| Setting                                   | Value | Details |
| :---------------------------------------- | :---: | :------ |
| `allow_materialized_view_with_bad_select` | `1`   | Required for migrations to MES 11.0.0+ when moving to an external ClickHouse Cloud setup. Must be configured prior to starting the migration. See [ClickHouse Migration](https://developer.criticalmanufacturing.com/explore/guides/migration/version-specific/1100/infrastructure/#step-3-configure-clickhouse-cloud-settings-if-applicable). |

Table: Mandatory server-level ClickHouse settings

## Databases

{% include-markdown 'includes/tech/mes-clickhouse-databases.md' %}

## Reserved Names

MES deployment and runtime operations rely on a fixed naming convention for ClickHouse roles, settings profiles, and quotas. The following object names **must not be used for any other purpose** in the ClickHouse instance.

| Reserved Name                | Object Type      | Description |
| :--------------------------- | :--------------- | :---------- |
| `<SystemName>_<KEY>_ROLE`    | Role             | Role attached to the MES user that holds the grants required to perform MES operations. |
| `<SystemName>_<KEY>_PROFILE` | Settings Profile | Settings profile assigned to the role to enforce the mandatory user-level ClickHouse settings. |
| `<SystemName>_<KEY>_QUOTA`   | Quota            | Quota assigned to the role. It is created only for roles that need runtime usage limits, currently the [DWH Playground (Read)](#dwh-playground-read) role. Otherwise, it is dropped during provisioning. |

Table: ClickHouse reserved names

Where `<SystemName>` is the name of the MES, the same value that gives the databases their names, and `<KEY>` is one of the keys listed in [Users, Roles, and ACLs](#users-roles-and-acls).

The role attached to the [Default User](#default-user) is not covered by this convention. The customer creates and names that role.

!!! warning "Changed in Version 12.0.0"
    Earlier versions named these objects after the ClickHouse user, as `<user>_role`, `<user>_profile`, and `<user>_quota`. They also used the temporary objects `<user>_new_role` and `<user>_new_profile` during installation and upgrade. Version 12.0.0 removes the temporary objects. The upgrade drops the roles, settings profiles, and quotas that use the old naming, and creates them again with the new naming.

## Users, Roles, and ACLs

MES requires two categories of ClickHouse users:

- **Installation User** — Must already exist in the ClickHouse installation before setup. The Environment Manager uses it to connect and provision the remaining users. See:

    - [Default User](#default-user)

- **MES Users** — Created automatically or manually, depending on the provisioning mode selected when creating the [[installation-guide-installation#clickhouse|Customer Environment]]. Each MES user is paired with a role, and permissions are granted to the role rather than directly to the user. See:

    - [Analytics (Read/Write) Admin](#analytics-readwrite-admin)
    - [MES (Read/Write) User](#mes-readwrite)
    - [Analytics (Read) User](#analytics-read)
    - [Analytics (Read) / DWH (Read/Write) User](#analytics-read--dwh-readwrite)
    - [DWH (Read) User](#dwh-read)
    - [DWH Playground (Read) User](#dwh-playground-read)

For each MES user, the role must first be created and assigned to the user before the permissions in the sections below can be applied. From version 12.0.0, the name of the role is built from the name of the system and a fixed key, in the form `<SystemName>_<KEY>_ROLE`:

```sql
CREATE ROLE IF NOT EXISTS `<SystemName>_<KEY>_ROLE`;
GRANT `<SystemName>_<KEY>_ROLE` TO <user>;
```

Each MES user has its own key:

| Key                                    | User |
| :------------------------------------- | :--- |
| `CLICKHOUSE_MES_READWRITE`             | [MES (Read/Write)](#mes-readwrite) |
| `CLICKHOUSE_ANALYTICS_READ`            | [Analytics (Read)](#analytics-read) |
| `CLICKHOUSE_ANALYTICS_READWRITE_ADMIN` | [Analytics (Read/Write) Admin](#analytics-readwrite-admin) |
| `CLICKHOUSE_ANALYTICS_READ_DWH_WRITE`  | [Analytics (Read) / DWH (Read/Write)](#analytics-read--dwh-readwrite) |
| `CLICKHOUSE_DWH_READ`                  | [DWH (Read)](#dwh-read) |
| `CLICKHOUSE_DWH_READ_PLAYGROUND`       | [DWH Playground (Read)](#dwh-playground-read) |

Table: ClickHouse role keys

On a system named MES, the role of the Analytics (Read) user is `MES_CLICKHOUSE_ANALYTICS_READ_ROLE`.

The permissions assigned to MES users follow the standard ClickHouse security model. For a detailed breakdown of each privilege, refer to [ClickHouse SQL Reference: GRANT Permissions](https://clickhouse.com/docs/sql-reference/statements/grant#privileges).

### Default User

The **Default User** is the account that must already exist in the customer ClickHouse installation. This user is either created manually by the customer or corresponds to the default user provided by their ClickHouse setup.

The Environment Manager uses this account to connect to ClickHouse during environment creation and configuration. Therefore, the following administrative grants must be assigned to a role attached to this user to allow for the automatic provisioning of users required by **Critical Manufacturing MES**.

| Category                | Permissions                                                                            | WITH Grant Option? |
| :---------------------- | :------------------------------------------------------------------------------------- | :----------------: |
| **User Management**     | `CREATE USER`, `ALTER USER`, `DROP USER`                                               | No                 |
| **Role Management**     | `CREATE ROLE`, `ALTER ROLE`, `DROP ROLE`, `ROLE ADMIN`                                 | No                 |
| **Settings & Profiles** | `CREATE SETTINGS PROFILE`, `ALTER SETTINGS PROFILE`, `DROP SETTINGS PROFILE`           | No                 |
| **Quota Management**    | `CREATE QUOTA`, `ALTER QUOTA`, `DROP QUOTA`                                            | No                 |
| **Backup & Restore**    | `BACKUP`                                                                               | No                 |
| **Database Management** | `CREATE DATABASE`, `DROP DATABASE`, `SHOW`                                             | Yes                |
| **Data Management**     | `SELECT`, `INSERT`, `ALTER`, `CREATE TABLE`, `DROP TABLE`, `UNDROP TABLE`, `CREATE TEMPORARY TABLE`, `CREATE VIEW`, `DROP VIEW`, `CREATE DICTIONARY`, `DROP DICTIONARY`, `TRUNCATE`, `OPTIMIZE`, `CREATE ROW POLICY`, `ALTER ROW POLICY`, `DROP ROW POLICY`, `SHOW ROW POLICIES`, `DICTGET` |  Yes  |

Table: Default User required permissions — all scoped to `*.*` (Global)

You can apply all the above permissions using the following SQL statement:

```sql
GRANT
    CREATE USER, ALTER USER, DROP USER,
    CREATE ROLE, ALTER ROLE, DROP ROLE, ROLE ADMIN,
    CREATE SETTINGS PROFILE, ALTER SETTINGS PROFILE, DROP SETTINGS PROFILE,
    CREATE QUOTA, ALTER QUOTA, DROP QUOTA,
    BACKUP, SHOW ACCESS
ON *.* TO <default_user>_role;

GRANT 
    CREATE DATABASE, DROP DATABASE, SHOW, SELECT, INSERT, ALTER,
    CREATE TABLE, DROP TABLE, UNDROP TABLE, CREATE TEMPORARY TABLE, 
    CREATE VIEW, DROP VIEW, CREATE DICTIONARY, DROP DICTIONARY, TRUNCATE, OPTIMIZE, 
    CREATE ROW POLICY, ALTER ROW POLICY, DROP ROW POLICY, SHOW ROW POLICIES, DICTGET, CLUSTER
ON *.* TO <default_user>_role WITH GRANT OPTION;
```

!!! INFO "Permission Hierarchy"
    Permissions like `ALL` are administrative shorthands that encompass multiple granular privileges (e.g., `SELECT`, `INSERT`, `CREATE TABLE`). When configuring the **Default User**, ensure that the `WITH GRANT OPTION` is included to allow the Environment Manager to delegate these functional rights to the MES sub-users.

### Analytics (Read/Write) Admin

Administrative user with extended privileges for managing all analytics databases and deployments.

- **Databases**: `<SystemName>`, `<SystemName>ODS`, `<SystemName>CDM`, `<SystemName>DWH`
- **Required Permissions**:

    ```sql
    GRANT
        CREATE DATABASE, DROP DATABASE, SHOW, SELECT,
        INSERT, ALTER, CREATE TABLE, DROP TABLE, UNDROP TABLE,
        CREATE TEMPORARY TABLE, CREATE VIEW, DROP VIEW,
        CREATE DICTIONARY, DROP DICTIONARY, TRUNCATE, OPTIMIZE,
        CREATE ROW POLICY, ALTER ROW POLICY, DROP ROW POLICY, 
        SHOW ROW POLICIES, DICTGET, CLUSTER
    ON <SystemName>*.* TO `<SystemName>_CLICKHOUSE_ANALYTICS_READWRITE_ADMIN_ROLE`;
    ```

### MES (Read/Write)

User responsible for reading and writing core operational MES data.

- **Databases**: `<SystemName>`
- **Required Permissions**:

    ```sql
    GRANT
        SELECT, INSERT, ALTER, CREATE TABLE, CREATE VIEW, CREATE DICTIONARY, CLUSTER
    ON <SystemName>.* TO `<SystemName>_CLICKHOUSE_MES_READWRITE_ROLE`;
    ```

### Analytics (Read)

User with read-only access for general reporting and data consumption across all Analytics databases.

- **Databases**: `<SystemName>`, `<SystemName>ODS`, `<SystemName>CDM`, `<SystemName>DWH`, `system`
- **Required Permissions**:

    ```sql
    GRANT 
        SELECT ON <SystemName>*.* TO `<SystemName>_CLICKHOUSE_ANALYTICS_READ_ROLE`,
        SELECT(volume_name, policy_name) ON system.storage_policies,
        SELECT(value, name) ON system.build_options,
        CREATE TEMPORARY TABLE ON *.*
    TO `<SystemName>_CLICKHOUSE_ANALYTICS_READ_ROLE`;
    ```

### Analytics (Read) / DWH (Read/Write)

User responsible for processes that populate the DWH, with read-only access to source databases.

- **Databases**: `<SystemName>CDM`, `<SystemName>DWH`
- **Required Permissions**:

    ```sql
    GRANT SELECT ON <SystemName>CDM.* TO `<SystemName>_CLICKHOUSE_ANALYTICS_READ_DWH_WRITE_ROLE`;
    
    GRANT
        SHOW,
        SELECT, INSERT, ALTER, CREATE TABLE, CREATE VIEW, CREATE DICTIONARY,
        DROP TABLE, DROP VIEW, DROP DICTIONARY, UNDROP TABLE, TRUNCATE, OPTIMIZE,
        CREATE ROW POLICY, ALTER ROW POLICY, DROP ROW POLICY, SHOW ROW POLICIES, 
        DICTGET, CLUSTER
    ON <SystemName>DWH.* TO `<SystemName>_CLICKHOUSE_ANALYTICS_READ_DWH_WRITE_ROLE`;
    ```

### DWH (Read)

User with read-only access exclusively to the Data Warehouse.

- **Databases**: `<SystemName>DWH`
- **Required Permissions**:

    ```sql
    GRANT SELECT ON <SystemName>DWH.* TO `<SystemName>_CLICKHOUSE_DWH_READ_ROLE`;
    ```

### DWH Playground (Read)

User with read-only access to the Data Warehouse for Cube Explorer ad-hoc queries. Unlike other MES users that run controlled application-driven workloads, this user can be unpredictable in cost. Quotas are applied to protect the ClickHouse cluster from runaway queries.

- **Databases**: `<SystemName>DWH`
- **Required Permissions**:

    ```sql
    GRANT SELECT ON <SystemName>DWH.* TO `<SystemName>_CLICKHOUSE_DWH_READ_PLAYGROUND_ROLE`;
    ```

- **Recommended Quotas**:

    ```sql
    CREATE QUOTA OR REPLACE `<SystemName>_CLICKHOUSE_DWH_READ_PLAYGROUND_QUOTA`
        FOR INTERVAL 1 hour MAX execution_time = 900,
        FOR INTERVAL 1 hour MAX result_rows = 1000000
        TO `<SystemName>_CLICKHOUSE_DWH_READ_PLAYGROUND_ROLE`;
    ```

## User-Level Settings Profiles

Once users and roles are created, a settings profile must be applied to ensure correct MES operation. The following settings are mandatory for all MES ClickHouse users:

| Setting                                       | Value  | Details |
| :-------------------------------------------- | :----: | :------ |
| `compatibility`                               | `26.3` | Locks ClickHouse behavior to version 26.3 semantics. Ensures consistent query execution and feature availability regardless of the actual ClickHouse server version. |
| `do_not_merge_across_partitions_select_final` | `0`    | Prevents data duplication in ODS state tables (e.g., `CoreDataModel_T_Material`). While default in ClickHouse 25.3 and automatically enforced from MES 11.2.3+, it must be manually verified for older versions or custom configurations to ensure it remains at `0`. |
| `http_max_field_value_size`                   | `262144` | Raises the maximum length (in bytes) of an HTTP header field value (256 KiB, up from the 128 KiB default). Required to support the large queries that the CDM Builder sends over HTTP when it batches multiple records into a single statement. |
| `max_query_size`                              | `10000000` | Raises the maximum size (in bytes) of the SQL string that the parser will accept (~10 MB, up from the 256 KiB default). Required to support the large queries generated by MES analytics workloads (for example, queries with extensive `IN` clauses). |
| `throw_on_max_partitions_per_insert_block`    | `0`    | Prevents insert failures when data spans a large number of partitions. Disabling this check ensures bulk inserts into partitioned tables complete without errors. |
| `transform_null_in`                           | `1`    | Ensures correct NULL handling in `IN` clauses by enabling NULL-safe equality comparisons. Required for accurate query results across MES analytics queries. |

Table: Mandatory user-level ClickHouse settings

When using automatic provisioning, MES creates these profiles automatically. For manual provisioning, run the following statement once for each MES user role:

```sql
-- Run this once for each MES user role, replacing <KEY> with one of:
--   CLICKHOUSE_MES_READWRITE
--   CLICKHOUSE_ANALYTICS_READ
--   CLICKHOUSE_ANALYTICS_READWRITE_ADMIN
--   CLICKHOUSE_ANALYTICS_READ_DWH_WRITE
--   CLICKHOUSE_DWH_READ
--   CLICKHOUSE_DWH_READ_PLAYGROUND
CREATE SETTINGS PROFILE OR REPLACE `<SystemName>_<KEY>_PROFILE`
  SETTINGS
    compatibility = '26.3',
    do_not_merge_across_partitions_select_final = 0,
    http_max_field_value_size = 262144,
    max_query_size = 10000000,
    throw_on_max_partitions_per_insert_block = 0,
    transform_null_in = 1
  TO `<SystemName>_<KEY>_ROLE`;
```

## Row Policies

From version 12.0.0, the MES creates ClickHouse row policies to restrict the rows that a Data Platform query returns. Each policy is created with its table and applies to the `<SystemName>_CLICKHOUSE_ANALYTICS_READ_ROLE` role.

The privileges that support this behavior are already listed in the sections above:

- `CREATE TEMPORARY TABLE ON *.*` on the Analytics (Read) role. Data Platform creates a temporary table for each request to hold the Data Groups of the caller, and the row policy reads that table.
- `CREATE ROW POLICY`, `ALTER ROW POLICY`, `DROP ROW POLICY`, and `SHOW ROW POLICIES` on the Analytics (Read/Write) Admin role and on the role attached to the Default User.

Do not remove these privileges. Without them, tables are created without a policy, and every Data Platform query returns every row.

!!! info
    For more information on what the policies do, see [[operation-guide-data-set-data-access-security|Data Set Data Access Security]].

## High Availability

Version 12.0 supports ClickHouse running as a replicated cluster. A replicated cluster keeps a copy of the data on more than one server, so that Data Platform stays available when a server fails.

The requirements depend on how you deploy ClickHouse.

| Deployment                       | Replication   | Requirements |
| :------------------------------- | :------------ | :----------- |
| ClickHouse in the MES stack      | Not supported | None. ClickHouse installed in the MES stack (External dependencies mode: All) runs as a single instance and is not supported in Staging or Production environments. |
| External self-managed ClickHouse | Supported     | Two or more ClickHouse servers, three ClickHouse Keeper instances, and a cluster defined in the ClickHouse server configuration. A load balancer in front of the replicas is strongly recommended. |
| ClickHouse Cloud                 | Supported     | None. The service handles replication for you. |

Table: ClickHouse replication support per deployment option

### ClickHouse Keeper

A self-managed replicated cluster needs ClickHouse Keeper. Deploy three Keeper instances.

The Keeper needs a majority of instances to agree on the state of the data. Always use an odd number of instances.
That is because with three instances, the cluster keeps working when one instance fails, because the other two still form a majority. With two instances, a single failure leaves no majority and the cluster stops.

!!! note "Note"
    ClickHouse Cloud does not need Keeper instances. The service manages replication internally.

### Load Balancer

A load balancer in front of the replicas is not required, but it is strongly recommended. The MES connects to the load balancer rather than to a single replica, so the connection stays up when a replica fails.

Without a load balancer, the MES connects to one replica directly. If that replica goes down, the MES loses connectivity, and it needs manual reconfiguration and a restart.

Configure the load balancer for failover. A failover configuration defines a chain of priorities, in which all connections reach the primary replica and another replica takes over only when the primary fails.

If your load balancer cannot do failover, configure sticky sessions instead, so that one session stays on one replica. Failover remains the recommended configuration.

For the configuration steps, see [[installation-guide-planning-and-installation-guides-clickhouse#load-balancer|ClickHouse Connection Setup]].

### Cluster Name

On a self-managed cluster, the MES needs the name of the ClickHouse cluster. Set the `Cluster Name` when you create the [[installation-guide-installation#clickhouse|Customer Environment]].

The name must match the cluster defined in the ClickHouse server configuration. The Environment Manager, the HouseKeeper, and the Aggregation Engine use this name to create and change tables on the cluster. Without it, the MES creates the tables on a single server and the data is not replicated.

### Table Engines

The MES chooses the table engine based on the deployment:

- Self-managed cluster - tables use the `Replicated*MergeTree` engine family.
- Single instance - tables use the standard `*MergeTree` engine family.
- ClickHouse Cloud - tables use the standard `*MergeTree` engine family, and the service converts them to `Shared*MergeTree` tables.

!!! note "Note"
    The `Shared*MergeTree` engine family exists only in ClickHouse Cloud, and the `Replicated*MergeTree` engine family is not supported there. The MES handles this difference for you.

### Read-After-Write Consistency

The Data Platform builds the analytics models with dbt, which needs to read the data it has just written. On a replicated cluster, a query that reaches a different replica may not see that data yet.

- Self-managed cluster - configure the load balancer for failover, as described in [[installation-guide-planning-and-installation-guides-clickhouse#load-balancer|ClickHouse Connection Setup]]. All connections then reach the same replica, so a query always sees the data written before it.
- ClickHouse Cloud - MES sets `select_sequential_consistency` to `1` in the settings profile of each MES user. For more information, see [User-Level Settings Profiles](#user-level-settings-profiles).

### Migration of Existing Installations

When you upgrade an existing environment to version 12.0 with a cluster name configured, the MES converts the databases and the tables that already exist to the cluster and to the `Replicated*MergeTree` engine family. You do not need to convert them yourself.

The same conversion runs when you restore a backup package, even when the restore does not include a version upgrade.