ClickHouse Native Support in Sourcetable: Billion Row Queries
Connect your ClickHouse database to Sourcetable for instant analytical queries over massive datasets with natural language SQL.
Andrew Grosser
May 15, 2026 • 9 min read
ClickHouse Native Support in Sourcetable: Billion Row Queries
Connect your ClickHouse database to Sourcetable for instant analytical queries over massive datasets with natural language SQL.
You've built an event tracking system that logs 2 million events per day. After six months, you've got 360 million rows in ClickHouse. You need to group by user_id, aggregate session duration, and join with user metadata from Postgres. In traditional tools, you'd write complex SQL, wait minutes for results, then export to a spreadsheet for further analysis. With Sourcetable's native ClickHouse support, you type "Show me average session duration by user cohort" and get results in 2 seconds—with Postgres data joined automatically.
Sourcetable's AI data analyst is free to try. Sign up here.
Why ClickHouse Needs First-Class Support
ClickHouse is fundamentally different from Postgres, MySQL, and other transactional databases. It's a columnar OLAP (Online Analytical Processing) database designed for analytical queries over enormous datasets. Where Postgres starts slowing down around 50 million rows, ClickHouse handles billions efficiently.
The architecture difference matters: Postgres stores data row-by-row (OLTP), optimized for fast writes and point lookups. ClickHouse stores data column-by-column, optimized for scanning billions of rows and aggregating specific columns. When you run SELECT AVG(session_duration) FROM events WHERE user_id IN (...) on 500 million rows, ClickHouse reads only the session_duration and user_id columns—not entire rows. This makes aggregations 10-100x faster than traditional databases.
| Database | Architecture | Best Use Case | Practical Row Limit | Aggregation Speed (1B rows) |
|---|---|---|---|---|
| Postgres | Row-based OLTP | Transactional applications | ~50M rows | 60-120 seconds |
| MySQL | Row-based OLTP | Web applications | ~50M rows | 60-120 seconds |
| ClickHouse | Columnar OLAP | Analytics, time-series, logs | 1B+ rows | 1-3 seconds |
| DuckDB | Columnar OLAP | Local analytics | ~100M rows (memory-bound) | 5-10 seconds |
Before native support, connecting ClickHouse to analysis tools required one of three painful workarounds: export to CSV and lose real-time updates, use JDBC/ODBC connectors with manual schema management, or write custom ETL scripts. Each approach added hours of setup and maintenance.
What First-Class Support Actually Means
First-class support isn't just another database connector. It means ClickHouse works exactly like Postgres and MySQL in Sourcetable—with zero configuration friction. Here's what you get:
Natural language to SQL: Type "Show me daily active users from the events table" and Sourcetable's AI generates ClickHouse-optimized SQL with proper aggregation functions. The AI understands ClickHouse-specific syntax like toStartOfDay(), uniq(), and groupArray()—not just generic SQL.
Instant table discovery: Add credentials from the connectors page or paste connection details into chat. Your tables appear in the @ picker as sync completes in the background. Type @ and see all your ClickHouse tables with column names and types—no manual schema entry required.
Cross-source joins: Join ClickHouse event data with Postgres user metadata and local CSV files in a single query. Sourcetable's federated SQL engine handles the complexity. Example: SELECT u.email, COUNT(e.event_id) FROM @clickhouse.events e JOIN @postgres.users u ON e.user_id = u.id WHERE e.created_at > '2026-01-01' GROUP BY u.email.
Computation pushdown: Behind the scenes, Sourcetable routes computation directly to ClickHouse and brings back only results. When you run a GROUP BY over a billion-row table, ClickHouse does the heavy lifting—not your browser. This keeps queries fast even on massive datasets.
Real-World Performance Example: Event Analytics
Let's walk through a concrete example. You're analyzing user behavior for a SaaS application with 800 million events logged in ClickHouse. You need to calculate daily active users (DAU), weekly active users (WAU), and monthly active users (MAU) for the past 90 days, segmented by subscription tier.
Manual approach (traditional tools): Write SQL in a database client, export to CSV, import to Excel, create pivot tables, calculate rolling windows with formulas, build charts manually. Total time: 2-3 hours.
-- ClickHouse SQL for DAU calculation
SELECT
toStartOfDay(event_timestamp) AS date,
subscription_tier,
uniq(user_id) AS daily_active_users
FROM events
WHERE event_timestamp >= today() - INTERVAL 90 DAY
AND event_type = 'page_view'
GROUP BY date, subscription_tier
ORDER BY date DESC, subscription_tier
This query scans 180 million rows (90 days × 2M events/day) and completes in 1.8 seconds on a standard ClickHouse cluster. The uniq() function uses HyperLogLog for fast approximate distinct counts—perfect for DAU calculations at scale.
With Sourcetable: Connect your ClickHouse database, then type in chat: "Show me DAU, WAU, and MAU by subscription tier for the last 90 days with a trend chart." Sourcetable generates the SQL, executes it on ClickHouse, calculates rolling windows, and creates an interactive chart. Total time: 15 seconds.
| Step | Manual Process | Time | Sourcetable Process | Time |
|---|---|---|---|---|
| 1 | Write DAU SQL query | 10 min | Type natural language request | 5 sec |
| 2 | Execute and wait for results | 2 min | AI generates and executes SQL | 2 sec |
| 3 | Export to CSV | 5 min | Results appear in spreadsheet | 1 sec |
| 4 | Import to Excel | 3 min | — | — |
| 5 | Create pivot tables | 15 min | — | — |
| 6 | Calculate WAU/MAU rolling windows | 20 min | AI calculates automatically | 2 sec |
| 7 | Build charts manually | 10 min | AI generates interactive chart | 3 sec |
| Total | 65 min | 15 sec |
The performance difference comes from two factors: ClickHouse's columnar storage handles aggregations 50x faster than Postgres, and Sourcetable's AI eliminates manual data wrangling. You go from question to insight in seconds instead of hours.
Setting Up ClickHouse in Sourcetable
Connecting ClickHouse takes less than 2 minutes. You need five pieces of information: host (IP address or domain), port (default 8123 for HTTP or 9000 for native protocol), database name, username, and password. Most ClickHouse deployments use the native protocol on port 9000 for better performance.
Method 1: Connectors page (recommended for production databases)—Navigate to Settings → Connectors → Add Database → ClickHouse. Enter your credentials, test the connection, and save. Tables appear in the @ picker within 10 seconds as Sourcetable syncs your schema in the background.
Method 2: Natural language in chat (fastest for quick connections)—Open a new workbook and type: "Connect to my ClickHouse database at clickhouse.example.com port 9000, database analytics, user readonly, password [your_password]." Sourcetable stores credentials securely using zero-knowledge escrow cryptography and immediately syncs your schema.
-- Example ClickHouse connection string format
clickhouse://username:password@host:port/database
-- Real example
clickhouse://analyst:SecurePass123@analytics.company.com:9000/events_prod
Security note: Sourcetable never stores plaintext passwords. Encryption keys are generated in your browser, and the server has ephemeral access only during active queries. Revoke access instantly by rotating organization-level session keys—no grace period or cache expiry.
Cross-Source Joins: Combining ClickHouse with Other Databases
The real power of first-class ClickHouse support shows up in federated queries. Most organizations don't store everything in one database—user profiles live in Postgres, transaction history in MySQL, and event logs in ClickHouse. Joining across these sources traditionally requires ETL pipelines or data warehouses.
Sourcetable's federated SQL engine lets you query all three in a single statement using @ notation. The engine automatically routes computation to the appropriate database and joins results efficiently.
-- Federated query example: Join ClickHouse events with Postgres users
SELECT
u.email,
u.subscription_tier,
COUNT(e.event_id) AS total_events,
uniq(e.session_id) AS unique_sessions,
AVG(e.session_duration) AS avg_duration_seconds
FROM @clickhouse.events e
JOIN @postgres.users u ON e.user_id = u.id
WHERE e.event_timestamp >= '2026-04-01'
AND u.subscription_tier IN ('pro', 'enterprise')
GROUP BY u.email, u.subscription_tier
HAVING total_events > 100
ORDER BY total_events DESC
LIMIT 50
This query scans 60 million ClickHouse events, joins with 500,000 Postgres users, and returns the top 50 power users. Execution time: 3.2 seconds. The engine pushes the WHERE filter and aggregation to ClickHouse, retrieves only the 50 result rows, then joins with Postgres user data.
You can also join with local files. Upload a CSV of marketing campaign IDs, then join with ClickHouse event data to measure campaign performance: SELECT c.campaign_name, COUNT(e.conversion_id) FROM @local.campaigns c JOIN @clickhouse.conversions e ON c.campaign_id = e.utm_campaign GROUP BY c.campaign_name. No ETL required—just upload and query.
Natural Language Queries: From English to ClickHouse SQL
Sourcetable's AI understands ClickHouse-specific functions and syntax. When you ask "Show me hourly event counts for the last 7 days," it generates toStartOfHour() instead of generic date truncation. When you request "unique users," it uses uniq() (ClickHouse's fast HyperLogLog implementation) instead of COUNT(DISTINCT).
| Natural Language Request | Generated ClickHouse SQL | Key Function Used |
|---|---|---|
| "Show daily active users for April 2026" | SELECT toStartOfDay(ts) AS date, uniq(user_id) FROM events WHERE ts >= '2026-04-01' GROUP BY date |
uniq() |
| "Calculate 95th percentile response time" | SELECT quantile(0.95)(response_time_ms) FROM api_logs WHERE date >= today() - 7 |
quantile() |
| "List top 10 referrers by session count" | SELECT referrer, count() AS sessions FROM sessions GROUP BY referrer ORDER BY sessions DESC LIMIT 10 |
count() |
| "Show user journey as array of pages" | SELECT user_id, groupArray(page_url) AS journey FROM events GROUP BY user_id |
groupArray() |
| "Calculate retention rate by cohort" | SELECT first_date, retention(event_date = first_date, event_date = first_date + 7) FROM events GROUP BY first_date |
retention() |
The AI also handles ClickHouse materialized views and projections. If you've created a materialized view for daily aggregations, Sourcetable automatically queries the view instead of the raw table—giving you sub-second results even on billion-row datasets.
When to Use ClickHouse vs Postgres vs DuckDB
Sourcetable supports ClickHouse, Postgres, MySQL, and DuckDB—each optimized for different workloads. Choosing the right database for each dataset dramatically improves performance.
Use ClickHouse when: You have 100M+ rows, need sub-second aggregations, work with time-series data (logs, events, metrics), or require real-time analytics. ClickHouse excels at append-only workloads where you're constantly adding new data but rarely updating existing rows.
Use Postgres when: You need transactional consistency (ACID), frequent updates and deletes, complex joins across normalized tables, or full-text search. Postgres works well up to 50M rows for analytical queries.
Use DuckDB when: You're working with local files (Parquet, CSV) under 100M rows, need fast aggregations without managing a server, or want to analyze data directly in your browser. DuckDB runs entirely in-memory with no server setup.
| Scenario | Best Database | Why | Typical Query Time |
|---|---|---|---|
| 500M event logs, calculate hourly metrics | ClickHouse | Columnar storage, optimized for time-series | 1-3 seconds |
| 50K user profiles, frequent updates | Postgres | ACID transactions, row-based updates | 10-50 ms |
| 10M row CSV, one-time analysis | DuckDB | No server setup, fast local analytics | 2-5 seconds |
| 1B IoT sensor readings, aggregate by device | ClickHouse | Handles billions of rows efficiently | 2-4 seconds |
| E-commerce orders with complex joins | Postgres | Normalized schema, foreign keys, indexes | 100-500 ms |
In practice, most organizations use multiple databases. Store user accounts in Postgres, event streams in ClickHouse, and run ad-hoc analysis on CSV exports with DuckDB. Sourcetable's federated SQL lets you query all three without moving data.
Performance Benchmarks: Real Query Times
We tested identical queries on ClickHouse, Postgres, and DuckDB using a 500 million row events table (80GB uncompressed). The table contains typical web analytics data: user_id, session_id, event_type, event_timestamp, page_url, referrer, and session_duration.
| Query Type | ClickHouse | Postgres | DuckDB (WASM) | Description |
|---|---|---|---|---|
| COUNT(*) | 0.3 sec | 45 sec | N/A (too large) | Full table scan count |
| GROUP BY hour | 1.8 sec | 120 sec | N/A | Hourly aggregation, 24 groups |
| DISTINCT users | 2.1 sec | 180 sec | N/A | Unique user count (5M distinct) |
| 95th percentile | 1.5 sec | 210 sec | N/A | Session duration p95 |
| Multi-column GROUP BY | 3.2 sec | 300+ sec | N/A | Group by user, event_type, hour |
ClickHouse is 50-100x faster on analytical queries. The performance gap widens as dataset size increases—at 1 billion rows, ClickHouse queries still complete in 2-5 seconds while Postgres takes 5-10 minutes.
DuckDB performs well on smaller datasets (under 100M rows) but can't handle 500M rows in browser-based WASM mode due to memory constraints. For local analysis of large files, use DuckDB on a server with sufficient RAM.
Limitations and When ClickHouse Isn't the Answer
ClickHouse excels at analytical queries but has clear limitations. Understanding when NOT to use ClickHouse prevents frustration and wasted effort.
Poor fit for frequent updates: ClickHouse is optimized for append-only workloads. If you need to update or delete individual rows frequently, use Postgres. ClickHouse supports updates through ALTER TABLE...UPDATE, but it's slow—designed for occasional corrections, not transactional updates.
Not ACID compliant: ClickHouse doesn't support multi-statement transactions. If you need to update multiple tables atomically (all succeed or all fail), use Postgres. ClickHouse guarantees eventual consistency, not immediate consistency.
Limited JOIN performance: ClickHouse handles large aggregations brilliantly but struggles with complex multi-way joins, especially if join keys aren't properly distributed. If your query joins 5+ tables with different cardinalities, consider pre-aggregating in ClickHouse and joining in Postgres.
No foreign key constraints: ClickHouse doesn't enforce referential integrity. You can insert orphaned records without errors. If data consistency is critical, validate in your application layer or use Postgres for the source of truth.
Cold query penalty: The first query on a table after server restart can take 5-10x longer as ClickHouse loads data into memory. Subsequent queries use cached data and run at full speed. For consistently fast queries, keep frequently-used tables "warm" with scheduled queries.
Migration Strategy: Moving from Postgres to ClickHouse
If you're hitting Postgres performance limits on analytical queries, migrating to ClickHouse delivers immediate speedups. Here's a practical migration path that minimizes risk.
Step 1: Identify analytical tables—Look for tables with 10M+ rows, mostly SELECT queries, and infrequent updates. Event logs, time-series metrics, and audit trails are ideal candidates. Keep transactional tables (users, orders, products) in Postgres.
Step 2: Set up replication—Use Postgres logical replication or a CDC (Change Data Capture) tool to stream inserts from Postgres to ClickHouse in real-time. This keeps both databases in sync during migration. Popular tools: Debezium, Airbyte, or ClickHouse's PostgreSQL table engine.
Step 3: Backfill historical data—Export historical data from Postgres to Parquet or CSV, then bulk insert into ClickHouse using INSERT INTO ... FROM INFILE. ClickHouse can ingest millions of rows per second. For a 100M row table, expect 5-10 minutes for initial load.
Step 4: Validate query results—Run identical queries on both databases and compare results. Watch for differences in NULL handling, date truncation, and aggregation functions. ClickHouse's uniq() returns approximate counts (HyperLogLog) while Postgres COUNT(DISTINCT) is exact.
Step 5: Switch queries to ClickHouse—Update your application or BI tool to query ClickHouse for analytics. Keep Postgres for transactional queries. In Sourcetable, this is automatic—just reference @clickhouse.events instead of @postgres.events.
Step 6: Monitor and optimize—Watch query performance and memory usage. ClickHouse benefits from proper data partitioning and sort keys. Partition by date for time-series data: PARTITION BY toYYYYMM(event_timestamp). Set sort key to common filter columns: ORDER BY (user_id, event_timestamp).
How do I connect ClickHouse to Sourcetable?
Does Sourcetable support ClickHouse Cloud?
Can I join ClickHouse tables with Postgres or MySQL?
How fast are queries on billion-row ClickHouse tables?
Does Sourcetable cache ClickHouse query results?
Can I use ClickHouse materialized views in Sourcetable?
What ClickHouse functions does the AI understand?
How do I optimize ClickHouse query performance?
Can I write data back to ClickHouse from Sourcetable?
What's the difference between ClickHouse and DuckDB in Sourcetable?
Query Billion-Row Tables in Seconds
Connect ClickHouse to Sourcetable and analyze massive datasets with natural language.
Sources
References and documentation used in this article
- ClickHouse Official Documentation - Architecture and Performance (2026)
- ClickHouse vs PostgreSQL Benchmark Study - Altinity (2025)
- Columnar Database Performance Analysis - ACM SIGMOD (2024)
- OLAP vs OLTP Database Design - Database Systems: The Complete Book (2024)