Let's break down your questions one by one, based on how Hibernate handles sequence-based ID generation under the hood:

1. What advantages does pooled-lo have over the default pooled optimizer? #

First, let's clarify the core difference in how they work, since this explains their tradeoffs:

• Pooled optimizer: When fetching a batch of IDs, it calls nextval on the sequence once. The batch of IDs spans (current_nextval - allocationSize + 1) to current_nextval. For example, if allocationSize=5 and nextval returns 10, your app uses IDs 6-10. The sequence value jumps straight to 10, and the next batch will pull 15, using 11-15.
• Pooled-lo optimizer: It makes two database calls per batch: first currval (to get the current sequence value), then nextval (to advance the sequence). The ID batch spans currval to nextval - 1. Using the same allocationSize=5, if currval is 5 and nextval returns 10, your app uses 5-9. The next batch will take currval=10 and nextval=15, using 10-14.

The key advantage of pooled-lo is no wasted sequence values. If your app restarts with unused IDs in a pooled batch (e.g., you only used ID 6 from the 6-10 batch), the remaining 7-10 are lost forever. With pooled-lo, restarting the app will pick up from the last currval, so no IDs are skipped unless you manually modify the sequence.

That said, your observation about "extra database calls" is actually reversed: pooled only makes one call per batch, while pooled-lo makes two. So pooled is lighter on database roundtrips.

2. Why is pooled set as the default optimizer (Hibernate 5+ when allocationSize > 1)? #

Hibernate chose pooled as the default for three practical, real-world reasons:

• Better performance: One database call per batch vs. two means lower latency and higher throughput, especially in high-concurrency apps. Every extra roundtrip adds overhead, so minimizing that is a priority for most use cases.
• Wider database compatibility: Not all databases support currval (the call pooled-lo relies on). Some databases restrict currval to the same session that called nextval, or have limited sequence dialect support. nextval is universal across all sequence-supporting databases, so pooled works everywhere.
• Sequence gaps are acceptable for most apps: For 99% of applications, ID gaps don't matter. IDs are just unique identifiers, and a few skipped numbers don't impact business logic. Trading minor gap risks for better performance and compatibility is a sensible default.

3. Does pooled-lo offer significant advantages? #

Only in niche scenarios where strict ID continuity is non-negotiable:

• If your business demands that IDs are strictly consecutive (e.g., financial transaction numbers, compliance-mandated serial numbers where gaps could raise audit concerns), pooled-lo is the right choice.
• For all other cases, the extra database call per batch and potential compatibility issues make pooled-lo a worse choice than the default pooled optimizer. The "advantage" of no gaps isn't significant enough to justify the tradeoffs for most apps.

Quick Additional Notes #

• For both optimizers, always set your sequence's INCREMENT BY value equal to Hibernate's allocationSize. Mismatches will cause ID conflicts or unexpected gaps.
• Neither optimizer eliminates all gaps entirely: if multiple apps/servers are using the same sequence, gaps can still occur (e.g., one server grabs a batch, another grabs the next before the first uses all its IDs). Pooled-lo only prevents gaps from app restarts or unused batches in a single instance.

内容的提问来源于stack exchange，提问作者len