DEV Community: Aman Puri

How to build real-time customer-facing analytics on Postgres (without slowing down OLTP)

Aman Puri — Fri, 26 Jun 2026 19:09:20 +0000

SaaS applications rely on Postgres as the source of truth for user, product, and transactional data. But exposing that data back to users through real-time, in-product dashboards creates a problem: analytical queries frequently grind your transactional database to a halt.

Customer-facing dashboards aren't like internal BI reports. They live inside your product, where every active user means another concurrent query. Your latency budget here is tight. Web performance research shows that users perceive responses under 100 milliseconds as instantaneous. Slower than that starts eroding the experience; anything in seconds feels broken.

This guide walks through a two-stage approach for scaling Postgres-backed analytics. We'll cover how to push native Postgres to its limits using materialized views and advanced indexing, then define the exact triggers that tell you when to move on. From there, we'll detail how to implement a real-time OLAP architecture that eliminates the operational headache of DIY data pipelines. The goal: sub-100 ms dashboards without compromising your primary database.

TL;DR

Customer-facing analytics is a different workload from BI: it needs sub-100 ms p95 on analytical queries, high concurrency, freshness within the SLA tier you commit to customers, strict tenant isolation, and predictable cost.
Stage 1 (Postgres-only): use pre-aggregated tables/materialized views, composite indexes starting with tenant_id, careful RLS, and BRIN for time-series.
Graduate when 2+ breaking points hit: buffer cache churn, million-row scans per request, high-cardinality index bloat, tighter freshness SLAs, or ad-hoc filtering needs.
Stage 2 (Postgres + CDC + real-time OLAP): stream changes via CDC into a columnar OLAP database (e.g., ClickHouse) and serve dashboards from OLAP, not replicas or warehouses.
Avoid DIY CDC outages: replication slots/WAL retention can fill disks; prefer managed CDC connectors.

What customer-facing analytics requires (eight non-negotiables)

Most database performance escalations happen because teams treat all data retrieval as one big "analytics" workload. When engineers complain that Postgres can't handle their analytics anymore, they're usually lumping together four completely different workloads.

OLTP handles high-throughput, row-level reads and writes powering your core application. Internal BI runs heavy aggregations for executives and analysts, where queries can safely take minutes. Ad-hoc data science is unpredictable, exploratory work running in notebooks. Customer-facing analytics is the in-product dashboards and reporting exposed directly to your end users.

Customer-facing analytics is by far the strictest of these four. It demands sub-100 ms latency like a hot OLTP query path combined with complex aggregations like BI. It requires strict tenant isolation to prevent data leaks. It must support concurrency that scales linearly with your active user base.

Because of this profile, any solution needs to meet eight non-negotiable requirements. Use these as your benchmark for deciding when to optimize Postgres and when to move to a dedicated OLAP database.

1. Sub-100 ms p95 query latency

For an API response that feels instant, your data store needs to return results in under 100 milliseconds at p95 on analytical queries. If your database takes 500 milliseconds to aggregate data, network overhead and front-end rendering will push total latency well past what feels sluggish.

2. Linear concurrency scaling

In an internal BI tool, fifty concurrent queries is heavy load. In a customer-facing dashboard, fifty concurrent queries is a quiet Tuesday morning. Your data layer needs to handle hundreds or thousands of simultaneous requests without exhausting connection pools or queueing queries behind each other.

3. Tunable freshness per dashboard

Different features need different freshness guarantees. A monthly billing rollup tolerates a 30-minute delay. A live marketplace inventory dashboard or security audit log needs seconds-level freshness. Your architecture needs to support streaming ingestion for those strict SLAs.

Pick the freshness tier intentionally per dashboard, not as a single global setting. Each tier maps to a different architecture:

Freshness tier	Recommended architecture

| 5 to 30 minutes | Scheduled rollup jobs against Postgres summary tables; standard cron or workers |
| 1 to 5 minutes | Incremental rollup workers; hot summary tables refreshed per write batch |
| 1 to 10 seconds | CDC stream from Postgres into a real-time OLAP database like ClickHouse |

Most B2B SaaS customer-facing dashboards live in the 1 to 10 second tier. Tighter than that is rare and usually only matters for fraud signals or trading-style tooling.

The looser tiers are not a free pass. As data volume grows, the rollup jobs themselves become expensive: minute-to-hour-long aggregations that consume primary database resources and contend with OLTP. The granularity of rollups you can afford shrinks as the underlying data grows. Polymarket hit this even though their customer-facing dashboards did not need second-level freshness.

4. Strict tenant isolation

B2B SaaS applications are inherently multi-tenant. Every analytical query must be strictly bounded by a tenant identifier. Whether you implement this through API-layer authorization or database-level Row Level Security, cross-tenant data leakage is catastrophic. Your architecture must prevent it by design.

5. Pre-aggregations and materialized views

You can't compute heavy metrics from raw event data on every page load. Your system needs to pre-aggregate data into summary tables or materialized views, absorbing compute cost at ingestion time rather than query time.

6. High-cardinality column support

SaaS analytics frequently require filtering and grouping by high-cardinality dimensions: user IDs, API keys, session tokens, unique SKU numbers. Your database needs to index and query these columns efficiently without suffering from massive index bloat or degraded scan performance.

7. Time-series-friendly storage

Dashboard queries are almost exclusively time-windowed: "Show me API requests over the last 7 days grouped by hour." Your storage engine needs to be optimized for time-series access patterns, physically ordering data by time so the query engine can skip irrelevant date ranges.

8. Predictable cost scaling

As your user base grows, the cost of serving analytical queries needs to scale predictably. Architectures that couple storage and compute too tightly, or price based on scanned data volume per query, will produce explosive cloud bills as product adoption increases.

A common failure mode is running raw analytical queries directly against your OLTP tables. Complex aggregations over the live transactional schema scan large volumes of data and evict hot operational data from Postgres shared buffers. As cache hit ratio drops, your transactional application slows down, lock contention spikes, and dashboards become unusable.

The pattern that does work for early-stage Postgres analytics is pre-aggregation: absorb the compute cost at ingestion time into summary tables, and serve dashboards from those rather than the OLTP schema. That is exactly what Stage 1 is.

Stage 1: run customer-facing analytics on Postgres only

Most B2B SaaS startups should start their analytics journey entirely within Postgres. The operational benefits of a small surface area (a single database, backup strategy, and security boundary) are worth more than the performance ceiling at this stage.

The sweet spot for Stage 1: an early-stage product, moderate data volumes, and dashboards that can tolerate one to five minute data freshness (matching the rollup-tier of the freshness SLA matrix above).

How Stage 1 works

Your application writes directly to normalized OLTP tables. Rather than querying these tables for dashboards, you implement scheduled or incremental background jobs to build pre-aggregated analytics tables. A cron job might aggregate raw user events into an org_daily_metrics table. Your customer-facing API then reads strictly from these pre-aggregated tables, applying tenant filters for data isolation.

Postgres tactics that extend Stage 1

To maximize Postgres for this workload, you need several specific database tactics.

Composite indexes starting with the tenant identifier, like (org_id, timestamp), are mandatory. They ensure queries only scan data belonging to the requesting customer.

If you implement Row Level Security (RLS) for tenant isolation, design your policies carefully. Naive RLS policies that join to permissions tables add tens to hundreds of milliseconds per query. Optimized RLS strategies use pre-calculated session variables to evaluate permissions, reducing overhead to under a millisecond.

Materialized views offer a native way to pre-compute complex aggregations. But refreshing a standard materialized view acquires an exclusive lock, blocking all reads. To avoid this, use REFRESH MATERIALIZED VIEW CONCURRENTLY. This allows concurrent reads, but requires the view to have at least one unique index.

When Stage 1 breaks

For large time-series event tables, traditional B-tree indexes become massive and inefficient. Block Range Indexes (BRIN) on timestamp columns can reduce index sizes by orders of magnitude. A BRIN index stores minimum and maximum values for physical block ranges, letting the query planner skip irrelevant blocks entirely during time-windowed queries.

Even with these optimizations, Stage 1 has a ceiling. Postgres is row-oriented, built for transactional integrity rather than analytical scans.

Define explicit triggers that tell you when to graduate from Stage 1 before your application falls over.

Technical breaking points:

Buffer cache churn: Dashboard queries scan so much data they flush hot OLTP data from memory, causing production writes to slow down.
Row scan volume: Your aggregations require scanning millions of rows per request, making it impossible to stay within 100 ms latency even with heavy indexing.
High cardinality failure: Analytics require filtering on highly cardinal dimensions like API keys or individual SKUs, leading to massive index bloat and degraded B-tree performance.

Business breaking points:

Freshness demands: Customers start demanding sub-30-second data freshness at high volumes, making incremental rollups and concurrent materialized view refreshes too slow.
Ad-hoc filtering: Users need to dynamically filter dashboards by custom attributes, breaking the utility of pre-calculated summary tables.

