# 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**.

{% hint style="info" %}
JDBC connections are **not thread-safe**. Always use one connection per thread or task.
{% endhint %}

#### Setup

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

#### Issue Concurrent Inserts

```java
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

```java
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
```

{% hint style="info" %}
This uses the default `ForkJoinPool.commonPool()` for parallelism. You can also provide a custom `Executor` to `supplyAsync()` for better control over thread usage.
{% endhint %}

### 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
