Optimizing Large IN Queries with Spring AOP and Multi‑Threaded Splitting

In many projects, IN‑list queries with hundreds or thousands of parameters cause severe PostgreSQL performance degradation and slow API responses. The author presents a solution originally written in 2021 and later refined, which uses Spring AOP to transparently split large IN queries, run them in parallel, and combine the results.

Defining the AOP annotation

@Target(ElementType.METHOD)
@Retention(RetentionPolicy.RUNTIME)
public @interface SplitWorkAnnotation {
    ThreadPoolEnum setThreadPool();
    Class
handlerReturnClass() default MergeFunction.class;
    int splitLimit() default 1000;
    int splitGroupNum() default 100;
}

The annotation specifies the thread pool to use, the class that merges partial results, the size limit that triggers splitting, and the number of items per split group.

A parameter‑level annotation marks the argument that needs splitting:

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

Implementing the AOP aspect

@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();
        if (args == null || args.length == 0 || splitLimit <= splitGroupNum) {
            return pjp.proceed();
        }
        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 splitParam = args[splitIdx];
        if (!(splitParam instanceof Object[]) && !(splitParam instanceof List) && !(splitParam instanceof Set)) {
            return pjp.proceed();
        }
        boolean tooSmall = (splitParam instanceof Object[] && ((Object[]) splitParam).length <= splitLimit)
                || (splitParam instanceof List && ((List
) splitParam).size() <= splitLimit)
                || (splitParam instanceof Set && ((Set
) splitParam).size() <= splitLimit);
        if (tooSmall) return pjp.proceed();
        // optional deduplication for List
        if (splitParam instanceof List) {
            List
list = (List
) splitParam;
            if (list.size() > 1) {
                splitParam = new ArrayList<>(new HashSet<>(list));
            }
        }
        int batchNum = getBatchNum(splitParam, 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 batch = b;
            final Object param = splitParam;
            futures[b] = CompletableFuture.supplyAsync(() -> {
                Object[] newArgs = Arrays.copyOf(args, args.length);
                newArgs[splitIdx] = getPartParam(param, splitGroupNum, batch);
                try {
                    return pjp.proceed(newArgs);
                } catch (Throwable e) {
                    throw new RuntimeException(e);
                }
            }, threadPool.getThreadPoolExecutor());
        }
        CompletableFuture.allOf(futures).get();
        Class
handlerCls = anno.handlerReturnClass();
        List
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 groupSize) {
        if (param instanceof Object[]) {
            Object[] arr = (Object[]) param;
            return (arr.length + groupSize - 1) / groupSize;
        } else if (param instanceof Collection) {
            int size = ((Collection
) param).size();
            return (size + groupSize - 1) / groupSize;
        }
        return 1;
    }

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

Result‑handling interface and a simple merge implementation

/**
 * Interface for handling merged results.
 */
public interface HandleReturn {
    Object handleReturn(List results);
}

/**
 * Default merge strategy that concatenates List results.
 */
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 collection parameter with @NeedSplitParam , developers can achieve automatic parallel execution of sub‑queries without changing business logic, dramatically reducing response time for massive IN‑list queries.

The article concludes with a call for readers to like and follow the author’s public account for more architecture guidance.