BATCH UPDATE/DELETE operations

[dahbka-lis]

Information

BATCH UPDATE and BATCH DELETE FROM operations allow to update/delete records in large tables while minimizing the risk of lock invalidation and transaction rollback by weakening guarantees.

Documentation

v25.1.2

• BATCH UPDATE
• BATCH DELETE FROM

How to enable and configure

// cluster.yaml

table_service_config:
  // Required flags
  enable_oltp_sink: true
  enable_batch_updates: true

  // Optional
  batch_operation_settings:
    max_batch_size: 10000
    min_batch_size: 1
    start_retry_delay_ms: 50
    max_retry_delay_ms: 30000
    partition_execution_limit: 10

Tasks

v25.1.2

• implement batch update & delete operations #15695
• [KQP] Write a doc for BATCH operations #17161
• Implement stress and compatibility tests for BATCH operations #18672
• KqpTableSinkSettings.IsBatch() == false for the BATCH UPDATE query #19394
• [KQP] Improve BATCH operations by custom pruning table partitions #19734
• [KQP] Unexpected crash with BATCH operations from stress tests #19775
• [KQP] Write a doc for NoTx transaction mode with SDK and CLI examples #21065 + link it to BATCH docs

v25.2

• [KQP] PartitionPruner for BATCH queries to stable-25-1 #21075
• Flaky BATCH DELETE large tests, fix locks invalidation #23716

v25.3.1

• Some bugs with BATCH operations after enabling, /ru-prestable #28865
• Set sequential scan for BATCH operations #28934

Future works

• [KQP] Support OLAP executing for BATCH operations #21196

Development

v25.1.2

• BATCH UPDATE/DELETE queries for per row updates with NoTx #13340
• Fixes + new features for BATCH UPDATE/DELETE #14994
• Add target batch_operations to kqp/ut/ya.make #17203
• Add size and rows limits for batch operations large tests #17270
• Add docs for batch operations #17283
• 25-1: Fixes for BATCH UPDATE/DELETE operations #18135
• Disable OLAP support for BATCH operations, add tests for ON and OLAP #18293
• Impl compatibility tests for BATCH operations #18969
• feat: add batch update+delete ops to simple queue #19419
• Fixes for BATCH operations for tables with index and empty result rows #19463
• [KQP] Fix nullptr errors with BATCH operations and not created tables #19766

v25.2

• [KQP] Add configuration for the custom partition pruning for DataExecuter and PartitionedExecuter #19919
• [KQP] Compatibility changes for BATCH operations #21204
• [KQP] Fix flaky tests for BATCH operations with gvit's single partitioning optimizations #23758

v25.3

• Remove bytes counters, fix issues returning and logs for BATCH operations #28853
• Sequential reads for BATCH queries #28906

[dahbka-lis]

Test Plan for BATCH operations

Setup

• Unit tests for BATCH UPDATE and BATCH DELETE.
• Compatibility tests.
• Stress tests with simple_queue.
• A cluster of 8 nodes with TPC-H data at a scale of 100.

Unit Tests

Correct Situation

To verify the correct execution of BATCH UPDATE/DELETE queries, the following situations have been prepared:

• Executing a query on row tables with 1 partition.
• Executing a query on row tables with a large number of partitions (from 2 to 100).
• Executing a query on large row tables (8 partitions, 100,000 rows each).
• Executing a query on row tables with NULL primary keys.
• Executing a query on row tables with index.
• Each test is run several times, changing the number of rows processed per transaction (from 1 to 100_000, depending on the test) and the number of partitions processed at a time (from 1 to 128, depending on the test).

Incorrect Situation

To check the correct return of an error when executing BATCH UPDATE/DELETE queries if the required conditions are not met, the following situations have been prepared:

• Non-idempotent update (only for BATCH UPDATE).
• The query consists of multiple queries or has subqueries (maybe it's non-idempotent).
• The query includes ON (for UPDATE ON or DELETE FROM ... ON cases, maybe it's non-idempotent).
• The query includes RETURNING (it's impossible to support for big tables).
• The query modifies a column table (only for row tables).
• Execution in transaction mode (only for implicit tx mode).
• The query edits a table that was not created or unknown columns.

For the above situations, a query execution error is expected, which is implemented in the tests.

Cluster

Manual testing of queries was conducted on a cluster of 8 nodes with TPC-H data at a scale of 1-100 and with EnableOltpSink and EnableBatchUpdates flags enabled. The following cases were checked:

• Correct execution of a query on large row tables with one or more partitions (more than 60 million rows).
• Correct execution of a query on large row tables with a large number of partitions (more than 600 million rows, more than 100 partitions).
• Correct execution of a query with parallel writing to the same row table by another user (for this, a script was written in Go, which performed more than 100 UPSERT and BulkUpsert queries per second).
• Correct execution of a query on large row tables with partition splitting during its execution.
• Correct execution of a query with parallel BATCH queries.

Since the query is executed synchronously, completion with a successful result was expected for each case. Ultimately, each check showed a positive result, and all table rows were modified or deleted according to the semantics of BATCH UPDATE/DELETE.