MySQL has dropped its newest release
, categorized as “Early Access” and available at https://labs.mysql.com/
.

While this post is not going to go into depth, I wanted to at least validate the management changes you verify between normal MySQL upgrades.

The new release version is `26.7`, the first version with a date-based release number convention. While this is numerically increased, the first problem I found was a character ordering problem not found in prior scripts. This is because 2 is before 9, historically for any version, the versions listed alphabetically would always list older before newer. MySQL isn’t the only product that uses this naming convention, or the first to switch from one to the other, however some random MySQL script on some customer installation is going to have some small issue and there will be the unnecessary followup flamewar. See later as to why I mentioned this.

Docker Containers Setup

Preamble

TMP_DIR=${TMP_DIR:-/tmp}
TEST_CASE="first-look"
rm ${TMP_DIR}/${TEST_CASE}.*

Install MySQL 9.7

The following will install the current MySQL 9.7 docker version.

REGISTRY_NAME="container-registry.oracle.com/mysql/community-server:9.7"
CONTAINER_NAME="mysql097"

docker pull ${REGISTRY_NAME}
MYSQL_PASSWD="M#$(date | md5sum - | cut -c-20)"
OUTPUT=$(docker run -d --name ${CONTAINER_NAME} \
   --platform linux/amd64 \
   -e MYSQL_ROOT_PASSWORD=${MYSQL_PASSWD} \
   ${REGISTRY_NAME})
echo $?
echo $OUTPUT
# Not completely accurate
# docker logs ${CONTAINER_NAME} | grep "ready for connections"
sleep 5
docker exec -it ${CONTAINER_NAME} mysql -uroot -p${MYSQL_PASSWD} -sN -e "SELECT VERSION()" | grep -v "can be insecure"
docker stop ${CONTAINER_NAME} && docker rm ${CONTAINER_NAME}

Install MySQL 26.7 EA

In a separate terminal window run.

# See https://labs.mysql.com/ to obtain valid and current direct download links
wget https://downloads.mysql.com/snapshots/pb/mysql-26.7.0-labs-release/mysql-community-server-26.7.0-labs-docker-el9-x86_64.tar.gz
docker load -i mysql-community-server-26.7.0-labs-docker-el9-x86_64.tar.gz
REGISTRY_NAME="localhost/mysql/community-server:26.7.0"
CONTAINER_NAME="mysql267"
...

New Variables

The 26.7 Release Notes
are not accompanied by a current reference Manual (e.g. 9.7
) so this default checking gives a first approximate look.

What it doesn’t find is for example the setup for a replica, or different distros, or following the installation of any components in which there is noted new work.

# Run for both containers
$ docker exec -it ${CONTAINER_NAME} mysql -uroot -p${MYSQL_PASSWD} -s -e "SELECT VERSION(); SELECT VARIABLE_NAME FROM performance_schema.global_variables ORDER BY 1" > ${TMP_DIR}/${TEST_CASE}.variables.${CONTAINER_NAME}.txt

$ diff -y --suppress-common-lines ${TMP_DIR}/${TEST_CASE}.variables.*.txt
9.7.1   						      |	26.7.0-er
							      >	admin_force_pqc
							      >	admin_tls_kex
							      >	admin_use_pqc_sign
							      >	force_pqc
							      >	innodb_autoinc_preallocate
							      >	mysqlx_force_pqc
							      >	mysqlx_tls_kex
							      >	mysqlx_use_pqc_sign
							      >	replication_force_pqc
							      >	replication_tls_kex
							      >	replication_use_pqc_sign
							      >	tls_kex
							      >	use_pqc_sign

One of my primary goals in further evaluation is the Post-quantum cryptography support with OpenSSL 3.5. You can find some intro information in my post Q Day Is Coming: A Plain-English Guide to Post-Quantum Cryptography
. Almost all these variables align with this functionality. The kex variables is the standard abbreviation for key exchange. The pqc variables are related to the negotiation of a PQC (or Hybrid) group. As you can see in the next section, all pqc related variables are OFF.

Based on this information, without looking into source, the force_pqc, tls_kex and use_pqc_sign would be the required three variables to enable post quantum key exchange.

Variable Values

$ docker exec -it ${CONTAINER_NAME} mysql -uroot -p${MYSQL_PASSWD} -s -e "SELECT VERSION(); SELECT VARIABLE_NAME, VARIABLE_VALUE FROM performance_schema.global_variables ORDER BY 1" > ${TMP_DIR}/${TEST_CASE}.var-value.${CONTAINER_NAME}.txt

