13

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();
JoeG
  • 7,191
  • 10
  • 60
  • 105
  • 1
    PostgreSQL need similar settings (namely `ResultSet.TYPE_FORWARD_ONLY` & a specific fetch-size). However, you can control fetch-size as you like (f.ex. [the JDBC docs contains an example with `st.setFetchSize(50)`](https://jdbc.postgresql.org/documentation/head/query.html#query-with-cursor)). There is also another limitation: only a single query can be run in this "scrollable" mode. – pozs Apr 06 '17 at 13:40
  • I did see that example in the docs with the 50. The code above uses Integer.MIN_VALUE, didn't know what postgresql would do with that, hence my question. Separately, can you explain the limitation a bit more? Is that across all connections? I am assuming other queries can run in parallel though? – JoeG Apr 06 '17 at 13:57
  • 2
    no, PostgreSQL's JDBC driver allows multiple queries in a single statement. (f.ex. `"INSERT INTO ...; SELECT ...;"`): that's not allowed in scrollable mode (and usually not portable anyway). -- Don't use a negative value for fetch-size in PostgreSQL. Use `@QueryHint(name=HINT_FETCH_SIZE,value="1")` for row-by-row read, *>1* for batch-by-batch read (with some caching) and `"0"` to turn off scrollability. – pozs Apr 06 '17 at 14:02
  • 1
    For contrast [`setFetchSize(rows)`](https://docs.oracle.com/javase/7/docs/api/java/sql/Statement.html#setFetchSize(int)) should throw `SQLException` when *the condition `rows >= 0` is not satisfied* (that is not honored by MySQL's JDBC driver). – pozs Apr 06 '17 at 14:05
  • @pozs, I think your last comment is what I am looking for as an answer, maybe submit that? Would like to know any performance differences with Java streams and fetch_size=1 vs fetch_size=25 though. That is, should it always be 1? – JoeG Apr 06 '17 at 14:15
  • 1
    this depends on how many rows are you expecting & how wide are your rows. So I advise you to test multiple variants. But I predict that fetch-size=1 won't be the fastest, and above some value, it will became slower and slower again (if you reach the number of your rowcount f.ex. then it actually won't scroll anything) – pozs Apr 06 '17 at 14:31

1 Answers1

17

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.

pozs
  • 34,608
  • 5
  • 57
  • 63