Performance

The Arrow Path

For anything over ~10K rows, skip fetchall() and use the Arrow path. Data is serialized entirely on the JVM side and deserialized once in Python — instead of one JNI call per cell.

# PyArrow Table — fastest
table = conn.read_sql("SELECT * FROM events", format="arrow")

# Straight to a DataFrame
df = conn.read_sql("SELECT * FROM events", format="pandas")
df = conn.read_sql("SELECT * FROM events", format="polars")
rel = conn.read_sql("SELECT * FROM events", format="duckdb")

# Stream without materializing everything at once
for batch in conn.iter_arrow_batches("SELECT * FROM events", batch_size=50_000):
    process(batch)

How it works: JdbcArrowReader on the JVM side iterates the ResultSet in row groups, writes each group into Arrow IPC format in a byte buffer, and hands the buffer to Python. Python deserializes with pa.ipc.open_stream() — this is near-zero-copy. The result is a complete pyarrow.Table assembled from those batches.

The JNI crossing count drops from rows × columns (row-by-row) to a handful of buffer handoffs regardless of result size.

Batch Size Tuning

batch_size controls how many rows are read per Arrow IPC buffer on the JVM side. The default is 10,000.

# Larger batches — better throughput, more JVM heap per buffer
table = conn.read_sql("SELECT * FROM events", format="arrow", batch_size=100_000)

# Smaller batches — lower memory pressure, start streaming earlier
for batch in conn.iter_arrow_batches("SELECT * FROM events", batch_size=5_000):
    process(batch)

For wide tables (many columns) or tables with large text/blob columns, reduce batch_size to keep per-buffer heap usage reasonable. For narrow numeric tables, larger batches improve throughput.

When to Use fetchall()

Row-by-row fetch is fine for small results. The overhead of the Arrow pipeline (JVM serialization, IPC deserialization) isn't worth it below ~10K rows.

Result size	Recommended path
< ~10K rows	cursor.fetchall()
10K–100K rows	conn.read_sql(format="pandas") or "arrow"
> 100K rows	conn.read_sql(format="arrow")
Very large / can't fit in memory	conn.iter_arrow_batches(batch_size=50_000), or read_sql(..., stream=True)

For very large reads, pass stream=True to read_sql/read_arrow: it runs the read through a server-side cursor (autocommit temporarily off) so the driver streams batches instead of buffering the whole result client-side — instant time-to-first-batch and bounded memory.

Connection Pool

Opening a JDBC connection takes 40–60 ms. For short-lived queries in a service or notebook, that adds up fast. The pool keeps connections warm and handles broken-connection replacement automatically.

from jdbc4py import ConnectionPool

pool = ConnectionPool(
    engine="postgres",
    host="localhost",
    database="mydb",
    user="me",
    password="secret",
    min_size=2,
    max_connections=10,
)

Key parameters:

Parameter	Default	Notes
min_size	1	Connections opened at startup
max_connections	—	Hard cap on total connections
max_size	10	Alias for max_connections
max_idle_seconds	300	Evict idle connections after this long
max_age_seconds	3600	Recycle connections older than this
checkout_timeout	30.0	How long to wait when pool is full
validate_on_checkout	True	Ping before handing out a connection

A background daemon thread handles eviction and replenishes back to min_size. The pool is thread-safe — share it across threads, each borrows a connection and returns it when done.

For web services, set min_size to match your typical concurrent query count. For batch jobs that run briefly, min_size=1 is fine.

Multi-Database Parallelism

jdbc4py connections are independent — no global state beyond the JVM. You can run queries against different databases in parallel using a thread pool:

import concurrent.futures
from jdbc4py import ConnectionPool

pg_pool = ConnectionPool(engine="postgres", host="pg-host", database="analytics", ...)
mysql_pool = ConnectionPool(engine="mysql", host="mysql-host", database="users", ...)

def query(pool, sql):
    with pool.connect() as conn:
        return conn.read_sql(sql, format="pandas")

with concurrent.futures.ThreadPoolExecutor() as ex:
    f1 = ex.submit(query, pg_pool,    "SELECT * FROM orders")
    f2 = ex.submit(query, mysql_pool, "SELECT * FROM users")
    orders_df = f1.result()
    users_df  = f2.result()

The one constraint: all JDBC JARs must be on the classpath before the first connection is opened, because the JVM classpath is frozen at startup. If you're connecting to multiple databases, start the JVM explicitly upfront:

from jdbc4py.jvm import JVMManager

JVMManager.ensure_started(jars=[
    "drivers/postgres/postgresql-42.7.10.jar",
    "drivers/mysql/mysql-connector-j-9.1.0.jar",
])

Prepared Statement Cache

jdbc4py caches prepared statements per connection, keyed by SQL text. Repeated executions of the same query reuse the prepared statement without another server round-trip.

The cache holds up to 128 queries × 4 idle statements per query. When the per-connection query limit is reached it evicts the oldest-inserted bucket (FIFO). The cache survives connection pool checkout/return cycles for the lifetime of the physical connection.

Query Cancellation

Cancel a running query from another thread:

import threading, time
from jdbc4py import connect, QueryCancelledError

conn = connect(engine="postgres", host="localhost", ...)
cursor = conn.cursor()

def run():
    try:
        cursor.execute("SELECT pg_sleep(30)")
    except QueryCancelledError:
        print("cancelled")

t = threading.Thread(target=run, daemon=True)
t.start()
while not cursor.executing:
    time.sleep(0.01)
conn.cancel()
t.join()

Or use run_query_async() for a cleaner interface:

from jdbc4py import run_query_async

with run_query_async(conn, "SELECT pg_sleep(30)") as task:
    try:
        cursor = task.wait(timeout=5.0)
    except TimeoutError:
        task.cancel()

cancel() calls Statement.cancel() on the JDBC driver — the query is interrupted at the database level, not just abandoned on the client side.

Write Performance

The write path uses executemany() with batch_size rows per call (default 10,000). For bulk inserts, larger batches are generally faster:

conn.write_arrow(big_table, "staging", batch_size=50_000)

For if_exists="replace", a DROP + CREATE + INSERT sequence runs. On MySQL and Oracle, DDL auto-commits, so the write is split into two transactions. On PostgreSQL and SQL Server, it's a single transaction.