JDBC Backend network usage

[ldap4life]

Hi, im experimenting with the JDBC backend and using postgres as the database. Im noticing on a medium sized directory (~13,000) entries poor performance but what I think is excessive network utilization, about 52MB per second which seems high so i am wondering if this is typical, or if there are any optimizations I could look at? The entries are not excessively large, about 10 attributes each with each attribute less than 100 chars. The following is a screenshot of data transfer over a 5 minute window from postgres. In that timeframe there are about 4000 search requests, 750 add requests, and 250 modify requests. Despite the poor performance I experience at my app, etimes in the access logs look low. If I switch the same performance test to the je backend, I get much smoother results. jdbc url: db-directory jdbc:postgresql://database.com:5432/db?user=user&password=password&sslmode=require&connectTimeout=30&socketTimeout=60&tcpKeepAlive=true

[vharseko]

Thanks for the detailed report — the numbers made the cause easy to pin down.

Root cause. The JDBC backend's cursor (JDBCStorage.CursorImpl) fetches rows in eager batches of 1000 (select k,v … order by k limit 1000), and every cursor repositioning discards the buffer and re-fetches a fresh 1000-row batch — even when the requested key is already in the buffer. One-level searches enumerate children by repositioning once per child, so listing N children transfers N × 1000 rows from PostgreSQL instead of ~N. At your rate (~13 searches/s) that works out to almost exactly the ~52 MB/s you measured. The JE backend does the same repositioning as a local B-tree seek, which is why it feels smooth there.

Workaround available right now — shrink the cursor fetch size via a system property (add it to OPENDJ_JAVA_ARGS / java.properties):

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

For your directory size this should cut database network traffic by roughly ~20×, at the cost of a few extra round-trips on long sequential scans. Values in the 20–100 range are reasonable; please share what you observe.

Proper fix (serving repositioning from the already-fetched buffer + adaptive batch sizing, expected to reduce dn2id scope-scan traffic by ~1000× for one-level searches) is tracked in #860.

[ldap4life]

tested and experienced the same results. I repeated the test with fetch size 1 and noted about half the network
utilization. Digging further, I checked active queries on postgres during the test, there were 12 queries over 2 tables. 1 of the tables
is 13MB and dispersed over ~10k rows. The other table is ~3.5MB but only has 1 row. Could this concentration on the 3.5MB table be the source of my issue?

  pid  |       usename        |        state        |                                                                    query                                                                     
-------+----------------------+---------------------+----------------------------------------------------------------------------------------------------------------------------------------------
 81951 | opendj | idle in transaction | select k,v from opendj_89664c245635f826d983b56d437e14c284eefeea28dd3b638bc391c2 where k>$1 order by k offset $2 rows fetch next $3 rows only
 81946 | opendj | idle in transaction | select k,v from opendj_89664c245635f826d983b56d437e14c284eefeea28dd3b638bc391c2 where k>$1 order by k offset $2 rows fetch next $3 rows only
 81919 | opendj | idle in transaction | select v from opendj_67108e419e0a2f8d03c6b56381be82971d3f5253ba33b13c865e5138 where h=$1 and k=$2
 81940 | opendj | idle in transaction | select v from opendj_67108e419e0a2f8d03c6b56381be82971d3f5253ba33b13c865e5138 where h=$1 and k=$2
 81917 | opendj | idle in transaction | select k,v from opendj_89664c245635f826d983b56d437e14c284eefeea28dd3b638bc391c2 where k>$1 order by k offset $2 rows fetch next $3 rows only
 81948 | opendj | idle in transaction | select k,v from opendj_89664c245635f826d983b56d437e14c284eefeea28dd3b638bc391c2 where k>$1 order by k offset $2 rows fetch next $3 rows only
 81920 | opendj | idle in transaction | select v from opendj_67108e419e0a2f8d03c6b56381be82971d3f5253ba33b13c865e5138 where h=$1 and k=$2
 81944 | opendj | idle in transaction | select k,v from opendj_89664c245635f826d983b56d437e14c284eefeea28dd3b638bc391c2 where k>$1 order by k offset $2 rows fetch next $3 rows only
 81918 | opendj | idle in transaction | select k,v from opendj_89664c245635f826d983b56d437e14c284eefeea28dd3b638bc391c2 where k>$1 order by k offset $2 rows fetch next $3 rows only
 81947 | opendj | active              | select k,v from opendj_89664c245635f826d983b56d437e14c284eefeea28dd3b638bc391c2 where k>$1 order by k offset $2 rows fetch next $3 rows only
 81954 | opendj | idle in transaction | select k,v from opendj_89664c245635f826d983b56d437e14c284eefeea28dd3b638bc391c2 where k>$1 order by k offset $2 rows fetch next $3 rows only
 81921 | opendj | active              | select v from opendj_67108e419e0a2f8d03c6b56381be82971d3f5253ba33b13c865e5138 where h=$1 and k=$2