The rule is simple: when any two of these breaking points become true, you've outgrown native Postgres. At that point, evaluate offloading in Stage 2.

Stage 2: offload analytics with CDC to a real-time OLAP database

When Postgres can no longer serve both high-throughput transactions and complex analytical reads, you need to physically separate the workloads.

How CDC-based offloading works

Your application continues writing to Postgres for OLTP operations. Change Data Capture (CDC) streams committed row-level changes into a dedicated, real-time OLAP database. Your customer-facing dashboards then read from the OLAP database, enforcing tenant_id filters at the API layer.

This separation lets Postgres dedicate its memory and CPU entirely to serving the application, while the OLAP database handles analytical aggregations. The choice of OLAP database matters: not every analytical database is built for in-product latency.

Why read replicas and cloud warehouses are the wrong fit for in-product dashboards

Requirement	Postgres read replica	Cloud data warehouse (Snowflake/BigQuery)	Real-time OLAP (ClickHouse)
Sub-100 ms p95 latency	Limited (row-store scans cap throughput)	Designed for batch BI (seconds to minutes)	Yes (columnar store, sparse indexes)
Horizontal concurrency	Limited (connection pool ceilings)	Costly under high concurrency (queueing, scaling spend)	Yes (scales linearly with nodes)
Freshness SLA	Seconds (physical replication)	Minutes to hours (batch-oriented)	Seconds (CDC streaming)Most B2B SaaS customer-facing dashboards live in the 1 to 10 second tie
High-cardinality support	Limited (index bloat on row-store)	Yes (brute-force scan)	Yes (designed for high-cardinality columns)
Predictable cost scaling	Unpredictable for analytics (Postgres is built for OLTP; analytical scans do not scale linearly)	Unpredictable for customer-facing concurrency	Predictable (engine built for analytics; scales linearly with workload)

A fourth option, headless analytics SaaS, sits on top of one of these stores rather than next to them. It is covered separately below.

Postgres read replicas share the row-oriented limitations of the primary. They don't compress data effectively for analytics, and they still require heavy B-tree indexes that bloat memory. They make sense for offloading read-only traffic from the primary, not for high-concurrency analytical reads.

Cloud data warehouses like Snowflake and BigQuery are traditional, batch-oriented engines built for internal BI and complex long-running queries. They are excellent at that workload.

They were not designed for sub-100 ms in-product latency. Routing user-facing dashboards through a cloud data warehouse routinely produces queries taking several seconds, blowing past the 100 ms budget.

Concurrency is also expensive. Cloud data warehouses are priced for batch jobs rather than customer-facing traffic: consumption-based compute, minimum-billed durations on resume, and horizontal scaling under concurrency combine to make bills hard to predict.

A real-time OLAP database like ClickHouse or Apache Pinot is built for this profile. These systems are columnar, reading only the specific columns requested. They use sparse indexes to skip vast amounts of data without B-tree memory overhead, and they compress data aggressively by grouping similar values together in column-oriented storage, which both reduces storage cost and lets queries scan less data per request.

Where headless analytics SaaS fits

Headless analytics platforms like Cube, Hex embedded, Preset embedded, and GoodData sit on top of an OLAP database, not in place of one. They provide a semantic layer, an API surface, query caching, and an embed-friendly UI toolkit. That accelerates the front-end build, but it does not change the latency, concurrency, or freshness profile of the underlying store. Pair a headless analytics layer with a real-time OLAP database when you want a faster ship path on the UI side; do not treat it as a substitute for the OLAP database itself.

The operational risks of DIY CDC (replication slots and WAL growth)

Moving to this architecture traditionally introduces a severe ETL Tax. Operating logical replication pipelines with tools like Debezium and Kafka turns a small engineering group into a distributed systems maintenance team.

The primary risk centers on Postgres replication slots.

By default, Postgres uses max_slot_wal_keep_size = -1, which lets replication slots retain unlimited Write-Ahead Log (WAL) files if a consumer falls behind or goes offline.

If your self-hosted CDC consumer crashes silently on Friday evening, Postgres will dutifully retain every transaction log generated over the weekend. By Monday morning, hundreds of gigabytes of WAL files will have filled your primary disk, triggering emergency read-only mode and taking your entire SaaS application offline.

Add schema drift and replication slot lag management, and the operational cost of DIY CDC quickly eclipses the value of the analytics dashboard.

CDC operations are only one line item in the ETL Tax: pipeline engineering hours, warehouse compute, data staleness, schema drift incidents, vendor sprawl across DMS or Kinesis or Fivetran plus the warehouse plus monitoring. Hidden costs routinely run 2x to 4x the license fees. The right answer is not a better DIY pipeline; it is removing the pipeline from your ops surface entirely.

How managed CDC connectors reduce risk

The solution is a managed, integrated CDC connector. ClickHouse Managed Postgres is vanilla Postgres with open-source ClickHouse integration baked in. ClickPipes, powered by PeerDB , is a single managed service that streams committed Postgres changes directly to ClickHouse, replacing what teams would otherwise build with Debezium, Kafka, and Kafka Connect.

This turns a complex, high-risk data engineering problem into a simple configuration step. The platform handles WAL management, replication slot monitoring, and schema synchronization automatically, connecting OLTP source to OLAP destination without intermediate infrastructure.

A unified query layer with pg_clickhouse

CDC moves the data. The second half of the integration story is how you query it. The pg_clickhouse extension is an Apache 2.0 Postgres foreign data wrapper that exposes ClickHouse tables as foreign tables inside Postgres, with full pushdown of filters, GROUP BY, ORDER BY, HAVING, and SEMI JOINs to ClickHouse's columnar engine.

pg_clickhouse benefits every query path, customer-facing dashboards included. The API-layer pattern stays exactly as before: bounded endpoints, tenant_id enforced server-side, queries assembled by your service. What changes is the engine underneath. Queries against ClickHouse foreign tables get pushed down to the columnar engine automatically, so existing API code, ORMs, internal dashboards, and cron jobs all get columnar speedups without any rewrites or new clients.

How to model CDC data in ClickHouse

Once data lands in ClickHouse, model it for the access pattern rather than copying the normalized Postgres schema unchanged.

Denormalization into wide event tables is a strong default for the hottest customer-facing dashboards because it gives you the lowest possible query latency on a known access pattern.

JOINs are also a first-class capability in ClickHouse, with all standard SQL join types (INNER, LEFT, RIGHT, FULL, CROSS, plus SEMI, ANTI, and ASOF), multiple join algorithms (hash, parallel hash, grace hash, full sorting merge, partial merge, direct), automatic optimization with statistics, and continued performance gains in 2025 and 2026. Use JOINs where they keep your schema simpler and your data easier to manage; reach for denormalization where you need the absolute floor on query latency.

For high-volume CDC and upsert flows, ClickHouse provides the ReplacingMergeTree table engine, which identifies rows with the same primary key and keeps the latest version. Background merges deduplicate asynchronously, and the FINAL keyword in SELECT guarantees deduplicated results at query time. FINAL has been significantly optimized for production use, so reach for it whenever a dashboard query needs immediate consistency.

Outside CDC, ClickHouse also supports standard SQL UPDATE and DELETE for individual or scattered row changes, and ALTER TABLE operations or partition drops for bulk data management. Pick the mechanism that matches the shape of the change rather than treating any one engine as the only option.

Where the source data is naturally immutable (event logs, request logs, telemetry), favor an append-only model. It keeps the schema simple and lets the merge process do less work.

Customer-facing analytics patterns (three real SaaS examples)

Here's how this maturity journey maps to real-world SaaS environments. Three distinct business models, each handling the transition from Stage 1 Postgres constraints to Stage 2 real-time OLAP.

This path is well-trodden. Polymarket scaled customer-facing prediction-market data by pairing Postgres with ClickHouse.

Pattern 1: product usage dashboards in SaaS

A project management SaaS provides workspace admins with a dashboard tracking daily active sessions and feature adoption. In Stage 1, the team relies on an org_daily_metrics rollup table in Postgres, refreshed every minute via a background worker.

As the user base crosses millions of daily events, the rollup window starts overrunning and the materialized-view refresh begins locking the primary database during peak write hours. Customers also start asking for sub-30-second freshness on the live-session counter. They graduate to Stage 2, streaming raw application events via CDC directly into a wide event table in ClickHouse.

Dashboards now query ClickHouse directly. Workspace admins can filter adoption metrics dynamically by user role and date range, with queries returning inside the dashboard's latency budget.

Pattern 2: marketplace seller dashboards

An e-commerce marketplace provides merchants with a live view of gross merchandise value (GMV) and pending orders. In Stage 1, the team rolls up order metrics into per-merchant summary tables in Postgres, refreshed every couple of minutes by a background worker. That serves the long tail of small merchants comfortably, but power sellers with thousands of daily transactions cannot tolerate minute-scale staleness during a launch or a flash sale.

