ClickHouse for Web Analytics: Materialized Views, MergeTree, PostgreSQL Integration

Why ClickHouse When You Already Have PostgreSQL

PostgreSQL is an excellent OLTP database: transactions, ACID, foreign keys, JSONB. But when the events table crossed 50 million rows and the analytics dashboard started responding in 12 seconds, it became clear that something different was needed.

ClickHouse is a columnar database built for analytical queries. Where PostgreSQL scans all columns of a row, ClickHouse reads only the columns you need. The difference is orders of magnitude on aggregations.

I connected ClickHouse to a production project as a second database: PostgreSQL stayed for core data, ClickHouse took analytics, logs and metrics.

Architecture: PostgreSQL + ClickHouse

Why "Both" Not "Either-Or"

ClickHouse doesn't replace PostgreSQL. It has no transactions, UPDATE works through mutations (asynchronously), DELETE too. It's not suitable for CRUD operations. For analytics it is ideal.

Typical architecture:

┌─────────────┐     ┌──────────────┐     ┌──────────────┐
│   Rails App  │────▶│  PostgreSQL  │     │  ClickHouse  │
│              │     │  (OLTP)      │     │  (OLAP)      │
│              │────▶│              │     │              │
└─────────────┘     └──────────────┘     └──────────────┘
       │                                        ▲
       │          ┌──────────────┐              │
       └─────────▶│  Background  │──────────────┘
                  │  Job (ETL)   │
                  └──────────────┘

The application writes core data to PostgreSQL. A background job (Sidekiq/SolidQueue) streams events and metrics to ClickHouse. Dashboards read from ClickHouse.

What to Store in ClickHouse

Simple rule: if data is written frequently, read with aggregations, and does not require UPDATE, it belongs in ClickHouse.

Installation and Configuration

Installing on Ubuntu

# Add ClickHouse repository
sudo apt-get install -y apt-transport-https ca-certificates curl gnupg
curl -fsSL 'https://packages.clickhouse.com/rpm/lts/repodata/repomd.xml.key' | sudo gpg --dearmor -o /usr/share/keyrings/clickhouse-keyring.gpg
echo "deb [signed-by=/usr/share/keyrings/clickhouse-keyring.gpg] https://packages.clickhouse.com/deb stable main" | sudo tee /etc/apt/sources.list.d/clickhouse.list
sudo apt-get update

# Install server and client
sudo apt-get install -y clickhouse-server clickhouse-client

# Start
sudo systemctl start clickhouse-server
sudo systemctl enable clickhouse-server

# Verify
clickhouse-client --query "SELECT version()"
# 24.8.x

Docker (for development)

# docker-compose.yml
services:
  clickhouse:
    image: clickhouse/clickhouse-server:24.8-alpine
    ports:
      - "8123:8123"   # HTTP interface
      - "9000:9000"   # Native protocol
    volumes:
      - clickhouse_data:/var/lib/clickhouse
      - ./clickhouse/config.xml:/etc/clickhouse-server/config.d/custom.xml
    environment:
      CLICKHOUSE_DB: analytics
      CLICKHOUSE_USER: app
      CLICKHOUSE_PASSWORD: secure_password
    ulimits:
      nofile:
        soft: 262144
        hard: 262144

volumes:
  clickhouse_data:

Basic Configuration

<!-- /etc/clickhouse-server/config.d/custom.xml -->
<clickhouse>
    <max_concurrent_queries>100</max_concurrent_queries>
    <max_server_memory_usage_to_ram_ratio>0.8</max_server_memory_usage_to_ram_ratio>

    <!-- Data compression -->
    <compression>
        <case>
            <min_part_size>10000000000</min_part_size>
            <min_part_size_ratio>0.01</min_part_size_ratio>
            <method>zstd</method>
            <level>3</level>
        </case>
    </compression>

    <!-- Query logging -->
    <query_log>
        <database>system</database>
        <table>query_log</table>
        <flush_interval_milliseconds>7500</flush_interval_milliseconds>
    </query_log>
</clickhouse>

Table Engines: MergeTree and Its Family

MergeTree: The Core Engine

