Imported from jeremylongshore/tons-of-skills-marketplace (plugins/saas-packs/clickhouse-pack/skills/clickhouse-prod-checklist/SKILL.md). Install upstream with npx skills add jeremylongshore/tons-of-skills-marketplace --skill clickhouse-prod-checklist. Copyright stays with the author (MIT).

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

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

• ClickHouse Operations Guide
• Backup & Restore
• Monitoring with Prometheus
• server configuration reference
• backup & recovery reference
• monitoring & verification reference

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.