Skip to main content

ClickHouse Configuration

ClickHouse serves as the primary analytics database for storing profiling data and metrics. It provides high-performance columnar storage optimized for analytical queries.

Data Retention​

The Helm chart renders the clickhouse.retention value into a retention YAML file and mounts it into the database migration job. The migration job applies the file after schema migrations on every install and upgrade, updating TTL clauses and related MergeTree settings on existing profiling and metrics tables.

The default Helm configuration deletes profiling data, metrics, and settled recommendations after 720 hours (30 days):

clickhouse:
  retention:
    common:
      delete: { after-hours: 720 }
    recommendations:
      delete: { after-hours: 720 }

Set delete: disabled to retain a section's data indefinitely. ClickHouse processes expired data asynchronously during merges, so rows can remain visible after the configured time has elapsed.

Retention sections​

The configuration requires common and recommendations sections:

  • common configures profiling and metrics tables.
  • recommendations configures recommendations tables.

The optional top-level apply key controls how the migration job runs retention ALTER statements. sync (the default) waits for every replica and fails the migration once ClickHouse's distributed_ddl_task_timeout elapses (180 seconds by default). async submits the statements without waiting. Use async when TTL changes on large tables or clusters exceed the timeout.

The following example applies retention asynchronously, deletes common data after 7 days, moves it to the cold volume after 1 day, and recompresses it with ZSTD level 7 after 3 days. Recommendations remain for 60 days.

clickhouse:
  retention:
    apply: async
    common:
      policy: tiered
      delete: { after-hours: 168 }
      tiers:
        - move: { after-hours: 24, to: { volume: cold } }
        - recompress: { after-hours: 72, codec: { zstd: 7 } }
      settings:
        merge-with-ttl-timeout-sec: 14400
        ttl-only-drop-parts: true
    recommendations:
      delete: { after-hours: 1440 }

policy must name a ClickHouse storage policy available on every target server. Each move destination must be a disk or volume defined by that policy.

ClickHouse Cloud

ClickHouse Cloud does not support custom storage policies or TTL TO DISK/TO VOLUME moves, so the policy and tiers[].move options only apply to self-hosted deployments. ClickHouse Cloud manages data tiering automatically; see Implementing a hot/warm/cold architecture. delete retention works on all deployments.

FieldValuesBehavior
apply (top-level)sync (default) or asyncsync waits for retention ALTERs on every replica; async submits them without waiting.
policyClickHouse storage policy nameSets the table's storage_policy. Required when tier rules move data outside the current policy.
deletedisabled or { after-hours: HOURS }Removes expired data or retains it indefinitely. HOURS must be a positive integer.
tiers[].move{ after-hours: HOURS, to: { volume: NAME } } or { after-hours: HOURS, to: { disk: NAME } }Moves data to a ClickHouse volume or disk.
tiers[].recompress{ after-hours: HOURS, codec: CODEC }Recompresses data with lz4, { lz4hc: LEVEL } (1–12), or { zstd: LEVEL } (1–22).
settings.merge-with-ttl-timeout-secNon-negative integerSets the minimum delay between delete-TTL merges. ClickHouse defaults to 14400 seconds.
settings.ttl-only-drop-partsBooleanDrops complete expired parts instead of deleting rows where supported. Defaults to true.

Override retention settings​

Helm deep-merges clickhouse.retention with the chart defaults, so a values file only needs the fields it changes. Overriding common keeps the default recommendations section, and vice versa. Individual fields can also be set on the command line:

helm upgrade --install backend zymtrace/backend \
  --namespace zymtrace \
  --set clickhouse.retention.common.delete.after-hours=168

Misspelled section names and sections without a delete rule fail at template time; the migration job validates the remaining fields before applying any changes.

Configuration Modes​

Connect to existing ClickHouse​

This mode connects to your existing ClickHouse cluster, including ClickHouse Cloud, on-premises clusters, and single-node instances.

Supported ClickHouse Deployments

  • ClickHouse Cloud: Managed ClickHouse service with automatic scaling and management
  • ClickHouse Clusters: Multi-node distributed setups with shards and replicas
  • Single-node ClickHouse: Standalone ClickHouse server installations

Database Setup Options​

You have two options for setting up the required databases:

  1. Manual Setup: Create databases and users manually using SQL commands.
  2. Automatic Setup: Enable autoCreateDBs to let Zymtrace create databases during migration

Option 1: Manual Database Setup​

