SELECT queries in ClickHouse: differences from PostgreSQL, PREWHERE, SAMPLE

SELECT Queries in ClickHouse: How I Retrained My Brain After 10 Years of PostgreSQL

When I sat down with ClickHouse after ten years of PostgreSQL, I tried running a familiar SELECT * FROM bets WHERE sport = 'football' ORDER BY created_at DESC LIMIT 10. It worked, but fast — suspiciously fast. Then I learned about PREWHERE and SAMPLE — constructs that don't and can't exist in PostgreSQL due to row-based storage.

ClickHouse doesn't just execute SQL — it rethinks it for a columnar architecture. Below are all the differences that broke my queries (and sometimes production).

1. Basic Syntax: A Familiar Face with a Columnar Character

A basic SELECT looks familiar:

-- Simple selection
SELECT user_id, amount, odds 
FROM betting.bets 
WHERE created_at >= today() - 7 
ORDER BY amount DESC 
LIMIT 100;

But the difference starts when you look at EXPLAIN. PostgreSQL builds plans with Seq Scan, Index Scan, Bitmap Heap Scan. ClickHouse shows the number of granules and partitions read.

Key difference: In PostgreSQL, SELECT * is sometimes fine (if you need almost all columns). In ClickHouse, SELECT * reads all columns from disk. If you don't need 20 out of 30 columns — list only the ones you need. We saved 70% disk I/O this way.

2. PREWHERE — An Optimization PostgreSQL Envies

PREWHERE filters BEFORE columns are unpacked and read.

-- Without PREWHERE (slower)
SELECT user_id, amount, odds, sport
FROM betting.bets
WHERE outcome = 'win' AND amount > 1000;

-- With PREWHERE (faster)
SELECT user_id, amount, odds, sport
FROM betting.bets
PREWHERE outcome = 'win'
WHERE amount > 1000;

How it works:

1. ClickHouse first reads the outcome column (one file on disk)
2. Filters rows, keeping only win
3. Only for the filtered rows, reads the remaining columns
4. Then applies amount > 1000

When ClickHouse applies PREWHERE automatically: If you write WHERE outcome = 'win', the optimizer may automatically move lightweight conditions to PREWHERE. But I always write it explicitly for complex conditions.

What burned me: PREWHERE doesn't work with columns from PRIMARY KEY. ClickHouse still reads the index first. Don't try to optimize what's already fast.

3. SAMPLE — Approximate Answer in 0.1 Seconds

In business, you sometimes get questions like: "Estimate the approximate volume of bets per hour, give or take 5%." Absolute accuracy isn't needed.

-- 10% random rows (SAMPLE 0.1)
SELECT 
    toHour(created_at) AS hour,
    count() * 10 AS estimated_total_bets
FROM betting.bets
SAMPLE 0.1
WHERE created_at >= now() - INTERVAL 1 HOUR
GROUP BY hour;

How SAMPLE works physically: ClickHouse reads not every granule entirely, but every Nth one. This works because data on disk is not shuffled — within a granule, it's sorted by ORDER BY.

My rules for SAMPLE:

• For aggregates with billions of rows — SAMPLE 0.01 is enough with 2-3% accuracy
• Don't use for exact calculations (finance, payouts)
• Only works if the table was created with a SAMPLE BY key (or ORDER BY)

4. FINAL — A Minefield for Beginners

If you use ReplacingMergeTree (a deduplication engine), rows may have multiple versions. FINAL forces ClickHouse to merge them on the fly.

-- Slow (but sometimes necessary)
SELECT user_id, max(amount)
FROM betting.bets_replacing
FINAL
GROUP BY user_id;

Why I almost never use FINAL: It forces reading all parts and merging them in memory. If you have a billion rows, the query will run out of memory.

Alternatives to FINAL:

• Grouping with argMax (recommended)
• Periodic OPTIMIZE TABLE ... FINAL in the background
• Don't use ReplacingMergeTree at all

