Arrow as the Wire Format Between Java and Python

Part 2 of the series: Ingesting Infor LN at Speed

In the previous post we established the problem: a Python ingestion pipeline reading very wide Infor LN tables over a Java JDBC driver, where every individual cell crossed the Java/Python boundary on its own. A 45,000-row × 200-column table meant nine million boundary crossings, all of them Python-level work, all of them holding the GIL. Measured throughput was 12,810 rows/min in isolation and around 1,100 rows/min once the batch was running at its configured concurrency.

The diagnosis ruled out the two obvious responses. Making each conversion cheaper had already been done three times over and was insufficient. Doing more of them at once was actively harmful, because the GIL was the contended resource.

There is a third response that people reach for at this point and that I want to dispose of before going further: make the machine bigger. It is the reflex answer to a slow pipeline, and here it would have bought almost nothing. More vCPU gives you more cores. The workload was not short of cores it was short of one thing, an interpreter lock, and there is exactly one of those per process no matter what you provision. We could have doubled the instance size and watched the throughput stay flat while the bill did not. When the bottleneck is a serialisation point rather than a capacity limit, scaling up is a way of spending money to confirm your diagnosis was wrong.

The only real option was to stop touching cells individually. That is a wire format question.

1. The Insight

Apache Arrow is a columnar in-memory format with a specification, and crucially implementations on both sides of this boundary that use the same memory layout. pyarrow and the Java Arrow libraries are not two formats that convert to each other. They are two implementations of one format.

Which means the conversion doesn’t have to happen at the boundary at all.

Instead of pulling rows into Python and converting them there, convert the entire ResultSet into Arrow inside the JVM, where it is fast, compiled, and not holding a Python lock. Serialise one batch of that into an Arrow IPC blob. Hand Python a single byte array. Let pyarrow read it back.

The complexity collapses:

One crossing per 10,000 rows instead of two million. That is the whole idea, and everything that follows is the engineering required to actually make it work in production.

2. The Core Loop

config = (JdbcToArrowConfigBuilder(allocator, utc)
          .setTargetBatchSize(batch_size)
          .setBigDecimalRoundingMode(RoundingMode.HALF_UP)
          .setJdbcToArrowTypeConverter(jpype.JProxy(Function, dict(apply=_convert_type)))
          .build())
it = JdbcToArrow.sqlToArrowVectorIterator(rs, config)
while it.hasNext():
    root = it.next()
    try:
        baos = ByteArrayOutputStream()
        writer = ArrowStreamWriter(root, None, baos)
        writer.start()
        writer.writeBatch()
        writer.end()
        writer.close()
        tbl = pa.ipc.open_stream(_jbytes_to_py(baos.toByteArray())).read_all()
    finally:
        root.close()          # free off-heap buffers before the next batch
    yield tbl

Walking through it:

JdbcToArrow comes from the arrow-jdbc module. sqlToArrowVectorIterator drains the ResultSet into Arrow VectorSchemaRoot batches entirely in Java. Nothing about this step involves Python. The driver’s cell-by-cell reads still happen they have to, that is the JDBC API but they happen in compiled JVM code that is not contending for anything Python owns.

setTargetBatchSize controls how many rows land in each VectorSchemaRoot. This is now the knob that determines your boundary-crossing count, and it trades directly against peak off-heap memory. We kept it aligned with the old fetchmany size for continuity.

ArrowStreamWriter serialises one batch into an in-memory ByteArrayOutputStream using the Arrow IPC stream format. Note the null for the dictionary provider we have no dictionary-encoded columns, and passing one you don’t need is a good way to introduce a subtle bug.

pa.ipc.open_stream(...).read_all() reads the IPC bytes back on the Python side. This is zero-copy in pyarrow: the resulting table’s buffers point into the bytes object rather than duplicating it.

root.close() in a finally. More on this in trap four, but note it now: it is not optional and it is not garbage-collected for you.

The loop still yields pa.table objects, which meant the downstream loader interface did not change at all. That mattered for shipping this incrementally.

3. The Last Mile, Where Half the Gain Hides

There is one line in that block doing more work than it looks like: _jbytes_to_py.