$ diff -y --suppress-common-lines ${TMP_DIR}/${TEST_CASE}.var-value.*.txt | grep ">" | cut -d'>' -f2-
	admin_force_pqc	OFF
	admin_tls_kex
	admin_use_pqc_sign	OFF
	force_pqc	OFF
	innodb_autoinc_preallocate	50
	mysqlx_force_pqc	OFF
	mysqlx_tls_kex
	mysqlx_use_pqc_sign	OFF
	replication_force_pqc	OFF
	replication_tls_kex
	replication_use_pqc_sign	OFF
	tls_kex

There would appear to be no changes to default values for existing variables, however this is just the sandbox docker installation.

Alphabetical and Chronological

I mentioned earlier about chronological and alphabetical issues. This is what I mean. My line of code was to diff mysql97 and mysql267 where previously these would wash out alphabetically, but this does not happen now (i.e. historically it would be mysql8, mysql84, mysql97). mysql267 preceeds mysql97, and hence why the container for simplicity of commands is called mysql097.

$ diff -y --suppress-common-lines ${TMP_DIR}/${TEST_CASE}.variables.*.txt
26.7.0-er       				      |	9.7.1
admin_force_pqc 			      <
admin_tls_kex   				      <
admin_use_pqc_sign      		      <
force_pqc       				      <
innodb_autoinc_preallocate            <
mysqlx_force_pqc			      <
mysqlx_tls_kex  				      <
mysqlx_use_pqc_sign     		      <
replication_force_pqc   		      <
replication_tls_kex     		      <
replication_use_pqc_sign	      <
tls_kex 					      <
use_pqc_sign    				      <

New Status Variables

$ docker exec -it ${CONTAINER_NAME} mysql -uroot -p${MYSQL_PASSWD} -s -e "SELECT VERSION(); SELECT VARIABLE_NAME FROM performance_schema.global_status ORDER BY 1" > ${TMP_DIR}/${TEST_CASE}.status.${CONTAINER_NAME}.txt

$ diff -y --suppress-common-lines ${TMP_DIR}/${TEST_CASE}.status.*.txt
9.7.1   						      |	26.7.0-er
							      >	Mysqlx_force_pqc
							      >	Mysqlx_tls_kex
							      >	Mysqlx_use_pqc_sign

There would appear to be three new status variables related only to the mysqlx access of the post quantum support. Nothing for admin or replication, however again this is a primary and not a replica.

Status Values

Looking at values on two different systems is more complicated for many factors, however a quick scan reveals no obvious changes in volumes or measure.

