Spring Data JPA Pagination Queries and HikariCP Connection Pool Utilization in JHipster

The short answer

There is no fixed proportion of connections attributable to the count query versus the data query — it depends entirely on which query is slower in your schema. But in the scenario you describe (large tables, missing indexes, concurrent load), the count query is almost always the dominant connection holder, because a SELECT COUNT(*) that degenerates into a sequential scan can hold a connection orders of magnitude longer than an indexed LIMIT/OFFSET data query. Pool exhaustion happens when the arrival rate of requests outpaces the release rate of connections, and the slowest statement sets that release rate.

What is confirmed behavior

• Spring Data JPA's Page execution runs two statements: the data query and a derived count query. This is standard behavior when you return Page<T> from a repository.
• Inside a single @Transactional service method, both statements execute on the same connection, held for the entire transaction — including any downstream calls made before commit. So the real question is often not "count vs. data" but "how long is the transaction open?"
• HikariCP hands out one connection per active transaction; with a fixed maximum-pool-size, any statement whose latency grows with table size becomes the bottleneck under concurrency.

How to measure the split instead of guessing

Before changing anything, get per-statement timing. Enable SQL logging temporarily in a staging load test:

logging.level.org.hibernate.SQL: DEBUG
logging.level.org.hibernate.type.descriptor.sql.BasicBinder: TRACE

Better, expose HikariCP metrics through Micrometer (hikaricp.connections.active, hikaricp.connections.pending) and correlate spikes in pending threads with slow-query log entries from the database. Run the generated count query manually:

EXPLAIN ANALYZE SELECT COUNT(*) FROM your_table WHERE ...;

If you see a sequential scan on a large table, you have found your connection hog. If the count is fast but connections still pile up, look at transaction duration instead — the data query's connection is being held open by code after the query, not by the query itself.

Does a covering index help?

Usually yes, significantly, when the count query has a selective WHERE clause: an index whose columns satisfy the filter lets the database count from the index without touching the heap, collapsing hold time from seconds to milliseconds. Two caveats: a bare COUNT(*) over the whole table with no filter often still scans (PostgreSQL may use an index-only scan on the smallest index; MySQL/InnoDB behavior differs), and dialect-specific plans mean you must verify with EXPLAIN ANALYZE on your database rather than assume. This is the point most worth verifying in your environment before recommending anything else.

Should you split count and data into separate transactions?

Generally no. Two transactions mean two connection acquisitions per request, which increases total pool pressure and adds latency; it also opens a consistency window where the total and the page contents disagree. The only defensible split is making the whole read path @Transactional(readOnly = true) so nothing downstream extends a writable transaction, and ensuring no remote calls happen inside it. Better options, in order of impact:

1. Add the covering index and re-measure — this usually resolves the exhaustion outright.
2. If the total count is not needed by the UI, return Slice<T> instead of Page<T>; Spring Data then skips the count query entirely.
3. Keep transactions short: fetch the page, exit the transaction, then do downstream work.
4. Treat raising maximum-pool-size as a last resort — it shifts contention to the database server rather than removing it.

One detail that would change this advice

If your EXPLAIN ANALYZE shows the count query is already fast (single-digit milliseconds) yet the pool still exhausts, the cause is transaction scope, not the count query — share the slow-query log or a HikariCP metrics snapshot and the recommendation shifts to transaction-boundary refactoring. Version note: this reflects long-standing Spring Data JPA and HikariCP behavior; exact count-query generation varies slightly across Spring Data releases, so confirm against your JHipster-generated dependencies.