You have a Java byte[]. You need Python bytes. The obvious way to write that conversion is also the way that quietly reintroduces the exact problem you just spent a week eliminating, one level down because a naive conversion iterates the array element by element, and now you are crossing the boundary once per byte instead of once per cell. For a batch of any size that is worse than what you started with.

The fix relies on JPype exposing Java primitive arrays through Python’s buffer protocol:

def _jbytes_to_py(jarr) -> bytes:
    """Bulk-convert a Java byte[] to Python bytes via the buffer protocol
    (one crossing, not per-element)."""
    try:
        return memoryview(jarr).tobytes()
    except Exception:
        return bytes(bytearray(jarr))

memoryview(jarr).tobytes() is a single memcpy. One crossing, regardless of size. The fallback is there for JPype configurations where the buffer protocol isn’t available on that array type, and it should essentially never fire if you find it firing regularly, your throughput numbers are lying to you.

The general principle is worth stating on its own, because it generalises well beyond this stack:

A columnar transfer that ends with a per-element copy is not a columnar transfer.

Bulk-transfer optimisations have a habit of being undone at the seams. You do the hard structural work, get the data into a single contiguous buffer, and then hand it off through an interface that walks it. Check the last mile. It is one line of code and it is worth roughly half your gain.

4. Four Traps on the Way to Production

Everything above is the happy path, and none of it worked first time. Four problems stood between the design and a running pipeline, each with an unhelpful failure mode.

Trap 1 — Version Lockstep is Mandatory

The Arrow JARs and pyarrow must match exactly:

arrow-jdbc-18.1.0.jar   ↔   pyarrow==18.1.0

The IPC format is specified as stable across minor versions, and in theory a small mismatch is harmless. In practice, mismatches surface as opaque deserialisation errors on the Python side errors that point at your data, not at your dependency graph, and which will send you off investigating a perfectly innocent column for an afternoon.

We pin both versions and verify them in-container at deploy time, not just in the lockfile. The JARs and the Python package are installed by different mechanisms into the same image, and there are a distressing number of ways for those two to drift apart without anything failing loudly until runtime.

Trap 2 — Java 17 Seals the Memory Arrow Needs

Arrow’s off-heap allocator reaches into java.nio internals that the Java module system, from 17 onward, seals off by default. Without explicit permission, RootAllocator fails at construction before you have read a single row.

The JVM therefore has to be started by us, with our flags, rather than lazily by JayDeBeApi when it first needs a connection:

jpype.startJVM(
    "--add-opens=java.base/java.nio=ALL-UNNAMED",
    "-Darrow.enable_unsafe_memory_access=true",
    "-Darrow.memory.debug.allocator=false",
    classpath=[infor_jar] + sorted(glob(f"{arrow_jars}/*.jar")),
    convertStrings=True,
)

Two things to notice.

First, explicit JVM startup is now a hard requirement, not a stylistic preference. If any code path can trigger a lazy JVM start before this call, you will get a JVM without --add-opens and every Arrow extraction in the process will fail for the lifetime of that process. Start it once, early, at application init.

Second, the classpath now carries both the Compass driver and the Arrow JARs sixteen of them. There is one JVM in the process and both extraction paths share it. That is what makes the per-table fallback possible: the legacy JDBC path and the Arrow path are not separate runtimes, they are two ways of using the same one.

arrow.memory.debug.allocator=false is worth flagging explicitly. Allocator debugging is genuinely useful while you are getting the lifecycle right, and it is expensive. Turn it on while developing trap four; turn it off before you benchmark.

Trap 3 — Unmapped JDBC Type: 2014

The Compass driver declares its timestamp columns as JDBC type 2014, TIMESTAMP_WITH_TIMEZONE. arrow-jdbc‘s default type converter does not map it and throws.

The fix is to inject a custom converter through a JPype proxy of java.util.function.Function that’s the setJdbcToArrowTypeConverter call in the config builder above:

_TS_WITH_TZ = 2014
def _convert_type(field_info):
    if field_info.getJdbcType() == _TS_WITH_TZ:
        return ArrowType.Timestamp(TimeUnit.MILLISECOND, "UTC")
    return JdbcToArrowUtils.getArrowTypeFromJdbcType(field_info, utc)

Delegate everything you don’t care about back to the default. Only intercept the type that fails.

Now the part that cost real debugging time:

It must be MILLISECOND, not MICROSECOND.