$ diff -y --suppress-common-lines ${TMP_DIR}/${TEST_CASE}.status-value.*.txt
9.7.1							      |	26.7.0-er
Bytes_received	2115					      |	Bytes_received	3343
Bytes_sent	52456					      |	Bytes_sent	90408
Caching_sha2_password_rsa_public_key	-----BEGIN PUBLIC KEY |	Caching_sha2_password_rsa_public_key	-----BEGIN PUBLIC KEY
Connections	14					      |	Connections	17
Error_log_buffered_bytes	1224			      |	Error_log_buffered_bytes	1752
Error_log_buffered_events	10			      |	Error_log_buffered_events	12
Error_log_latest_write	1784693965980559		      |	Error_log_latest_write	1784693440620348
Handler_commit	592					      |	Handler_commit	596
Handler_external_lock	6477				      |	Handler_external_lock	6489
Handler_read_key	1752				      |	Handler_read_key	1756
Handler_read_next	4160				      |	Handler_read_next	4166
Handler_read_rnd_next	1768				      |	Handler_read_rnd_next	3087
Innodb_buffer_pool_bytes_data	19775488		      |	Innodb_buffer_pool_bytes_data	19906560
Innodb_buffer_pool_load_status	Buffer pool(s) load completed |	Innodb_buffer_pool_load_status	Buffer pool(s) load completed
Innodb_buffer_pool_pages_data	1207			      |	Innodb_buffer_pool_pages_data	1215
Innodb_buffer_pool_pages_flushed	199		      |	Innodb_buffer_pool_pages_flushed	198
Innodb_buffer_pool_pages_free	6985			      |	Innodb_buffer_pool_pages_free	6977
Innodb_buffer_pool_read_requests	16159		      |	Innodb_buffer_pool_read_requests	16200
Innodb_buffer_pool_reads	1063			      |	Innodb_buffer_pool_reads	1070
Innodb_buffer_pool_write_requests	1971		      |	Innodb_buffer_pool_write_requests	1993
Innodb_data_fsyncs	75				      |	Innodb_data_fsyncs	69
Innodb_data_read	17485312			      |	Innodb_data_read	17691136
Innodb_data_reads	1089				      |	Innodb_data_reads	1098
Innodb_data_writes	258				      |	Innodb_data_writes	253
Innodb_data_written	3336704				      |	Innodb_data_written	3312640
Innodb_dblwr_pages_written	58			      |	Innodb_dblwr_pages_written	57
Innodb_dblwr_writes	4				      |	Innodb_dblwr_writes	3
Innodb_log_write_requests	841			      |	Innodb_log_write_requests	840
Innodb_log_writes	20				      |	Innodb_log_writes	18
Innodb_os_log_fsyncs	16				      |	Innodb_os_log_fsyncs	12
Innodb_os_log_written	53760				      |	Innodb_os_log_written	52736
Innodb_pages_created	145				      |	Innodb_pages_created	146
Innodb_pages_read	1062				      |	Innodb_pages_read	1069
Innodb_pages_written	199				      |	Innodb_pages_written	198
Innodb_redo_log_checkpoint_lsn	29775665		      |	Innodb_redo_log_checkpoint_lsn	30006513
Innodb_redo_log_current_lsn	29775665		      |	Innodb_redo_log_current_lsn	30006513
Innodb_redo_log_flushed_to_disk_lsn	29775665	      |	Innodb_redo_log_flushed_to_disk_lsn	30006513
Innodb_redo_log_uuid	1560291093			      |	Innodb_redo_log_uuid	2583501521
Innodb_system_rows_read	4888				      |	Innodb_system_rows_read	4894
Max_used_connections_time	2026-07-22 04:19:37	      |	Max_used_connections_time	2026-07-22 04:10:50
							      >	Mysqlx_force_pqc	OFF
Mysqlx_ssl_server_not_after	Jul 19 04:19:20 2036 GMT      |	Mysqlx_ssl_server_not_after	Jul 19 04:10:35 2036 GMT
Mysqlx_ssl_server_not_before	Jul 22 04:19:20 2026 GMT      |	Mysqlx_ssl_server_not_before	Jul 22 04:10:35 2026 GMT
							      >	Mysqlx_tls_kex
							      >	Mysqlx_use_pqc_sign	OFF
Open_tables	74					      |	Open_tables	63
Opened_tables	155					      |	Opened_tables	144
option_tracker_usage:Traditional Optimizer	15	      |	option_tracker_usage:Traditional Optimizer	23
Performance_schema_session_connect_attrs_longest_seen	112   |	Performance_schema_session_connect_attrs_longest_seen	116
Queries	28						      |	Queries	44
Questions	27					      |	Questions	43
Rsa_public_key	-----BEGIN PUBLIC KEY-----\nMIIBIjANBgkqhkiG9 |	Rsa_public_key	-----BEGIN PUBLIC KEY-----\nMIIBIjANBgkqhkiG9
Select_scan	4					      |	Select_scan	6
Sort_rows	1604					      |	Sort_rows	2921
Sort_scan	3					      |	Sort_scan	5
Ssl_server_not_after	Jul 19 04:19:20 2036 GMT	      |	Ssl_server_not_after	Jul 19 04:10:35 2036 GMT
Ssl_server_not_before	Jul 22 04:19:20 2026 GMT	      |	Ssl_server_not_before	Jul 22 04:10:35 2026 GMT
Table_locks_immediate	4				      |	Table_locks_immediate	6
Table_open_cache_hits	3084				      |	Table_open_cache_hits	3101
Table_open_cache_misses	155				      |	Table_open_cache_misses	144
Tls_library_version	OpenSSL 3.5.1 1 Jul 2025	      |	Tls_library_version	OpenSSL 3.5.5 27 Jan 2026
Uptime	7128						      |	Uptime	7658
Uptime_since_flush_status	7128			      |	Uptime_since_flush_status	7658

This is not an exhaustive comparison, however there appears to be no new table objects. There are 3 new replication applier related columns which align with the release notes.

