JDBC backend: cursor repositioning re-fetches full 1000-row batches, causing excessive database network traffic #860

Problem

Reported in discussion #859: with the JDBC backend on PostgreSQL (~13,000 entries, ~10 small attributes each), a moderate load (~4,000 searches, ~750 adds, ~250 modifies over a 5-minute window) produces ~52 MB/s of network traffic between OpenDJ and the database. Access log etimes are low, but application-level performance is poor. The same load on the JE backend runs smoothly.

Root cause

JDBCStorage.CursorImpl iterates via keyset pagination (select k,v from <table> where k>? order by k limit <batchSize>) and always eagerly fetches batchSize (default 1000) full (k,v) rows:

• CursorImpl.positionToKeyOrNext() unconditionally clears the buffer and re-fetches a fresh 1000-row batch — even when the requested key is already sitting in the buffer (typically it is the very next row).
• DN2ID.ChildrenCursor.next() advances to the next sibling via positionToKeyOrNext(nextSibling()), i.e. one repositioning per child.
• EntryContainer.getIDSetFromScope() runs for every search whose index candidate set exceeds IndexFilter.FILTER_CANDIDATE_THRESHOLD (10 entries), cursoring dn2id for the scope set (up to index-entry-limit, default 4000 entries).

Combined effect: a one-level search enumerating N children transfers N × 1000 rows (DN key + value + protocol overhead ≈ 65 bytes/row) from the database instead of ~N rows. For ~60 children that is ~3.9 MB per search — at ~13 searches/s this matches the reported 52 MB/s. EntryContainer.hasSubordinates() (invoked per returned entry when the client requests numSubordinates/hasSubordinates) and subtree delete hit the same ChildrenCursor/SubtreeCursor pattern.

The JE backend implements the same SPI with local B-tree seeks, where repositioning is nearly free — the storage SPI usage pattern is fine, the JDBC cursor implementation amplifies it.

Secondary contributors:

• subtree-scope searches read up to 5 × 1000 dn2id rows per search (SubtreeCursor is sequential, so no repositioning amplification);
• add/modify index maintenance (WriteableTransactionTransactionImpl.update(TreeName, key, UpdateFunction)) does a full EntryIDSet read + write per touched index key.

Workaround

-Dorg.openidentityplatform.opendj.jdbc.fetchsize=50

Reduces the per-repositioning transfer ~20×, at the cost of more round-trips on long sequential scans. Values in the 20–100 range are reasonable for small/medium directories.

Proposed fix

1. Serve positionToKeyOrNext() from the already-fetched buffer. Buffered rows are sorted and contiguous: if the target key is ≤ the last buffered key, discard smaller rows from the head of the deque and answer without touching the database. This collapses ChildrenCursor's N × 1000-row pattern into ~N/1000 fetches (~1000× less dn2id traffic for one-level searches).
2. Adaptive batch sizing. Start with a small first fetch (e.g. 32–100 rows) and grow geometrically up to the configured maximum while the consumer keeps iterating — most cursor opens in the pluggable backend read only a handful of rows (hasSubordinates reads 1–2).