When to trade write cost for scan stability — choosing Delete_Insert mode for update-heavy columnar tables
Columnar tables are a proven win for bulk scans and analytical aggregation. But the moment a workload involves frequent updates, query performance tends to drift downward as incremental data piles up. OceanBase’s Delete_Insert table mode tackles this by splitting every update into two complete rows at write time, one that deletes the old version and one that inserts the new. The query path no longer has to merge row by row, so scan efficiency stays steady even under continuous update load. This article starts from the root cause, then walks through the design, how to use it, measured performance, and where it fits.
Why Columnar Queries Slow Down Over Time
Many teams go through a similar arc after moving analytical queries from row store to column store. Right after the switch, bulk scans and aggregation queries get dramatically faster, and everyone is happy. But as upstream data keeps flowing in, especially once the workload involves frequent status changes, such as order states, inventory counts, or backfilled user tags, queries that used to return in seconds gradually stretch to tens of seconds. The more updates there are, the slower queries get, and the degradation is persistent. It does not recover on its own.
To understand the root cause, it helps to look at how an ordinary columnar table handles updates. The columnar storage format is naturally suited to large sequential scans, but it is not built for modifying a single row in place. To keep writes efficient, ordinary columnar tables typically use a read-time merge strategy: the baseline data already written to disk and organized by column stays untouched, while every new update and delete is first written into a separate incremental structure. When a query arrives, the storage engine has to compare and merge the baseline and incremental data row by row on the primary key, reconstruct the latest version of each row, and only then hand the result up for filtering and aggregation.
The cost of this mechanism shows up on the query path. When primary keys in the incremental data overlap with those in the baseline, a process that could otherwise scan block after block sequentially has to stop and check, row by row, whether each row was updated or deleted and which version to keep. The very strengths of columnar storage, block-level filtering, columnar computation, and vectorized execution, get repeatedly interrupted because the engine has to wait for multiple data sources to be merged before it can continue. The more incremental data there is, the more often these interruptions happen, and the more noticeable the slowdown becomes.
How the Industry Approaches the Problem
There are a few broad schools of thought for resolving the tension between columnar storage and frequent updates.
One approach sticks with read-time merge. ClickHouse’s MergeTree engine is append-oriented; updates and deletes are deduplicated through background merges or by the FINAL keyword at query time, which still means handling multi-version data while reading. TiFlash uses a similar two-layer LSM structure (Delta plus Stable), with incremental data eventually folded into the Stable layer through full merges. Both perform well when data is mostly appended, but once high-frequency updates enter the picture, the merge cost on the query path is just as unavoidable.
Another approach is Merge-on-Write. StarRocks primary key tables are a typical example: on write, the primary key index locates the position of the old row, marks it as deleted in a delete bitmap, and appends the new row to a new data file. At query time, the engine simply skips old data using the bitmap, with no row-by-row merge. Hologres does something similar, coordinating three structures: a primary key index, columnar data files, and delete bitmap files. This keeps query cost low, but it requires maintaining a global bitmap structure on the write path and implementing multi-version visibility control for it.
OceanBase takes a middle path. As an HTAP database, OceanBase sorts baseline data by primary key during merges, which means row numbers shift as merges happen. Relying on row numbers to locate a delete bitmap, as StarRocks does, would require rebuilding the mapping after every merge, which adds considerable complexity. So OceanBase chose an implementation that does not depend on a delete bitmap: at write time, each update is split into a delete plus an insert with full column data stored, and at query time each incremental data block filters independently, then skips old versions using delete markers. This avoids maintaining a global bitmap, keeps changes to the existing LSM-Tree architecture minimal, and already covers the performance needs of most real-time update scenarios.
Getting to Know the Delete_Insert Table
The core idea of the Delete_Insert table is simple: rather than stitching data together at query time, split it apart and store it ready at write time. Specifically, every UPDATE is rewritten as two actions, delete the old row and insert the new row. Both rows land in storage in full, all-column form, and a delete marker distinguishes which version is valid. As a result, every data block is self-contained at query time. Filter conditions can be pushed directly down to each block and evaluated independently, with no need to merge against baseline data first; at the end, the engine only has to skip old versions based on the delete markers. In other words, Delete_Insert turns read-time merge into write-time split, trading a bit more write cost for large, continuous scans on the query path.
Enabling a Delete_Insert table is lightweight. You specify it through the merge_engine table attribute at table creation, with no extra database-level or tenant-level switch required. It is supported only for columnar tables, and the attribute cannot be changed after the table is created, so it needs to be chosen deliberately at design time.
The most common use is to enable Delete_Insert on a pure columnar order table:
CREATE TABLE orders (
order_id BIGINT NOT NULL,
user_id BIGINT NOT NULL,
status VARCHAR(16) NOT NULL,
amount DECIMAL(18, 2),
updated_at TIMESTAMP,
PRIMARY KEY (order_id)
) merge_engine = delete_insert
WITH COLUMN GROUP (each column);
If the upper layer needs both columnar storage for analysis and row storage for point lookups, you can create the table with redundant columnar and row storage, maintaining both organizations in the same table:
CREATE TABLE orders (
order_id BIGINT NOT NULL,
user_id BIGINT NOT NULL,
status VARCHAR(16) NOT NULL,
amount DECIMAL(18, 2),
updated_at TIMESTAMP,
PRIMARY KEY (order_id)
) merge_engine = delete_insert
WITH COLUMN GROUP (all columns, each column);
If you do not specify merge_engine, it defaults to partial_update, which follows the traditional read-time merge mode. Only columnar tables that explicitly declare merge_engine = delete_insert will persist updates as delete the old row plus insert the new row.
How Storage and Queries Differ
The difference between Delete_Insert and an ordinary columnar table comes down to two paths: the write side and the query side. Because the cost lands in different places, the two suit different scenarios.
The write-side difference is about how much gets written. On UPDATE, an ordinary columnar table records only the modified columns plus the primary key. Unchanged columns are not rewritten, so the incremental data stays small, but each incremental record is incomplete on its own and must rely on baseline data to reconstruct the full row. A Delete_Insert table is different: every UPDATE writes two rows, an old version carrying a delete marker and a new version, both in full-column form. The incremental data is larger, but every row is self-contained and independently readable.
The query-side difference is about how much gets computed. To run a query, an ordinary columnar table must first merge baseline and incremental data row by row on the primary key to determine the latest value of each row, and only then apply the filter conditions to the result. During this process, the block-level filtering and vectorized execution that columnar storage relies on most get frequently interrupted. In a Delete_Insert table, because each incremental data block is complete in itself, filter conditions can be pushed directly down to the block and evaluated independently, with blocks not interfering with one another; at the end, the engine only needs to skip old versions of the same primary key based on delete markers.
The table below lays the key differences side by side:
In short, Delete_Insert shifts part of the cost that the query used to bear forward onto the write side. The payoff is that bulk scans are no longer frequently interrupted, and query time does not keep degrading as updates accumulate. That is precisely what makes it a good fit for real-time analytics.
Performance Gains
To show the gap between the two modes under update load directly, we ran a comparison on a columnar table of 100 million rows. The ordinary columnar table and the Delete_Insert columnar table have identical schemas, differing only in merge_engine. The benchmark query is a full-table scan with a filter:
SELECT * FROM test_column_table WHERE c3 > 99999990;
By controlling the proportion of rows touched by random UPDATE or random INSERT, we observed how the two tables behaved at different incremental scales. All timings below are in milliseconds.
The first scenario is random UPDATE. As the proportion of updated rows rose from 0% to 20%, query time on the ordinary columnar table grew rapidly, while the Delete_Insert table fluctuated only slightly.
With no updates at all, the two are essentially even, which shows that Delete_Insert does not sacrifice baseline scan performance on static data. As soon as 1% of rows are updated, the ordinary columnar table jumps to the 20-second range because of merge overhead, while Delete_Insert stays in the millisecond range. At a 20% update ratio, the ordinary columnar table degrades to the minute range, while the Delete_Insert table only rises from milliseconds to seconds. The gap keeps widening as updates grow, consistent with the expectation that read-time merge overhead grows linearly, or even super-linearly, with incremental scale.
The second scenario is random INSERT. New data does not create primary key conflicts with the baseline, but it still forms incremental data blocks that have to be scanned.
In the INSERT scenario, the Delete_Insert table is almost unaffected by the incremental ratio, because each newly inserted data block is complete in itself and filter conditions can be evaluated directly on the block. The ordinary columnar table still has to process these new data blocks along the primary key path, so its timing keeps climbing as the insert volume grows.
Putting both sets of results together, the conclusion is clear: with zero increments the two modes are even, but once there is more than 1% updated or newly inserted data, the Delete_Insert table’s query time is significantly lower than the ordinary columnar table’s. Under a high update load at the 20% level, the gap reaches two to three orders of magnitude. Put another way, Delete_Insert keeps columnar query performance from continuously degrading as write volume accumulates.
When to Use a Delete_Insert Table
Once you understand the mechanism and performance characteristics, how do you decide in practice? Three kinds of scenarios are especially well suited to Delete_Insert tables.
The first is the real-time data warehouse. The upstream continuously syncs changes from business databases through CDC or Kafka, while the downstream needs to run analytical queries at any time, with both data freshness and query response guaranteed. Ordinary columnar storage easily falls into query degradation under this write-while-read load, whereas Delete_Insert moves the cost forward to the write path, which matches this pattern well.
The second is HTAP mixed workloads. The same table has to support updates from online business while also serving BI reports and ad hoc queries. Ordinary columnar storage struggles to do both, and row storage is not suited to large-scale scans. A Delete_Insert columnar table paired with redundant row storage lets point lookups go through row storage and analytics go through columnar storage, without the two interfering.
The third is high-frequency status update workloads. Fields such as order status, device status, and user tags get repeatedly backfilled and overwritten. An ordinary columnar table faces a large amount of incremental data with overlapping primary keys, where merge overhead is heaviest; Delete_Insert avoids exactly this by writing two full-column rows and filtering by block at query time.
Conversely, if a table is essentially read-only after writing with almost no updates, or is small in scale with simple queries, then the extra storage and write cost of enabling Delete_Insert is not worth it, and an ordinary columnar table is the better choice.
There is a simple rule of thumb for deciding: if a columnar table sees continuous UPDATE or DELETE and its query performance shows observable degradation over time, it is worth trying the Delete_Insert table mode. Sometimes performance tuning does not require re-architecting anything; choosing the right storage mode is enough to solve the problem.
Top comments (0)