JDBC fetch size

When the Lobster Data Platform queries a database via a connection in Via Bridge (Standard) mode, it retrieves the results in batches. The fetch size controls two values:

How many rows the bridge transfers to the platform in an HTTP Response,
How many rows the JDBC driver of the bridge preloads from the database per DB roundtrip.

The default value is 500 rows per HTTP response, unless a value is configured on either side.

For connections with many narrow rows (e.g., ID queries, reference tables), a higher fetch size reduces the number of HTTP roundtrips between the platform and the bridge and increases throughput. However, an HTTP response limit of 2 MB applies. See /v1/docs/en/jdbc-fetch-size#bekannte-einschränkung-2mbhttplimit.

Configuration via the bridge JVM property (primary)

-Dde.lobster.remoteJdbc.fetchSize=<N>

Set in the scope of the JVM arguments in the ./bin/bridge.json file of the bridge installation. This single property controls both levels simultaneously:

HTTP batch size (bridge to platform): The bridge uses N as the frame size, thereby overriding the value requested by the platform.
DB cursor size (bridge to database): Before reading, the bridge calls ResultSet.setFetchSize(N) to adjust the internal prefetch of the JDBC driver accordingly. This is a hint; some drivers ignore it (see /v1/docs/en/jdbc-fetch-size#datenbankspezifisches-verhalten-von-resultsetsetfetchsize).

If the property is missing, the bridge uses the batch size requested by the platform (default 500) and does not call setFetchSize().

Known network throttling: 2 MB HTTP limit

The HTTP client of the RemoteJdbcDriver (Jetty) has a fixed response buffer of 2 MB.

Exceeding this limit generates the following message (see Control Center > Logs > Messages per profile):

java.lang.IllegalArgumentException: Buffering capacity 2097152 exceeded

At the default value of 500 rows, the 2 MB limit allows for an average of about 4 KB per row (including protobuf overhead). For connections with wide rows or LOB columns, the value for N is between 100–200.

If a single response exceeds 1.7 MB, the bridge writes a WARN message to the bridge log, at most once every 10 minutes. Since the warning threshold is below the limit, the WARN message in the bridge log precedes the client-side error.

WARN  de.lobster.remote.RemoteJdbcServlet - SYSTEM:DATAWIZARD:TUNNEL response size 1823456 bytes for alias 'mydb' approaches the 2 MB Jetty client limit — reduce -Dde.lobster.remoteJdbc.fetchSize to avoid 'Buffering capacity 2097152 exceeded'

Database-specific behavior of ResultSet.setFetchSize()

setFetchSize() is non-binding (a JDBC hint). The driver may follow or ignore the value. Examples:

Database	Behavior
PostgreSQL	Ignored with `autocommit=true` (platform default): all rows are loaded into the bridge heap at once; no server-side cursor. Only takes effect with `autocommit=false`.
MySQL/MariaDB	Positive values buffer all rows in the heap first. True streaming requires `Integer.MIN_VALUE` and `useCursorFetch=true`. All rows reside in the bridge heap regardless of the fetch size set.
SQL Server	Adaptive buffering (default): large result sets are streamed automatically; the fetch size has little effect in this case. With `selectMethod=cursor`, the fetch size is taken into account.
Oracle	Default prefetch: 10 rows. `setFetchSize(N)` is well respected – 500 significantly reduces DB roundtrips. Wide rows or LOBs can exceed the 2 MB limit at 500 rows; reduce further if needed.
H2/HSQLDB	Usually ignored; all rows are retrieved at once.

Additional parameters

NOTE:
Configuration in ./bin/bridge.json is the recommended approach. The property can be set independently per bridge instance, and the change takes effect as soon as the bridge is restarted. Restarting the platform is significantly more disruptive.

Platform JVM property (Via Bridge (Standard))

-Dde.lobster.remoteJdbc.fetchSize=<N>

Set in the scope of the JVM arguments in the ./bin/platform.json file of the platform. Controls only the HTTP batch size, i.e., how many rows the platform requests per response. The bridge uses this value if it has not set its own property. The DB cursor on the bridge side is not affected.

The JDBC URL additionally accepts a connection-related parameter fetch_size=N. However, the platform generates the URL automatically; it is not editable by users.

Bridge JVM property (Via Bridge (Legacy))

-Dde.lobster.tunnel.fetchSize=<N>

Set in the scope of the JVM arguments in the ./bin/bridge.json file of the bridge installation. Applies only to the older TunnelDriver, i.e., for connections in Via Bridge (Legacy) mode. Calls ResultSet.setFetchSize(N) before materializing the result set and only affects the DB cursor. The complete result set is still sent in a single HTTP response; this property does not limit the HTTP response size.

Recommendations

Scenario	Recommended action
Standard installation, no known issues	No configuration needed.
Via Bridge (Standard) – 2 MB limit is reached	Set `-Dde.lobster.remoteJdbc.fetchSize=200` in `./bin/bridge.json`.
Via Bridge (Standard) – narrow rows, many roundtrips	Increase `-Dde.lobster.remoteJdbc.fetchSize` in `./bin/bridge.json` (e.g., 1000–2000) to improve throughput.
Via Bridge (Standard) – Oracle with many DB roundtrips	Set `-Dde.lobster.remoteJdbc.fetchSize=500` in `./bin/bridge.json` (increases cursor prefetch; observe the 2 MB limit).
Via Bridge (Standard) – PostgreSQL/MySQL with large result sets	Fetch size tuning does not protect the bridge heap (autocommit buffers all rows). Instead, filter queries or limit result sets.
Via Bridge (Legacy) – Oracle with many DB roundtrips	Set `-Dde.lobster.tunnel.fetchSize=500` in `./bin/bridge.json`.
Via Bridge (Legacy) – PostgreSQL/MySQL	Fetch size tuning has no effect; all rows are buffered.