They graduate to Stage 2 by implementing CDC to stream the order event log into ClickHouse. Using AggregatingMergeTree, they maintain real-time, incrementally updated materialized views of merchant GMV.

The seller dashboard refreshes on a 30-second interval. By the time it does, the latest orders are already in ClickHouse, and queries return inside the dashboard's latency budget even during peak ordering windows.

Pattern 3: developer-facing usage analytics

An API-as-a-Service platform needs to expose detailed usage analytics so developers can monitor latency, error rates, and cost forecasts. In Stage 1, the platform runs daily rollups grouped by api_key_id.

As developers demand real-time visibility into active incidents, daily rollups become obsolete. The platform starts streaming raw API request logs directly into ClickHouse, where months of request data sit efficiently behind sparse indexes and columnar compression.

The developer dashboard now lets users drill into specific error codes over custom time windows, with queries returning inside the dashboard's latency budget.

Anti-patterns that break customer-facing analytics

Scaling customer-facing analytics means avoiding several destructive architectural traps that engineering teams frequently fall into.

Anti-pattern: running OLAP queries on production Postgres

This is the most common failure mode. Allowing internal BI tools or heavy customer-facing dashboards to execute large table scans on your primary Postgres database flushes your shared_buffers. This evicts hot transactional data from memory, forcing your application to perform slow disk reads for routine operations. Your OLTP path chokes.

Anti-pattern: hand-rolled CDC pipelines without a data platform team

Treating CDC infrastructure as a side project is a recipe for production outages. Unmonitored Kafka clusters, schema drift breaking downstream schemas, replication slot lag causing WAL disk exhaustion. These will consume your engineering team's cycles. If you can't staff a dedicated data platform team, rely on managed CDC connectors.

Anti-pattern: letting the front end send raw SQL

Allowing the client application to generate and send raw SQL strings to your API is a security and maintenance hazard. Raw SQL bypasses semantic layers, makes query optimization impossible, and creates severe SQL injection risks.

The right pattern is the opposite. Build stable, bounded endpoints (like /api/analytics/overview) that securely resolve tenant_id from the authentication token, enforce strict cardinality limits and date range boundaries, and accept only semantic parameters from the client. The API assembles optimized database queries server-side so malicious or buggy client code cannot issue unbounded analytical queries against your database.

Anti-pattern: treating materialized views as the long-term answer at scale

Postgres materialized views are useful for Stage 1 freshness on the order of tens of seconds. They are not a permanent customer-facing analytics architecture. As event volume grows, refresh windows lengthen, REFRESH MATERIALIZED VIEW CONCURRENTLY increasingly competes with primary writes for I/O, and the view's freshness SLA falls behind what customers see. Materialized views buy you headroom inside Stage 1; they do not let you skip the graduation to Stage 2.

Anti-pattern: row-level security as the only tenant isolation mechanism

RLS is a useful safety net, but treating it as the sole isolation boundary couples tenant-scoped query performance to RLS policy execution. Naive RLS policies join to permissions tables and add tens to hundreds of milliseconds per query. Strong tenant isolation enforces tenant_id at the API layer, partitions or indexes data on tenant_id, and uses RLS as a last-line defense rather than the primary control.

Anti-pattern: measuring success on internal team load times

Internal engineers running dashboards from a fast laptop on a fast network are a poor proxy for the customer experience. A SaaS customer's dashboard might be opened on a coffee-shop connection, halfway around the world, against a tenant with millions of events. Define your latency SLA on customer-side p95 and p99 over a representative population, not on internal team perception. Architectures that look fine internally regularly fail in production for the customers who matter most.

Build order: how to ship customer-facing analytics in six steps

The architecture only works if you build it in the right order. Skipping a step routinely costs a quarter of engineering time recovering from a production fire.

Define dashboard metrics explicitly. Resist "let users query anything" as a v1 goal. Pick the 8 to 15 metrics customers actually need, document the dimensions and date ranges they will filter on, and treat that document as the contract your API and storage will enforce.
Ship Stage 1 with pre-aggregated summary tables in Postgres and indexed lookups. Use composite indexes that lead with tenant_id. Add REFRESH MATERIALIZED VIEW CONCURRENTLY only after verifying the unique-index requirement. Use BRIN indexes on time columns for large append-only event tables.
Add an API-layer cache keyed on (tenant_id, metric, date_range) with a 5 to 30 second TTL. This single layer absorbs the bulk of dashboard concurrency before any database tuning becomes necessary, and it is straightforward to add or remove without touching the storage architecture.
Graduate to Stage 2 when two or more Stage 1 triggers fire. Stream Postgres changes into a real-time OLAP database via CDC. With ClickHouse Managed Postgres, you don't migrate from Postgres; you activate ClickHouse alongside it from the same console. Your application keeps querying its Postgres connection string. pg_clickhouse transparently routes analytical queries to ClickHouse foreign tables, so the same API endpoints, ORMs, and dashboards get columnar speedups without rewrites or new clients.
Expose freshness in the UI and alert on freshness lag p95 the way you alert on query latency. A "Last updated 8 seconds ago" indicator next to each chart is enough to make the SLA visible to customers.

This order keeps the Stage 1 surface area small, defers infrastructure complexity until the workload actually requires it, and turns the Stage 2 graduation into a known, planned move instead of an emergency.

Conclusion: when to stay on Postgres or move to real-time OLAP

Real-time customer-facing analytics has a strict baseline. An architecture that cannot satisfy all five of these is not yet a customer-facing analytics architecture:

Analytical queries return in under 100 ms at p95.
API responses complete in under 300 ms at p95.
Data is fresh within the SLA tier you committed to your customers.
Tenant data is securely isolated.
The workload imposes zero load on your primary OLTP database.

If you're hitting Postgres scaling limits now, moving to real-time OLAP doesn't require a re-platforming project. ClickHouse Managed Postgres is vanilla Postgres with open-source ClickHouse integration: the OLTP database, the columnar OLAP database, managed CDC via ClickPipes, and a unified query layer via pg_clickhouse, behind one console. You don't migrate from Postgres. You activate ClickHouse next to it, and the Stage 1 to Stage 2 graduation becomes a configuration change rather than a quarter of pipeline engineering.

Frequently asked questions

Can Postgres handle customer-facing analytics dashboards?

Yes at small scale. It stops scaling once concurrency and scan volume grow, because analytical queries evict OLTP cache and increase contention. Use Postgres-only with pre-aggregations early, then offload analytics when latency or stability degrades.

What latency should customer-facing analytics target?

Aim for sub-100 ms p95 query latency on analytical queries at the data store so the full API response can stay under a few hundred milliseconds and feel instantaneous in-product.

When should I move analytics off Postgres?

When two or more breaking points appear, for example buffer cache churn, million-row scans per request, high-cardinality index bloat, stricter freshness SLAs, or users demand ad-hoc filtering that rollups can't support.

Is a Postgres read replica enough for analytics?

No. Replicas are still row-stores with the same scan/index limitations, and heavy analytics remain slow and expensive while adding operational complexity.

Why not use Snowflake or BigQuery for in-product dashboards?

They are traditional, batch-oriented engines optimized for internal BI. Customer-facing dashboards need sub-100 ms latency, which is outside the design point of those engines, and high concurrency on consumption-priced compute can produce unpredictable bills.

What is the recommended architecture for real-time analytics on Postgres data?

Keep Postgres for OLTP, use CDC to stream changes into a real-time OLAP database, and serve dashboards from the OLAP system with strict tenant_id filtering at the API layer.

What's the biggest operational risk with DIY CDC (Debezium/Kafka)?

Replication slots and WAL retention. If a consumer falls behind, WAL can grow until disk fills and Postgres goes read-only. Managed CDC reduces this risk with monitoring and backpressure handling.

How do I keep tenant data isolated in analytics?

Enforce tenant_id scoping in the API layer (and/or RLS) and design indexes/partitions around tenant_id so queries only scan a tenant's data.

Should I use materialized views in Postgres for dashboards?

Yes for Stage 1, but refresh behavior matters. REFRESH MATERIALIZED VIEW CONCURRENTLY avoids blocking reads but requires a unique index and won't meet tight freshness at scale.

Do I need to denormalize data when moving from Postgres to OLAP?

Denormalization into wide event tables is a strong default for the hottest customer-facing dashboards because it gives you the lowest possible query latency. JOINs are also fully supported in ClickHouse, with all standard SQL join types, multiple algorithms, and automatic optimization, so you can keep a more normalized schema where it makes the data easier to manage. Pick per-dashboard rather than applying a blanket rule.

How do updates and deletes work in ClickHouse with CDC?

ReplacingMergeTree is the standard pattern for high-volume CDC: updates land as new rows and background merges deduplicate asynchronously. Use the FINAL keyword in SELECT to get deduplicated results at query time; FINAL has been significantly optimized for production. Outside CDC, standard SQL UPDATE and DELETE are also available for scattered row changes, and ALTER TABLE operations cover bulk data management.

