Large IN clauses: chunking and alternatives? | JPA & Hibernate

Q: How does Hibernate in_clause_parameter_padding help?

Hibernate's hibernate.query.in_clause_parameter_padding pads IN lists to the next poweroftwo size. This means an IN list of 3, 4, 5, 6, 7, or 8 items all generate the same query with 8 parameters, hitting the same cached plan. Without padding (default): IN (?, ?, ?) plan A (3 params) IN (?, ?, ?, ?)

Definition

Never dump 10,000 literals into one IN clause: chunk into bounded batches, pass as a database array and join, or keep the filter server-side as a subquery.

Chunking -- splits the ID list into bounded partitions (e.g., 500-1000 each), runs one IN query per chunk, and combines results in the application.
Array-join -- passes all IDs as a single database array (e.g., PostgreSQL = ANY(:ids) or unnest(:ids)) and joins in one query.
Server-side filter -- expresses the ID filter as a subquery or join so the IDs never leave the database: WHERE id IN (SELECT id FROM flagged_accounts).
IN clause limit -- Oracle caps at 1,000 literals per IN clause; PostgreSQL accepts more but degrades with parse time and plan cache bloat.
in_clause_parameter_padding -- Hibernate setting that pads IN lists to the next power-of-two size so varying list lengths hit the same cached query plan.

At National Bank of India, the fraud team has a list of 10,000 suspicious account numbers to look up. Handing the clerk a single slip listing all 10,000 numbers is unwieldy: the clerk chokes, and some systems reject slips longer than 1,000 lines. Instead, you either hand over the numbers in neat bundles of 500 (chunking), write all 10,000 into a scratch register the clerk already has and say "match against this register" (array-join), or point the clerk to the flagged accounts already recorded in the system (server-side filter).

Understanding the Concepts

Why are large IN clauses a problem?

Databases impose limits on IN clause size and penalize large lists. Oracle throws ORA-01795 if an IN clause contains more than 1,000 literals. PostgreSQL accepts larger lists but generates a giant query string that defeats prepared-statement caching, bloats parse time, and binding thousands of parameters is itself slow.

JAVA

package com.bank.repository;

import com.bank.model.Account;
import java.util.List;

// WRONG: naive approach with 10,000 IDs
public interface AccountRepository {
    // SELECT a FROM Account a WHERE a.accountId IN (:ids)
    List<Account> findByAccountIdIn(List<String> ids);
    // With 10,000 IDs:
    // Oracle: ORA-01795 (max 1000 literals)
    // PostgreSQL: giant query string, plan cache miss,
    //   slow parsing, 10,000 bind parameters
}

TEXT

Problems with large IN clauses:
  1. Oracle: ORA-01795 beyond 1,000 entries in IN clause
  2. PostgreSQL: query string grows to megabytes
  3. Plan cache: each list size generates a unique plan
     IN (?, ?) != IN (?, ?, ?) -> different plans cached
  4. Parse time: parsing a query with 10,000 parameters is slow
  5. Network: sending 10,000 literals over JDBC is expensive

How does chunking work?

Chunking splits the ID list into bounded partitions, runs one query per chunk, and combines the results in the application. Each query stays within database limits and remains cacheable.

JAVA

package com.bank.service;

import com.bank.model.Account;
import com.bank.repository.AccountRepository;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import java.util.ArrayList;
import java.util.List;

@Service
public class ChunkedQueryService {

    private static final int CHUNK_SIZE = 500;
    private final AccountRepository repo;

    public ChunkedQueryService(AccountRepository repo) {
        this.repo = repo;
    }

    @Transactional(readOnly = true)
    public List<Account> findByIds(List<String> ids) {
        List<Account> results = new ArrayList<>();
        for (int i = 0; i < ids.size(); i += CHUNK_SIZE) {
            List<String> chunk = ids.subList(
                i, Math.min(i + CHUNK_SIZE, ids.size()));
            results.addAll(repo.findByAccountIdIn(chunk));
        }
        return results;
    }
}

TEXT

10,000 IDs with CHUNK_SIZE = 500:
  20 queries, each with IN (?, ?, ..., ?) where ? count = 500
  Each query is within Oracle's 1,000 limit
  Each query hits the same plan cache entry (same parameter count)
  Total: 20 round-trips, all fast and cacheable

Compared to one query with 10,000 parameters:
  1 round-trip, but slow parsing, no plan caching, may fail on Oracle

How does array-join work on PostgreSQL?

PostgreSQL supports passing IDs as a single array parameter and using = ANY(:ids) or unnest(:ids) as a virtual table. This executes in one query, one round-trip, and the planner can use a hash join.

JAVA

package com.bank.repository;

import com.bank.model.Account;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;

import java.util.List;

public interface AccountRepository extends JpaRepository<Account, String> {

