Python driver: lower latency under concurrent use — wanted upstream?

[zahariash]

Hello!

Thanks for keeping this db alive and kicking :)

Goal. My use case is an asyncio service with mostly light 1–20 ms queries and some large exports to polars or occasional longer query. On main, one export or one big parameterized compile stalls every other Python thread, including the event loop, because the driver holds the GIL there. I have changes that fix this and want to ask whether they are wanted upstream before opening PRs.

The changes.

• Release the GIL while preparing a parameterized statement (parse, bind, plan).
• Release the GIL while building Arrow batches for get_as_arrow() / get_as_pl().
• Release the GIL while get_as_df() reads number, date and string columns.
• Skip the per-element signature walk for homogeneous list parameters such as embeddings.
• Docs: what Connection(num_threads=...) means when several connections run queries at once.

Results (main → branch, default settings; laptop with 16 logical CPUs, 5 rounds; cloud VM with 2 vCPUs, 3 rounds):

	16 logical CPUs	2 vCPUs
Async mix: small-query p95	32.2 → 1.70 ms	66.0 → 5.60 ms
Async mix: event-loop lag p99	23.8 → 0.97 ms	50.4 → 3.94 ms
Async mix: small-query throughput	312 → 2,691 q/s	132 → 1,303 q/s
Other thread's stall while compiling a parameterized query	352 → 2.24 ms	–
Other thread's stall during get_as_pl(), 200k × 4	36.8 → 1.19 ms	–
Heavy LDBC queries (7), alone: geomean	160 → 162 ms	610 → 621 ms

The export in the mix is slower (+12% on 16 cores, +32% on 2 vCPUs: 35.3 → 39.4 ms and 70.3 → 92.5 ms) because it no longer blocks the small queries; LDBC and the heavy queries are within noise. Method: LDBC SNB SF1 (the graph-benchmark-ldbc queries plus 7 heavy ones), synthetic graphs, and an async mix of 3 clients running small parameterized queries while 1 client repeatedly exports 100k rows to polars; interleaved rounds, medians.

Threads per query: "auto" (work in progress, not proposed yet). AsyncConnection(max_threads_per_query="auto") sets each query's thread limit at start: the database's threads divided among the queries then running, at least 2, all when it starts alone. It replaces an earlier static even split, which slowed heavy queries running alone by 50–100%. The default stays 0.

16 logical CPUs	default → "auto"
LDBC, 30 queries, 4 in flight: throughput, p95	129 → 151 q/s, 180 → 67 ms
Async mix: small-query p99	9.9 → 3.9 ms
Synthetic one-hop aggregate, 4 in flight	484 → 1,012 q/s
Heavy LDBC alone, geomean (LDBC one at a time: unchanged)	162 → 161 ms

Costs: 8 in flight on 4 connections, p95 +19%; 4 heavy in flight, p50 +11–20% at equal throughput. On 2 vCPUs (minimum 2) it behaves like the default; minimum 1 trades one-hop 4-in-flight +58% and mix p99 15.9 → 7.1 ms for LDBC 4-in-flight p95 +31%. Blocker: a cached parameterized plan keeps aggregate partitions sized by its first run's thread limit; a count(DISTINCT) first run while others run is later 47% slower alone (30% on 2 vCPUs). Sizing them by the database's thread count is an engine change, so "auto" stays opt-in.

Questions.

I wonder:

1. Are any of these wanted?
2. Any concern with releasing the GIL in these paths?
3. Would a load-aware policy like "auto" be wanted, and would that engine change be acceptable?
4. One PR per change, or a batch?

No is a fine answer: a low-latency driver may matter only to me, and I can keep this in a fork.

[adsharma]

Have you tested with 3.15 + free threading? I'm thinking "defer" at this point to figure out how the no-GIL APIs stabilize (or not).

[zahariash]

No, I haven't.
I can't use ft version (often even standard 3.15) because of state of deps support.

I may give it a go with some synthetic tests.

[zahariash]

I ran the synthetic tests on CPython 3.15.0rc3, standard and free-threaded builds, driver main against the branch. On 3.15t in both modes: the default, where importing the driver re-enables the GIL (the module doesn't declare free-threading support), and PYTHON_GIL=0. pyarrow has no 3.15 or 3.15t wheels, so the Arrow/polars export path is untested and the async mix exports to pandas instead. Medians of 3 rounds, main → branch:

	3.12 (GIL)	3.15 (GIL)	3.15t default (GIL re-enabled)	3.15t PYTHON_GIL=0
longest stall in another thread while a parameterized query compiles	393 → 3.6 ms	385 → 3.0 ms	395 → 2.4 ms	2.0 → 1.8 ms
gc.collect() p50 in a thread while another compiles	3.2 → 3.0 ms	3.9 → 4.0 ms	3.3 → 3.5 ms	123 → 3.4 ms
async mix: small-query p95	26.7 → 8.1 ms	26.8 → 7.3 ms	47.1 → 7.6 ms	2.1 → 1.9 ms
async mix: event-loop lag p99	25.1 → 7.2 ms	26.0 → 7.8 ms	55.7 → 7.0 ms	14.4 → 5.3 ms
async mix: export p50	68.4 → 62.5 ms	59.7 → 61.8 ms	83.3 → 60.5 ms	127 → 56.7 ms

Standard 3.15, and free-threaded 3.15t by default, behave like 3.12 and get the same gains, since the driver re-enables the GIL on import. With PYTHON_GIL=0 the plain stall is gone on main too, but a long native call that stays attached to its thread state still blocks stop-the-world GC and the event loop, so the GIL releases become thread-state detaches and are still needed there. The test suite passes in all modes, apart from the tests that need pyarrow or the other missing wheels, including the cancellation, close()-in-flight, UDF re-entrancy, timeout and Ctrl-C tests with the GIL off.

With PYTHON_GIL=0, concurrent use already crashed intermittently on main. The cause is the driver's import cache, the Python objects it loads on first use: it was filled lazily without synchronization, relying on the GIL, so first calls from several threads at once corrupted the heap. That is not related to these changes, although the Ctrl-C guard in one of the open bug-fix PRs touches the cache on every call and made it show up more often. The fix loads each entry once under a lock, taken with the GIL released; the fix is in LadybugDB/ladybug-python#69. With it, the same runs give 0 crashes: the async mix went from 10 of 180 runs on main to 0 of 60 with the fix, and stays at 0 of 60 with the open PRs and with the full branch on top. The fix does not declare free-threading support; that still needs its own audit.

[adsharma]

Prefer this incantation:

  // instead of py::gil_scoped_release sprinkled inside the body:
  .def("execute", &PyConnection::execute,
       py::call_guard<py::gil_scoped_release>())

Let's do it for the first 3 places you called out.

[adsharma]

Unless you're very invested in the python eco-system, there is another path to using ladybug in a high concurrency env:

https://github.com/LadybugDB/ladybug-adbc-rs