Best real-time analytics database for star schema and fast joins (2026 guide)

Aman Puri — Fri, 26 Jun 2026 19:08:50 +0000

Modern OLAP databases like ClickHouse now handle star schemas and complex cross-table joins at massive scale. That gives data engineering teams a real choice: flatten data into one big table when the workload calls for it, or keep a normalized fact and dimension model when flexibility, storage efficiency, or update patterns matter more. Both are valid, and the right answer depends on your use case.

This guide gives you a practical framework for evaluating modern join capabilities, performance limits, and operational tradeoffs.

The old rule that real-time analytics requires aggressive denormalization? It's obsolete in 2026. Vectorized query execution, memory-efficient join algorithms, and automatic query optimization have eliminated this constraint. You don't have to sacrifice schema flexibility for query speed anymore.

TL;DR

Modern real-time OLAP databases can run star schemas with fast cross-table joins in 2026. You don't always need "one big table."
ClickHouse is the best fit when you need sub-second joins + high concurrency on normalized fact/dimension models.
Evaluate engines on (1) memory-efficient join algorithms, (2) automatic join optimization + statistics-based reordering, (3) runtime filters/predicate pushdown, (4) concurrency isolation, (5) real-time updates/upserts for dimensions, (6) ingestion vs query isolation.
ClickHouse handles both single-table aggregations and multi-join analytical queries. Apache Druid/Pinot excel at single-table, high-QPS aggregations but are more limited when multi-table joins are required.
Denormalize only for edge cases like single-digit ms latency, very large dimensions, or expensive distributed shuffle joins you can't avoid.

Why denormalization is no longer required for real-time analytics

For over a decade, denormalized wide tables were the default path to sub-second dashboard performance, especially when managing tail latency (p99). Older analytical databases struggled with multiple joins under high concurrency, so flattening data into a single table was often the most reliable way to hit strict latency SLAs.

The 'one big table' approach is still a strong fit for many workloads, particularly when ingestion patterns are append-only and dimensions rarely change. At petabyte scale, though, it comes with tradeoffs around storage, update flexibility, and schema evolution that are worth weighing against a normalized alternative.

Denormalization comes with three tradeoffs to weigh against its performance benefits.

First, repeating dimension values like user demographics or product categories across fact rows increases storage footprint, which can meaningfully affect cloud bills at billion-row scale.

Second, dimension updates become more expensive. Changing a single attribute may require rewriting historical partitions, which can introduce variance into streaming pipelines.

Third, ad-hoc multi-dimensional analysis is harder to support without maintaining additional pre-joined tables for new query patterns.

None of these are dealbreakers, and many teams accept them in exchange for the predictability of single-table scans. The right choice depends on your workload, update patterns, and operational preferences.

Modern real-time analytics databases have expanded what's possible on the join side. Today's engines implement advanced join reordering, bloom filter pushdowns, and parallel execution strategies, dynamically pruning data before scanning.

The result is that normalized fact and dimension tables, star schemas, and snowflake schemas are now viable options for sub-second customer-facing dashboards, alongside denormalized wide tables. You have a real choice between logical data modeling and pre-joined wide tables, and the best fit depends on your workload.

How to evaluate real-time analytics databases for star schema joins

If you're evaluating real-time OLAP databases broadly, our complete evaluation framework covers all five criteria in depth: latency and concurrency, ingestion throughput, SQL flexibility, cost efficiency, and operational complexity.

For star schema workloads specifically, three requirements matter most: join execution, dimension mutability, and query-ingest isolation. Here's how different architecture classes perform.

Requirement 1: Which join algorithms prevent out-of-memory failures?

To avoid catastrophic query failures, a database needs modern algorithms like grace hash joins (which safely spill to disk when memory runs out) and parallel hash joins. Relying solely on basic broadcast joins will cause out-of-memory errors on large dimensions. It's just a matter of time.

Modern real-time OLAP (ClickHouse): Built for relational modeling at speed. These systems offer vectorized execution and multiple join algorithms including hash joins, sort-merge joins, grace hash joins, and more, with the query planner selecting the best strategy based on table sizes and available memory. This makes them well-suited for complex star schema queries.

Append-only event stores (Apache Pinot, Apache Druid): Fast for single-table aggregations, but their architectures are limited. They primarily support lookup and broadcast joins, which restricts them to simple dimension table enrichments. Complex multi-table analytical queries are either unsupported or operationally constrained.

Requirement 2: Does it support updates and upserts for slowly changing dimensions?

Managing slowly changing dimensions within a star schema requires lightweight updates and upserts. You can't afford to rewrite large historical data partitions for every change.

Modern real-time OLAP (ClickHouse): These databases natively support row-level updates and deletes. In ClickHouse, both operations use the same patch parts mechanic: a small mask or patch file applied immediately for instant consistency, then materialized during background merges. For managing slowly changing dimensions, the ReplacingMergeTree engine handles deduplication by version during merges, keeping only the latest row for each key. Ideal for managing mutating entities in a normalized schema.

Append-only event stores (Apache Pinot, Apache Druid): Neither engine supports SQL standard UPDATE or DELETE statements. Their segment-based, append-only architecture makes real-time mutability operationally complex and inefficient. Simple updates typically require re-ingesting and rebuilding entire data segments.

Requirement 3: Can it isolate ingestion from join-heavy queries?

A real-time system needs to stream thousands of events per second into large fact tables while simultaneously serving sub-second slice-and-dice queries. Without resource contention.

Different systems take different paths to isolation. Apache Druid and Pinot bake it into the architecture with dedicated broker, router, and server roles, and Pinot adds workload-based resource isolation with named CPU and memory budgets. This works, but operators have to provision and manage multiple specialized components from day one.

ClickHouse handles isolation through resource management and workload scheduling, letting teams specialize compute when it makes sense rather than mandating it upfront. ClickHouse Cloud goes further by separating storage and compute via SharedMergeTree, then layering specialized compute services on top: dedicated read-write services for ingestion, independently scaling read-only services for query traffic, and isolated compute pools that share the same underlying data. The result is near-perfect isolation for mixed workloads without managing discrete node tiers or duplicating data across clusters.

Summary: ClickHouse vs. Druid/Pinot for star schema joins

Core requirement	ClickHouse	Apache Druid / Pinot
Memory-efficient join algorithms	Excellent (Grace Hash, Parallel Hash, Shuffle)	Limited (Primarily lookup/broadcast joins)
Real-time data mutability	Excellent (Row-level updates and deletes via patch parts)	Limited (Segment-based, append-only model)
Ingestion vs. query isolation	Excellent (Resource management, workload scheduling, plus dedicated read-only and read-write compute services in Cloud)	Good (Dedicated broker/router nodes; Pinot supports workload-based resource isolation)

For a full comparison across all evaluation dimensions, including concurrency, cost efficiency, and operational complexity, see our complete real-time OLAP evaluation guide.

How ClickHouse executes fast star schema joins

Before 2024, industry discussions often categorized ClickHouse as a single-table engine that struggled with complex schemas. That was historically grounded, but recent releases (25.9 and beyond) have fundamentally changed ClickHouse's join execution capabilities. Benchmarks running 17 join-heavy queries across fact and dimension tables showed ClickHouse Cloud was faster and cheaper than Snowflake and Databricks at every scale, from 721 million to 7.2 billion rows, with zero tuning.

The numbers back this up. Automatic global join reordering, introduced in version 25.9, uses a greedy optimization algorithm with column statistics to determine optimal join order across multi-table queries. On a six-table TPC-H query (scale factor 100), enabling join reordering with column statistics improved execution from 3,903 seconds to 2.7 seconds, a 1,450x speedup with 25x less memory. As of version 25.10, column statistics can be created automatically, making this optimization available without manual setup.

These performance gains come from a series of optimizations across recent releases.

Global join reordering (25.9) evaluates table cardinality estimates to determine optimal build and probe sides for multi-table queries. Runtime bloom filters (25.10) generate a filter on the build side and apply it as a pre-filter on the probe side, skipping irrelevant rows before the join executes, delivering a 2.1x speedup and 7x reduction in memory consumption on TPC-H benchmarks. Lazy columns replication (25.10) avoids unnecessary copying of large string values during joins, achieving over 20x faster execution on self-join workloads.

ClickHouse supports multiple join algorithms including parallel hash join, grace hash join, full sorting merge join, and more, with the query planner selecting the best strategy based on table sizes and available memory.

To see how these optimizations perform on real join workloads, explore the coffeeshop benchmark.

Beyond standard relational joins, ClickHouse has eliminated the performance tax on semi-structured data. The native JSON type shreds JSON into dynamic sub-columns transparently with no upfront schema definition required. This matters because real-world data varies: some workloads have semi-structured data with a known set of keys (e.g., application event payloads), while others involve completely dynamic JSON with thousands of unpredictable keys (e.g., user-generated metadata or IoT telemetry). ClickHouse handles both cases efficiently using advanced serialization techniques that store JSON paths as compressed columnar subcolumns.

