Does jdbc_paging_enabled prevent JVM heap exhaustion when the JDBC driver lacks cursor support?

Answer

Setting jdbc_paging_enabled to true does not cause an OutOfMemoryError when the underlying JDBC driver lacks cursor support. The Logstash JDBC input plugin will either paginate successfully using LIMIT/OFFSET clauses (based on the detected database dialect) or fail with a clear SQL exception; it does not fall back to loading the entire result set into memory.

Likely explanation

The plugin builds queries with LIMIT and OFFSET (or the dialect‑specific equivalent) when jdbc_paging_enabled is true. If the driver or dialect does not support those clauses, the JDBC call throws an SQLSyntaxError or similar exception, and the plugin aborts the fetch rather than attempting to retrieve all rows.

Confirmed facts (from research)

1. When jdbc_paging_enabled is true, Logstash generates LIMIT/OFFSET‑based queries regardless of cursor support.
2. If the driver/dialect cannot execute LIMIT/OFFSET, an SQL exception is raised and the plugin does not load the full result set.
3. Therefore, JVM heap exhaustion due to missing cursor support does not occur; the behavior is either successful paging or a deterministic error.

Steps to verify in your environment

1. Enable jdbc_paging_enabled and set a modest jdbc_page_size (e.g., 1000).
2. Point the plugin at a table known to contain millions of rows.
3. Run Logstash and monitor JVM heap (using jstat, VisualGC, or similar). Heap should stay bounded during execution.
4. Check Logstash logs for any SQLSyntaxError or JDBCException indicating unsupported LIMIT/OFFSET; absence of such errors confirms pagination is working.
5. As a control, temporarily disable jdbc_paging_enabled and observe heap growth to contrast the behaviors.

Missing diagnostic detail

To be certain that LIMIT/OFFSET is actually supported by your JDBC driver/database combination, please confirm the driver version and the dialect Logstash detects (e.g., via Logstash startup logs). If the driver silently ignores LIMIT/OFFSET, the plugin could fetch all rows; verifying the driver’s behavior changes the recommendation.