ClickHouse® 26.3 introduces a new MergeTree table setting called table_readonly. When enabled, the table rejects write operations while continuing to serve read queries.

Although the feature is small, it solves a common operational problem: protecting tables that should no longer change after they have been finalized or archived.

This article explains:

• What table_readonly is
• How it works
• What it does and does not protect
• Practical production use cases
• Limitations to be aware of

All examples in this article apply to ClickHouse 26.3 or later.

What is table_readonly?

table_readonly is a MergeTree table setting introduced in ClickHouse 26.3.

When enabled,

• INSERT operations are rejected.
• Other write operations that would modify the table are blocked.
• SELECT queries continue to work normally.

A simple example:

CREATE TABLE events
(
    id UInt64,
    event_time DateTime,
    message String
)
ENGINE = MergeTree
ORDER BY id
SETTINGS table_readonly = 1;

Trying to insert data results in an error similar to:

Exception: Table is in readonly mode.

The setting can also be enabled on an existing table.

ALTER TABLE events
MODIFY SETTING table_readonly = 1;

The table immediately becomes read-only without changing its schema or stored data.

Why was this feature added?

Many analytical datasets eventually become immutable.

Examples include:

• Historical event logs
• Archived customer activity
• Monthly financial snapshots
• Completed ETL outputs
• Regulatory datasets

Once these datasets are finalized, additional writes are usually mistakes rather than expected operations.

Before ClickHouse 26.3, preventing accidental writes typically required permission management, application logic, or operational procedures.

table_readonly moves this protection to the table itself.

What happens internally?

One interesting detail is that read-only MergeTree tables do not run background threads.

Normally, MergeTree tables perform background tasks such as merges and maintenance.

For tables marked as read-only, these background activities stop because no new data can be inserted that would require further maintenance.

This makes the feature useful for archived datasets that are expected to remain unchanged.

Is this a security feature?

Partially-but not in the way many people think.

It is better described as write protection, not a replacement for database security.

table_readonly protects a table against modification.

It does not:

• authenticate users
• replace RBAC
• replace user permissions
• control who can read the table
• encrypt data

Instead, it adds another layer of protection by ensuring the table itself refuses modifications, regardless of whether an application accidentally issues an INSERT or another write operation against it.

Think of it as similar to marking a document as read-only in a file system-it helps prevent changes but is not a complete security model.

Practical Use Cases

1. Archiving Historical Data

Many organizations archive old partitions or yearly datasets after they become inactive.

For example:

events_2022
events_2023
events_2024

Once 2024 data has been finalized, the table can be marked read-only.

This guarantees that historical records remain unchanged.

2. Financial Reporting

Monthly or quarterly reports often depend on data that has already been approved.

Changing historical values after reporting can create inconsistencies.

Marking reporting tables as read-only ensures the published dataset remains stable.

3. Compliance and Audit Data

Audit logs should generally be append-only during collection and immutable once finalized.

After an audit period closes, enabling table_readonly helps prevent accidental modifications.

4. Protecting Static Reference Datasets

Some lookup tables change very rarely.

Examples include:

• country codes
• product catalogs for a released version
• archived configuration snapshots

Making these tables read-only reduces the risk of unintended updates.

5. Preventing Operational Mistakes

Operational errors happen.

Examples include:

• ETL jobs writing to the wrong table
• incorrect deployment scripts
• accidental manual inserts

Instead of silently accepting bad writes, ClickHouse rejects them immediately.

Example Workflow

Imagine a yearly analytics pipeline.

Raw Events
      ↓
Daily Aggregation
      ↓
Monthly Aggregation
      ↓
Yearly Archive

After the yearly archive is validated:

ALTER TABLE yearly_sales
MODIFY SETTING table_readonly = 1;

From that point onward:

• Analysts can continue querying the table.
• Dashboards continue working.
• Accidental writes are rejected.

Hands-On Example

Let's see how the feature works in practice.

Step 1: Create a MergeTree Table

CREATE TABLE yearly_sales
(
    order_id UInt64,
    sale_date Date,
    amount Decimal(10,2)
)
ENGINE = MergeTree
ORDER BY order_id;

Step 2: Insert Some Data

INSERT INTO yearly_sales VALUES
(1, '2025-01-10', 1200.50),
(2, '2025-02-14', 875.00),
(3, '2025-03-05', 2400.75);

Verify the data:

SELECT *
FROM yearly_sales;

Result:

order_id   sale_date	amount
1	     2025-01-10	        1200.50
2	     2025-02-14	        875.00
3	     2025-03-05	        2400.75

Step 3: Make the Table Read-Only

ALTER TABLE yearly_sales
MODIFY SETTING table_readonly = 1;

The table is now in read-only mode.

Step 4: Read Queries Continue to Work

SELECT
    count(*) AS total_orders,
    sum(amount) AS total_sales
FROM yearly_sales;

Output:

total_orders	total_sales
3	                4476.25

Nothing changes for users who only need to query the data.

Step 5: Try Writing New Data

Now attempt another insert:

INSERT INTO yearly_sales
VALUES (4, '2025-04-01', 950.00);

ClickHouse® rejects the operation because the table has been marked as read-only.

The exact error message may vary between versions, but the operation fails because write operations are no longer allowed on the table.

Step 6: Make the Table Writable Again

If you need to update the table later, disable the setting.

ALTER TABLE yearly_sales
MODIFY SETTING table_readonly = 0;

After this change, write operations are accepted again.

Limitations

table_readonly is intentionally simple.

It should not be confused with broader access-control features.

Keep in mind:

• It is a MergeTree table setting.
• It does not replace RBAC.
• It does not determine who can query a table.
• It should be used together with proper user permissions and operational practices for production environments.

Should You Use It?

If your workload contains datasets that become immutable after a certain point, the answer is generally yes.

Typical candidates include:

• archived datasets
• completed reporting tables
• historical analytics
• compliance records
• long-term snapshots

For continuously ingested tables, enabling table_readonly would prevent normal write operations and is therefore not appropriate.

Conclusion

table_readonly is not one of the largest features in ClickHouse 26.3, but it addresses a practical operational need.

By allowing MergeTree tables to reject writes while continuing to serve queries, it provides a straightforward way to protect finalized datasets. An additional benefit is that read-only MergeTree tables no longer run background threads, making the feature particularly suitable for archived or sealed tables.

Used alongside ClickHouse's existing authentication and authorization features, table_readonly helps reduce the risk of accidental data modification without changing how applications query the data.

References

clickhouse-docs