CREATE TABLE page_views
(
    event_date Date,
    event_time DateTime,
    user_id UInt64,
    session_id String,
    url String,
    referrer String,
    device_type Enum8('desktop' = 1, 'mobile' = 2, 'tablet' = 3),
    country_code FixedString(2),
    browser String,
    os String,
    load_time_ms UInt16,
    is_bounce UInt8
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(event_date)
ORDER BY (event_date, user_id, session_id)
TTL event_date + INTERVAL 12 MONTH
SETTINGS index_granularity = 8192;

Breaking it down:

• PARTITION BY toYYYYMM(event_date) — data is split by month. A query for a specific month reads only one partition, not the entire table
• ORDER BY (eventdate, userid, session_id) — physical sort order on disk. This is the primary key. Queries on these columns use the sparse index and run instantly
• TTL event_date + INTERVAL 12 MONTH — automatic deletion of data older than one year. No manual cleanup needed
• index_granularity = 8192 — every 8192 rows, an entry is created in the primary index. Balance between index size and search precision

ReplacingMergeTree: For Deduplication

CREATE TABLE user_sessions
(
    session_id String,
    user_id UInt64,
    started_at DateTime,
    ended_at DateTime,
    page_count UInt16,
    total_time_seconds UInt32,
    version UInt32
)
ENGINE = ReplacingMergeTree(version)
PARTITION BY toYYYYMM(started_at)
ORDER BY (session_id);

ReplacingMergeTree keeps only the row with the maximum version for each unique key (ORDER BY) during merges. This solves the duplication problem on repeated inserts.

Important nuance: deduplication happens during background merges, not at INSERT time. Until a merge occurs, duplicates are visible. For guaranteed deduplication at read time, use FINAL:

-- Guaranteed deduplication
SELECT * FROM user_sessions FINAL WHERE user_id = 12345;

-- Or via subquery (faster on large volumes)
SELECT * FROM user_sessions
WHERE (session_id, version) IN (
    SELECT session_id, max(version)
    FROM user_sessions
    GROUP BY session_id
);

AggregatingMergeTree: Pre-aggregation

CREATE TABLE daily_metrics
(
    date Date,
    page String,
    views AggregateFunction(count, UInt64),
    unique_users AggregateFunction(uniq, UInt64),
    avg_load_time AggregateFunction(avg, Float32),
    bounce_rate AggregateFunction(avg, UInt8)
)
ENGINE = AggregatingMergeTree()
PARTITION BY toYYYYMM(date)
ORDER BY (date, page);

AggregatingMergeTree stores intermediate states of aggregate functions. During merges it combines them automatically. Result: queries against aggregated data run instantly, even on billions of source events.

SummingMergeTree: For Counters

CREATE TABLE hourly_counters
(
    hour DateTime,
    endpoint String,
    requests UInt64,
    errors UInt64,
    total_duration_ms UInt64
)
ENGINE = SummingMergeTree()
PARTITION BY toYYYYMMDD(hour)
ORDER BY (hour, endpoint);

During merges, rows with the same key are summed. Perfect for counters: just insert (now(), '/api/users', 1, 0, 45) on each request, and ClickHouse aggregates automatically.

Data Types: The Right Choice Saves 10x

Numeric Types

-- Bad: everything as UInt64
user_id UInt64,        -- 8 bytes
age UInt64,            -- 8 bytes (max 255 — why 8 bytes?)
is_active UInt64,      -- 8 bytes (0 or 1 — why 8 bytes?)

-- Good: appropriate types
user_id UInt64,        -- 8 bytes — IDs can be large
age UInt8,             -- 1 byte — sufficient up to 255
is_active UInt8,       -- 1 byte — boolean via UInt8
http_status UInt16,    -- 2 bytes — codes 100-599
price Decimal64(2),    -- 8 bytes — exact arithmetic for money

In a columnar database, every byte saved per value is multiplied by the number of rows. 100M rows × 7 saved bytes = 700MB less on disk and in memory.

String Types

-- FixedString for fixed-length data
country_code FixedString(2),     -- 'US', 'RU', 'DE' — always 2 bytes
currency FixedString(3),          -- 'USD', 'EUR', 'RUB'
ip_v4 IPv4,                       -- 4 bytes instead of ~15 for string

-- LowCardinality for repeating values
browser LowCardinality(String),   -- 'Chrome', 'Firefox', 'Safari' — dictionary encoding
os LowCardinality(String),        -- 'Windows', 'macOS', 'Linux'
device_type LowCardinality(String),

-- Enum for known value sets
status Enum8('active' = 1, 'inactive' = 2, 'banned' = 3),

LowCardinality is one of the most powerful techniques. For a browser column, ClickHouse creates a dictionary: {1: 'Chrome', 2: 'Firefox', 3: 'Safari'}, and stores only numeric IDs. On 100M rows, this saves 5-10x compared to plain String.

Dates and Time

event_date Date,                -- 2 bytes — date only
event_time DateTime,            -- 4 bytes — second precision
event_time_ms DateTime64(3),    -- 8 bytes — millisecond precision
event_time_us DateTime64(6),    -- 8 bytes — microsecond precision

Use Date for partitioning and day-level filtering, DateTime for regular timestamps, DateTime64(3) only when milliseconds matter (performance monitoring).

Materialized Views: Automatic Aggregation

The Concept

A Materialized View in ClickHouse is a trigger on INSERT. When data arrives in the source table, the materialized view automatically transforms and inserts it into the target table.

-- Source table: raw events
CREATE TABLE raw_events
(
    event_time DateTime,
    event_type LowCardinality(String),
    user_id UInt64,
    page String,
    duration_ms UInt32
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(event_time)
ORDER BY (event_time, user_id);

-- Target table: hourly aggregated metrics
CREATE TABLE hourly_page_stats
(
    hour DateTime,
    page String,
    views UInt64,
    unique_users UInt64,
    avg_duration Float32,
    max_duration UInt32
)
ENGINE = SummingMergeTree()
PARTITION BY toYYYYMM(hour)
ORDER BY (hour, page);

-- Materialized View: automatic aggregation
CREATE MATERIALIZED VIEW mv_hourly_page_stats
TO hourly_page_stats
AS
SELECT
    toStartOfHour(event_time) AS hour,
    page,
    count() AS views,
    uniqExact(user_id) AS unique_users,
    avg(duration_ms) AS avg_duration,
    max(duration_ms) AS max_duration
FROM raw_events
WHERE event_type = 'page_view'
GROUP BY hour, page;

Now on INSERT INTO raw_events, data is automatically aggregated into hourly_page_stats. Querying the aggregated table takes milliseconds instead of seconds.

Cascading Materialized Views

-- Level 1: hourly aggregation (from raw_events)
-- (already created above)

-- Level 2: daily aggregation (from hourly_page_stats)
CREATE TABLE daily_page_stats
(
    date Date,
    page String,
    views UInt64,
    unique_users UInt64,
    avg_duration Float32
)
ENGINE = SummingMergeTree()
PARTITION BY toYYYYMM(date)
ORDER BY (date, page);

CREATE MATERIALIZED VIEW mv_daily_page_stats
TO daily_page_stats
AS
SELECT
    toDate(hour) AS date,
    page,
    sum(views) AS views,
    sum(unique_users) AS unique_users,
    avg(avg_duration) AS avg_duration
FROM hourly_page_stats
GROUP BY date, page;

-- Level 3: monthly aggregation (from daily_page_stats)
CREATE TABLE monthly_page_stats
(
    month Date,
    page String,
    views UInt64,
    unique_users UInt64
)
ENGINE = SummingMergeTree()
ORDER BY (month, page);

CREATE MATERIALIZED VIEW mv_monthly_page_stats
TO monthly_page_stats
AS
SELECT
    toStartOfMonth(date) AS month,
    page,
    sum(views) AS views,
    sum(unique_users) AS unique_users
FROM daily_page_stats
GROUP BY month, page;

Three levels of aggregation: hour → day → month. Each level reads from the previous one. The query "how many views last year" hits monthly_page_stats, 12 rows instead of 500 million.

Conversion Funnel via Materialized View

CREATE TABLE conversion_funnel
(
    date Date,
    funnel_step LowCardinality(String),
    user_count UInt64,
    step_order UInt8
)
ENGINE = SummingMergeTree()
PARTITION BY toYYYYMM(date)
ORDER BY (date, step_order);

CREATE MATERIALIZED VIEW mv_conversion_funnel
TO conversion_funnel
AS
SELECT
    toDate(event_time) AS date,
    event_type AS funnel_step,
    uniqExact(user_id) AS user_count,
    CASE event_type
        WHEN 'page_view' THEN 1
        WHEN 'add_to_cart' THEN 2
        WHEN 'checkout_start' THEN 3
        WHEN 'payment_complete' THEN 4
    END AS step_order
FROM raw_events
WHERE event_type IN ('page_view', 'add_to_cart', 'checkout_start', 'payment_complete')
GROUP BY date, funnel_step, step_order;

Integration with Rails

The clickhouse-activerecord Gem

# Gemfile
gem 'clickhouse-activerecord', '~> 1.0'

# config/database.yml
production:
  primary:
    adapter: postgresql
    database: myapp_production
  clickhouse:
    adapter: clickhouse
    host: localhost
    port: 8123
    database: analytics
    username: app
    password: secure_password
    debug: false

# app/models/clickhouse_record.rb
class ClickhouseRecord < ActiveRecord::Base
    self.abstract_class = true
    connects_to database: { writing: :clickhouse, reading: :clickhouse }
end

# app/models/analytics/page_view.rb
module Analytics
  class PageView < ClickhouseRecord
    self.table_name = 'page_views'
  end
end

Direct Client (for Complex Queries)

# app/services/clickhouse_client.rb
class ClickhouseClient
  def initialize
    @connection = Faraday.new(url: ENV['CLICKHOUSE_URL'] || 'http://localhost:8123') do |f|
      f.request :url_encoded
      f.adapter Faraday.default_adapter
    end
  end

  def query(sql, format: 'JSON')
    response = @connection.post('/', "#{sql} FORMAT #{format}", {
      'X-ClickHouse-User' => ENV.fetch('CLICKHOUSE_USER', 'default'),
      'X-ClickHouse-Key' => ENV.fetch('CLICKHOUSE_PASSWORD', ''),
      'X-ClickHouse-Database' => ENV.fetch('CLICKHOUSE_DB', 'analytics'),
    })

    raise "ClickHouse error: #{response.body}" unless response.success?

    JSON.parse(response.body)
  end

  def insert(table, rows)
    columns = rows.first.keys
    values = rows.map { |r| columns.map { |c| format_value(r[c]) }.join(',') }
    sql = "INSERT INTO #{table} (#{columns.join(',')}) VALUES #{values.map { |v| "(#{v})" }.join(',')}"
    execute(sql)
  end

  def execute(sql)
    response = @connection.post('/', sql, {
      'X-ClickHouse-User' => ENV.fetch('CLICKHOUSE_USER', 'default'),
      'X-ClickHouse-Key' => ENV.fetch('CLICKHOUSE_PASSWORD', ''),
      'X-ClickHouse-Database' => ENV.fetch('CLICKHOUSE_DB', 'analytics'),
    })
    raise "ClickHouse error: #{response.body}" unless response.success?
    true
  end

  private

  def format_value(val)
    case val
    when String then "'#{val.gsub("'", "\\\\'")}'"
    when Time, DateTime then "'#{val.strftime('%Y-%m-%d %H:%M:%S')}'"
    when Date then "'#{val.strftime('%Y-%m-%d')}'"
    when nil then 'NULL'
    when true then '1'
    when false then '0'
    else val.to_s
    end
  end
end

ETL: Moving Data from PostgreSQL to ClickHouse

# app/jobs/sync_events_to_clickhouse_job.rb
class SyncEventsToClickhouseJob < ApplicationJob
  queue_as :analytics

  BATCH_SIZE = 10_000

  def perform
    last_synced_id = Rails.cache.read('clickhouse:last_event_id') || 0

    Event.where('id > ?', last_synced_id)
         .find_in_batches(batch_size: BATCH_SIZE) do |batch|
      rows = batch.map do |event|
        {
          event_time: event.created_at,
          event_type: event.event_type,
          user_id: event.user_id || 0,
          session_id: event.session_id || '',
          page: event.page || '',
          referrer: event.referrer || '',
          device_type: event.device_type || 'desktop',
          country_code: event.country_code || 'XX',
          browser: event.browser || 'Unknown',
          duration_ms: event.duration_ms || 0,
        }
      end

      ClickhouseClient.new.insert('raw_events', rows)
      Rails.cache.write('clickhouse:last_event_id', batch.last.id)

      Rails.logger.info("[ClickHouse Sync] Synced #{batch.size} events, last_id=#{batch.last.id}")
    end
  end
end

Queries: From Simple to Complex

Basic Analytics Queries

-- Top 10 pages for the last 7 days
SELECT
    page,
    count() AS views,
    uniqExact(user_id) AS unique_users,
    round(avg(duration_ms)) AS avg_duration_ms
FROM raw_events
WHERE event_type = 'page_view'
  AND event_time >= now() - INTERVAL 7 DAY
GROUP BY page
ORDER BY views DESC
LIMIT 10;

-- Hourly traffic for today
SELECT
    toStartOfHour(event_time) AS hour,
    count() AS views,
    uniqExact(user_id) AS users
FROM raw_events
WHERE event_type = 'page_view'
  AND event_date = today()
GROUP BY hour
ORDER BY hour;

-- Device distribution
SELECT
    device_type,
    count() AS count,
    round(count() * 100.0 / sum(count()) OVER (), 2) AS percentage
FROM raw_events
WHERE event_date >= today() - 30
GROUP BY device_type
ORDER BY count DESC;

Cohort Analysis

-- Cohorts by registration week: weekly retention
WITH cohorts AS (
    SELECT
        user_id,
        toMonday(min(event_time)) AS cohort_week
    FROM raw_events
    WHERE event_type = 'page_view'
    GROUP BY user_id
)
SELECT
    c.cohort_week,
    count(DISTINCT c.user_id) AS cohort_size,
    count(DISTINCT IF(
        toMonday(e.event_time) = c.cohort_week + INTERVAL 1 WEEK,
        e.user_id, NULL
    )) AS week_1,
    count(DISTINCT IF(
        toMonday(e.event_time) = c.cohort_week + INTERVAL 2 WEEK,
        e.user_id, NULL
    )) AS week_2,
    count(DISTINCT IF(
        toMonday(e.event_time) = c.cohort_week + INTERVAL 3 WEEK,
        e.user_id, NULL
    )) AS week_3,
    count(DISTINCT IF(
        toMonday(e.event_time) = c.cohort_week + INTERVAL 4 WEEK,
        e.user_id, NULL
    )) AS week_4
FROM cohorts c
LEFT JOIN raw_events e ON c.user_id = e.user_id
WHERE c.cohort_week >= today() - INTERVAL 8 WEEK
GROUP BY c.cohort_week
ORDER BY c.cohort_week;

Conversion Funnel with windowFunnel

-- Built-in funnel function — a killer feature of ClickHouse
SELECT
    level,
    count() AS users,
    round(count() * 100.0 / max(count()) OVER (), 2) AS conversion_rate
FROM (
    SELECT
        user_id,
        windowFunnel(86400)(
            event_time,
            event_type = 'page_view',
            event_type = 'add_to_cart',
            event_type = 'checkout_start',
            event_type = 'payment_complete'
        ) AS level
    FROM raw_events
    WHERE event_date >= today() - 30
    GROUP BY user_id
)
GROUP BY level
ORDER BY level;

windowFunnel is one of ClickHouse's killer features. It calculates which funnel step each user reached, accounting for a time window (86400 seconds = 24 hours). One query instead of a series of JOINs.

Moving Averages and Trends

-- 7-day moving average of views
SELECT
    date,
    views,
    round(avg(views) OVER (
        ORDER BY date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    )) AS moving_avg_7d,
    round(views * 100.0 / lagInFrame(views, 7) OVER (ORDER BY date) - 100, 1) AS wow_change_pct
FROM daily_page_stats
WHERE page = '/'
  AND date >= today() - 60
ORDER BY date;

User Path Analysis (Sessionization)

-- Most frequent 3-page sequences
SELECT
    path,
    count() AS frequency
FROM (
    SELECT
        user_id,
        session_id,
        arrayStringConcat(
            groupArray(page) OVER (
                PARTITION BY user_id, session_id
                ORDER BY event_time
                ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING
            ),
            ' → '
        ) AS path
    FROM raw_events
    WHERE event_type = 'page_view'
      AND event_date >= today() - 7
)
WHERE length(splitByString(' → ', path)) = 3
GROUP BY path
ORDER BY frequency DESC
LIMIT 20;

Performance Optimization

Correct ORDER BY

ORDER BY in ClickHouse controls the physical data layout on disk. It is the primary key.

-- Bad: ORDER BY high-cardinality field first
ORDER BY (user_id, event_date)
-- Query WHERE event_date = '2026-05-01' scans ALL data

-- Good: low-cardinality fields first
ORDER BY (event_date, user_id)
-- Query WHERE event_date = '2026-05-01' reads only one granule

Rule: in ORDER BY, fields you filter on most frequently go first. Usually that's the date.

Skip Indexes

ALTER TABLE raw_events
    ADD INDEX idx_url url TYPE bloom_filter(0.01) GRANULARITY 4;

ALTER TABLE raw_events
    ADD INDEX idx_country country_code TYPE set(100) GRANULARITY 4;

ALTER TABLE raw_events
    ADD INDEX idx_duration duration_ms TYPE minmax GRANULARITY 4;

• bloom_filter — for high-cardinality strings (URL, email). False positive ~1%
• set — for columns with limited value sets (country, browser). Stores the set of unique values per granule
• minmax — for numeric fields. Stores min/max per granule, and if the target value is outside the range the granule is skipped

Projections

ALTER TABLE raw_events
    ADD PROJECTION prj_by_user (
        SELECT
            user_id,
            event_type,
            event_time,
            page,
            duration_ms
        ORDER BY (user_id, event_time)
    );

ALTER TABLE raw_events MATERIALIZE PROJECTION prj_by_user;

A projection is an alternative physical data order. The main table is sorted by (event_date, user_id), the projection by (user_id, event_time). Queries WHERE user_id = X automatically use the projection.

Benchmarks: ClickHouse vs PostgreSQL on Real Queries

All tests on a table with 100M rows, same machine (4 cores, 16GB RAM).

The difference is in orders of magnitude. On aggregation queries over large volumes, ClickHouse is 100-200x faster.

Why:

• Columnar storage: query SELECT count(*) WHERE event_date = X reads only the event_date column (~2 bytes per row), not all 20 columns
• Vectorized execution: data processing with SIMD instructions, in batches of 8192 rows
• Compression: columns with repeating data compress 10-20x (LZ4/ZSTD)
• Sparse index: instead of B-tree — a sparse index with 8192-row granularity. On 100M rows that's ~12K index entries

Streaming Inserts and Buffering

Buffer Table for High Write Frequency

-- Main table
CREATE TABLE events_main (...)
ENGINE = MergeTree() ...;

-- Buffer table
CREATE TABLE events_buffer AS events_main
ENGINE = Buffer(
    analytics,          -- database
    events_main,        -- destination table
    16,                 -- num_layers
    10, 100,            -- min/max time (seconds)
    10000, 1000000,     -- min/max rows
    10000000, 100000000 -- min/max bytes
);

The application writes to events_buffer. ClickHouse accumulates data in memory and periodically flushes to events_main. This removes the overhead of high-frequency small INSERTs.

Async Inserts

-- Server-level setting
SET async_insert = 1;
SET wait_for_async_insert = 0;
SET async_insert_max_data_size = 10000000;
SET async_insert_busy_timeout_ms = 5000;

With async_insert, ClickHouse buffers small inserts and combines them into batches automatically. The client sends one row at a time and ClickHouse inserts in bulk. Perfect for event tracking.

Replication and High Availability

ReplicatedMergeTree + ZooKeeper

CREATE TABLE page_views_replicated ON CLUSTER '{cluster}'
(
    event_date Date,
    event_time DateTime,
    user_id UInt64,
    page String
)
ENGINE = ReplicatedMergeTree('/clickhouse/tables/{shard}/page_views', '{replica}')
PARTITION BY toYYYYMM(event_date)
ORDER BY (event_date, user_id);

For production with high availability you need at least 3 ClickHouse nodes and 3 ZooKeeper nodes, or ClickHouse Keeper as the built-in replacement. Data replicates automatically; if a node goes down, reads and writes continue.

For smaller projects (under 1TB of data, under 10K queries/sec) a single ClickHouse node is enough. Backups via clickhouse-backup.

Backup and Recovery

# Install clickhouse-backup
wget https://github.com/Altinity/clickhouse-backup/releases/download/v2.6.0/clickhouse-backup-linux-amd64.tar.gz
tar xzf clickhouse-backup-linux-amd64.tar.gz
sudo mv clickhouse-backup /usr/local/bin/

# Create backup
clickhouse-backup create daily_backup_$(date +%Y%m%d)

# List backups
clickhouse-backup list

# Restore
clickhouse-backup restore daily_backup_20260527

# Upload to S3
clickhouse-backup upload daily_backup_20260527

Monitoring ClickHouse

Prometheus + Grafana

# prometheus.yml
scrape_configs:
  - job_name: 'clickhouse'
    static_configs:
      - targets: ['localhost:9363']
    metrics_path: '/metrics'

ClickHouse exposes Prometheus metrics out of the box on port 9363. Key metrics to watch:

# Queries
ClickHouseProfileEvents_Query
ClickHouseProfileEvents_SelectQuery
ClickHouseProfileEvents_InsertQuery
ClickHouseProfileEvents_FailedQuery

# Performance
ClickHouseProfileEvents_RealTimeMicroseconds
ClickHouseProfileEvents_ReadRows
ClickHouseProfileEvents_ReadBytes

# Resources
ClickHouseMetrics_MemoryTracking
ClickHouseMetrics_BackgroundMergesAndMutationsPoolTask
ClickHouseAsyncMetrics_MaxPartCountForPartition

System Tables for Diagnostics

-- Current queries
SELECT query_id, elapsed, query FROM system.processes;

-- Merge health
SELECT
    database, table,
    count() AS parts,
    sum(rows) AS total_rows,
    formatReadableSize(sum(bytes_on_disk)) AS size
FROM system.parts
WHERE active
GROUP BY database, table
HAVING parts > 100
ORDER BY parts DESC;

-- Errors in the last hour
SELECT
    type,
    event_time,
    exception_code,
    exception,
    query
FROM system.query_log
WHERE type = 'ExceptionWhileProcessing'
  AND event_time >= now() - INTERVAL 1 HOUR
ORDER BY event_time DESC;

Final ClickHouse Implementation Checklist

Before You Start

• [ ] Identify which data to move: logs, events, metrics — not transactional data
• [ ] Estimate volume: PostgreSQL handles under 10M rows/month, ClickHouse is justified past 100M
• [ ] Choose integration method: clickhouse-activerecord gem or direct HTTP client
• [ ] Design schema with correct types: LowCardinality, Enum, FixedString

Table Design

• [ ] ORDER BY: low-cardinality fields first (date, event type)
• [ ] PARTITION BY: toYYYYMM(date) for most cases
• [ ] TTL: auto-delete old data (12 months for events, 36 for aggregates)
• [ ] Engine: MergeTree for append-only, ReplacingMergeTree for deduplication, SummingMergeTree for counters

Materialized Views

• [ ] Create MV for each aggregation level: hour → day → month
• [ ] Verify MV works: insert test data and query the target table
• [ ] Cascading MVs: verify data flows through all levels

ETL and Synchronization

• [ ] Background job for transferring data from PostgreSQL
• [ ] Idempotency: re-runs don't create duplicates
• [ ] Lag monitoring: difference between last event in PostgreSQL and ClickHouse

Production

• [ ] Backups: daily clickhouse-backup + upload to S3
• [ ] Monitoring: Prometheus + Grafana dashboard for ClickHouse
• [ ] Alerts: too many parts, high memory usage, failed queries
• [ ] Load testing: test with realistic data volumes
• [ ] Documentation: data schema, ETL pipeline, key queries