The result: no parsing bottleneck at query time, even when joining tables that contain complex JSON documents.

The infrastructure architecture ensures these complex joins run without impacting ingestion. ClickHouse Cloud separates storage and compute using the SharedMergeTree engine, so distributed queries scale on stateless compute nodes while background merges run independently. For ops teams, this means user-facing queries stay responsive during heavy ingestion. For developers, the same query patterns work identically whether running locally or on a production cluster.

When to normalize vs. denormalize for real-time analytics workloads

Different analytical workloads need different data modeling strategies. Here's when to use a normalized star schema versus falling back to traditional denormalization.

Recipe: Star schema for customer-facing dashboards

Decision logic: This workload demands exploratory, ad-hoc multi-dimensional slice-and-dice capabilities from many concurrent external users. A star schema works well here because it gives analysts and customers flexibility to ask any question without being constrained by a rigid, pre-joined wide table. Storing dimensions separately simplifies upstream streaming ingestion pipelines and optimizes storage costs by preventing data duplication.

Recipe: Keep dimension tables small and ensure they replicate across all compute nodes for fast broadcast joins. Use materialized views to pre-aggregate the heaviest fact table patterns, but perform final analytical joins against normalized dimension tables at query time for flexibility. Index your dimension tables properly to maximize runtime filter performance.

Recipe: Star schema for e-commerce analytics with slowly changing dimensions

Decision logic: Modern retail analytics requires joining real-time, high-volume clickstream logs with frequently updating dimension tables: fluctuating product inventory, dynamic pricing, evolving user profiles. These are classic Slowly Changing Dimensions. A star schema handles constant updates efficiently without re-processing billions of historical event rows, which would immediately cripple pipeline performance.

Recipe: Use the ReplacingMergeTree engine for dimension tables that change frequently (users, products, pricing). ReplacingMergeTree handles deduplication by version or timestamp during background merges, keeping only the latest row for each key. For individual row corrections (e.g., fixing a single product price), use Lightweight Updates, which apply instantly via patch parts with minimal overhead. At query time, join these dimensions against append-only fact tables using ClickHouse's optimized join algorithms. Your dashboard reflects the latest data without pipeline lag.

Recipe: Hybrid schema for observability and telemetry (minimal joins)

Decision logic: Observability workloads prioritize raw ingestion speed over complex relational mapping. The primary goal is ingesting millions of log lines or trace spans per second while enabling simple, fast filtering on a single table. Cross-table joins are rare and typically isolated to low-cardinality metadata enrichment, like mapping an IP address to a geographic location. The sheer volume and velocity of machine-generated data makes query-time joins for every single query impractical.

Recipe: Rely primarily on a denormalized wide-table schema or use the native JSON type to ingest raw, unstructured logs without strict schemas. For necessary metadata enrichment, skip traditional SQL joins. Use ClickHouse's optimized dictionary functions for point lookups instead. They're faster than hash joins and consume a fraction of the memory.

When a single wide table is still the right choice

Modern engines have made star schemas viable for real-time workloads, but denormalization remains the right call for many use cases. Flatten data into a single table when:

Ultra-low latency requirements: When sub-second rendering is too slow and your application demands single-digit millisecond response times, typical of real-time bidding or algorithmic ad tech.
Static, unchanging event streams: If your event data is written once and never logically updated, the primary downside of denormalization (update anomalies) becomes irrelevant.
Large dimension tables: When your dimension tables grow so large they rival the fact table in size, forcing a distributed network shuffle join that becomes prohibitively expensive.

In these edge cases, pre-joining data during ETL guarantees the database only performs rapid sequential scans, trading increased storage costs for maximum query throughput.

Operational considerations: Cost, concurrency, and updates at scale

Beyond initial query benchmarks, the true test of a real-time analytics database lies in day-two operations. Evaluating these systems requires a rigorous look at compute costs, concurrency thresholds, and ecosystem integration.

Cost considerations: storage, compression, and scan efficiency

The cost argument for star schemas is often understated. In a denormalized wide table, you repeat dimension values like product names, user demographics, and region strings across every fact row. At billion-row scale, that redundancy adds meaningful storage overhead, especially in cloud environments where storage costs scale linearly.

A normalized star schema eliminates this duplication by design: each dimension value is stored once. ClickHouse amplifies the savings further because columnar storage groups values of the same type together, allowing compression algorithms like LZ4 and ZSTD to exploit patterns that are invisible in row-oriented layouts. The result: 10x to 20x compression is typical on the fact table alone, and dimension tables compress even more aggressively because they're smaller and more repetitive.

Compute costs follow from this too, though the relationship depends on how your engine bills you. Some cloud warehouses charge directly per byte scanned, so a star schema join that reads a 2 GB fact table plus a 50 MB dimension is cheaper than scanning a 20 GB denormalized table with repeated dimension strings. Other engines bill on compute time, where scanning less data still helps because smaller scans generally mean faster queries and less compute consumed.

But raw scan efficiency matters just as much as data volume. ClickHouse's vectorized execution and columnar compression let it scan large datasets at a fraction of the cost of traditional cloud warehouses. In practice, ClickHouse can often scan 20 GB faster and cheaper than a slower engine scans 2 GB. The real savings come from combining a normalized schema with an engine that scans efficiently in the first place.

Handling dimension updates at scale

In a star schema, fact tables are append-only: events, transactions, clicks. They don't change. But dimension tables do: a product price gets corrected, a user upgrades their plan, a store changes its region mapping. These are classic slowly changing dimensions, and they're the operational pain point of star schemas.

ClickHouse handles this split cleanly. For fact tables, use standard MergeTree with append-only inserts and no mutation overhead. For dimension tables, you have two complementary tools. ReplacingMergeTree handles deduplication by version during background merges, keeping only the latest row for each key, making it ideal for dimensions that update in bulk (e.g., a nightly product catalog refresh). For individual row corrections (e.g., fixing a single product price mid-day), Lightweight Updates apply instantly via patch parts: ClickHouse writes a compact patch containing only the changed values, applied immediately for instant consistency, then materialized during background merges. No table locks, no partition rewrites.

This means your fact table stays fast and untouched while your dimension tables stay current, which is exactly the operational model a star schema requires.

Managing star schema transformations in production

The operational overhead of a star schema, including defining dimension tables, orchestrating fact-dimension relationships, handling incremental loads, is only manageable with proper tooling.

dbt has become the standard here. The ClickHouse dbt adapter supports incremental materializations and full-refresh strategies, letting you model fact and dimension tables with different update patterns: append-only inserts for facts, full refresh or merge for dimensions. You define your dimension and fact models as SQL, version them in git, and dbt handles the orchestration.

For a full list of ClickHouse's ecosystem integrations, including visualization, ingestion, orchestration, and language clients, see our complete evaluation guide.

Conclusion: Choosing a real-time analytics database for star schema joins

The real-time data engineering landscape has shifted. Logical relational modeling and real-time performance are no longer mutually exclusive, which means star schemas, snowflake schemas, and denormalized wide tables are all viable strategies in 2026. The right choice depends on your workload, update patterns, latency targets, and operational preferences.

Successfully evaluating a database architecture in 2026 means looking past outdated benchmarks. Data architects need to test actual memory-efficient join algorithms, verify optimizer intelligence, and stress-test concurrency limits under intense ingestion.

If you're building customer-facing analytics applications, or if you're struggling to migrate off a slow, expensive cloud data warehouse, ClickHouse Cloud provides the query execution speed and schema flexibility required to power sub-second multi-dimensional analysis without the traditional operational burden. Many teams start by adding ClickHouse as a real-time serving layer alongside their existing data warehouse, and some later choose to consolidate entirely.

Don't take these performance claims on faith. Spin up a free ClickHouse Cloud trial, load as much of your own data as possible, and run your actual queries at realistic scale. That's the only way to know if it fits your workload. For evidence of how ClickHouse performs on standardized join workloads, explore the fully open and reproducible coffeeshop benchmark.

Frequently asked questions

Which real-time analytics database is best for star schema and cross-table joins?

In 2026, ClickHouse is a strong choice for star schemas because it supports modern join algorithms, automatic join optimization, and high concurrency. Druid/Pinot are typically better for single-table real-time analytics with limited joins.

Does ClickHouse support star schema (fact + dimension tables) in production?

Yes. ClickHouse supports multi-table joins and is commonly used with normalized fact/dimension models when configured with appropriate join strategies and resource limits.

How many joins can ClickHouse (or an OLAP database) handle in one query?

There's no fixed limit. It depends on memory, join strategy, and data sizes. Multiple joins to small dimensions are handled efficiently, and joins between larger tables can be optimized through join algorithm selection, runtime filtering, and resource management settings.

