Java 8 JPA Repository Stream row-by-row in Postgresql

Question

Reading this about MySQL defines certain query hints (@QueryHints annotation) that are required in order to make MySQL process the result set row-by-row. Does anyone know for sure what (if anything) is required for PostgreSQL?

Here is the reference query definition in the repository for MySQL:

@QueryHints(value = @QueryHint(name = HINT_FETCH_SIZE, value = "" + Integer.MIN_VALUE))
@Query(value = "select t from Todo t")
Stream<Todo> streamAll();

Answer 1

Its PostgreSQL equivalent is:

@QueryHints(value = @QueryHint(name = HINT_FETCH_SIZE, value = "1")

For contrast setFetchSize(rows) should throw SQLException when the condition rows >= 0 is not satisfied. This is not honored by MySQL's JDBC driver.

Though in PostgreSQL you can actually set up values greater than 1 to allow some caching by PostgreSQL's JDBC driver. The docs' example of 50 seems reasonable (unless you have irrationally wide rows). You could also choose this value to be a portion of the expected row count. Test a few variants before deploying your application.