Nuffing.com

Sometimes, we see ClickHouse queries that should normally complete in milliseconds take several seconds to finish or worse, time out entirely. When that happens, there is a good chance that the schema is the real culprit. The problem is often not the query itself, nor is it a hardware bottleneck. Instead, it can stem from a schema design decision made months ago that nobody questioned at the time.

Schema design in ClickHouse is one of those tasks that appears to be a simple, one-time setup activity, but it often becomes a recurring operational concern. When the schema is designed poorly, problems tend to surface over time, including slow queries, oversized partitions, and mutation jobs that run for hours while production traffic continues to grow. 

In this article, we will cover the core concepts of ClickHouse, systematically walk through design decisions, from core concepts and operational patterns to monitoring and evolution, with the goal of giving you a framework for making and maintaining schema decisions in production.

Core Concepts for ClickHouse Schema Design

Distributed Tables, Local Tables, Shards, and Replicas

Before writing a single CREATE TABLE, it helps to have a clear mental model of how ClickHouse actually stores and serves data across a cluster. ClickHouse divides data across shards with each shard holding a horizontal slice of the total dataset. Each shard can have one or more replicas for fault tolerance. The replicas within a shard hold identical data; the shards themselves hold different data.

The two table types you’ll work with constantly are:

• Local tables (ReplicatedMergeTree and its variants): the actual storage layer. Each node stores its own local table containing its shard’s data. Queries against a local table only see that node’s data.
• Distributed tables (Distributed engine): a logical routing layer that sits on top of the local tables. When you query a distributed table, ClickHouse fans the query out to all shards, collects the results, and merges them. Distributed tables don’t store data themselves.

N.B. schema changes need to be applied to local tables on every node, and the distributed table definition needs to match. It sounds obvious, but it is a common source of confusion when onboarding teams who are used to a single-server database.

Shard key selection matters for data distribution. A poorly chosen shard key (or rand() used as a lazy default) can lead to uneven data distribution, e.g. one shard holding 60% of the data while others hold 20% each — this creates hot spots and makes capacity planning unreliable. The shard key should distribute data evenly and, ideally, align with how you query, if most queries filter by tenant_id, sharding by tenant_id means queries for a single tenant hit one shard instead of all of them.

Partition Key and Primary Key (Sparse Index)

These two concepts trip up almost everyone coming from a relational background, because they sound like the same thing but serve entirely different purposes in ClickHouse. The partition key controls how data is physically divided into separate directories on disk. Each partition is stored and managed independently, which means:

• Queries that filter on the partition key can skip entire partitions without reading them (partition pruning)
• Old data can be dropped by dropping a partition, instant, no heavy delete operation
• Background merges only happen within a partition, not across them

For time-series data, partitioning by month (toYYYYMM(event_time)) is the most common pattern. It gives you clean data lifecycle management (drop old months instantly) and good pruning behavior for time-bounded queries.

The primary key in ClickHouse is not a uniqueness constraint, it’s a sparse index. ClickHouse stores one index entry per 8192 rows (one granule), not one per row. This makes it memory-efficient even at billions of rows, but it means the primary key is designed for range scans and filtering, not point lookups.

The ORDER BY clause defines the physical sort order of data on disk, and the primary key must be a prefix of ORDER BY. This is worth saying clearly: the sort order is what makes your queries fast or slow. If your most common query filters by (tenant_id, event_type, event_time), your ORDER BY should reflect that. Data is stored sorted by those columns, so ClickHouse can skip irrelevant granules efficiently.

Here’s a concrete example that puts these together:

CREATE TABLE events_local
(
    tenant_id     UInt32,
    event_time    DateTime,
    event_type    LowCardinality(String),
    user_id       UInt64,
    session_id    UUID,
    properties    String,
    ingested_at   DateTime DEFAULT now()
)
ENGINE = ReplicatedMergeTree(
    '/clickhouse/tables/{shard}/events',
    '{replica}'
)
PARTITION BY toYYYYMM(event_time)
ORDER BY (tenant_id, event_type, event_time)
SETTINGS index_granularity = 8192;

A few decisions that can be taken as below:

• LowCardinality(String) for event_type, if this column has fewer than 10,000 distinct values, this encoding dramatically reduces storage and speeds up filtering.
• PARTITION BY toYYYYMM(event_time), monthly partitions, suitable for a 12–18 month hot data retention window.
• ORDER BY (tenant_id, event_type, event_time), optimized for queries that filter by tenant first, then by event type, then narrow by time range.
• The ZooKeeper path uses {shard} and {replica} macros so the same DDL can be run on every node without modification.

