clickhouse-prod-checklist

ClickHouse Production Checklist

Overview

Comprehensive go-live checklist for ClickHouse covering server tuning, schema design, backup configuration, monitoring, and operational readiness.

Prerequisites

• ClickHouse instance provisioned (Cloud or self-hosted)
• Application integration code tested in staging

Checklist

1. Schema & Engine Design

• Tables use MergeTree family engines (not Memory, Log, or TinyLog)
• ORDER BY columns match primary filter/group patterns
• PARTITION BY is coarse (monthly or weekly, never by ID)
• TTL configured for data retention policy
• LowCardinality(String) used for low-cardinality columns
• CODEC(ZSTD) applied to large String/JSON columns
• ReplacingMergeTree used with FINAL or dedup logic if upserts needed

2. Server Configuration (Self-Hosted)

<!-- Key production settings in config.xml / users.xml -->

<!-- Memory: set to ~80% of available RAM -->
<max_server_memory_usage_to_ram_ratio>0.8</max_server_memory_usage_to_ram_ratio>

<!-- Query limits -->
<max_concurrent_queries>150</max_concurrent_queries>
<max_memory_usage>10000000000</max_memory_usage>  <!-- 10GB per query -->
<max_execution_time>300</max_execution_time>       <!-- 5 min timeout -->

<!-- Merge settings -->
<background_pool_size>16</background_pool_size>
<background_schedule_pool_size>16</background_schedule_pool_size>

<!-- Logging -->
<query_log>
    <database>system</database>
    <table>query_log</table>
    <flush_interval_milliseconds>7500</flush_interval_milliseconds>
</query_log>

3. Backup Configuration

-- ClickHouse native BACKUP to S3
BACKUP TABLE analytics.events
    TO S3(
        'https://my-bucket.s3.us-east-1.amazonaws.com/backups/events',
        'ACCESS_KEY',
        'SECRET_KEY'
    )
    SETTINGS compression_method = 'zstd';

-- Incremental backup (base + delta)
BACKUP TABLE analytics.events
    TO S3('s3://my-bucket/backups/events-incremental')
    SETTINGS base_backup = S3('s3://my-bucket/backups/events-base');

ClickHouse Cloud: Backups are automatic. Configure retention and frequency in the Cloud console under Service Settings.

• Backup schedule configured (daily minimum)
• Backup restore tested and documented
• Point-in-time recovery possible (incremental backups)
• Backup stored in different region/account from primary

4. Monitoring & Alerting

-- Key metrics to monitor
-- 1. Query latency (p50/p95/p99)
-- 2. Active parts per table (alert > 300)
-- 3. Merge queue depth
-- 4. Memory usage
-- 5. Disk space free
-- 6. Replication lag (if clustered)

-- Health check query (use for load balancer probes)
SELECT 1;

-- More thorough health check
SELECT
    (SELECT count() FROM system.processes) AS running_queries,
    (SELECT sum(value) FROM system.metrics WHERE metric = 'MemoryTracking') AS memory_bytes,
    (SELECT count() FROM system.merges) AS active_merges;

• Prometheus endpoint configured (/metrics via ClickHouse Exporter or Cloud endpoint)
• Grafana dashboard with key panels (QPS, latency, memory, parts, merges)
• Alerts on: error rate > 5%, p95 latency > 5s, parts > 300, disk < 20%
• Query log monitoring for slow/failed queries

5. Security

• Default user password changed or user disabled
• Application user has minimal privileges (see clickhouse-security-basics)
• TLS enabled (HTTPS on port 8443)
• IP allowlist configured
• Secrets in environment variables or secret manager (not code)

6. Application Integration

• Connection pooling configured (max_open_connections)
• Graceful shutdown calls client.close()
• Insert batching in place (10K+ rows per INSERT)
• Retry logic for transient errors (see clickhouse-rate-limits)
• Health check endpoint includes ClickHouse ping

// Health check endpoint
app.get('/health', async (req, res) => {
  try {
    const { success } = await client.ping();
    res.json({ status: success ? 'healthy' : 'degraded', clickhouse: success });
  } catch {
    res.status(503).json({ status: 'unhealthy', clickhouse: false });
  }
});

7. Operational Readiness

• Incident runbook documented (see clickhouse-incident-runbook)
• On-call escalation path defined
• Key rotation procedure documented
• Schema migration process in place (see clickhouse-migration-deep-dive)
• Load testing completed at expected peak traffic

8. Verification Queries

-- Verify table sizes and part health
SELECT database, table, count() AS parts, sum(rows) AS rows,
       formatReadableSize(sum(bytes_on_disk)) AS size
FROM system.parts WHERE active
GROUP BY database, table ORDER BY sum(bytes_on_disk) DESC;

-- Verify no pending mutations
SELECT * FROM system.mutations WHERE NOT is_done;

-- Verify no replication lag
SELECT database, table, queue_size, inserts_in_queue
FROM system.replicas WHERE queue_size > 0;

Error Handling

Issue	Detection	Action
Parts > 300	Monitoring alert	Review insert patterns, wait for merges
Disk > 80%	Disk alert	Add storage, drop old partitions
Query p95 > 5s	Latency alert	Check system.query_log for slow queries
Replication lag	Replica check	Investigate network, merge backlog

Resources

• ClickHouse Operations Guide
• Backup & Restore
• Monitoring with Prometheus

Next Steps

For SDK version upgrades, see clickhouse-upgrade-migration.