arrow-jdbc‘s timestamp consumer is hardcoded to milliseconds. If you declare MICROSECOND on the vector which is the natural choice, since it is what you actually want downstream and what most warehouses use the type declaration and the consumer disagree, and you get a ClassCastException thrown from deep inside the Arrow write path. The stack trace points at Arrow internals. It does not point at your converter. There is nothing in the message that suggests a precision mismatch.

Declare millisecond precision here and cast up in the Silver layer, where it is a vectorised operation on a whole column rather than a per-value conversion in transit.

Trap 4 — Off-Heap Memory Has No Garbage Collector

Arrow buffers live outside the Java heap and outside Python’s reference counting. Neither runtime’s collector will ever free them. If you do not close them yourself, nothing will.

The discipline this requires:

• Every batch closes its VectorSchemaRoot immediately after serialisation that’s the finally: root.close() in the core loop, and it must be per-batch, not per-table, or peak memory grows with table size.
• Every extraction closes its RootAllocator, the iterator, the ResultSet and the Statement in a finally block.

Miss any of these and you get a slow native-memory leak and here is what makes it genuinely nasty: no Python profiler will show you the leak. The memory isn’t in the Python heap. tracemalloc sees nothing. memory_profiler sees nothing. The Java heap looks fine too, because the buffers are off-heap there as well. What you observe is a container whose RSS climbs steadily across a long-running batch and eventually gets OOM-killed by the orchestrator, with every in-process metric reporting healthy memory right up to the moment it dies.

5. The Architectural Consequence Nobody Planned

The Arrow path yields raw driver types. decimal128 where the old path produced a canonical decimal. timestamp[ms] because of trap three. Source-cased column names, straight out of LN, rather than the normalised names the hand-built schema produced. Sentinel dates LN’s various flavours of “this field is empty” encoded as impossible timestamps passed through untouched.

The old path had fixed all of that in transit, because it was already touching every cell anyway and the marginal cost of also normalising was near zero.

The obvious response was to re-add a normalisation step. And the obvious response was wrong, because normalisation in transit means per-value work, and per-value work is the entire thing we had just spent the project eliminating.

What we did instead:

• Bronze became raw driver output. Whatever the driver gives us, that is what lands in Parquet. No conformance, no renaming, no cleansing.
• Each run writes a _schema.json sidecar next to its Parquet file, describing what that run actually produced.
• Silver’s normalise hook does the conformance at read time, driven by that sidecar conforming types and cleansing sentinel dates as vectorised column operations over an already-materialised Arrow table.

The same logical work happens. It happens in a different place, and in that place it is a vectorised cast over a column instead of a function call per value. The cost difference is not marginal; it is the difference between O(cells) of Python and O(columns) of compiled kernel calls.

The Arrow migration pushed the pipeline further toward genuine ELT, because the cheapest place to do per-value work turned out to be not in transit at all.

There is a version of the medallion architecture that everyone nods along to Bronze is raw, Silver is conformed and a version that teams actually build, where Bronze quietly accumulates “just this one small transformation” because the ingestion code is right there and it is touching the data anyway. We had the second kind. A performance constraint forced us into the first kind, and the architecture is better for reasons that have nothing to do with performance: Bronze is now genuinely reproducible from source, schema drift is recorded per-run rather than silently absorbed, and the conformance rules live in one place where they can be changed without a re-ingest.

6. Where This Leaves Us

The transport problem is solved. Batches cross the boundary in bulk, conversion happens in the JVM, the last mile is a memcpy, and normalisation moved somewhere it costs almost nothing.

For a single table in isolation the improvement is roughly what the complexity analysis predicts the headline figure is 663,000 rows/min against the 12,810 rows/min single-threaded baseline, which is better than fifty times faster, with the full benchmark in the next post.

But recall from post one that isolated throughput was never the number that mattered. The pipeline runs many tables at once, and the whole reason concurrency had been strangling us was a Python-level bottleneck that Arrow has now removed. Which meant the obvious next move was to turn the concurrency back up and collect the rest of the win.

That is when ingestion started failing en masse with an error message that was, in the most literal sense, untrue.

Next in this series: JDBC Error 101: the .ionapi file is invalid (it isn’t) what happened when we turned concurrency back up, and how we shipped the whole thing without a regression.