Understanding Database Insert Operations and Batch Insertion Strategies

In the era of the Internet, every user action generates data that must be stored efficiently, typically in a database. Depending on the use case, relational databases like MySQL or NoSQL stores such as Redis are chosen, and the challenge becomes how to insert large volumes of data without degrading performance.

An interview dialogue is used to illustrate why developers should understand the rationale behind batch‑insertion sizes rather than merely stating that "batch insert works".

Fundamentals of Database Insert Operations

When data is written, it is first placed in an in‑memory cache and later flushed to disk by background threads. This approach reduces the latency gap between RAM and disk, lowers I/O cost, and enables write merging.

Databases also employ a Write‑Ahead Log (WAL) so that changes are recorded in a transaction log before the actual data pages are updated, providing crash‑recovery capabilities.

Data is stored in fixed‑size pages (e.g., 4 KB, 8 KB). Understanding page size helps optimize space management and insert performance.

Single‑Row vs. Batch Insertion

Inserting rows one by one incurs a transaction overhead for each operation; batch insertion groups many rows into a single transaction, dramatically reducing overhead but introducing complexities such as validation and error handling.

While batch inserts improve throughput by reducing disk I/O, excessively large batches can lock resources and increase response time, so an optimal batch size must be chosen.

How to Determine an Appropriate Batch Size

Key considerations include:

Disk I/O capacity – avoid saturating the disk.

Memory usage – ensure enough RAM for buffering.

Transaction size – balance between too many small transactions and overly large ones that hold locks.

Lock strategy – minimize contention among concurrent writers.

An estimation example assumes a record with an int (4 B), a varchar average 50 B (max 255 B), a date (3 B), and a float (4 B). Using these figures, the article shows how to calculate the maximum number of records that fit into 8 GB of usable memory and a 512 GB disk.

Practical Application with MyBatis

MyBatis supports batch insertion via the <foreach> tag:

<insert id="insertMultiple" parameterType="list">
    INSERT INTO tableName (column1, column2, ...)
    VALUES
    <foreach collection="list" item="record" separator=",">
        (#{record.column1}, #{record.column2}, ...)
    </foreach>
</insert>

Enabling batch mode with ExecutorType.BATCH accumulates statements until a manual commit:

SqlSession session = sqlSessionFactory.openSession(ExecutorType.BATCH);

Setting an appropriate batchSize prevents Out‑Of‑Memory errors, and committing the session only once after all inserts avoids performance degradation caused by frequent commits.

Conclusion

The article summarizes that understanding the underlying mechanisms of inserts, carefully estimating record size, and tuning hardware‑related parameters enable developers to choose a suitable batch size and apply MyBatis techniques for optimal database write performance.