Parallel inserts

When handling large volumes of insert operations, processing them sequentially can severely limit throughput. If your client sends insert statements one at a time, waiting for a response before issuing the next, performance quickly becomes constrained by round-trip latency.

For example, with 5 ms of network latency, the theoretical maximum is only 200 inserts per second—regardless of how fast CrateDB can handle each insert internally.

Solution: Run Inserts in Parallel

Instead of sending inserts sequentially, issue them concurrently. This means sending each insert as soon as it's ready—without waiting for a response from the previous one.

This asynchronous approach allows you to:

• Eliminate artificial latency bottlenecks

• Fully utilize CrateDB’s internal concurrency and distributed architecture

• Dramatically increase throughput

Consider Bulk Inserts First

Before implementing parallel inserts, evaluate whether bulk operations are a better fit.

• Bulk inserts are more efficient when you have access to batches of data

• Parallel inserts are more suitable for high-throughput streaming use cases where rows arrive individually

Example: Parallel Inserts in Java with JDBC

The example below demonstrates how to perform asynchronous inserts using CompletableFuture in Java, along with a connection pool via HikariCP.

JDBC connections are not thread-safe. Always use one connection per thread or task.

Setup

HikariDataSource ds = new HikariDataSource();
ds.setJdbcUrl("crate://localhost:5432/doc?user=crate");

Issue Concurrent Inserts

List<CompletableFuture<Integer>> futures = new ArrayList<>();

IntStream.range(0, 1000).forEach(i -> {
    CompletableFuture<Integer> insertFuture = CompletableFuture.supplyAsync(() -> {
        try (Connection conn = ds.getConnection()) {
            PreparedStatement stmt = conn.prepareStatement(
                "INSERT INTO t1 (id) VALUES (?)"
            );
            stmt.setInt(1, i);
            return stmt.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    });

    futures.add(insertFuture);
});

Wait for Completion and Aggregate Results

CompletableFuture<Integer> rowCountFuture = CompletableFuture
    .allOf(futures.toArray(new CompletableFuture[0]))
    .thenApply(ignored -> futures.stream()
        .mapToInt(CompletableFuture::join)
        .sum()
    );

int rowCount = rowCountFuture.get(); // total rows inserted

This uses the default ForkJoinPool.commonPool() for parallelism. You can also provide a custom Executor to supplyAsync() for better control over thread usage.

Performance Testing

To measure the impact of parallel inserts on your setup:

1. Configure your environment: Set up a test table and monitoring tools

2. Run tests with varying levels of concurrency: e.g., 10, 50, 100 parallel tasks

3. Record throughput: Measure inserts per second

4. Plot the results: Identify the concurrency level that gives you the best performance

5. Test different node and shard configurations if needed

Over time, this testing will give you a clearer understanding of your cluster’s insert capacity under different loads.

Tips & Considerations

• Use a connection pool to avoid creating too many connections (e.g., HikariCP, Vibur, Apache DBCP)

• Each thread or task must use a dedicated connection

• If your use case involves batches of data, combine parallelism with bulk operations for maximum throughput

• Don’t over-parallelize on a small cluster—you may saturate CPU, memory, or disk