When connecting to an existing ClickHouse cluster, we strongly recommend that you create a dedicated user, assign appropriate permissions, and create a new database for zymtrace.

If you're using a distributed ClickHouse cluster with multiple shards/replicas, you have two options:

  1. Use the {cluster} macro (recommended): A common convention where {cluster} is configured as a macro that resolves to your cluster name
  2. Find the actual cluster name: Query your ClickHouse installation to get the specific cluster name
-- Check if the {cluster} macro is available
SELECT * FROM system.macros;

-- Find available clusters in your ClickHouse installation
SELECT cluster FROM system.clusters;

SQL Commands for Database Setup​

Choose the appropriate commands based on your ClickHouse deployment.

Caution:

This script will drop the zymtrace_profiling database if it exists. Run it only if this is your first time setting up Zymtrace with ClickHouse, or if you intentionally want to reset the database, knowing that this will permanently delete any existing data.

ClickHouse Cloud and Single Node​

For single-node ClickHouse installations or ClickHouse Cloud:

-- Clean slate: remove any existing profiling database
DROP DATABASE IF EXISTS zymtrace_profiling SYNC;
DROP DATABASE IF EXISTS zymtrace_metrics SYNC;

CREATE DATABASE zymtrace_profiling;
CREATE DATABASE zymtrace_metrics;

CREATE USER IF NOT EXISTS zymtrace_user
IDENTIFIED WITH sha256_password BY 'YOUR NEW PASSWORD HERE';

GRANT
    SELECT,
    INSERT,
    UPDATE,
    ALTER,
    DELETE,
    CREATE,
    DROP,
    SHOW,
    OPTIMIZE,
    TRUNCATE,
    SYSTEM SYNC REPLICA
ON zymtrace_profiling.* TO zymtrace_user;

GRANT
    SELECT,
    INSERT,
    UPDATE,
    ALTER,
    DELETE,
    CREATE,
    DROP,
    SHOW,
    OPTIMIZE,
    TRUNCATE,
    SYSTEM SYNC REPLICA
ON zymtrace_metrics.* TO zymtrace_user;

Note that SYSTEM SYNC REPLICA is only strictly required when running in ClickHouse Cloud and optional when using a self-hosted single node instance.

Make sure to replace YOUR NEW PASSWORD HERE with a secure password.

Reference - https://clickhouse.com/docs/sql-reference/statements/grant

Option 2: Automatic Database Setup​

Alternatively, you can enable autoCreateDBs: true in your configuration to let Zymtrace automatically create the required databases during migration. This option requires that your ClickHouse user has CREATE permissions on the server.

When to use automatic setup:

  • Development environments where quick setup is preferred
  • When you have administrative access to grant broad CREATE permissions
  • Testing scenarios where database recreation is acceptable

When to use manual setup:

  • Production environments requiring strict permission control
  • When following security best practices with minimal required permissions
  • Enterprise environments with database administration policies
info

When using ClickHouse Cloud, ensure you use port 8443 and enable secure connection. For ClickHouse clusters, specify the cluster name to enable distributed queries.

Helm Configuration​

ClickHouse Cloud / Single Node​

This configuration applies to both ClickHouse Cloud and standalone single-node ClickHouse installations.

clickhouse:
  mode: "use_existing"
  use_existing:
    host: "https://abc123def.us-east-1.aws.clickhouse.cloud:8443" # For ClickHouse Cloud, 
           # OR "http://clickhouse.internal.company.com:8123" # For single-node
    user: "default" # For Cloud, or "zymtrace_user" for single-node
    password: "your-cloud-password" # For Cloud, or "secure-password" for single-node
    database: "zymtrace"
    secure: true # For Cloud, or false/true for single-node depending on TLS
    # clusterName not needed for Cloud or single-node
    # autoCreateDBs: true  # or create the DBs yourself using the SQL above

HTTP Interface Requirement​

Protocol Requirement

Only the ClickHouse HTTP interface is supported due to limitations in the official ClickHouse Rust client. The native protocol port is not supported. Always use the HTTP interface port:

  • HTTP: Port 8123 (typically for internal/dev environments)
  • HTTPS: Port 8443 (recommended for production and ClickHouse Cloud)

URL Format Requirements​

The host field must include:

  • Protocol: http:// or https://
  • Hostname/IP: The ClickHouse server address
  • Port: The HTTP interface port

Next Steps​

After configuring ClickHouse, proceed to configure the other storage components:

Configure Postgres | Configure Object Storage