Materialized Views and Aggregated Tables

Materialized views in ClickHouse are not the same as in PostgreSQL. They are real-time incremental aggregations; every time data is inserted into the source table, the materialized view processes those rows and writes the aggregated result to a target table. There’s no scheduled refresh and it happens synchronously with the insert.

This makes them powerful for pre-computing aggregations that would otherwise require scanning billions of rows at query time. A common pattern is to maintain hourly or daily rollup tables alongside the raw events table. 

For example, create the target table for aggregated counts and later create the MATERIALIZED VIEW with aggregation.

CREATE TABLE events_hourly_agg
(
    tenant_id    UInt32,
    event_type   LowCardinality(String),
    hour         DateTime,
    event_count  AggregateFunction(count, UInt64)
)
ENGINE = AggregatingMergeTree()
PARTITION BY toYYYYMM(hour)
ORDER BY (tenant_id, event_type, hour);



CREATE MATERIALIZED VIEW events_to_hourly
TO events_hourly_agg
AS
SELECT
    tenant_id,
    event_type,
    toStartOfHour(event_time) AS hour,
    countState() AS event_count
FROM events_local
GROUP BY tenant_id, event_type, hour;

Operationally, materialized views add write amplification, every insert into the source table triggers a write to the view’s target table. For high-ingestion workloads, this is worth monitoring. They also need to be maintained when the source schema changes, which is often forgotten until something breaks.

Operational Design Patterns

Time-Series and Event Analytics Schemas

The vast majority of ClickHouse deployments are built around time-series or event data clickstreams, application logs, metrics, IoT sensor readings. This is where ClickHouse’s design shines, and there are well-established patterns to follow.

The core principle is time as the primary organizing dimension. Partition by time (monthly or weekly depending on data volume), and include event_time in the ORDER BY so range scans are efficient. Keep raw events immutable and resist the temptation to update them in place.

For retention management, the TTL clause handles automatic expiry without manual intervention:

TTL event_time + INTERVAL 90 DAY DELETE

TTL event_time + INTERVAL 30 DAY TO DISK 'cold_storage'

The first script automatically deletes rows older than 90 days while the second scripts move cold data to a cheaper storage tier. This is operationally cleaner than scheduled delete jobs, which in ClickHouse would trigger heavy mutations.

Bulk Ingestion vs. Real-Time Streaming

How data arrives significantly affects schema and operational behavior. ClickHouse handles both, but they stress the system differently.

Bulk ingestion, i.e. large batch inserts; for example, nightly ETL from a data warehouse, is relatively forgiving. ClickHouse is designed for large INSERT batches, each batch creates one or a few data parts, and the background merge process handles compaction. 

The risk is inserting too many small batches in rapid succession, which creates a flood of tiny parts that overwhelm the merge queue. The rule of thumb is: batch size matters more than frequency. Aim for inserts of at least 10,000 –100,000 rows per batch.

Real-time streaming via Kafka requires more care. The ClickHouse Kafka table engine or tools like Vector/Benthos handle ingestion, but the operational concern is the same: small, frequent inserts create merge pressure. Configure consumers to buffer and batch messages before inserting, and monitor system.parts for signs of part accumulation.

SELECT
    table,
    count() AS part_count,
    sum(rows) AS total_rows,
    formatReadableSize(sum(bytes_on_disk)) AS disk_size
FROM system.parts
WHERE active = 1
GROUP BY table
ORDER BY part_count DESC;

A healthy table has tens to low hundreds of active parts. Thousands of parts is a warning sign that inserts are too small or merges are falling behind.

Multi-Tenant Schema Isolation

If your ClickHouse cluster serves multiple tenants, you need to decide early how to isolate their data. The main options are:

• Database-per-tenant: each tenant gets their own database (and potentially their own set of tables). Clean isolation, simple access control, but doesn’t scale past a few dozen tenants without becoming a management burden.
• Table-per-tenant: all tenants share a database, each with their own table. Works at moderate scale but schema changes need to be applied to every tenant table, which is operationally painful at hundreds of tenants.
• Shared table with tenant_id column: all tenant data in one table, filtered by tenant_id. This is the most operationally maintainable pattern at scale. The key requirement is that tenant_id must be the leading column in ORDER BY so that per-tenant queries efficiently skip irrelevant data without a full scan.

ORDER BY (tenant_id, event_type, event_time)

With this sort order, a query filtering on tenant_id = 42 skips all granules that don’t contain that tenant’s data, making it effectively as fast as if the table contained only that tenant’s rows.

