Production & Use Cases

Everything you need to run ClickHouse in production: monitoring, backups, security, data ingestion, and real-world architecture patterns.

📊 Monitoring

-- System tables for monitoring
SELECT * FROM system.metrics;        -- Current metrics
SELECT * FROM system.events;         -- Cumulative counters
SELECT * FROM system.query_log;      -- Query history
SELECT * FROM system.parts;          -- Data parts info
SELECT * FROM system.replicas;       -- Replication status

-- Useful monitoring queries
SELECT
    database,
    table,
    formatReadableSize(sum(bytes_on_disk)) AS size,
    sum(rows) AS total_rows,
    count() AS parts
FROM system.parts
WHERE active
GROUP BY database, table
ORDER BY sum(bytes_on_disk) DESC;

💡 Grafana + ClickHouse

The most popular monitoring setup: export ClickHouse metrics to Prometheus and visualize in Grafana. ClickHouse has a built-in Prometheus endpoint at /metrics.

💾 Backups

-- Built-in backup (ClickHouse 22.8+)
BACKUP TABLE events TO Disk('backups', 'events_backup.zip');

-- Restore
RESTORE TABLE events FROM Disk('backups', 'events_backup.zip');

-- Backup entire database
BACKUP DATABASE default TO S3('https://bucket.s3.amazonaws.com/backups/');

⚠️ Backup Strategy

Daily incremental backups to S3/GCS
Weekly full backups
Test restores monthly
Replication is NOT a backup!

🔐 Security

-- Create users with specific permissions
CREATE USER analyst IDENTIFIED BY 'strong_password';
GRANT SELECT ON analytics.* TO analyst;

CREATE USER etl_service IDENTIFIED BY 'service_password';
GRANT INSERT ON raw_data.* TO etl_service;

-- Row-level security
CREATE ROW POLICY us_only ON events
FOR SELECT
USING country = 'US'
TO us_analyst;

📥 Data Ingestion Patterns

Method	Speed	Use Case
clickhouse-client	~1M rows/sec	Batch loads, CSV/TSV files
HTTP API	~500K rows/sec	Application inserts, REST APIs
Kafka Engine	~2M rows/sec	Real-time streaming from Kafka
S3 Table Function	~5M rows/sec	Bulk loads from cloud storage
clickhouse-local	~3M rows/sec	Local file processing

-- Load from S3
INSERT INTO events
SELECT * FROM s3(
    'https://bucket.s3.amazonaws.com/data/*.parquet',
    'AWS_KEY', 'AWS_SECRET',
    'Parquet'
);

-- Stream from Kafka
CREATE TABLE events_kafka (
    event_time DateTime,
    user_id UInt64,
    event_type String
) ENGINE = Kafka
SETTINGS
    kafka_broker_list = 'kafka:9092',
    kafka_topic_list = 'events',
    kafka_group_name = 'clickhouse_consumer',
    kafka_format = 'JSONEachRow';

🏢 Real-World Architecture

🎓 Course Complete!

🏆 Congratulations!

You've completed the ClickHouse course! You now understand:

Columnar storage and why it's fast
MergeTree and its variants
Data types and schema design
SQL queries, aggregations, and window functions
Performance optimization techniques
Materialized views for real-time aggregation
Replication, sharding, and production deployment

Next steps: Build a real project! Try loading your company's data into ClickHouse and see the speed difference yourself.

Replication Back to Hub

October 2026 practice

Checked against the ClickHouse documentation in October 2026. The JSON type has been production-ready since version 25.3. ClickHouse Cloud rewrites a MergeTree create onto the matching SharedMergeTree engine.

Read Iceberg with the icebergS3 table function or the Iceberg table engine. v1 and v2 reads are supported. v3 reads are partial: deletion vectors can be read, and manifest compaction is not supported. The engine docs warn that externally changing schemas do not behave like a normal MergeTree. Enable allow_insert_into_iceberg only when you mean to write, and expect the documented write limits. Read Delta with the deltaLake table function. Treat lake writes as a separate, limited path.

Kafka ingestion is a Kafka engine table plus a materialized view into MergeTree. Do not point dashboards at the Kafka table. Delivery is at-least-once. An identical retry block can be deduplicated when the target is replicated. A rebalance can still duplicate rows. Storing offsets in Keeper is a separate, explicit engine mode. Self-hosted backups use BACKUP and RESTORE. ClickHouse Cloud backups are a service control, not that local statement. Watch system.query_log, system.parts, and system.replicas. Put passwords in a named collection or a user secret, and use TLS and row policies for access.

CREATE TABLE events_kafka (payload String)
ENGINE = Kafka
SETTINGS kafka_broker_list = 'broker:9092',
         kafka_topic_list = 'events',
         kafka_group_name = 'clickhouse',
         kafka_format = 'JSONAsString';

CREATE MATERIALIZED VIEW events_kafka_mv TO events
AS SELECT payload FROM events_kafka;