    @Query(value = "SELECT * FROM accounts "
                 + "WHERE account_id = ANY(:ids)",
           nativeQuery = true)
    List<Account> findByIdsArray(String[] ids);
    // Pass a Java String[] -> PostgreSQL text[]
    // One query, one round-trip, no literal count limit
    // Planner uses hash join for efficient matching
}

JAVA

package com.bank.service;

import com.bank.model.Account;
import com.bank.repository.AccountRepository;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import java.util.List;

@Service
public class ArrayJoinService {

    private final AccountRepository repo;

    public ArrayJoinService(AccountRepository repo) {
        this.repo = repo;
    }

    @Transactional(readOnly = true)
    public List<Account> findByIds(List<String> ids) {
        String[] idArray = ids.toArray(new String[0]);
        return repo.findByIdsArray(idArray);
    }
}

TEXT

SQL: SELECT * FROM accounts WHERE account_id = ANY($1)
  $1 = '{SAV2001,SAV2002,...,SAV10000}'
  One query, one round-trip.
  PostgreSQL treats the array as a virtual set.
  Hash join against the accounts table: efficient.
  No literal count limit.

How does server-side filtering work?

When the IDs originate from the same database (e.g., the output of another query), keep the filter server-side as a subquery or join. The IDs never travel to the application and back.

JAVA

package com.bank.repository;

import com.bank.model.Account;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import java.util.List;

public interface AccountRepository extends JpaRepository<Account, String> {

    // Server-side filter: IDs never leave the database
    @Query("SELECT a FROM Account a "
         + "WHERE a.accountId IN "
         + "(SELECT f.accountId FROM FlaggedAccount f "
         + " WHERE f.reason = :reason)")
    List<Account> findFlaggedAccounts(String reason);
    // No application-side ID list needed!
    // Database evaluates the subquery internally.
    // Planner can use indexes on both tables.
}

TEXT

Server-side filter SQL:
  SELECT * FROM accounts
  WHERE account_id IN (
      SELECT account_id FROM flagged_accounts
      WHERE reason = 'SUSPICIOUS'
  )

Advantage: no IDs travel over the network.
  The database evaluates the subquery internally.
  Works for any number of IDs.
  Often the fastest approach because the planner
  can choose hash join, merge join, or index scan.

How does Hibernate in_clause_parameter_padding help?

Hibernate's hibernate.query.in_clause_parameter_padding pads IN lists to the next power-of-two size. This means an IN list of 3, 4, 5, 6, 7, or 8 items all generate the same query with 8 parameters, hitting the same cached plan.

TEXT

Without padding (default):
  IN (?, ?, ?)     -> plan A (3 params)
  IN (?, ?, ?, ?)  -> plan B (4 params)
  IN (?, ?, ?, ?, ?) -> plan C (5 params)
  Each list size generates a unique plan. Cache pollution.

With padding enabled:
  IN (?, ?, ?)           -> padded to IN (?, ?, ?, ?)       -> plan A (4 params)
  IN (?, ?, ?, ?)        -> plan A (4 params)
  IN (?, ?, ?, ?, ?)     -> padded to IN (?, ?, ?, ?, ?, ?, ?, ?) -> plan B (8 params)
  Fewer unique plans. Better cache hit rate.

JAVA

// application.properties:
// spring.jpa.properties.hibernate.query.in_clause_parameter_padding=true

// How padding works:
// 3 items -> padded to 4  (next power of 2)
// 5 items -> padded to 8
// 9 items -> padded to 16
// 17 items -> padded to 32
// Extra slots are filled with the last value repeated.
// Semantically identical but same plan for range of sizes.

What are common mistakes with large IN clauses?

The most common mistakes involve dumping all IDs into one query and moving data unnecessarily between the application and database.

JAVA

// WRONG: 10,000 IDs in one IN clause
List<String> tenThousandIds = ...;
List<Account> accounts = repo.findByAccountIdIn(tenThousandIds);
// Oracle: ORA-01795
// PostgreSQL: slow parse, plan cache miss

// CORRECT: chunk into bounded batches
List<Account> results = new ArrayList<>();
for (int i = 0; i < tenThousandIds.size(); i += 500) {
    List<String> chunk = tenThousandIds.subList(
        i, Math.min(i + 500, tenThousandIds.size()));
    results.addAll(repo.findByAccountIdIn(chunk));
}

JAVA

// WRONG: fetch IDs from DB, then send back as IN clause
List<String> flaggedIds = flaggedRepo.findAllAccountIds();
// Returns 10,000 IDs to the application
List<Account> accounts = accountRepo.findByAccountIdIn(flaggedIds);
// Sends 10,000 IDs back to the database

// CORRECT: server-side subquery
@Query("SELECT a FROM Account a WHERE a.accountId IN "
     + "(SELECT f.accountId FROM FlaggedAccount f)")