Schema Evolution and Operational Impact

Adding Columns, Partitions, and Handling Mutations

Schema changes in ClickHouse are generally safer than in OLTP databases, but they are not without operational cost. Adding a column is fast and non-blocking. ClickHouse uses lazy evaluation, the new column returns a default value for existing rows without rewriting data on disk. It is one of the rare DDL operations you can run in production without much anxiety:

ALTER TABLE events_local ON CLUSTER my_cluster
ADD COLUMN geo_country LowCardinality(String) DEFAULT '';

Dropping a column triggers a background data rewrite (mutation) to remove that column from existing parts. This is heavier and can be slow on large tables. Mutations are expensive in ClickHouse. For example when you run the following command, ClickHouse does not update the row in place but finds all parts with the matching condition, creates new versions of those parts with the modification already applied, replaces old parts after processing and continues serving queries while mutations run in the background.

ALTER TABLE events_local
UPDATE status = 'processed'
WHERE id = 123; 

The guidance here is simple: avoid mutations in hot paths. For data corrections, prefer inserting corrected rows and using a ReplacingMergeTree or CollapsingMergeTree engine to handle deduplication, rather than updating rows in place.

If you must run a mutation, monitor its progress:

SELECT
    command,
    parts_to_do,
    is_done,
    latest_fail_reason
FROM system.mutations
WHERE table = 'events_local' AND is_done = 0;

Monitoring Schema-Related Issues

Identifying Slow Queries and Partition Problems

The most useful table in ClickHouse for day-to-day schema health monitoring is system.query_log. Queries that are reading an unexpectedly high number of rows relative to what they return are usually a sign of poor partition pruning or an ORDER BY that doesn’t align with the filter. Skipping indexes (secondary indexes in ClickHouse) are often added with good intentions but not actually used. Check whether they’re being utilized by execute the following:

SELECT
    table,
    name,
    type,
    expr
FROM system.data_skipping_indices
WHERE database = 'mydb';

Then cross-reference with system.query_log to see if queries against that table are actually benefiting, if read_rows remains high after adding an index, it may not be matching the query pattern.

Capacity Planning for Growth

Schema decisions have long-term storage implications that are not always obvious at design time. A few metrics are worth tracking regularly, such those included in this storage growth per table over time monitoring query below:

SELECT
    table,
    formatReadableSize(sum(bytes_on_disk)) AS total_size,
    sum(rows) AS total_rows,
    count() AS part_count,
    max(modification_time) AS last_modified
FROM system.parts
WHERE active = 1 AND database = 'mydb'
GROUP BY table
ORDER BY sum(bytes_on_disk) DESC;

Track the table with total size, rows, partition count on a weekly basis and plot the trend. A table that grows 20% month-over-month with a 90 day TTL will eventually reach a stable size but a table with no TTL and unbounded growth will eventually cause disk pressure that affects the entire cluster.

Partition-level granularity is also useful for anticipating when TTL drops will occur and what storage they will free:

SELECT
    partition,
    formatReadableSize(sum(bytes_on_disk)) AS size,
    sum(rows) AS rows,
    count() AS parts
FROM system.parts
WHERE active = 1 AND table = 'events_local'
GROUP BY partition
ORDER BY partition DESC;

The above query shows the size per partition for the ClickHouse table events_local. 

Integrating with Your Multi-Database Environment

Data Flow from OLTP to ClickHouse

Most ClickHouse deployments exist downstream of an OLTP database. Orders come in through PostgreSQL, user events flow through MySQL, and ClickHouse ingests and aggregates that data for analytics. This pipeline introduces a class of schema problems that don’t exist in single-database setups.

The OLTP schema and the ClickHouse schema should not be the same schema. OLTP tables are normalized, they are designed to minimize write amplification and enforce referential integrity. ClickHouse schemas are deliberately denormalized, trading write efficiency for read efficiency. A join that’s trivial in PostgreSQL can be expensive in ClickHouse at scale, so the right pattern is to resolve joins at ingestion time, pushing denormalized, enriched records into ClickHouse rather than replicating normalized tables and joining at query time.

This means the ingestion pipeline, whether it’s Kafka, Debezium CDC, Airbyte, or a custom ETL, is also a transformation layer. Fields get renamed, types get cast, related records get joined and flattened, and low-cardinality string fields get encoded appropriately. Operationally, this pipeline is part of the schema: changes to it have the same impact as changes to the table definition.

Managing Model Changes, Versioning, and Rollback

