Day 92 of #100DaysOfClickHouse

Read-Only Tables in ClickHouse® 26.3: How table_readonly Works and When to Use It

ClickHouse® 26.3 introduces a useful new MergeTree table setting called table_readonly, designed to help administrators protect finalized datasets from accidental modification while keeping them fully available for analytical queries. Although it is a relatively small feature compared to some of the larger additions in recent releases, it addresses a common operational challenge faced by many organizations that manage historical, archived, or compliance-sensitive data.

When the table_readonly setting is enabled, ClickHouse® rejects INSERT operations and other write requests that would modify the contents of the table, while continuing to execute SELECT queries normally. Existing applications, dashboards, reports, and analytical workloads can continue accessing the data without interruption, but any attempt to change the table is immediately blocked. This allows organizations to preserve the integrity of finalized datasets without relying solely on application logic or administrative procedures.

The setting can be configured either during table creation or applied later using an ALTER TABLE statement, making it easy to convert an existing MergeTree table into a read-only dataset without changing its schema or migrating any data. Because the configuration takes effect immediately, administrators can quickly protect a table once it has been validated, archived, or published for long-term reporting.

One of the primary motivations behind introducing table_readonly is the growing need to manage immutable datasets. In many analytical environments, data eventually reaches a stage where it should never change again. Historical event logs, archived customer activity, yearly sales summaries, completed ETL outputs, regulatory datasets, audit records, and approved financial reports are all examples of information that should remain unchanged after validation. Before ClickHouse® 26.3, preventing accidental writes typically required carefully managing user permissions, adding safeguards within applications, or relying on operational discipline. The new setting moves this protection directly into the database engine, ensuring that the table itself refuses any modification attempts regardless of where they originate.

Another significant advantage is its impact on MergeTree's background processing. Under normal circumstances, MergeTree tables continuously execute background tasks such as merging data parts and performing maintenance operations to optimize storage. Since a table marked as read-only can no longer receive new data, these background merge threads become unnecessary. ClickHouse® therefore stops running these maintenance operations for read-only tables, reducing resource consumption and making the feature particularly beneficial for archived datasets that are expected to remain unchanged indefinitely.

Although the feature provides strong protection against accidental writes, it should not be confused with a security mechanism. The table_readonly setting does not authenticate users, enforce Role-Based Access Control (RBAC), manage user permissions, encrypt stored data, or determine who can query the table. Those responsibilities remain part of ClickHouse®'s existing security model. Instead, table_readonly serves as an additional operational safeguard that complements authentication and authorization by ensuring that even authorized users or applications cannot accidentally modify a table that has been intentionally locked.

The feature has numerous practical applications in production environments. Organizations managing data warehouses can mark yearly archive tables as read-only once reporting periods have closed. Financial institutions can protect approved monthly and quarterly reports from accidental updates after publication. Compliance and audit datasets can be preserved once collection periods end, helping maintain regulatory consistency. Static reference datasets, such as archived product catalogs, country code mappings, or historical configuration snapshots, can also benefit from being marked as immutable. In addition, table_readonly provides an extra layer of defense against common operational mistakes, such as ETL jobs targeting the wrong table, deployment scripts inserting data into archived datasets, or manual SQL operations modifying production history.

The article demonstrates these capabilities through a complete hands-on example. A MergeTree table is created and populated with sample sales data before being converted into read-only mode using ALTER TABLE ... MODIFY SETTING table_readonly = 1. Once enabled, analytical queries continue to execute exactly as before, allowing dashboards and reports to function normally. However, any attempt to insert additional rows immediately fails because the database now rejects write operations against the protected table. If business requirements change and updates become necessary later, administrators can simply disable the setting by changing it back to 0, instantly restoring normal write behavior without affecting the stored data.

Like any feature, table_readonly has important limitations. It is available only for MergeTree-family tables and should not be considered a replacement for comprehensive access-control mechanisms. Organizations should continue using RBAC, user permissions, network security, and encryption where appropriate, while treating table_readonly as an additional layer of operational protection. Furthermore, it is unsuitable for tables that receive continuous data ingestion, since enabling the setting would intentionally block normal write operations required by those workloads.

Overall, table_readonly is a practical quality-of-life improvement introduced in ClickHouse® 26.3 that simplifies the management of immutable datasets. By allowing MergeTree tables to reject write operations while remaining fully queryable, and by eliminating unnecessary background maintenance for archived tables, it provides administrators with a lightweight yet highly effective mechanism for protecting historical data. Whether used for financial reporting, compliance, long-term analytics, audit records, or archived operational data, table_readonly helps ensure data integrity while reducing the risk of accidental modifications in production environments.