Hibernate pagination dialect mismatch between local H2 and production PostgreSQL in Eclipse JPA projects

Answer

The dialect that Hibernate uses for setFirstResult/setMaxResults pagination is the dialect resolved when the SessionFactory is built for the deployed artifact. In a packaged Eclipse JPA project that is normally the hibernate.dialect property in the persistence.xml that ends up in the WAR/EAR, with a fallback to auto-detection from the production JDBC URL if the property is absent. Eclipse run configurations and the JPA facet can change the dialect while you run inside the IDE, but those changes do not ship with the build.

Confirmed facts vs likely explanation

Confirmed: Hibernate resolves the dialect at SessionFactory creation from the hibernate.dialect property. If missing, it auto-detects from the JDBC URL. Eclipse JPA facet injections are IDE-only and do not affect the packaged artifact. A server runtime can override the value via its own Hibernate configuration or datasource settings.

Likely explanation for your symptom: The persistence unit used locally has an H2 dialect set by an Eclipse run configuration. The persistence.xml packaged for production either still references the H2 dialect or omits the dialect property entirely. With a mismatched or missing dialect Hibernate can fall back to in-memory pagination, which produces the HHH000104 warning and degrades or fails on large result sets on PostgreSQL.

Steps needed for this case

1. Verify the artifact. Open the deployed META-INF/persistence.xml inside the WAR/EAR and confirm the property is present and correct for production:

   <property name="hibernate.dialect" value="org.hibernate.dialect.PostgreSQLDialect"/>

2. Check startup dialect. Enable SessionFactory logging, e.g. log.level.org.hibernate.cfg=DEBUG, and confirm the dialect reported at startup is PostgreSQL, not H2.
3. Validate generated SQL. Run a simple paginated query and inspect logs for LIMIT ? OFFSET ? style SQL. In-memory pagination will show a full select without limit/offset.
4. Remove IDE-only overrides. Do not rely on Eclipse run configuration or JPA facet defaults to set the dialect. Keep one explicit hibernate.dialect per environment in version-controlled persistence.xml or externalized properties.

JOIN FETCH and in-memory pagination

With the correct PostgreSQL dialect, standard limit/offset pagination is supported. The in-memory warning can still appear when Hibernate cannot safely apply pagination to a query with a collection JOIN FETCH. That is a query shape limitation, not a dialect mismatch. The dialect fix eliminates the fallback caused by dialect mis-resolution; remaining in-memory cases require query restructuring or separate count queries.

One diagnostic that would change the recommendation: what dialect name is logged at production startup for the persistence unit? If it is already PostgreSQL, the issue is query shape rather than configuration.