How to Load Data in Bulk — Keeping Writes Fast, Transactions Safe, and Analytics Instant
In scenarios such as e-commerce flash sales, financial risk control, and telecom network big data, businesses now expect data freshness measured not in hours or minutes but in seconds or even less. The value of data for decision-making decays quickly once it is delayed — an inventory update that arrives a minute late can lead to overselling, and a risk-control signal that lags by a few seconds may only fire after a fraudulent transaction has already gone through. Whether data can join analytical queries the moment it is written directly determines how timely and accurate business decisions can be.
This places demanding requirements on a data platform’s ingestion capabilities: it must not only write large batches of data into the system quickly, but also make that data queryable immediately after the write completes, all without disrupting the online workloads already running. Traditional approaches tend to trade one of these dimensions off against another. This article looks at OceanBase’s direct load mechanism and how it balances high-throughput writes, strong transactional guarantees, and coexistence of multiple workloads.
Ingestion Challenges in Real-Time Analytics
To serve both writes and analytics, many teams adopt multi-system architectures like Lambda or Kappa, where data has to travel through components such as Kafka, Flink, Hudi, Hive, and ClickHouse, with each stage involving ingestion, synchronization, transformation, and governance. The longer the pipeline, the more latency accumulates, and the harder it becomes to keep data consistent across systems — making write-then-analyze difficult to achieve in practice. Traditional batch-oriented ETL adds T+1 latency on top of this, so analytical results inherently lag behind the events they describe.
Turning to ingestion itself, traditional ETL and standard SQL write paths have a clear throughput bottleneck. Every write has to go through SQL-layer parsing, transaction lock contention, and the synchronization cost of Redo log. When the data volume reaches millions or tens of millions of rows, these accumulated per-row costs substantially slow down ingestion and fall short of the write-then-analyze goal.
In HTAP mixed-workload environments, the problem grows more complex. On one hand, large-batch ingestion must not block the online transactions already running — TP and AP workloads need to coexist. On the other hand, data consistency cannot be compromised: written data must satisfy ACID guarantees and become visible to analytical queries immediately after commit.
Finding the balance between high-throughput ingestion and online transactions is a question every real-time analytics platform has to answer.
The Core Mechanism of Direct Load
To understand what direct load optimizes, it helps to split a single ingestion into two stages:
Stage one: data reaches the storage layer. Data goes through SQL parsing, execution plan generation by the optimizer, and reading and scheduling by the executor before finally arriving at the storage layer.
Stage two: data is written into the storage layer. Data is actually persisted at the storage layer. The standard path writes data into the MemTable, whereas direct load builds SSTables directly.
The core optimization of direct load lives in the second stage: it bypasses the MemTable, sorts data by primary key, and builds SSTables directly to insert into the storage layer. This is the most fundamental difference from the standard write path.
The Write Path
The overall write path can be summarized as:
Raw data → type conversion → sort by primary key → build SSTable directly → insert into storage layer
Compared with the standard path of writing into the MemTable row by row, building SSTables directly offers the following advantages:
Substantially lower lock overhead: a table lock is taken only once at the start of ingestion; there is no row-lock contention during the write, and multiple threads build in parallel without interfering with one another.
More efficient conflict detection: tables without a primary key can skip conflict detection entirely, while tables with a primary key scan the existing data at most once.
Less log volume: instead of logging each row individually, the encoded macroblock data is written to the log as a whole.
No stalls from a full MemTable: because the MemTable is not involved, there is no scenario where a full MemTable must be frozen and flushed, and no risk of writes stalling because background compaction cannot keep up.
Data is persisted directly in the storage engine’s final format (SSTable), skipping the multi-round compaction chain from MemTable to Mini SSTable to Major SSTable.
Full Load and Incremental Load
Depending on where data is written, direct load comes in two forms: full direct load and incremental direct load. The difference lies in which layer of the LSM-Tree the data ultimately lands in.
Full direct load: sorts the incoming data by primary key, merges it with existing data, and writes it directly into the baseline Major SSTable. It suits empty tables or tables with a small data volume.
Incremental direct load: sorts the incoming data by primary key and writes it directly into the Mini SSTable of the incremental layer. It suits tables that already hold a large data volume, avoiding a full rewrite of the baseline.
Both approaches bypass the layered structure of the LSM-Tree — such as the complex BTree structure within the MemTable — and instead generate SSTable files directly through efficient sorting, significantly improving the speed of bulk data writes.
Integration with the LSM-Tree Storage Engine
OceanBase’s storage engine is built on an LSM-Tree architecture, in which data is naturally divided into two layers:
Incremental data: newly written user data lands quickly in the MemTable in row-store form, and is later flushed into Mini SSTable, Minor SSTable, and so on. Incremental data is maintained independently by each replica and contains all multi-version information.
Baseline data: generated as Major SSTables through periodic or adaptive Major Compaction. Under the same version number, the baseline data across all replicas is physically consistent, and in AP scenarios it can be configured in column-store mode to serve analytical queries directly.
Baseline compaction here uses a daily-compaction mechanism: a tenant periodically, or based on user action, selects a global version number, and all replicas complete a round of Major Compaction at that version number, ultimately producing baseline data at the same version. Built on distributed multi-replica foundations, the baseline data across all replicas is physically consistent under the same version number, providing a stable and reliable read foundation for analytical queries.
Both direct load paths fit naturally into this incremental-baseline two-layer structure. Full load lands directly in the column-store baseline Major SSTable, while incremental load first lands in the Mini SSTable of the incremental layer and is later incorporated into the column-store baseline through the normal compaction flow. Whichever path is taken, the entire process requires no extra data movement or format-conversion pipeline.
After Ingestion: Column Store and the Vectorized Engine Accelerate Analytics
Direct load solves the throughput problem of getting data into the database; once the data enters the column-store baseline, analytical query performance is further improved by the vectorized execution engine. In column-store mode, each column is stored as a separate SSTable, and the SSTables of all columns combine into a virtual SSTable that serves as the column-store baseline data. This layout lets analytical queries read only the columns involved, reducing scans of irrelevant data.
Building on this, the vectorized engine processes data in batches rather than iterating row by row, significantly reducing iteration overhead, using the CPU cache more efficiently, and parallelizing computation with SIMD instructions. In public benchmarks, the OceanBase vectorized engine, compared with the previous version, improves ClickBench by about 14x, TPC-H 100G by about 40x, and TPC-DS 100G by about 26.5x. Together, direct load, the column-store baseline, and vectorized queries form a coherent path from write to analysis.
Full Database Feature Support
While improving write throughput, direct load does not sacrifice existing database functionality. Indexes, constraints, partitions, transactions, and a wide range of data types are all supported during ingestion.
Indexes and constraints
Primary keys, global indexes, and local indexes are built automatically and in parallel during ingestion based on the PX framework.
Uniqueness and not-null constraints are validated in real time during ingestion.
Conflict handling supports strategies such as IGNORE and REPLACE.
Partitioned table support
Supports partitioning strategies such as Range, Hash, and List.
Compatible with multi-level partitioning.
Partition-level parallel ingestion, leveraging the strengths of the distributed architecture.
Supports specifying partitions to import, loading only specific partitions.
Transactional consistency
Data is not externally visible during ingestion.
Automatic rollback on ingestion failure.
Data type support
All standard SQL data types.
LOB large objects (CLOB/BLOB).
Extended types such as JSON and GIS (continually being improved).
Data Sources and Formats
In real-world business, the data to be ingested often comes from a variety of heterogeneous systems. Combined with its external table capability, OceanBase direct load supports efficient ingestion directly from many data sources:
File formats: common analytical data formats such as CSV, JSON, Parquet, and ORC.
Storage sources: local disk, OSS, S3, HDFS, NFS, COS, OBS, ODPS, and more.
Catalog integration: Hive Metastore, ODPS Catalog, and the Iceberg table format.
Character sets: multiple character sets such as UTF-8 and GBK.
Compression formats: gzip, snappy, lz4, zstd, and others.
Data can be read directly from external storage systems and written into OceanBase via direct load, eliminating the multi-step chain of staging, cleansing, and reloading found in traditional approaches.
Ingestion Methods
Direct load offers several methods, so DBAs, data engineers, and application developers can each choose the one they prefer.
SQL support is native and compatible with MySQL’s LOAD DATA syntax, so a single command does the job:
-- Import from a local file
LOAD DATA /* direct(true, 0) parallel(32) */ INFILE 'orders.csv' INTO TABLE orders;
-- Import from remote storage (OSS/S3 supported)
LOAD DATA /* direct(true, 0) parallel(32) */ INFILE 'oss://bucket/path/data.csv' INTO TABLE orders;
INSERT SELECT, combined with internal or external tables, can perform ETL entirely within OceanBase or import directly from external files:
-- ETL with OceanBase INSERT SELECT
INSERT /* direct(true, 0) enable_parallel_dml parallel(32) */ INTO summary_table
SELECT * FROM detail_table
WHERE date >= '2026-01-01';
-- Import data via an external table with OceanBase INSERT SELECT
INSERT /* direct(true, 0) enable_parallel_dml parallel(32) */ INTO detail_table
SELECT * FROM FILES(location = 's3://bucket/path', type = 'parquet', pattern='*');
For scenarios that require loading files in bulk from the command line, you can use the OB Loader tool:
obloader -h 127.0.0.1 -P 2881 \
-u user@tenant#cluster -p password \
--table=logs \
--direct-load \
--file-path=/data/logs/
In addition, in KV scenarios, batch writes through the OceanBase Table API can also take the direct load path. These integration methods work out of the box, with no extra components or middleware required.
High-Concurrency Guarantees: How Direct Load Coexists with Online Workloads
Once transactional correctness is settled, another key question is: how can large-batch ingestion avoid affecting the online workloads already running? OceanBase addresses this challenge through multi-layer resource isolation and an elastic architecture.
A Multi-Layer Resource Isolation System
OceanBase provides multi-level resource isolation, from coarse-grained to fine-grained:
Isolation across tenants: ingestion tasks for different business lines run in different tenants, with independent resource quotas and no mutual interference.
Isolation between foreground and background tasks: direct load can run as a background task, with the system setting upper limits on its CPU and I/O priority so that the response time of foreground online transactions is not affected.
User-level and SQL-level isolation: through the Resource Group mechanism, administrators can set resource quotas for specific users or specific types of SQL, preventing a single large ingestion task from crowding out the compute resources of other workloads.
Strong Isolation Between TP and AP Replicas
In mixed-workload scenarios, OceanBase supports routing ingestion traffic and analytical queries to different replicas:
Write replicas take on the data write traffic of direct load.
Read replicas (read-only replicas) take on concurrent analytical query requests.
The two workloads are isolated at the physical level; what they share is the same strongly consistent copy of the data, not the same compute resources.
Elastic Scaling and Automatic Load Balancing
To handle ingestion peaks — such as data warm-up before a flash sale or end-of-day batch loading — OceanBase offers flexible scaling options:
Both integrated and disaggregated storage-compute forms: enterprises can choose the architecture that fits their deployment environment. In the storage-compute disaggregated mode, compute nodes can scale independently without affecting data storage.
Fast scaling in and out: within a single Zone, compute nodes can be added or removed in minutes to handle traffic spikes.
Built-in dynamic data balancing: after scaling out, the system automatically redistributes the data load across nodes, avoiding hot shards or bottleneck nodes.
Summary
Direct load, strong transactional guarantees, and resource isolation work together to form a complete capability loop for OceanBase in real-time analytics scenarios:
Direct load solves the throughput problem of loading large batches of data at high speed, and adapts to different data scales through its full and incremental paths.
Full database feature support keeps indexes, constraints, partitions, and a variety of data types usable even during high-speed ingestion.
Diverse integration methods cover native SQL syntax, OB Loader, and the Table API, letting different roles integrate conveniently.
Multi-Paxos + MVCC + WAL ensure the consistency and durability of ingested data, so transactional semantics are not sacrificed in the pursuit of speed.
Multi-layer resource isolation and replica routing ensure that ingestion does not disrupt the normal operation of online workloads.
From write to query, this set of capabilities forms a clear division of labor: direct load handles high-speed ingestion, the column-store baseline and vectorized engine accelerate queries, materialized views handle precomputation, and resource isolation ensures that multiple workloads coexist in the same cluster. Working together, they let enterprises perform high-throughput writes and real-time analytical queries within a single system, without maintaining multiple technology stacks and data-synchronization pipelines. Data becomes queryable the moment it enters the system, achieving a smooth transition from write to analysis.
Top comments (0)