$ docker exec -it ${CONTAINER_NAME} mysql -uroot -p${MYSQL_PASSWD} -s -e "SELECT VERSION(); SELECT TABLE_SCHEMA, TABLE_NAME FROM information_schema.tables ORDER BY 1,2" > ${TMP_DIR}/${TEST_CASE}.tables.${CONTAINER_NAME}.txt
$ diff -y --suppress-common-lines ${TMP_DIR}/${TEST_CASE}.tables.txt

docker exec -it ${CONTAINER_NAME} mysql -uroot -p${MYSQL_PASSWD} -s -e "SELECT VERSION(); SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION FROM information_schema.columns ORDER BY 1,2,4" > ${TMP_DIR}/${TEST_CASE}.columns.${CONTAINER_NAME}.txt

$ diff -y -W 400 --suppress-common-lines ${TMP_DIR}/${TEST_CASE}.columns.*.txt
9.7.1																								      |	26.7.0-er
		>	mysql	slave_relay_log_info	Applier_version	16
		>	mysql	slave_relay_log_info	Applier_worker_count	17
		>	mysql	slave_relay_log_info	Applier_event_memory_limit	18
		>	performance_schema	replication_applier_configuration	APPLIER_VERSION	8
		>	performance_schema	replication_applier_configuration	APPLIER_WORKER_COUNT	9
		>	performance_schema	replication_applier_configuration	APPLIER_EVENT_MEMORY_LIMIT	10

Plugins

There are no additional plugins found by comparing via SHOW PLUGINS, however the release notes reference the previously enterprise feature of Thread Pool Plugin now in MySQL Community Server. There are no installed components by default in the docker mode for either version.

Thread Pool

The use of the Thread Pool plugin requires a MySQL configuration modification for the mysqld process, which requires a restart.

$ OUTPUT=$(docker run -d --name ${CONTAINER_NAME} \
   --platform linux/amd64 \
   -e MYSQL_ROOT_PASSWORD=${MYSQL_PASSWD} \
   ${REGISTRY_NAME} \
   --plugin-load-add=thread_pool.so)

Thread Pool Logs

Following the installation instructions for the Thread Pool
for MySQL Enterprise you can see this plugin in 26.7.

$ docker logs ${CONTAINER_NAME}
...
2026-07-22T06:58:28.845787Z 0 [System] [MY-013852] [Server] Thread pool plugin started successfully with parameters: thread_pool_size = 1, thread_pool_algorithm = High Concurrency Algorithm, thread_pool_stall_limit = 6, thread_pool_prio_kickup_timer = 1000, thread_pool_max_unused_threads = 32, thread_pool_max_active_query_threads = 0, thread_pool_dedicated_listeners = 0, thread_pool_max_transactions_limit = 32, thread_pool_transaction_delay = 0, thread_pool_query_threads_per_group = 2, thread_pool_connection_report_interval = 120, thread_pool_longrun_trx_limit = 2000 ; 2000

Thread Pool Plugins

There are now 4 additional plugins in 26.7 with the thread pool plugin enabled.

mysql> SHOW PLUGINS;
+----------------------------------+----------+--------------------+----------------+-------------+
| Name                             | Status   | Type               | Library        | License     |
+----------------------------------+----------+--------------------+----------------+-------------+
...
| thread_pool                      | ACTIVE   | DAEMON             | thread_pool.so | PROPRIETARY |
| TP_THREAD_STATE                  | ACTIVE   | INFORMATION SCHEMA | thread_pool.so | PROPRIETARY |
| TP_THREAD_GROUP_STATE            | ACTIVE   | INFORMATION SCHEMA | thread_pool.so | PROPRIETARY |
| TP_THREAD_GROUP_STATS            | ACTIVE   | INFORMATION SCHEMA | thread_pool.so | PROPRIETARY |
+----------------------------------+----------+--------------------+----------------+-------------+
50 rows in set (0.023 sec)

Thread Pool Tables

Note, the Enterprise 9.7 documentation lists only 3 matching tables, tp_connections appears to be new.

mysql> SELECT TABLE_NAME
    ->        FROM INFORMATION_SCHEMA.TABLES
    ->        WHERE TABLE_SCHEMA = 'performance_schema'
    ->        AND TABLE_NAME LIKE 'tp%';
+-----------------------+
| TABLE_NAME            |
+-----------------------+
| tp_connections        |
| tp_thread_group_state |
| tp_thread_group_stats |
| tp_thread_state       |
+-----------------------+
4 rows in set (0.075 sec)

Conclusion

This is a quick validation I can install and validate a running MySQL 26.7 sandbox. I will be following up in future posts with additional findings.

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!