-- Instead of FINAL
SELECT user_id, argMax(amount, version) AS last_amount
FROM betting.bets_replacing
GROUP BY user_id;

5. IN/NOT IN with Subqueries vs JOIN

In PostgreSQL, JOIN is often faster than subqueries. In ClickHouse, the opposite is true — IN with a subquery often wins.

-- Fast in ClickHouse
SELECT user_id, sum(amount)
FROM betting.bets
WHERE user_id IN (SELECT user_id FROM betting.fraud_users)
GROUP BY user_id;

-- Slower (but more readable)
SELECT b.user_id, sum(b.amount)
FROM betting.bets b
JOIN betting.fraud_users f ON b.user_id = f.user_id
GROUP BY b.user_id;

Why IN is faster: ClickHouse turns the subquery into a set of constants in memory and filters using columnar operations. JOIN requires row-by-row matching.

When JOIN is still needed:

• More than two tables
• Need columns from both tables in SELECT
• Complex join conditions (not just equality)

6. ANY / ALL Modifiers — Relics from Early Days

These modifiers exist for compatibility with other DBMS. I rarely use them.

-- ANY: like MIN for grouping
SELECT user_id, ANY(sport) AS any_sport
FROM betting.bets
GROUP BY user_id;

-- ALL: like MAX
SELECT user_id, ALL(amount) AS all_amounts  -- array of all amounts
FROM betting.bets
GROUP BY user_id;

But I prefer explicit aggregations: min(), max(), groupArray().

7. DISTINCT and Its Performance

SELECT DISTINCT in ClickHouse is faster than in PostgreSQL, but not free.

-- All unique sports
SELECT DISTINCT sport FROM betting.bets;

-- DISTINCT with ORDER BY
SELECT DISTINCT user_id, created_at
FROM betting.bets
ORDER BY created_at DESC
LIMIT 100;

Under the hood: ClickHouse builds a hash table in memory. If you run DISTINCT on a column with a billion unique values — you'll get OOM.

My tips:

• Instead of SELECT DISTINCT user_id, use GROUP BY user_id (same result)
• For approximate unique count — uniq() and uniqHLL12()
• For top unique values — topK()

8. FORMAT — Output as the Client Needs

ClickHouse can return results in dozens of formats. I use five:

-- Human-readable (for console)
SELECT * FROM bets LIMIT 3 FORMAT Pretty;

-- Compact (default)
SELECT * FROM bets LIMIT 3 FORMAT PrettyCompact;

-- JSON for API
SELECT * FROM bets LIMIT 3 FORMAT JSON;

-- Row-by-row JSON (memory-saving when parsing)
SELECT * FROM bets LIMIT 3 FORMAT JSONEachRow;

-- CSV for Excel
SELECT * FROM bets LIMIT 3 FORMAT CSV;

# In command line, you can override the format
clickhouse-client --format=JSON --query="SELECT * FROM bets LIMIT 3"

9. Top 10 Queries for Betting Analytics (Production-Ready)

1. Bets by sport today

SELECT 
    sport,
    count() AS bets,
    sum(amount) AS total_staked,
    round(avg(odds), 2) AS avg_odds
FROM betting.bets
WHERE created_at >= today()
GROUP BY sport
ORDER BY total_staked DESC;

2. Top 10 players by turnover this week

SELECT 
    user_id,
    count() AS bets,
    sum(amount) AS total_staked,
    sumIf(amount * odds, outcome = 'win') AS total_won,
    round(total_won / total_staked, 4) AS roi
FROM betting.bets
WHERE created_at >= today() - 7
GROUP BY user_id
ORDER BY total_staked DESC
LIMIT 10;

3. Hourly bet volume today

SELECT 
    toHour(created_at) AS hour,
    count() AS bets,
    sum(amount) AS volume
FROM betting.bets
WHERE created_at >= today()
GROUP BY hour
ORDER BY hour;

4. Win rate by odds range