And tables stats:

Schema |                              Name                               | Type  |        Owner         | Persistence | Access method |    Size    | Description 
--------+-----------------------------------------------------------------+-------+----------------------+-------------+---------------+------------+-------------
public | opendj_67108e419e0a2f8d03c6b56381be82971d3f5253ba33b13c865e5138 | table | opendj | permanent   | heap          | 13 MB      | 
public | opendj_89664c245635f826d983b56d437e14c284eefeea28dd3b638bc391c2 | table | opendj | permanent   | heap          | 3496 kB    | 

[vharseko]

Great data — this narrows it down a lot. The two query shapes map to two different trees, and the table names are opendj_ + SHA-224 of /<baseDN>/<treeName> (baseDN in normalized form: lowercase, no spaces), so you can map yours directly:

BASEDN='dc=example,dc=com'   # your suffix, normalized: lowercase, no spaces after commas
for t in dn2id id2entry id2childrencount referral state; do
  printf 'opendj_%s  %s\n' "$(printf '%s' "/$BASEDN/$t" | openssl dgst -sha224 -r | cut -d' ' -f1)" "$t"
done

From the shapes alone: the 13 MB / ~10k-row table read with select v … where h=$1 and k=$2 is almost certainly id2entry — the actual entries your searches return. That component scales with entries returned, not with cursor batching, so no fetchsize value can shrink it.

The interesting one is the 3.5 MB table with a single row. My bet is id2childrencount rather than dn2id: it's a sharded-counter tree that legitimately holds only a couple of live rows, yet it is range-scanned (k>…) every time numSubordinates/hasSubordinates or the backend entry count is evaluated — matching the scan concentration you captured. And your write load (~750 adds + 250 mods per 5 min) updates those same one-or-two logical counters over and over; every update leaves a dead tuple, so the table balloons physically while count(*) stays at 1. One caveat: that bloat costs PostgreSQL CPU/IO on every scan, but it does not by itself add client↔server network bytes.

Could you post the output of:

select count(*) as live_rows, max(octet_length(k)) as max_key, max(octet_length(v)) as max_val
from opendj_89664c245635f826d983b56d437e14c284eefeea28dd3b638bc391c2;

select relname, n_live_tup, n_dead_tup, n_tup_upd, last_autovacuum, last_autoanalyze
from pg_stat_user_tables
where relname in ('opendj_89664c245635f826d983b56d437e14c284eefeea28dd3b638bc391c2',
                  'opendj_67108e419e0a2f8d03c6b56381be82971d3f5253ba33b13c865e5138');

