Optimizing Large IN Queries in PostgreSQL with Spring AOP and Multithreaded Splitting

In many projects, large IN queries with hundreds or thousands of parameters cause severe performance degradation in PostgreSQL, leading to slow API responses. To mitigate this, the article proposes splitting the IN list, executing the sub‑queries concurrently, and merging the results.

The solution is implemented as a custom Spring AOP annotation @SplitWorkAnnotation. By simply adding this annotation to a method, the developer can enable automatic parallel execution of large IN queries.

Definition of the AOP Annotation

@Target(ElementType.METHOD)
@Retention(RetentionPolicy.RUNTIME)
public @interface SplitWorkAnnotation {
    /** Set the thread pool (avoid using a shared pool) */
    ThreadPoolEnum setThreadPool();
    /** Return value handler class */
    Class<? extends HandleReturn> handlerReturnClass() default MergeFunction.class;
    /** Split when the list size exceeds this limit */
    int splitLimit() default 1000;
    /** Number of elements per split group */
    int splitGroupNum() default 100;
}

A parameter that needs to be split is marked with @NeedSplitParam:

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

Using AOP to Perform Parallel Splitting and Merging

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

    @Around("needSplit()")
    public Object around(ProceedingJoinPoint pjp) throws Throwable {
        Method targetMethod = ((MethodSignature) pjp.getSignature()).getMethod();
        SplitWorkAnnotation anno = targetMethod.getAnnotation(SplitWorkAnnotation.class);
        Object[] args = pjp.getArgs();
        int splitLimit = anno.splitLimit();
        int splitGroupNum = anno.splitGroupNum();
        // locate the parameter annotated with @NeedSplitParam
        int splitIdx = -1;
        for (int i = 0; i < targetMethod.getParameters().length; i++) {
            if (targetMethod.getParameters()[i].isAnnotationPresent(NeedSplitParam.class)) {
                splitIdx = i;
                break;
            }
        }
        if (splitIdx == -1) return pjp.proceed();
        Object needSplitParam = args[splitIdx];
        // only handle arrays, List or Set
        if (!(needSplitParam instanceof Object[]) && !(needSplitParam instanceof List) && !(needSplitParam instanceof Set))
            return pjp.proceed();
        // skip if size is below the split limit
        boolean notMeet = (needSplitParam instanceof Object[] && ((Object[]) needSplitParam).length <= splitLimit)
                || (needSplitParam instanceof List && ((List<?>) needSplitParam).size() <= splitLimit)
                || (needSplitParam instanceof Set && ((Set<?>) needSplitParam).size() <= splitLimit);
        if (notMeet) return pjp.proceed();
        // optional deduplication for List
        if (needSplitParam instanceof List) {
            List<?> list = (List<?>) needSplitParam;
            if (list.size() > 1) needSplitParam = new ArrayList<>(new HashSet<>(list));
        }
        int batchNum = getBatchNum(needSplitParam, splitGroupNum);
        if (batchNum == 1) return pjp.proceed();
        CompletableFuture<?>[] futures = new CompletableFuture[batchNum];
        ThreadPoolEnum threadPool = anno.setThreadPool();
        if (threadPool == null) return pjp.proceed();
        for (int b = 0; b < batchNum; b++) {
            final int curBatch = b;
            final Object paramCopy = needSplitParam;
            futures[b] = CompletableFuture.supplyAsync(() -> {
                Object[] newArgs = Arrays.copyOf(args, args.length);
                newArgs[splitIdx] = getPartParam(paramCopy, splitGroupNum, curBatch);
                try {
                    return pjp.proceed(newArgs);
                } catch (Throwable e) {
                    throw new RuntimeException(e);
                }
            }, threadPool.getThreadPoolExecutor());
        }
        CompletableFuture.allOf(futures).get();
        Class<? extends HandleReturn> handlerCls = anno.handlerReturnClass();
        List<Object> results = new ArrayList<>(futures.length);
        for (CompletableFuture<?> f : futures) results.add(f.get());
        return handlerCls.getDeclaredMethods()[0]
                .invoke(handlerCls.getDeclaredConstructor().newInstance(), results);
    }

    public Integer getBatchNum(Object param, Integer splitGroupNum) {
        if (param instanceof Object[]) {
            Object[] arr = (Object[]) param;
            return (arr.length + splitGroupNum - 1) / splitGroupNum;
        } else if (param instanceof Collection) {
            int size = ((Collection<?>) param).size();
            return (size + splitGroupNum - 1) / splitGroupNum;
        }
        return 1;
    }

    public Object getPartParam(Object param, Integer splitGroupNum, Integer batch)
            throws NoSuchMethodException, InvocationTargetException, InstantiationException, IllegalAccessException {
        if (param instanceof Object[]) {
            Object[] arr = (Object[]) param;
            int end = Math.min((batch + 1) * splitGroupNum, arr.length);
            return Arrays.copyOfRange(arr, batch * splitGroupNum, end);
        } else if (param instanceof List) {
            List<?> list = (List<?>) param;
            int end = Math.min((batch + 1) * splitGroupNum, list.size());
            return list.subList(batch * splitGroupNum, end);
        } else if (param instanceof Set) {
            List<?> list = new ArrayList<>((Set<?>) param);
            int end = Math.min((batch + 1) * splitGroupNum, list.size());
            Set<Object> set = (Set<Object>) param.getClass().getDeclaredConstructor().newInstance();
            set.addAll(list.subList(batch * splitGroupNum, end));
            return set;
        }
        return null;
    }
}

Definition of the Return‑Handling Interface

public interface HandleReturn {
    /** Merge the list of sub‑query results into a final result */
    Object handleReturn(List t);
}

A simple implementation that concatenates list results is provided:

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 annotating a service method with @SplitWorkAnnotation and marking the large IN parameter with @NeedSplitParam, developers can automatically split the query, run each part in a dedicated thread pool, and obtain a merged result without writing repetitive boilerplate code.

The article also lists suitable and unsuitable scenarios: it works best for bulk IN queries where the result can be simply merged (e.g., SUM, COUNT, TOP‑N after merging). It is not appropriate for paginated queries or cases that do not satisfy the additive property.

Finally, the author invites readers to like, follow, share, and consider subscribing to his knowledge platform for more advanced Spring, MyBatis, and micro‑service content.