SELECT 
    CASE 
        WHEN odds < 1.5 THEN '1.00-1.49'
        WHEN odds < 2.0 THEN '1.50-1.99'
        WHEN odds < 3.0 THEN '2.00-2.99'
        ELSE '3.00+'
    END AS odds_range,
    count() AS total_bets,
    countIf(outcome = 'win') AS wins,
    round(wins / total_bets, 4) AS win_rate
FROM betting.bets
WHERE created_at >= today() - 7
GROUP BY odds_range
ORDER BY odds_range;

5. Most active hours by day of week

SELECT 
    toDayOfWeek(created_at) AS dow,
    toHour(created_at) AS hour,
    count() AS bets
FROM betting.bets
WHERE created_at >= today() - 30
GROUP BY dow, hour
ORDER BY dow, hour;

6. Average bet and odds by user (LTV)

SELECT 
    user_id,
    avg(amount) AS avg_bet,
    avg(odds) AS avg_odds,
    count() AS total_bets,
    now() - max(created_at) AS hours_since_last_bet
FROM betting.bets
GROUP BY user_id
HAVING total_bets > 100
ORDER BY avg_bet DESC
LIMIT 50;

7. Approximate unique players per hour

SELECT 
    toStartOfHour(created_at) AS hour,
    uniq(user_id) AS unique_users_approx,
    uniqExact(user_id) AS unique_users_exact
FROM betting.bets
WHERE created_at >= today() - 1
GROUP BY hour
ORDER BY hour;

8. Payouts and refunds by day

SELECT 
    toDate(created_at) AS day,
    sum(amount) AS staked,
    sumIf(amount * odds, outcome = 'win') AS paid,
    sumIf(amount, outcome = 'void') AS refunded,
    round((paid + refunded) / staked, 4) AS net_hold_pct
FROM betting.bets
GROUP BY day
ORDER BY day DESC
LIMIT 30;

9. Parlays vs single bets

SELECT 
    bet_type,
    count() AS bets,
    avg(amount) AS avg_stake,
    avg(odds) AS avg_odds,
    avgIf(amount * odds, outcome = 'win') AS avg_payout
FROM betting.bets
GROUP BY bet_type;

10. Users with suspicious patterns (fraud)

SELECT 
    user_id,
    count() AS bets_5min,
    stddevPop(amount) AS stake_variance,
    stddevPop(odds) AS odds_variance
FROM betting.bets
WHERE created_at >= now() - INTERVAL 5 MINUTE
GROUP BY user_id
HAVING bets_5min > 30 AND stake_variance < 1 AND odds_variance < 0.1;

10. Common Mistakes When Migrating SQL from PostgreSQL to ClickHouse

Mistake 1: Using SELECT * in subqueries
In PostgreSQL, it's fine. In ClickHouse, it reads all columns at every level.

Mistake 2: Expecting ORDER BY in a subquery to persist
In ClickHouse, subqueries don't guarantee order, even with ORDER BY. Only sort at the top level.

Mistake 3: Correlated subqueries
ClickHouse optimizes correlated subqueries poorly. Rewrite them as JOINs or use window functions.

-- Bad (slow)
SELECT user_id, amount
FROM bets b1
WHERE amount = (SELECT max(amount) FROM bets b2 WHERE b2.user_id = b1.user_id);

-- Good (fast)
SELECT user_id, max(amount) AS max_amount
FROM bets
GROUP BY user_id;

Mistake 4: Expecting transactional integrity
ClickHouse doesn't have REPEATABLE READ. If you insert data during a query — you might see part of it.

Mistake 5: UPDATE and DELETE without ALTER TABLE
In ClickHouse, these are mutations, asynchronous and heavy. Don't update a million rows with one command.

What's Next

Now you know how to write SELECT in ClickHouse without surprises. In the next article — advanced aggregations and window functions.

---

← Previous: Loading Data into ClickHouse: How I Stopped Inserting One Row at a Time and Accelerated Ingestion by 500x
→ Next: ClickHouse Aggregate Functions: How I Stopped Fearing uniqHLL12 and quantileTDigest

— Editorial Team