When the upstream OLTP schema changes eg: a new column added to orders, a field renamed in users. The downstream ClickHouse schema and the ingestion pipeline both need to change in a coordinated way. Without a versioning discipline, these changes become brittle and difficult to roll back — a few practices that hold up well in production:

• Treat DDL as code: The schema changes should live in version-controlled migration files (tools like Flyway or a custom migration runner), not applied ad-hoc from a SQL client. Every ALTER TABLE that went to production should be traceable to a commit.
• Add before you remove: When renaming a column or changing a type, add the new column first and allow both the old and new column to coexist during a transition window. Update the ingestion pipeline to write to both, then cut over queries to the new column, then drop the old one. This avoids a hard cutover that can’t be rolled back.
• Schema rollback is hard therefore plan for it: Dropping a column or partition key change is not easily reversible. Before applying significant schema changes, take a backup of the affected table (or at minimum its most recent partition) so that recovery is possible without a full cluster restore.
• Document the lineage: For each ClickHouse table, maintain a short document describing where the data comes from, what transformations are applied, and what downstream queries or dashboards depend on it. When a schema change is proposed, this lineage makes the blast radius obvious before anything is applied.

Conclusion

ClickHouse schema design is not something that you set once, but is something you evolve over time. The implication is that choices you make when creating a table today do not just affect today’s queries but quietly shape how your system performs months down the line, from how efficiently queries run to how painful or painless future schema changes turn out to be.

Some points worth keeping in mind when designing the schema and data modeling: design your ORDER BY for readers, not writers; structure your sort key around how people query the data, not around the order it arrives in; partition by time and but avoid slicing things so finely that merge overhead becomes its own problem; Be deliberate with data types. LowCardinality and AggregateFunction are powerful tools, but only when applied with clear intent. Reaching for them out of habit rather than purpose tends to backfire. Your ingestion pipeline is part of your schema — how data flows in isn’t separate from how it’s stored, think of them as one connected system. 

And remember, keep an eye on the correct metrics from the start. Schema issues seldom make themselves known in an obvious way. Identifying them through regular monitoring is much less expensive than dealing with the aftermath. The central idea here is that decisions regarding schemas have a cumulative effect. Positive choices subtly simplify all other aspects, while negative ones discreetly complicate them.

Two weeks ago, we announced that Doltgres 1.0 is coming August 6th. In that post, we laid out the four things we’re focused on to get there: correctness, storage format stability, performance, and compatibility. Correctness was measured by one very concrete number: 99% compliance on our SQL Logic Test suite. At the time we were sitting at a little over 96%. Today, we’ve hit our target: Doltgres passes 99% of the suite. That’s one more box checked on the road to 1.0. 🎉

SQL Logic Test#

SQL Logic Test is a test suite originally built for SQLite, containing millions of statements and queries that exercise SQL expressions, joins, aggregates, and type coercion rules. We forked it years ago and extended it with more tests to measure how correctly Dolt (and now Doltgres) execute SQL statements. The test suite in SQL Logic Test specifically stress tests the expression support in each engine. 99% represents millions of individual queries whose results have to match PostgreSQL exactly, down to the type and formatting of every returned value. This gives us a high confidence that Doltgres can correctly execute a wide range of statements and expressions.

Establishing a Baseline Against PostgreSQL#

Before we could chase down our own bugs, we needed to answer a more basic question: how many of these tests are even valid against PostgreSQL? The suite was originally written for SQLite, and over the years it’s been adapted and extended for MySQL as we’ve used it to test Dolt. Postgres has never been the primary target, so we couldn’t assume the entire test suite would execute cleanly against Postgres.

We pointed our test runner at a real PostgreSQL server and ran the full suite against stock PostgreSQL first, to establish a baseline of how compatible the tests actually were with Postgres. That baseline surfaced a number of places where the tests themselves (or in many cases, the runner’s expectations about results) were still encoding SQLite or MySQL behavior and not compatible with slightly different behavior in Postgres. Fixing those was a prerequisite before we could start figuring out what changes were needed in Doltgres.

A few examples of what we found and fixed:

• Integer vs. float schema types. The test format encodes an expected type for each result column (I for integer, R for float/real), based on SQLite’s type affinity rules. Postgres is stricter about numeric types than SQLite, so expressions SQLite treats as integers may legitimately come back as floats from Postgres, and vice versa. We updated the runner’s schema comparison to treat I and R as compatible in both directions, and to normalize whole-number floats (like 3.000) to integer formatting (3) so the value comparison succeeds when the underlying values genuinely match. Limiting this to whole-number floats only means we still detect correctness errors if the values don’t logically match, but we’re more flexible on the returned result type so that we can use the same tests to match against Postgres.
• Empty result sets. SQLite reports SQLITE_NULL as the type for every column when a query returns zero rows, which doesn’t correspond to anything meaningful in Postgres. We updated the runner to skip schema-type verification entirely when both the expected and actual result sets are empty, since there’s nothing to compare.
• Postgres-specific type names in the harness. Our test harness inspects the driver’s reported column types to decide how to parse and compare each value. It was written with MySQL’s type names in mind (INT, BIGINT, DECIMAL, and so on), so it didn’t know what to do with Postgres-specific names like BOOL, INT2, or FLOAT4. We filled in the missing cases so those types get parsed and compared correctly instead of falling through and failing.
• MySQL-only statements. Some tests exercise MySQL-specific syntax or behavior that has no Postgres equivalent at all. Rather than force those through, we added skip directives so they’re excluded when running against Doltgres, the same way we already skip SQLite-specific tests.

In addition to those improvements, we also invested in running the suite in parallel, spinning up a single shared Doltgres server and fanning test files out across concurrent workers, each against its own isolated database. At the scale of millions of test queries, that’s the difference between a test run that takes minutes and one that takes hours, which matters a lot when you’re iterating on fixes.

Bugs the Tests Found in Doltgres#

With those changes in place, we could now run the SQL Logic Tests against a real PostgreSQL server and get over 99% correctness. There are still some issues for the test suite to run 100% against PostgreSQL, and we’ll keep chipping away at those in future passes. After these improvements, the remaining test failures with Doltgres were much more likely to be real Doltgres bugs that we needed to dig into. The most interesting was in our COALESCE() implementation: when called with mixed numeric types (say, an int4 and an int8, or an int4 and a float8), it was using a generic type conversion instead of Postgres’ assignment cast rules to compute the common type. That’s an important distinction. Assignment casts are what Postgres itself uses to widen mixed-type arguments to a common type, and using the wrong conversion path meant we could return incorrectly typed or incorrectly rounded results for a fairly common pattern in real SQL.

On Track for August 6th#

Correctly executing 99% of the SQL Logic Test suite was our target for Doltgres’ 1.0 release. It gives us high confidence that a wide range of statements and SQL expressions are executing correctly in Doltgres. By baselining the test suite against PostgreSQL, we discovered that we were closer to this milestone than we initially expected. We thought we still had many remaining gaps to fill to reach that milestone, but it turned out that how the results were being processed by the test runner accounted for most of the gap.

Executing queries correctly, and returning identical results as PostgreSQL, is the foundation for our 1.0 launch. Without correct query execution, the other goals, like fast execution of queries and tool compatibility, just don’t matter. Overall, we’re making great progress on our 1.0 punch list and remain on track for August 6th.

If you’re running Doltgres and you hit a query that returns the wrong result, an error you don’t expect, or behavior that just doesn’t match Postgres, please send us a GitHub issue and let us know. We want to find and fix as many of these as possible before 1.0 ships, and customer-reported issues go straight to the top of our queue.

If you haven’t started using Doltgres yet, give it a shot! You can install Doltgres by running brew intall doltgres on a Mac with Homebrew, or you download a binary from our GitHub releases. Our dev team hangs out on the DoltHub Discord server every day, so feel free to come by and tell us how it’s going. We’re closing in on 1.0 and every bit of feedback helps us get there!

ABILENE, KS — Romance was in the air at the Farris household as wife Allison was seen getting ready for bed without her mouthguard in place.

Mr. Aaron Farris, who had just finished brushing his teeth, felt a jolt of excitement as he noticed his wife had gotten ready for bed without the thick piece of plastic that keeps her from grinding her teeth like a chainsaw.

"Oh man, it’s so on. Play it cool, Aaron," said Mr. Farris to himself. "Let me double-check. Yup, mouthguard is out. Like the married version of candlelight and Marin Gaye. Little extra mouthwash, and we’ll be good to go."

As he prepared to get in bed, Farris made an extra show of not putting on his CPAP machine. "Mm, think I’m going to stay up for a bit," announced Farris to his wife. "Yeah, sleep apnea can wait. Got, um, something else on my mind."

At publishing time, Farris was sure he’d read the signals correctly as he discovered his wife also wasn’t wearing her standard woolen socks.

---

Rumors swirl about the current condition of Senator Mitch McConnell, but his staff have come out to say that even if he were dead he will still be able to finish his term.