What join pattern is most likely to be slow or expensive?

The most expensive pattern is usually a distributed shuffle join between two large tables. It forces heavy network data movement and large intermediate states.

How can I make star-schema joins faster in real-time analytics?

Keep dimensions small and well-partitioned, prefer broadcast/replicated dimensions when possible, and avoid large shuffle joins. Use pre-aggregation (e.g., materialized views) for the heaviest fact-table patterns.

OPTIMIZE TABLE ... FINAL in ClickHouse: when to use it, when to avoid it, and how merges work

Aman Puri — Fri, 26 Jun 2026 19:08:22 +0000

If you manage a ClickHouse cluster in production, you may have hit duplicate rows or the "too many parts" error. A search result, an older blog post, or a forum thread probably pointed you to OPTIMIZE TABLE ... FINAL. You ran it, the immediate symptom vanished, and you scheduled it in a cron job.

Weeks later, ClickHouse feels slower after OPTIMIZE, and your cluster's performance has degraded.

Key takeaways

Don't blindly run OPTIMIZE TABLE ... FINAL on a schedule. It forces a physical rewrite of active parts in the targeted partition or partitions, consuming significant I/O and CPU.
OPTIMIZE ... FINAL bypasses the background merge scheduler's normal size guardrails, including max_bytes_to_merge_at_max_space_in_pool, which is typically around 150 GB.
On self-managed ReplicatedMergeTree, OPTIMIZE creates a replicated merge task. Depending on alter_sync, ClickHouse waits for execution on the current replica or all replicas. Other replicas may merge locally or fetch the merged part, depending on settings and replica state, which can add merge, network, and replication-queue pressure.
For current-state queries on ReplacingMergeTree, use SELECT ... FINAL because background merges provide eventual deduplication, not immediate query correctness. Enable do_not_merge_across_partitions_select_final=1 only when the partition key guarantees all versions of the same logical row stay in the same partition.
To prevent "too many parts," fix ingestion: batch inserts into larger blocks, target roughly one insert per second, or use asynchronous inserts when client-side batching is not practical.

Why scheduled OPTIMIZE TABLE ... FINAL backfires

You might reach for OPTIMIZE TABLE ... FINAL in two common situations: when a ReplacingMergeTree query returns duplicate versions before background merges finish, or when inserts fail with a TOO_MANY_PARTS error because one partition has too many active parts.

Both symptoms create pressure for immediate relief, so forcing a merge can feel like the obvious next step.

The operation takes a while, but when it finishes, your part count drops and duplicates disappear. So you automate it to run nightly or hourly.

At first, the cluster seems healthier: part count drops, and duplicate versions in ReplacingMergeTree tables may disappear. But OPTIMIZE ... FINAL can create parts larger than the background merge scheduler would normally select.

New inserts still create new parts. If those new parts contain newer versions of rows already inside the oversized part, ClickHouse may not automatically merge them together. The result is that duplicates can accumulate again, while SELECT ... FINAL has to resolve more of that state at query time.

Very large parts can also make merge-dependent maintenance more expensive later. Lightweight deletes are reflected in query results when the delete completes, but ClickHouse physically removes the affected data later during background merges. If the affected rows live inside very large parts, that cleanup requires larger merge work. Heavier ALTER TABLE ... UPDATE and ALTER TABLE ... DELETE mutations can also compete with or wait behind merge activity, so they are best reserved for deliberate, large, often partition-aligned changes. During these rewrites, ClickHouse must keep old parts until the new parts are committed, so disk I/O and temporary disk usage can rise, and query latency can degrade.

The instinct to manually "vacuum" or compact a database is understandable. Many storage engines benefit from periodic operator-driven compaction.

In ClickHouse's MergeTree family, forcing large, unscheduled table rewrites usually works against the background merge scheduler. The scheduler's heuristics and safety limits are tuned to keep merges running as a resource-aware background process. Bypassing them shifts that work onto the same cluster serving live queries and ingestion.

What is a data part in ClickHouse (MergeTree)

In ClickHouse, an INSERT into a MergeTree-family table writes data into a new immutable unit on storage called a data part.

A part is a collection of physical assets rather than a single file. In common wide-format parts, this includes compressed column files, mark files that map index granules to byte offsets, a sparse primary index, checksums, and metadata. Compact parts and newer storage formats can organize these files differently, but the core idea is the same: a part is a self-contained, immutable unit of sorted columnar data. You can read more about the structure in the table parts documentation.

Parts are immutable. Once written, the engine does not update them in place. It creates new parts, merges existing parts, and later removes parts that are no longer active.

Part names encode their history. The format is partition_id_minimum_block_number_maximum_block_number_level_data_version, where data_version is optional and is incremented when a mutation writes a new part. For example, a part named all_1_4_1_6 belongs to the all partition, covers the block-number range 1 through 4, has merge level 1, and carries data version 6. The level field tracks merge history only: level 0 means the part has not been produced by a merge, while a higher level indicates one or more merge generations in its lineage. Mutations are tracked separately in data_version, so a part can remain level 0 after being rewritten by a mutation.

To see what your active parts look like, query system.parts:

SELECT
    partition,
    name,
    level,
    rows,
    formatReadableSize(bytes_on_disk) AS size
FROM system.parts
WHERE table = 'your_table_name'
  AND active = 1
ORDER BY bytes_on_disk DESC;

Examining levels and sizes tells you whether ingestion is creating many fragmented, level-0 parts or whether background merges are consolidating them into larger, higher-level parts.

Healthy ClickHouse part management means allowing the engine to maintain a balanced distribution of parts continuously, not forcing every partition into a single huge part.

How ClickHouse background merges work (and why partitions matter)

ClickHouse does not wait for manual intervention to clean up data. The engine continuously evaluates active parts in the background, selects candidates, and schedules merges to reduce fragmentation.

This process is scoped per partition. ClickHouse does not merge parts from two different partitions together.

The background merge scheduler balances reducing part count against write amplification. To protect the cluster from resource starvation, it operates under safety limits such as max_bytes_to_merge_at_max_space_in_pool.

This setting caps the total source-part size that an automatic background merge will select when enough resources are available. It roughly corresponds to the largest part size automatic merges normally create. The common default is around 150 GB.

Merging data requires reading source parts, merging rows according to the table engine's rules, writing compressed columnar output, and committing a replacement part. On large parts, ClickHouse can use vertical merge algorithms that process columns in a more memory-efficient way, but the operation still consumes CPU, memory, and I/O.

Merging multiple very large parts can create significant write amplification. That work competes with concurrent queries and ingestion for disk or object-storage bandwidth and can evict useful data from caches. The size limits keep normal background merging incremental and resource-aware.

OPTIMIZE TABLE in ClickHouse: what each variant does

OPTIMIZE TABLE. Running the base command without modifiers asks ClickHouse to initiate an unscheduled merge. It may perform no merge if the table does not have useful candidates. By default, ClickHouse does not notify the client when no merge is performed unless optimize_throw_if_noop is enabled. On replicated tables, wait behavior depends on alter_sync.

OPTIMIZE TABLE ... PARTITION. This scopes the optimize request to a specific partition. It is most appropriate for bounded maintenance on a closed partition, such as a historical month of data that will not receive more writes.

OPTIMIZE TABLE ... FINAL. This forces optimization even if the data is already in one part. In practice, it forces ClickHouse to merge active parts in each targeted partition down to a single part, bypassing the normal automatic-merge size guardrails. It should be treated as an infrequent, intentional administrative operation, not routine maintenance.

Why OPTIMIZE TABLE ... FINAL is expensive

ClickHouse must read the active source parts, apply the relevant MergeTree-family merge logic, write the merged output, and commit the new part. For large datasets, this is CPU- and I/O-intensive.

OPTIMIZE TABLE ... FINAL also bypasses the max_bytes_to_merge_at_max_space_in_pool safeguard. It can attempt merges that automatic background merging would not choose, limited primarily by available resources such as disk space.

Once very large parts exist, the background scheduler may not be able to merge future incoming parts with them. It will continue to merge smaller new parts where it can, but it may no longer be able to consolidate the whole partition automatically. For ReplacingMergeTree, this can mean duplicate versions accumulate again as new data arrives.

Why OPTIMIZE can add replication pressure

For self-managed ReplicatedMergeTree, OPTIMIZE creates a replicated merge task. Depending on alter_sync, ClickHouse waits for execution on the current replica or all replicas.

Other replicas may merge locally or fetch the merged part, depending on settings and replica state. Either way, a forced large merge can add substantial CPU, disk, network, and replication-queue work. During that window, replicas can have different part structures, so queries may use different execution plans depending on which replica serves the read.

A cron-scheduled OPTIMIZE ... FINAL makes this recurring. The cluster repeatedly absorbs large administrative merge work, and inserts can see higher latency or failures under pressure.

