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 — for analytics, logs, and metrics. Here's how it works in practice.

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. But for analytics — it's 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 doesn't 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 automatically combines them. 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 — 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 isn't about result sorting — it's about 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 — 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 isn't in percentages — it's 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 — 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 — minimum 3 ClickHouse nodes + 3 ZooKeeper nodes (or ClickHouse Keeper — 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 sufficient. 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: < 10M rows/month — PostgreSQL handles it, > 100M — ClickHouse is justified
• [ ] 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