dataValueSets export stops streaming in 2.43: default ORDER BY plus a lost JDBC fetch size

Development - Développement
1

Second finding from the same benchmarking run as the JSON serialisation thread. This one is independent of it: it affects both JSON and CSV, and it is present unchanged on master.

On 2.42 the export wrote rows as PostgreSQL produced them. On 2.43 the entire query is drained before the first byte reaches the client.

Time to first byte, 24-month window 2.42.5.2 2.43.1 master
JSON 0.16 s 3.30 s 3.04 s
CSV 0.20 s 3.23 s 3.41 s

CSV’s per-value cost didn’t change while its TTFB did, which is what separates this from the serialisation regression.

Setup as before: Sierra Leone demo database, PostgreSQL 16, same host and container limits for every run, 973,730 values.

Two independent causes; either alone produces the delay

An unconditional default ORDER BY. In HibernateDataExportStore.createExportQuery:

java

if (isEmpty(orders)) orders = List.of(Order.PE, Order.CREATED, Order.DE);

orders is left null for a plain export request, so the default always applies, producing ORDER BY pe.startdate, pe.enddate, dv.created, deid. 2.42’s main export SQL had no ORDER BY at all — the only ordering in SpringDataValueSetStore was in the paged lastUpdated sync variant, which needs it for limit/offset.

auto_explain on 2.43.1, 24-month window:

Sort  (cost=18925.34..18925.93 rows=236 width=98)
      (actual time=2600.226..3028.138 rows=973730 loops=1)
  Sort Key: pe.startdate, pe.enddate, dv.created, de.uid
  Sort Method: external merge  Disk: 118656kB
  • Startup time is 2,600 ms out of 3,028 ms — 86% of the query elapses before its first row is available. The sort’s input has a startup time of 1.35 ms and has delivered every row by 1,272 ms.
  • 116 MB spilled to disk on every export.
  • Row estimate 236 against 973,730 actual, a 4,126× underestimate. Probably worth a separate look — the CTE plus = ANY($1) structure of the generated SQL seems to defeat cardinality estimation here.

The top Sort node is byte-identical across JSON, JSON+gzip and CSV.

exportInGroups and sync mode genuinely need ordered input and request it explicitly. What’s new in 2.43 is that the default is applied to every export whether or not anything downstream consumes it.

No JDBC fetch size on the new path. 2.42 exported through jdbcTemplate.query(sql, ResultSetExtractor), using the @Primary JdbcTemplate bean configured with setFetchSize(1000); the service method is @Transactional, so autocommit is off — exactly the condition PgJDBC needs for a server-side cursor.

2.43 moved to a Hibernate native query. The Hibernate layer itself is lazy (NativeQuery.stream() runs over scroll(FORWARD_ONLY)), but no fetch size is applied anywhere on this path: not in hibernate-default.properties, not in HibernateConfig, no ConfigurationKey, and HibernateNativeQueryAPI has no way to express it. With PgJDBC’s default of 0 the driver materialises the whole result set in client memory before returning the first row.

DataSourceConfig still sets 1000 on the JdbcTemplate bean — the setting wasn’t removed, the export simply stopped going through the object that carries it.

To be straight about the evidence: this second cause is read from source, not observed. The sort blocks first, so it can’t be isolated by latency, and its cost is driver-side memory, which I didn’t measure.

Why this matters more than three seconds

Wall-clock cost of the sort is about 1.76 s on 973,730 rows. The concerns are elsewhere:

  • Heap. With no fetch size, the driver buffers the entire result set per request. At ~974k values that’s survivable; on an instance exporting 5–10M it’s a GC and OOM risk per concurrent export.
  • Temp disk under concurrency. 116 MB of temp writes per export; ten concurrent exports contend for over a gigabyte.
  • Client timeouts. A client with a socket read timeout receives nothing at all until the query finishes, which matters where that takes minutes rather than seconds.

Suggested fixes

  1. Apply a JDBC fetch size on the export query — smallest form is setFetchSize(n) in HibernateNativeQueryAPI.HibernateQuery.stream, or exposing it on SQL.Query so callers can choose.
  2. Apply the default ORDER BY only where something consumes it; exportInGroups and sync mode already request ordering explicitly.

Both are needed: with the sort in place, fixing the fetch size alone won’t move the first byte earlier.

HibernateDataExportStore.java is byte-identical in 2.43.1 and master, so this is present in current development, not only in the release.

1 Like