List<Account> findFlaggedAccounts();
// IDs never leave the database. One efficient query.

When should you use each strategy?

The choice depends on where the IDs come from and the database you use.

| Scenario | Strategy | Reason |
|---|---|---|
| IDs from same database | Server-side subquery | No round-trip, optimal plan |
| IDs from external source, PostgreSQL | Array-join (= ANY) | One query, no limit |
| IDs from external source, portable | Chunking (500-1000) | Works on all databases |
| IDs reused across queries in a job | Temp table + join | Insert once, join many |
| Variable-size IN lists | Enable padding | Better plan cache hits |

JAVA

// Decision guide for National Bank of India:
//
// Fraud team's 10K suspicious accounts from an external file:
//   PostgreSQL: array-join with = ANY(:ids)
//   Oracle: chunking with 500 per batch
//
// Daily batch processing flagged accounts from DB:
//   Server-side subquery (no round-trip)
//
// Spring Data findAllById(ids) with variable sizes:
//   Enable in_clause_parameter_padding

What about temporary tables for complex jobs?

When the same set of IDs is used across multiple queries in a batch job, insert them into a temporary table once and join against it repeatedly. This avoids resending the IDs for each query.

JAVA

package com.bank.service;

import jakarta.persistence.EntityManager;
import jakarta.persistence.PersistenceContext;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import java.util.List;

@Service
public class TempTableService {

    @PersistenceContext
    private EntityManager em;

    @Transactional
    public void processIds(List<String> ids) {
        // Create temp table
        em.createNativeQuery(
            "CREATE TEMP TABLE IF NOT EXISTS tmp_ids "
          + "(id VARCHAR(20) PRIMARY KEY)"
        ).executeUpdate();

        // Bulk insert IDs
        for (String id : ids) {
            em.createNativeQuery(
                "INSERT INTO tmp_ids (id) VALUES (:id) "
              + "ON CONFLICT DO NOTHING"
            ).setParameter("id", id).executeUpdate();
        }

        // Join against temp table (can be reused multiple times)
        List results = em.createNativeQuery(
            "SELECT a.* FROM accounts a "
          + "JOIN tmp_ids t ON a.account_id = t.id"
        ).getResultList();

        // Cleanup
        em.createNativeQuery("DROP TABLE IF EXISTS tmp_ids")
          .executeUpdate();
    }
}

Visual Overview

DIAGRAM

  Strategy decision tree:

  ┌─────────────────────────────────┐
  │  Where do the IDs come from?    │
  └─────────────┬───────────────────┘
                │
        ┌───────┴──────────┐
        │                  │
  Same database        External source
        │                  │
        ▼                  ▼
  Server-side        ┌───────────────┐
  subquery           │ Which database?│
  (best!)            └───────┬───────┘
                     ┌───────┴──────────┐
                     │                  │
               PostgreSQL           Other (Oracle, etc.)
                     │                  │
                     ▼                  ▼
               Array-join          Chunking
               = ANY(:ids)         (500 per batch)

  Plan caching with padding:
  ┌──────────────────────────────────────────────┐
  │  Without padding:                             │
  │  IN(?,?,?)  IN(?,?,?,?)  IN(?,?,?,?,?)        │
  │  3 different plans cached                     │
  │                                               │
  │  With padding (power of 2):                   │
  │  IN(?,?,?,?)  IN(?,?,?,?)  IN(?,?,?,?,?,?,?,?)│
  │  2 plans for 3 different list sizes           │
  └──────────────────────────────────────────────┘

Key Takeaway

Dumping thousands of IDs into a single IN clause is a classic mistake in JPA applications. Oracle rejects lists longer than 1,000 entries, and PostgreSQL accepts them but generates an enormous query string that defeats prepared-statement plan caching and slows parsing. The fix depends on where the IDs come from.

When the IDs originate from the same database, a server-side subquery is the best approach: no IDs travel to the application and back, and the database planner optimizes the query internally. When the IDs come from an external source and you use PostgreSQL, passing them as a single array with = ANY(:ids) executes in one efficient query with no literal-count limit. For portability across databases, chunking splits the list into bounded batches of 500-1000 and combines results.

In our National Bank of India system, when the fraud team has 10,000 suspicious account IDs from an external file, the array-join approach handles them in a single efficient query. When those suspicious accounts are already recorded in a flagged_accounts table, a server-side subquery avoids moving IDs over the wire entirely. Hibernate's in_clause_parameter_padding setting pads variable-size IN lists to power-of-two sizes so the plan cache is not polluted by every unique list length.

Remember one sentence: prefer server-side subqueries when IDs originate in the database, use array-join for external ID sets on PostgreSQL, and fall back to bounded chunking for portability, never dumping thousands of literals into one IN clause.

---