DEV Community

Alex Spinov
Alex Spinov

Posted on

ClickHouse Has a Free Analytics Database That Queries Billions of Rows in Seconds

#clickhouse #analytics #database #bigdata

PostgreSQL struggles with analytics on 100M+ rows. BigQuery charges per query. ClickHouse processes billions of rows per second on a single server — it's the fastest open-source analytics database.

What ClickHouse Gives You for Free

  • Columnar storage — reads only the columns your query needs
  • Real-time analytics — insert and query simultaneously
  • Billions of rows/second — on commodity hardware
  • SQL compatible — standard SQL with extensions
  • Compression — 10-50x data compression
  • Materialized views — pre-computed aggregations
  • ClickHouse Cloud — free tier: 10GB storage, unlimited queries

Quick Start 

# Docker
docker run -d -p 8123:8123 -p 9000:9000 clickhouse/clickhouse-server

# Or brew
brew install clickhouse
clickhouse server
Enter fullscreen mode Exit fullscreen mode

Create a Table 

CREATE TABLE events (
    event_id UUID DEFAULT generateUUIDv4(),
    user_id UInt64,
    event_type String,
    properties String,
    timestamp DateTime DEFAULT now()
) ENGINE = MergeTree()
ORDER BY (user_id, timestamp);
Enter fullscreen mode Exit fullscreen mode

Insert Data (High Throughput) 

INSERT INTO events (user_id, event_type, properties) VALUES
    (1, 'page_view', '{"page": "/dashboard"}'),
    (1, 'click', '{"button": "upgrade"}'),
    (2, 'page_view', '{"page": "/pricing"}');
Enter fullscreen mode Exit fullscreen mode

ClickHouse can ingest millions of rows per second.

Analytics Queries (Lightning Fast) 

-- Page views per day (scans billions in <1s)
SELECT 
    toDate(timestamp) as day,
    count() as views,
    uniq(user_id) as unique_users
FROM events
WHERE event_type = 'page_view'
    AND timestamp > now() - INTERVAL 30 DAY
GROUP BY day
ORDER BY day;

-- Top events by user
SELECT 
    event_type,
    count() as total,
    uniq(user_id) as users
FROM events
WHERE timestamp > now() - INTERVAL 7 DAY
GROUP BY event_type
ORDER BY total DESC
LIMIT 20;

-- Funnel analysis
SELECT
    windowFunnel(86400)(
        timestamp,
        event_type = 'signup',
        event_type = 'onboarding_complete',
        event_type = 'first_purchase'
    ) AS funnel_step,
    count() AS users
FROM events
GROUP BY funnel_step;
Enter fullscreen mode Exit fullscreen mode

Node.js Client 

import { createClient } from '@clickhouse/client';

const client = createClient({ url: 'http://localhost:8123' });

const result = await client.query({
  query: `SELECT event_type, count() as total FROM events GROUP BY event_type`,
  format: 'JSONEachRow'
});

const data = await result.json();
console.log(data);
Enter fullscreen mode Exit fullscreen mode

Performance Comparison

Query ClickHouse PostgreSQL BigQuery
COUNT 1B rows 0.1s 120s 3s
GROUP BY (1B rows) 0.5s timeout 5s
JOIN 100M × 10M 2s timeout 8s
Storage (1TB raw) ~100GB ~800GB 1TB

ClickHouse vs PostgreSQL vs BigQuery vs Redshift

Feature ClickHouse PostgreSQL BigQuery Redshift
Price Free (self-hosted) Free Pay per query Per hour
Speed (analytics) Fastest Slow Fast Fast
Real-time insert Yes Yes Streaming Batch
SQL support Extended Full Standard Standard
Self-hosted Yes Yes No No
Best for Analytics OLTP Ad-hoc Warehouse

The Verdict

ClickHouse is the analytics database for teams who outgrew PostgreSQL but don't want BigQuery's bill. Billions of rows in seconds, 10x compression, and it runs on a single server. For analytics, nothing is faster.

Need help building production web scrapers or data pipelines? I build custom solutions. Reach out: spinov001@gmail.com

Check out my awesome-web-scraping collection — 400+ tools for extracting web data.

Top comments (0)