ClickHouse Production Checklist Skill

ClickHouse Production Checklist

Overview

Comprehensive go-live checklist for ClickHouse covering schema and engine design, server tuning, backup configuration, monitoring, security, application integration, and operational readiness. Walk the eight sections top to bottom; each is a gate that must be green before production traffic is allowed. Deep configuration — server XML, backup SQL, and verification queries — lives in references/ so this file stays a fast operational scan.

Prerequisites

Before starting the checklist, confirm the following are in place:

A ClickHouse instance is provisioned (ClickHouse Cloud or self-hosted) and reachable from the environment you will run traffic from.
Application integration code has been exercised end-to-end in a staging environment against a ClickHouse of the same major version.
You have admin credentials able to read system.* tables and, for self-hosted, edit config.xml / users.xml.

Instructions

Work each section in order. Every box must be checked (or explicitly waived with a reason) before go-live.

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)

Set memory to ~80% of RAM, cap per-query memory and execution time, and size the merge pools to the core count. ClickHouse Cloud manages these for you.

<max_server_memory_usage_to_ram_ratio>0.8</max_server_memory_usage_to_ram_ratio>
<max_concurrent_queries>150</max_concurrent_queries>
<max_execution_time>300</max_execution_time>  <!-- 5 min: cap runaway scans -->

Full annotated config.xml / users.xml block and tuning rationale: server configuration reference.

3. Backup Configuration

[ ] 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

Native BACKUP ... TO S3 syntax, incremental base+delta backups, and recovery drill guidance: backup & recovery reference.

4. Monitoring & Alerting

[ ] Prometheus endpoint configured (/metrics via 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

Health-check queries and the full metrics list: monitoring & verification reference.

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 (see reference for the TypeScript /health handler)

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

Immediately before flipping traffic, run the three go-live verification queries (part health, pending mutations, replication lag) from the monitoring & verification reference under "Go-live verification queries". A clean launch returns zero rows from the mutations and replicas queries.

Output

Completing this checklist produces a go / no-go readiness decision:

Section-by-section status — each of the eight gates marked pass, or failed with the specific unchecked item.
Verification query results — part-health, mutation, and replication-lag output captured as launch evidence.
Go-live decision — GO only when all eight sections are green and the verification queries return the expected clean-launch results; otherwise a NO-GO with the blocking items listed.

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 |

Examples

Minimal health probe (load-balancer readiness check):

SELECT 1;

Go-live part-health check (fails the launch if any table exceeds its active-parts threshold):

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;

For the full thorough health-check query, the incremental S3 backup example, and the annotated server-config block, see the references/ files linked from each section above.

Resources

Next Steps

After a clean go-live, keep the deployment healthy: schedule a periodic backup restore drill, wire the monitoring alerts into your on-call rotation, and review clickhouse-incident-runbook for response procedures. For SDK version upgrades, see clickhouse-upgrade-migration.

Agent Skills: ClickHouse Production Checklist

Install this agent skill to your local

Skill Files