Optimizing Large IN Queries with Spring AOP and Multithreaded Splitting

In many projects we encounter IN queries with hundreds or thousands of parameters, which can severely degrade PostgreSQL performance and cause slow API responses. To mitigate this, the article proposes splitting the large IN list into smaller batches, executing them in parallel, and merging the results.

First, a simple example of a large IN query is shown:

SELECT * FROM device WHERE id IN (1, 2, 3, 4)

The query is then divided into two smaller queries:

SELECT * FROM device WHERE id IN (1, 2)
SELECT * FROM device WHERE id IN (3, 4)

To avoid repetitive boilerplate, a custom Spring AOP annotation is introduced. By annotating a method with @SplitWorkAnnotation , the framework automatically handles splitting, parallel execution, and result aggregation.

@SplitWorkAnnotation(setThreadPool = LIST_DEVICE_EXECUTOR, splitLimit = 20, splitGroupNum = 10)
public listDeviceDetail(Long projectId, @NeedSplitParam List
deviceId) {
    ...
}

The annotation defines four parameters:

setThreadPool : the thread pool to use (avoid the common pool for heavy tasks).

handlerReturnClass : a class that implements HandleReturn to process merged results (e.g., sum, count, top‑N).

splitLimit : the minimum size that triggers splitting.

splitGroupNum : the number of items per batch.

A marker annotation @NeedSplitParam is placed on the parameter that needs splitting; only one such parameter is supported.

@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.PARAMETER)
public @interface NeedSplitParam {}

The core AOP logic resides in SplitWorkAspect . It intercepts methods annotated with @SplitWorkAnnotation , determines the split parameter, calculates the number of batches, and launches a CompletableFuture for each batch using the specified thread pool.

@Aspect
@Component
@Slf4j
public class SplitWorkAspect {
    @Pointcut("@annotation(com.demo.SplitWorkAnnotation)")
    public void needSplit() {}

    @Around("needSplit()")
    public Object around(ProceedingJoinPoint pjp) throws Throwable {
        // extract annotation and arguments
        // split the large parameter into parts
        // execute each part asynchronously
        // collect futures and merge results via handlerReturnClass
    }
    // helper methods getBatchNum and getPartParam omitted for brevity
}

The helper method getBatchNum computes how many batches are needed based on the size of the split parameter and splitGroupNum . getPartParam extracts the sub‑list or sub‑array for a specific batch.

public Integer getBatchNum(Object needSplitParam1, Integer splitGroupNum) { ... }
public Object getPartParam(Object needSplitParam, Integer splitGroupNum, Integer batch) { ... }

The result‑handling interface HandleReturn defines a single method Object handleReturn(List t) . An example implementation MergeFunction simply concatenates lists:

public class MergeFunction implements HandleReturn {
    @Override
    public Object handleReturn(List results) {
        if (results == null) return null;
        if (results.size() <= 1) return results.get(0);
        List first = (List) results.get(0);
        for (int i = 1; i < results.size(); i++) {
            first.addAll((List) results.get(i));
        }
        return first;
    }
}

By applying the annotation to a service method, developers can achieve significant performance gains for large IN queries without modifying business logic, while retaining flexibility through customizable thread pools and result‑handling strategies.