Why the compatibility setting matters when you upgrade ClickHouse
When a self-managed ClickHouse cluster has to move to a newer release, some queries and applications may start returning different results or failing. The compatibility setting lets you pin default behavior to an earlier server version while you test your workloads against the new release. It is a testing aid, not a substitute for fixing queries: it buys time to find and correct incompatibilities, and it does not make them go away.
The starting point is knowing which versions are still covered. The ClickHouse security policy lists 26.9, 26.8, 26.7 and 26.3 as supported with security updates, and everything else as unsupported. On the GitHub releases page, the latest stable build is v26.9.20.23-stable, and the LTS lines are v26.8.29.10-lts and v26.3.53.4-lts, both published on October 16, 2026. Stable releases ship roughly monthly and LTS releases twice a year, so the version you run today may fall out of the supported list sooner than your change calendar suggests. Confirm the current list before you pick a target release.
The practical decision is which target to test against and how long the pin can stay in place. Start by reading the changelog for the target release, since the production guidance notes that minor backward-incompatible changes can occur. Then run your representative queries and application flows on a pre-production cluster with the setting applied, and again with it removed. Any difference you see is a item to fix in the query, the schema or the application, with the pin used only to keep production stable in the meantime. Record the pin as a dated, owned item in your change log, along with the target version and the queries it covers. A pin that stays on after the upgrade quietly keeps old behavior alive and makes later upgrades harder. Ask your team or partner to state the removal criteria up front, so the setting is taken out once the affected workloads pass on the new release.
What changes between 26.3, 26.8 and 26.9 and what to do about it
This table compares the 26.3 LTS line, the 26.8 LTS line and the 26.9 stable line on the changes that affect a compatibility pin. Read each row as three questions: what changed, what it can break in your applications, and what you must do before upgrading. The main takeaway is that a settings pin buys time for default changes, but it does not replace reading the changelog or testing query analysis on 26.9.
| Change | Impact on applications | Action needed |
|---|---|---|
| Mandatory analyzer in 26.9 | The older query analysis is removed, so queries that depended on it can fail or return different results. | Test representative queries on 26.8 or 26.9 first. See the mandatory analyzer upgrade topic for query-level detail. |
| Default settings the compatibility setting can pin | New defaults can change query results or server behavior between releases. | Pin the earlier defaults while applications are tested, and record which settings are pinned and why. |
| Minor backward-incompatible changes | These can appear in stable and LTS releases without a major version change. | Read the changelog for every release between your current version and your target before upgrading. |
| Changes the setting cannot hide | Removed features and behavior the server no longer offers cannot be pinned back by a settings value. | Identify them in the changelog and fix affected applications or schemas before the upgrade. |
| Supported versions | Per the ClickHouse security policy, security updates cover 26.9, 26.8, 26.7 and 26.3. Other versions receive none. | Treat the pin as temporary and remove it before the pinned release leaves the supported list. |
| LTS lines to target | 26.8.29.10-lts and 26.3.53.4-lts were published on October 16, 2026. | Confirm the current patch level on the release page before choosing a target version. |
| Removing the pin | A pin left in place keeps old defaults running on the new release. | Remove it after application tests pass and the new defaults are verified in production-like tests. |
How the compatibility setting pins defaults to an earlier version
The compatibility setting tells a ClickHouse session to use the default values that applied in an earlier release, while the cluster itself runs the new binary. Applications keep receiving the query behavior they were built against, so the team can move the servers forward first and then test each workload on its own schedule. The setting does not roll back the server code, and it does not change the data on disk. It only changes which defaults the query engine applies when a session or query does not set them explicitly. Because of that, the setting is a testing and transition aid, not a permanent configuration choice.
The setting can be applied at more than one level, and the level you choose determines how much of your traffic it affects. Setting it in a settings profile assigned to a specific user or role limits the change to the service accounts or teams you are testing, which is the usual way to run a controlled pilot. Setting it globally, or in the default profile, pins every session on the cluster, which makes the upgrade look finished while hiding incompatibilities. Before you pick a level, read the release notes and the settings documentation for your target version to list the defaults that changed and the version each one was tied to. Confirm that list in your own environment; do not assume it from a previous release. The ClickHouse documentation on access control describes how profiles are assigned, and it is the place to verify how a profile interacts with your existing role model.
Scoping the setting is what makes it useful, and it also creates a risk you must plan for. A pinned profile can make a pilot look clean while the unpinned workloads, such as ad hoc queries, dashboards or scheduled jobs running under other accounts, reveal the actual breaking changes later. Build an inventory of accounts and query sources, then decide which ones get the pin, and record the decision with an owner and a review date. Track which sessions still rely on the setting so you know when removal is safe. The setting exists to buy time for testing, so every pinned profile should have a removal plan that fits the support window of the version you are running, which you can confirm on the ClickHouse security policy page.
Where the compatibility setting stops working
Pinning defaults buys time while applications are tested, but it changes query behavior only. The limits below share one theme: data, downgrades, replication and removal dates each need their own checks before a pin goes into production.
Removed features stay removed
The setting restores earlier default behavior for settings that still exist, so it cannot bring back a feature that a release has removed. Read the release notes for removed functions, table engines and settings, and list each one your queries or jobs use. Run those workloads on a pre-production cluster running the new release, because they fail whatever value the setting holds.
Existing data keeps its format
Pinning a default changes how queries run, not how parts already on disk were written. Data written or merged by the new release may be readable only by versions that understand its format, so the pin offers no path back for that data. Check the release notes for format or storage changes, and record which version wrote each table before the upgrade starts.
Downgrades stay blocked by new features
If the cluster has already used features that an older binary cannot read, a downgrade is unsafe even while the setting is enabled. The setting does not move that boundary. Before relying on the pin, list the new features your schema and queries use, and check the rollback plan against that list.
Replication and Keeper stay separate
A query-level setting does not align how replicas or ClickHouse Keeper behave during a rolling upgrade. Replicas on different versions still have to replicate the same tables, so mixed-version behavior needs its own test apart from application compatibility. Confirm supported version combinations in the release notes and the replication guide, then test a single replica restart on the new release.
Some changes have no session switch
The setting covers only changes that expose a per-session switch. Changes to server configuration, storage behavior or internal query planning may have no matching setting, and those keep their new behavior in every session. For each breaking change in the release notes, confirm whether a setting exists before assuming the change can be pinned.
Pins can hide later failures
A test suite that passes with the old defaults pinned can look clean while queries still depend on behavior the release has marked for removal. When the pin is dropped, those queries fail unless they were tested without it. Set a dated removal target, run the suite on the new defaults in pre-production, and track each pinned setting until it is gone.
How to run a compatibility-pinned upgrade test
This sequence tests whether applications behave the same on a new ClickHouse release when default settings are pinned to the earlier version, then removes the pin once the workloads pass unpinned. It produces a documented test result, a verified rollback point and a clear record of every difference found.
Back up and record the current state
Before anything changes, the engineer running the test takes a BACKUP of every table in scope, records the current server version with
SELECT version(), and exports the settings the applications depend on, including settings profiles and any query-level settings the applications send. Store the backup in a location separate from the cluster under test and confirm the backup reports completion without errors. The step is done when the backup is listed as complete and the version and settings are saved in the change record.Build a representative pre-production copy
Restore the backup into a pre-production environment that matches the production topology and holds representative data. Load the query set the application actually runs, taken from query logs or recorded application traffic, so the test covers real access patterns. Compare row counts and table sizes between source and copy using queries against system tables. Keep the copy on the current production version until these checks pass. The step is done when counts match and the query set is loaded and runnable.
Deploy the new release to pre-production
Read the changelog for the target release and list the backward-incompatible changes that apply to your queries and settings, as the production guidance recommends. Install the new release on the pre-production nodes and restart them one at a time. Confirm each node reports the target version with
SELECT version(), that replicas are healthy and that no table is stuck in read-only mode. The step is done when every pre-production node runs the new release and the replication checks pass without manual repair.Apply the compatibility setting to test workloads
Set the
compatibilitysetting to the earlier version for the test application's user, profile or session, so defaults that changed between releases match the old behavior while the new binary runs. Scope the setting to the test workload only, so other clients on the pre-production cluster keep the new defaults. Confirm the value is in effect by queryingsystem.settingsfor changed settings from a test session. The step is done when the pinned value is visible in the test session and the team has listed which settings the pin affects, as documented in the release notes.Compare suite results against the baseline
Run the application test suite and the replayed query set against the pinned pre-production cluster, then run the same workload against the baseline version. Compare result sets, row counts, error codes and runtimes, and log every difference. Classify each difference as an application defect, an expected change in the release, or a setting the team must address. The step is done when every difference has an owner and a recorded disposition, and no unexplained difference remains.
Run the suite unpinned and fix differences
Remove the compatibility setting from the test workload and rerun the same suite and query set on the new release. Queries that depend on the old default behavior, such as aliases, JOIN handling or HAVING clauses where the analyzer changed, need code changes before the pin can be removed. Make the changes, review them, and rerun until the unpinned results match the baseline. The step is done when the unpinned run passes with no open differences and every query change is reviewed and merged.
Set the rollback point before production changes
Before any production node changes version, take a fresh BACKUP, record its name and the previous server version, and confirm the team can restore that backup to a spare environment. Check the release notes to see whether the target release allows a downgrade from the version you run now, because features used after the upgrade can block a downgrade. Confirm the previous binary is still available to reinstall. The step is done when the backup is complete, the restore has been rehearsed on a spare environment, and the rollback owner has signed off.
Roll the cluster and remove the pin
Upgrade production one replica at a time, checking replication health and read-only flags after each replica before moving to the next. If production applications set the compatibility value during the rollout, remove it from their configuration once the unpinned suite has passed. Then confirm with
SELECT version()that every replica runs the target release and querysystem.settingsto confirm no session still carries the pinned value. The step is done when all replicas report the target version and no session uses the pin.
When to pin, when to fix and when to remove the setting
Some applications depend on behavior that a new release changes by default, such as how a query is analyzed or how a function or type resolves. Pinning the compatibility setting to the earlier version lets those applications keep running while the team fixes its queries and tests the new release. Pinning buys time to fix queries, but it does not replace fixing them.
- Scope the pin: Apply it to the affected users, profiles or sessions rather than the whole cluster, so unaffected applications receive the new defaults.
- Find the breaking queries: Replay representative queries from the query log against a test cluster on the new release, and compare results and errors with the pinned run.
- Set the decision point: Keep the pin when a breaking change is confirmed and the fix needs application changes that cannot ship in the same window. Fix the query when the change is small and testable.
- Record each pin: Track every pinned workload with an owner and the change that will remove the pin, so it does not persist unnoticed.
Confirm that every pinned workload has a named owner, a passing test on the new release, and a removal ticket before the pin reaches production.
A compatibility setting restores earlier defaults, but it cannot bring back a feature that a release has removed. When an application calls a removed function, syntax or engine option, the only path forward is to change the application or the schema. Confirm each removal in the changelog for the target release, because the setting does not cover every change.
- Identify removals early: Read the changelog for removed or renamed features between your current version and the target, then search your query logs for them.
- Fix in code: Rewrite affected queries or client code to the supported replacement, and test them on representative data.
- Use migration aids with care: Some releases offer a temporary setting for a specific incompatibility, such as
analyzer_compatibility_allow_non_aggregate_in_havingduring the analyzer transition. Confirm it exists in your target version and schedule its removal. - Set the decision point: Fix the workload when the removed feature sits on a critical path that must run on the new release. Delay the upgrade only when the fix cannot be tested in time.
For each removed feature, check the query log for usage and confirm that the replacement passes tests on the target version before setting a cutover date.
Many upgrades change no defaults that affect your workloads. In that case the compatibility setting adds nothing and should not be set at all. Leaving a pin in place hides which applications still depend on old behavior and makes later upgrades harder to reason about. Read the changelog, run the test suite, and remove any leftover pins.
- Confirm with the changelog: Check the ClickHouse production FAQ guidance on reading changelogs, then check the release notes for backward-incompatible changes that touch your functions, types or default settings.
- Verify with tests: Run representative queries and application tests on a pre-production cluster with representative data, and compare results with the current version.
- Remove stale pins: Delete the setting from user profiles, session configuration and client libraries once tests pass without it.
- Set the decision point: Upgrade without the setting when tests pass unchanged. Keep a pin only where a named workload still fails and a fix is scheduled.
Check that no profile or client still sets the compatibility value, and that the pre-production test results are kept with the upgrade record.
What to prepare before you pin any setting
Pinning a compatibility setting only helps if you can roll back safely and know which workloads it protects. The items below cover recovery, inventory, testing, release notes and validation records, and each one narrows the risk of the others.
Verified backup with a test restore
Take a full backup with BACKUP and restore it on a spare cluster before any change. The backup documentation warns that a backup never test-restored will probably not restore properly when needed, so the restore is the proof, not the backup job's success status. Confirm the restore completes, row counts match on key tables, and queries return expected results. Replication alone does not protect against accidental deletes or corruption, so this backup is your real fallback.
Inventory of users, profiles and queries
List every settings profile, user and role that carries defaults, and every application that connects to the cluster. Pinned compatibility values apply through these profiles, so you need to know which accounts inherit them. Capture the exact SQL and query patterns each application sends, especially aggregations, HAVING clauses and JOINs, since those are where behavior changes first. Note which service accounts are shared across teams.
Pre-production environment with representative data
Stand up a non-production cluster on the target release with a representative copy of data and realistic query volume. The ClickHouse production guidance calls for a pre-production environment with representative data and queries. Keep schema, table engines and cluster topology the same as production so results reflect real behavior. Agree in advance which result differences are acceptable and which block the upgrade.
Changelog review for breaking changes
Read the release notes for every version between your current release and the target before testing. The production guidance notes that minor backward-incompatible changes can occur, so a version jump can carry changes you did not expect. Record each item that touches query analysis, default settings, data types or functions your applications use. Assign an owner to each item and decide whether it needs a test case, a code change or a temporary pin.
Validation record per application
Keep a written record of which ClickHouse release each application was tested against, who signed off and which settings were pinned. This record tells you when a pin is still needed and when it can be removed. Without it, a pin may outlive the reason it was added and quietly hide behavior your team never reviewed. Review the record at each upgrade cycle and before retiring any pinned value.
What makes a compatibility-pinned upgrade take longer
This table lists the factors that raise or lower the effort of pinning default settings during a ClickHouse upgrade. Read each row as a question to answer about your own estate before estimating the work. The main takeaway is that the number of dependent queries and the absence of representative test data usually drive effort more than cluster size.
| Driver | Effect on effort | How to keep it down |
|---|---|---|
| Applications and query patterns affected by changed defaults | Higher as more applications and query shapes depend on the defaults being pinned, since each one needs review and testing | Inventory query patterns from query logs and group them by changed default before testing |
| Settings profiles and per-user overrides | Higher when pins are spread across many profiles, users and client-side session settings, because each override must be found and tracked | Keep pins in one settings profile and document any per-user exceptions |
| Representative data in pre-production | Higher when test environments hold little or synthetic data, because behavior differences may only appear on realistic volumes and distributions | Confirm which datasets and query sets can be copied or sampled into pre-production under your data rules |
| Cluster size and topology | Higher on larger clusters with several shards and replicas, since pinned settings must match across nodes and test runs take longer | Test on a smaller cluster with the same version mix and settings before the production rollout |
| Test coverage in client code | Higher when application test suites do not exercise the affected queries, so teams must write or run checks by hand | Add regression tests for the queries that depend on changed defaults before starting the upgrade |
| Governance over removing the pin | Higher when no owner is named or approvals are informal, because removal waits on meetings and sign-offs | Name the approver, record the removal criteria and link the pin to a tracked ticket with a review date |
Questions teams ask before pinning ClickHouse settings
How long can a ClickHouse compatibility pin stay in place?
Keep the pin only until the application teams have tested their queries against the new release and fixed what breaks, then remove it. A pin hides changed defaults, so every week it stays, new code can quietly depend on the old behavior. Set a named owner and a removal date when the pin is created, and record it in the change ticket. Check every place the setting appears, including settings profiles and client configuration, because a pin copied into a shared profile often outlives the project that created it. Review the list at each upgrade step until the last pin is gone.
Can the compatibility setting make a ClickHouse downgrade safe?
No, not on its own. The setting changes how certain defaults behave during queries, but it does not reverse data written by the newer release, schema changes, or features that the new version introduced. Those can block a downgrade whether or not a pin is in place. Before upgrading, take a backup and run a restore test on a spare cluster, following the backup and restore documentation. Treat rollback as a separate plan that lists which new features the team has started using.
What does the team need to provide for a compatibility test cycle?
The team needs four things: a list of the queries each application runs, test data that resembles production in volume and shape, an owner for each application who can judge whether results are correct, and a pre-production cluster that runs the same ClickHouse version as production. Query history from the existing cluster is the fastest starting point for the query list. Application owners are needed because some result differences are acceptable and others are not, and only they can tell which is which. Agree on the time these owners will spend before the work starts.
Can an in-house ClickHouse team run the compatibility test cycle alone?
Many in-house teams can, provided they have a pre-production cluster, representative data, a query inventory and named application owners. The harder parts are collecting queries from every application, comparing results across versions in a repeatable way, and deciding which changed defaults matter for each workload. Teams that have not worked through an upgrade with a pinned setting before often underestimate the number of applications involved. A short internal pilot on one application shows how much effort the full cycle will need before anyone commits to a plan.
What drives the cost and effort of testing with a compatibility setting?
Cost follows the number of applications, the number of distinct queries, the volume of test data, the number of environments, and how many owners must review results. Internal staff time is usually the largest share, while an external partner is paid for the work in the agreed scope. Most of the cost is one-time, but each new ClickHouse release brings another pass, so removing pins promptly reduces repeat effort. Ask any quote to list the applications and query set in scope, the deliverables, what is excluded (such as fixing application code), the acceptance criteria, and what access the partner will need.
Does the compatibility setting affect ClickHouse replication or Keeper?
The setting is aimed at query behavior, so it does not change how ReplicatedMergeTree tables replicate or how ClickHouse Keeper stores coordination state. Replication and Keeper are still affected by the upgrade itself, because during a rolling upgrade replicas and Keeper nodes can run different versions for a period. That risk is separate from the pin and needs its own checks. Confirm the replication behavior for your target version in the release notes and in the replication documentation before you plan the rollout order.
How do you find which ClickHouse defaults changed between versions?
Read the release notes and changelog for every version between your current release and the target, since the ClickHouse production FAQ recommends reading the changelog before upgrading because minor backward-incompatible changes can occur. Look for entries that mention settings, defaults, query analysis, or behavior changes. Then compare the settings each version reports on a test server, so the list comes from the software rather than from memory. Cross-check the list against the query inventory, because a changed default only matters where your queries use it. The ClickHouse GitHub releases page lists each version's published notes.
Can DeployOpen run the ClickHouse compatibility test cycle under a fixed scope?
Yes. DeployOpen plans, implements and supports self-hosted open-source platforms inside your own infrastructure, and most engagements are fixed-scope, with scope, deliverables and timelines agreed in writing before work starts. For this topic, a written scope can cover the query inventory, the pre-production test runs, a report of result differences by application, and a plan for removing each pin. Application code changes, data access and the owners who approve results should be named in that scope. DeployOpen does not issue audit reports or certifications, so any compliance evidence still comes from your independent auditor.
How DeployOpen scopes and delivers a ClickHouse upgrade
DeployOpen plans and runs self-managed ClickHouse upgrades inside your own cloud accounts, data centers or restricted networks, and a compatibility-setting upgrade is scoped the same way as any other. The work starts from a written fixed scope that names the clusters, the target release, the deliverables and the timeline, all agreed before any change is made. Where you want help after the upgrade lands, including removing pinned settings on a schedule, ongoing support can run as a retainer under a separate written agreement.
The scope usually starts with an inventory of every ClickHouse server, the target version, and every application, dashboard, scheduled job and ingestion pipeline that connects to them. From that inventory we build the pinned-setting test plan: which default settings are held at the earlier version through the compatibility setting, which queries and users each pin affects, and how each application is tested against the new release with and without the pin. The plan also includes a removal plan with a named owner, the conditions that must be met before each pin is dropped, and the order in which settings are retired. Pre-production testing runs on representative data and queries, and the documentation covers the test results, the change record and the post-upgrade checks. Confirm with the vendor which settings a given release lists as changed before you accept a scope that assumes them.
DeployOpen prepares the systems, test records, configuration evidence and change documentation that your assessors or internal audit will ask for, but we do not issue audit reports, attestations or certifications. Independent auditors and certification bodies make those determinations. When you request a quote, ask for the inventory method, the list of environments tested, the criteria for removing each pin, who signs off on the removal plan, and what is handed over at the end. Treat the ClickHouse production FAQ as the baseline for the pre-production environment the scope must include.