ClickHouse Cloud uses SharedMergeTree with shared object storage and shared metadata, so it does not use the same explicit local-part replication model as self-managed ReplicatedMergeTree. The same principle still applies: forced large merges consume resources and should not be scheduled as routine maintenance.

OPTIMIZE TABLE ... FINAL vs. SELECT ... FINAL: what FINAL means

OPTIMIZE TABLE ... FINAL is a storage operation. It rewrites physical data parts on disk or object storage.

SELECT ... FINAL is a query-time modifier. It applies the table engine's merge logic when reading data and does not alter the underlying parts.

Older guidance often recommended avoiding SELECT ... FINAL because earlier implementations were much more expensive. ClickHouse has since optimized FINAL significantly. The ClickHouse 23.12 release notes describe improvements that split data into intersecting and non-intersecting ranges so ClickHouse can process ranges that do not need finalization as normal reads. The 24.1 release notes describe additional vertical processing optimizations for FINAL with ReplacingMergeTree.

SELECT ... FINAL still has overhead, especially when a query must read many candidate rows that are not filtered by the sorting key. But it is the right tool when users need correct current-state results from unmerged ReplacingMergeTree data. It gives query-time correctness without forcing a physical rewrite of the storage layer.

How to deduplicate rows and handle ReplacingMergeTree safely

Use ReplacingMergeTree for current-state deduplication when data arrives as immutable inserts. During background merges, ClickHouse identifies duplicates by the table's ORDER BY columns. If a version column is configured, it keeps the row with the highest version. Without a version column, replacement is based on merge order, so a version column is the safer pattern for update-style workloads.

Background merging is asynchronous, so you cannot rely on it for query-time correctness. Queries against unmerged data can still see multiple versions of the same logical row unless you use FINAL.

For better FINAL performance on partitioned tables, use do_not_merge_across_partitions_select_final when your schema guarantees that all versions of a logical row land in the same partition:

SELECT
    user_id,
    event_name,
    event_timestamp
FROM events_table FINAL
WHERE event_date = today()
SETTINGS do_not_merge_across_partitions_select_final = 1;

This setting tells ClickHouse to process partitions independently during SELECT ... FINAL, which can improve parallelism and reduce unnecessary cross-partition work. It is only correct when the partition key cooperates. If versions of the same row can land in different partitions, using this setting can produce incorrect current-state results.

If a dataset is large enough that SELECT ... FINAL is still too expensive for a hot query path, do not solve that by scheduling OPTIMIZE ... FINAL. Instead, shift the latest-state computation to ingestion time.

One common pattern is a materialized view into an AggregatingMergeTree table using argMaxState at ingest time and argMaxMerge at query time. That keeps a precomputed latest-state representation for fast reads, while the source ReplacingMergeTree can retain the raw immutable event stream and let background merges reclaim storage over time.

How to fix the "too many parts" error

The "too many parts" error appears when the number of active parts in a partition exceeds the parts_to_throw_insert limit. This protects the cluster when inserts create parts faster than background merges can consolidate them. Check system.merge_tree_settings in your environment for the exact default in your ClickHouse version.

This is an ingestion problem, not a merge engine failure.

OPTIMIZE TABLE ... FINAL is not a fix. It rewrites a snapshot of the parts you already have, but it does not change the rate at which new parts arrive. The exception can return as soon as ingestion resumes, while your storage is left with very large parts that can make future operations harder.

Find the rapidly growing partition

Run this diagnostic query to find where part growth is concentrated:

SELECT
    partition,
    count() AS active_parts,
    max(level) AS highest_level,
    sum(rows) AS total_rows,
    formatReadableSize(sum(bytes_on_disk)) AS total_size
FROM system.parts
WHERE table = 'your_table_name'
  AND active = 1
GROUP BY partition
HAVING active_parts > 150
ORDER BY active_parts DESC;

The 150 threshold is a monitoring heuristic, not a ClickHouse limit. It gives an early warning before inserts are delayed or rejected.

Fix excessive part creation with batch inserts

The most effective client-side fix is batching inserts. ClickHouse recommends fairly large batches: at least 1,000 rows per insert, and ideally 10,000 to 100,000 rows, while keeping insert query frequency around once per second when using synchronous inserts.

Sending hundreds or thousands of tiny inserts per second creates too many parts. Every flush creates at least one new part per affected partition, and the merge scheduler then has to consolidate them.

Use asynchronous inserts to batch on the server

If rewriting your upstream application to buffer data client-side is not practical, use server-side batching with asynchronous inserts.

Enable async_insert = 1 and use wait_for_async_insert = 1, which is the recommended production mode. ClickHouse buffers incoming inserts in memory and flushes them to storage when configurable thresholds are met, such as buffer size, elapsed time, or number of queued queries.

With wait_for_async_insert = 1, the client receives acknowledgment only after data is flushed to storage. That preserves durability expectations and lets your application receive insertion errors instead of hiding them in server logs.

Avoid over-partitioning

Check your schema for over-partitioning. A common mistake is using a granular daily PARTITION BY key for low-volume data or for workloads that write across many dates at once.

If each insert touches many partitions, ClickHouse creates at least one part per partition per flush. The problem multiplies quickly.

Switching from daily toYYYYMMDD(date) partitions to monthly or yearly partitions can reduce part pressure when the workload does not require daily partition management. Monitor merge health with system.merges for in-flight merges and system.part_log for historical merge activity.

When to use OPTIMIZE TABLE, SELECT ... FINAL, or do nothing

Trust background merges as your default. This is the right approach for most production workloads. The heuristics controlling merge selection and the limits protecting I/O bandwidth exist so operators do not have to intervene manually.

Use SELECT ... FINAL for reading deduplicated data. When you need a correct current-state view of a ReplacingMergeTree, apply FINAL at query time because background merges provide eventual deduplication, not immediate query correctness. Use partition-aware settings only when your partitioning scheme keeps every version of the same logical row in the same partition.

Use OPTIMIZE TABLE without FINAL as a merge request, not a guarantee. After a heavy backfill or maintenance event, a plain OPTIMIZE TABLE can ask ClickHouse to initiate an unscheduled merge. It may no-op if there are no useful merge candidates.

Use OPTIMIZE TABLE ... PARTITION for targeted maintenance. When an older partition is closed and will not receive more writes, optimizing that specific partition can be reasonable. It is more bounded than a table-wide forced rewrite.

Use OPTIMIZE TABLE ... FINAL only for bounded, one-time administrative operations. Reserve forced full rewrites for specific, scoped situations, such as collapsing all versions in a small, closed ReplacingMergeTree partition that will never receive further writes. Do not put it in a cron job or automated pipeline. In most cases, OPTIMIZE TABLE ... FINAL is probably the wrong lever to reach for. If in doubt, you should reach out to ClickHouse Support for guidance.

Conclusion

ClickHouse is designed to manage its own storage. Background merges, immutable parts, and merge-size limits exist so operators do not have to run periodic forced rewrites.

Use SELECT ... FINAL for query-time correctness, fix ingestion when part counts climb, and reserve OPTIMIZE TABLE ... FINAL for bounded administrative operations. Trust the part lifecycle, and avoid putting forced rewrites in a cron job.

Whether you run ClickHouse yourself or use ClickHouse Cloud, the same principle applies. To evaluate ClickHouse Cloud, spin up a free trial, load as much of your own data as practical, run an evaluation at realistic scale, and compare against your existing system.

Frequently asked questions

Should I run OPTIMIZE TABLE ... FINAL regularly in ClickHouse?

No. OPTIMIZE ... FINAL forces a heavy rewrite of active parts in the targeted partition or partitions, bypasses automatic merge guardrails, and can add sustained CPU and I/O pressure.

What does OPTIMIZE TABLE ... FINAL actually do?

It forces ClickHouse to optimize even when data is already in one part. For MergeTree-family tables, that commonly means merging active parts in each targeted partition down to a single part.

Is OPTIMIZE TABLE replicated across replicas?

For self-managed ReplicatedMergeTree, OPTIMIZE creates a replicated merge task. Depending on alter_sync, ClickHouse waits for execution on the current replica or all replicas. Other replicas may merge locally or fetch the merged part, depending on settings and replica state. In ClickHouse Cloud, SharedMergeTree uses shared storage and shared metadata, so the self-managed local-part replication model does not apply in the same way.

Why can OPTIMIZE ... FINAL make performance worse over time?

It can create very large parts and substantial write amplification. Those large parts may be outside the size range automatic merges normally select, so future inserts can create new parts that are harder to consolidate with the largest existing parts.

What should I use instead of OPTIMIZE ... FINAL to remove duplicates in ReplacingMergeTree?

Use SELECT ... FINAL when you need correct query-time deduplication. Background merges will clean up storage over time, but they are asynchronous and should not be relied on for immediate query correctness.

How do I speed up SELECT ... FINAL on partitioned tables?