…plus the tree-name mapping from the snippet above? If n_dead_tup is large, VACUUM (ANALYZE) on that table should visibly cut database-side load. (The idle in transaction sessions are expected — each LDAP operation holds a read-committed transaction until it completes and it's rolled back when the connection returns to the pool — but long operations can delay autovacuum, which feeds exactly this kind of bloat.)

On the fetchsize numbers: if that table really has 1–2 live rows, its scans return at most 1–2 rows regardless of the limit — fetchsize cannot affect them. So the halving at fetchsize=1 most likely came from the dn2id scans (which just weren't in flight in your 12-session snapshot), and the remaining half is dominated by the id2entry point reads, i.e. the result payload itself. Two things would pin that down:

• the search scope (one-level vs subtree) and roughly how many entries an average search returns — at your rate, ~25 MB/s of id2entry reads works out to ≈2,000 entries returned per second;
• the absolute MB/s you observed for fetchsize default / 50 / 1 ("about half" relative to which baseline?).

If the payload is what remains, the lever is caching rather than cursor tuning: the JE backend feels smooth because its cache is local, while JDBC pays the wire cost for every entry read — enabling the DS entry cache should remove most repeated id2entry reads for a hot working set.

Finally, the proper fix for the cursor re-fetch (#860) was merged today in #863: forward repositioning is now served from the already-fetched buffer and batches grow adaptively (32 → fetchsize). If you can build master (or grab a snapshot build), I'd retest with the default fetchsize — combined with a VACUUM of the counter table it should change the picture substantially.

[ldap4life]

re 1 row in that table, checking again, it has thousands so Im not sure what I was smoking when I checked that earlier even though I was certain it was 1. With the hash i havent been able to match table names, although im sure i have the basedn correctly normalized as instructed. For my purposes, im going to be switching to the JE backend, but may come back to the jdbc postgres in the future. Thank you for your efforts. Output of those queries in any case:

select count(*) as live_rows, max(octet_length(k)) as max_key, max(octet_length(v)) as max_val
from opendj_89664c245635f826d983b56d437e14c284eefeea28dd3b638bc391c2;
 live_rows | max_key | max_val 
-----------+---------+---------
     13908 |      78 |       8

 select relname, n_live_tup, n_dead_tup, n_tup_upd, last_autovacuum, last_autoanalyze
from pg_stat_user_tables
where relname in ('opendj_89664c245635f826d983b56d437e14c284eefeea28dd3b638bc391c2',
                  'opendj_67108e419e0a2f8d03c6b56381be82971d3f5253ba33b13c865e5138');
                             relname                             | n_live_tup | n_dead_tup | n_tup_upd | last_autovacuum |       last_autoanalyze       
-----------------------------------------------------------------+------------+------------+-----------+-----------------+------------------------------
 opendj_67108e419e0a2f8d03c6b56381be82971d3f5253ba33b13c865e5138 |      11014 |       1250 |       368 |                 | 2026-08-11 15:02:05.50977+00
 opendj_89664c245635f826d983b56d437e14c284eefeea28dd3b638bc391c2 |       1104 |          0 |         0 |                 | 

[vharseko]

Thanks for closing the loop — and no worries about the row count. Your numbers actually identify the table conclusively: 13,908 rows with keys ≤78 bytes and 8-byte values is one row per entry — that's dn2id (key = normalized DN, value = entry ID). So the scan concentration was exactly the #860 re-fetch pattern after all, and my sharded-counter/bloat guess was wrong: n_dead_tup = 0 rules out bloat.

The hash recipe not matching was my mistake: the normalized form reverses the RDN order (suffix first). For dc=example,dc=com the tree name is /dc=com,dc=example/dn2id:

printf '%s' '/dc=com,dc=example/dn2id' | openssl dgst -sha224 -r

(lowercased attribute names, equality-normalized values, RDNs reversed, , between RDNs, + inside multi-valued RDNs.)

Two closing notes for whenever you revisit JDBC:

• pg_stat shows the dn2id table has never been analyzed and its row estimate is far off (1,104 vs the real 13,908) — worth running ANALYZE after a bulk import. On our side we'll look at doing that automatically after import, and at stamping each table with COMMENT ON TABLE … IS '/…/dn2id' so \dt+ shows the tree name and this kind of investigation doesn't need a hash recipe.
• Use a build that includes [#860] Serve JDBC cursor repositioning from the fetched buffer and grow batches adaptively #863 — with repositioning served from the already-fetched buffer, the N×1000-row dn2id re-fetches your test measured are gone.

JE is a solid default; thanks a lot for the careful measurements — they directly shaped #860 and #863.