Use do_not_merge_across_partitions_select_final = 1 when all versions of the same logical row are guaranteed to stay within one partition. This lets ClickHouse process partitions independently during FINAL.

What causes the "too many parts" error?

Inserts are creating parts faster than background merges can consolidate them, usually because of too many small inserts or inserts that touch too many partitions.

What batch size should I target to avoid excessive part creation?

Use batches of at least 1,000 rows, ideally 10,000 to 100,000 rows, and keep synchronous insert frequency around once per second where possible.

Can ClickHouse batch small inserts automatically?

Yes. Use asynchronous inserts with async_insert = 1 and wait_for_async_insert = 1 so ClickHouse buffers small inserts and flushes them as larger parts based on configurable thresholds.

When is OPTIMIZE TABLE ... PARTITION appropriate?

It can be appropriate for bounded maintenance on a closed partition that will not receive more writes. It is safer than forcing a table-wide rewrite, but it should still be used deliberately.

ClickHouse JOINs Aren't Slow Anymore (You're Reading 2020's Docs)

Aman Puri — Tue, 12 May 2026 18:41:27 +0000

ClickHouse JOIN performance was rebuilt between 2022 and 2026. The "avoid JOINs in ClickHouse" advice from 2020 is still circulating:

"ClickHouse can't do JOINs."

"Denormalize everything."

"Only hash join is supported."

"JOINs OOM on anything bigger than RAM."

All four were accurate in 2020. None of them are accurate today.

In 2020, ClickHouse had one join algorithm, no disk spilling, no cost-based optimizer, and join order followed query syntax. If the right table did not fit in memory, the query crashed.

Between 2022 and early 2026, the join subsystem was rebuilt. Six algorithms ship by default. The optimizer reorders joins automatically using column statistics. Runtime bloom filters prune fact tables at the storage scan. Grace hash spills to disk instead of OOMing. None of it is behind feature flags.

Here is what shipped.

How Many JOIN Algorithms Does ClickHouse Support?

By the end of 2022 ClickHouse had five join algorithms. Today it has six, and the engine selects automatically:

Hash join. The classic.
Parallel hash join. Default since v24.12 (PR #70788). Near-linear scaling across CPU cores. Zero configuration.
Grace hash join. Disk-spilling for joins that exceed memory (PR #38191). GA in v24.3. Closes the most upvoted join issue in ClickHouse history, open since June 2020.
Full sorting merge. Memory-bounded sort-merge (PR #35796). Faster than hash on pre-sorted data.
Direct join. O(1) memory lookups against dictionaries and EmbeddedRocksDB. No hash table construction.
Paste join. Positional joins.

"ClickHouse only has hash join" has been false since November 2022.

ClickHouse JOIN OOM Crashes: Grace Hash and Disk Spilling

Grace hash join partitions both inputs into buckets via a secondary hash, processes one bucket pair at a time, and spills inactive buckets to disk. Full sorting merge bounds memory by the sort buffer rather than right-side cardinality.

Before PR #38191, a join where the right table exceeded memory crashed with OOM. After it, the join completes. It just takes longer.

Does ClickHouse Have a Query Optimizer for JOINs?

"ClickHouse has no query optimizer" was true. It has not been since 2024.

Equivalence-Set Predicate Pushdown (180× Speedup)

Equivalence-set predicate pushdown (PR #61216) is the highest-impact join optimization of the four-year arc. When tables join on t1.id = t2.id, a filter WHERE t1.id = 5 is logically equivalent to t2.id = 5. The optimizer recognizes this and pushes the filter to both sides before the join executes.

Benchmarks: up to 180× speedup.

Extended in 2026 (PR #96596) to propagate across chains of INNER JOINs using a Disjoint Set Union to track transitive equalities. Filter on t1.id, get it pushed to t2, t3, and beyond.

Automatic OUTER to INNER Conversion

Automatic OUTER to INNER conversion (PR #62907) recognizes when post-join filters make outer semantics redundant. A LEFT JOIN ... WHERE right_col IS NOT NULL becomes an INNER JOIN. One benchmarked query went from 32s to 0.006s.

Converting to INNER first unlocks predicate pushdown and reordering that are structurally impossible for OUTER JOINs.

Right-Side and OR-Condition Pushdown

Two further pushdown extensions:

Right-side predicate pushdown (PR #50532) with 27× improvement on applicable workloads.
OR-condition pushdown (PR #84735). Previously only AND conditions could be pushed.

No query hints required.

Cost-Based JOIN Reordering in ClickHouse (v25.9+)

Automatic join order optimization landed in v25.9.

Greedy JOIN Reordering with Column Statistics

PR #86822 introduced global join reordering using a greedy algorithm with column statistics. For queries joining three or more tables, the optimizer evaluates estimated cardinalities and picks the order that minimizes intermediate result sizes.

TPC-H SF100: 1,450× speedup, 25× memory reduction versus syntax-order execution.

DPsize Dynamic Programming JOIN Reordering

v25.12 added a DPsize dynamic programming algorithm (PR #91002) for exhaustive search on complex queries, with greedy fallback above the complexity threshold. PostgreSQL and Oracle planners work the same way.

Automatic Statistics Collection

Statistics collection went automatic in v25.10 (PR #89332). Runtime hash table sizes from previous executions feed back into future plans (PR #93912).

Hand-tuned join order is obsolete.

Runtime Bloom Filters for ClickHouse Star Schema JOINs

Star and snowflake schemas were historically where ClickHouse joins hurt. The fact table is large. The dimensions are small. The fact table got scanned in full, then most rows were discarded.

PR #89314, default since v25.10, builds bloom filters from build-side join keys during hash table construction and pushes them to the probe-side scan. Non-matching rows are discarded at the storage layer before reaching the join.

v25.10 release blog: 2.1× overall query speedup, 7× memory reduction on star-schema workloads. Filters can be pushed into PREWHERE (PR #95838) for further efficiency. An adaptive mechanism disables them at runtime when they stop filtering enough rows.

Does ClickHouse Support Correlated Subqueries?

True until April 2025. PR #76078 shipped correlated subquery decorrelation. EXISTS, scalar, and projection-list subqueries auto-convert into joins.

Beta with default enablement landed in August 2025 (PR #85107), closing issue #6697, open since 2019.

Migration from PostgreSQL or Snowflake no longer requires manual rewriting of correlated subqueries.

ClickHouse JOIN Limitations and Trade-offs in 2026

Four caveats remain:

Flat tables are still faster for sub-10ms p99 dashboards. Denormalization wins on extreme latency targets.
The optimizer depends on statistics. Stale or missing stats degrade plan quality. Monitor system.statistics.
Correlated subqueries are beta. Common patterns work. Edge cases need explicit join rewrites.
Grace hash trades speed for completion. Constant spilling means the data model needs adjustment, not the join algorithm.

When Should You Use JOINs in ClickHouse?

Workload	Verdict
Star/snowflake analytics	Yes. Runtime bloom filters target this exact pattern.
Multi-table reporting	Yes. The optimizer reorders.
Joins larger than RAM	Yes. Grace hash and full sorting merge guarantee completion.
Real-time dimension enrichment	Yes. Direct join is O(1) memory.
ASOF time-series joins	Yes. Full sorting merge variant is 2× faster, 2× less memory.
Sub-10ms p99 on complex joins	Conditional. Denormalize if the storage budget allows.
10+ table joins with no stats	Enable `allow_statistics_optimize` (default in v25.10+).

Responding to "avoid JOINs in ClickHouse" in 2026

Check the version.

Anyone benchmarking against ClickHouse 23.x, or quoting blog posts written before parallel hash join became default, is not evaluating ClickHouse 2026. They are evaluating a system that no longer exists.

The commit history is public. 50+ pull requests. Six algorithms. Cost-based optimization. Runtime filtering. Automatic algorithm selection, build-side selection, join reordering, predicate pushdown. None behind flags. All default.

Test it on the workload. That is the only benchmark that matters.

ClickHouse JOIN FAQ

Are ClickHouse JOINs production-ready in 2026?

Yes. Six algorithms ship by default, the optimizer reorders joins using column statistics, runtime bloom filters prune fact tables at the storage scan, and grace hash spills to disk instead of OOMing. None of it requires configuration.

How many JOIN algorithms does ClickHouse support?

Six: hash, parallel hash (default), grace hash (disk-spilling), full sorting merge, direct (O(1) key-value), and paste. The engine selects automatically.

Does ClickHouse still crash with OOM on large JOINs?

No. Grace hash join (GA in v24.3) partitions inputs and spills to disk. Full sorting merge bounds memory by the sort buffer. Both algorithms guarantee completion regardless of right-side size.

Should I denormalize tables in ClickHouse instead of using JOINs?

Conditional. For sub-10ms p99 dashboard queries, flat tables remain faster. For analytical workloads where query readability and storage efficiency matter, normalized star and snowflake schemas with JOINs perform well after the 2024 to 2026 optimizer work.