The Arrow Scan Rewrite: How GizmoSQL v1.37.0 Made Bulk Ingest 7x Faster

Early in my career, "on call" was the worst. Connectivity was slow — a modem, a phone line, and a lot of coffee. I'd remote in to fix whatever had broken, and the connection itself was half the job: dial, wait for the handshake, wait again for anything useful to render on the monitor.

Then broadband happened, and something changed that wasn't just "faster." Work that used to justify stepping away from the desk became fast enough that you stopped noticing it was happening at all. It changed the way I worked.

When I sat down to benchmark GizmoSQL's new bulk ingest path, that memory kept coming back. Look at what v1.37.0 does to loading TPC-H lineitem at scale factor 2: 36 seconds down to 5. Long enough to glance away, check something else, come back — versus just… done. It's not a bigger number on a benchmark chart. It's the difference between waiting on your database and not waiting on it at all.

Release at a Glance

36.0s → 5.0s loading TPC-H lineitem SF2 (11,997,996 rows, 16 columns, 50,000-row batches, Python ADBC over plaintext gRPC on localhost, fresh database, mode="create"). Same row count, same sum(l_quantity) checksum, on both runs. 7.2× faster wall-clock, 0.33 → 2.39 M rows/s.

What DoPut Used to Do

Every ADBC adbc_ingest() call and every Flight SQL bulk load ends up at the same place on the server: DoPut(CommandStatementIngest). Through v1.36, that handler earned its keep the hard way. For each incoming Arrow RecordBatch, it walked every row and every column, ran the cell's Arrow type through a seventeen-case switch, built a duckdb::Value by hand, and pushed it through a duckdb::Appender. Every cell got its own small handshake — negotiate the type, then hand the value across, one at a time.

That switch statement had to independently reimplement conversions DuckDB already owns elsewhere in its own codebase: epoch math for DATE32/DATE64, unit-aware scaling for TIME32/TIME64/TIMESTAMP, and recursive descent through LIST, STRUCT, and MAP. Decimals didn't fare well:

// -------- DECIMAL128 -> DuckDB DECIMAL --------
case arrow::Type::DECIMAL128: {
  auto typed = std::static_pointer_cast<arrow::Decimal128Array>(arr);
  std::string s = typed->FormatValue(row);
  // Parse to double – yes, this loses exactness but is simple + robust
  double d = std::stod(s);
  return duckdb::Value::DOUBLE(d);
}

A financial DECIMAL(18,2) column went in exact and came out as a rounded double. And append mode carried its own workaround: since the Appender bypasses the query planner, there was no way to get BY NAME default-filling for columns the client didn't send, so the server created a scratch <table>_interim_bulk_ingest_temp_<session> table, appended into that, then ran INSERT INTO target BY NAME SELECT * FROM interim and dropped it — a second table and a second pass, just to get defaults right, and only in one of three ingest modes.

None of this code was wrong, exactly — it was bespoke. Every Arrow type DuckDB's own Arrow reader already understood had to be re-taught to this one code path, and kept in sync by hand as Arrow and DuckDB both moved.

What v1.37.0 Does Instead: Stop Translating, Start Scanning

If the old path was dial-up — negotiate, then trickle each cell across one at a time — this is the broadband line: negotiate once, register the whole stream, and let the other end pull as fast as it can. DuckDB already has a vectorized, well-exercised Arrow importer: arrow_scan, the table function backing every duckdb.from_arrow() call and Arrow-file read. v1.37.0's fix is to stop competing with it and start using it. The incoming Flight stream is wrapped once as a real arrow::RecordBatchReader, exported over the Arrow C stream interface, and registered as a session-temporary view. Loading a batch becomes one statement, in every mode:

ArrowIngestStream ingest_stream(reader, arrow_schema);
const std::string ingest_view =
    "__gizmosql_ingest_" + strip_dashes(client_session->session_id);
ARROW_RETURN_NOT_OK(ingest_stream.RegisterView(conn, ingest_view));

const std::string insert_sql =
    "INSERT INTO " + target_table +
    " BY NAME SELECT * FROM " + QuoteIdent(ingest_view);
ARROW_ASSIGN_OR_RAISE(auto insert_res,
                       RunAndLogQueryWithResult(client_session, insert_sql));
total_rows = insert_res->GetValue(0, 0).GetValue<int64_t>();

if (total_rows != ingest_stream.total_rows())
  return Status::Invalid("row count mismatch: inserted vs. streamed");

BY NAME now fills in default column values in every mode, not just append — the interim table is gone entirely, along with the second pass it required. Nested types, decimals, timezone-aware timestamps, and dictionary encodings all convert through the exact same vectorized path DuckDB uses everywhere else, instead of a parallel implementation that had to track it. And because the row count DuckDB reports back from the INSERT is checked against what FlightIngestBatchReader actually counted off the wire, a silent partial load is caught immediately rather than discovered later.

The view is registered against a stream object that lives on the call stack, so it can't outlive the request — a small RAII guard drops it on every exit path, success or exception:

struct ViewDropper {
  duckdb::Connection& conn;
  std::string name;
  ~ViewDropper() {
    try { conn.Query("DROP VIEW IF EXISTS " + QuoteIdent(name)); }
    catch (...) {}
  }
} view_dropper{conn, ingest_view};

Same source, same destination — four steps instead of six

BEFORE · ≤v1.36.1                          AFTER · v1.37.0
────────────────────────────           ────────────────────────────
Flight DoPut stream (batches)          Flight DoPut stream (batches)
        │ reader->Next(), per batch            │ wrapped once as RecordBatchReader
        ▼                                       ▼
chunk loop, one batch at a time        ArrowIngestStream
        │ per row × per column                  │ (FlightIngestBatchReader)
        ▼                                        │ conn.TableFunction("arrow_scan")
ConvertArrowCellToDuckDBValue()                  ▼
  17-case type switch, hand-rolled       session-temp VIEW
  decimal / date / time math             __gizmosql_ingest_<session>
        │ Appender.Append(), per cell            │ one query, every mode
        ▼                                        ▼
duckdb::Appender                        INSERT INTO target BY NAME
  ├─ create/replace: straight in          SELECT * FROM view
  └─ append: scratch *_interim_temp               │ verify: rows inserted ==
       │ 2nd INSERT ... BY NAME, DROP             │ rows streamed by client
        ▼                                        ▼
target table                            target table

geoarrow.* columns are materialized as GEOMETRY at the same point everything else converts — more on that below.

Geometry Columns Land as GEOMETRY, Finally

GeoPandas — and anything else that speaks GeoArrow — tags a geometry column's Arrow field with an ARROW:extension:name metadata key like geoarrow.wkb. It's exactly what GizmoSQL's own GEOMETRY columns emit on the way out. On the way in, through v1.36, nothing looked at that metadata: GetDuckDBTypeFromArrowType() saw an Arrow binary array and nothing else, so a new table got a BLOB column, and appending geometry into an existing GEOMETRY column failed outright on a blob→geometry cast it had no way to perform.

The fix is a four-line metadata check, applied at table-creation time and honored automatically once ingest goes through arrow_scan, which already knows how to materialize GeoArrow extension types as GEOMETRY when the spatial extension is loaded:

bool IsGeoArrowField(const arrow::Field& field) {
  const auto& metadata = field.metadata();
  if (!metadata) return false;
  const int idx = metadata->FindKey("ARROW:extension:name");
  return idx >= 0 && metadata->value(idx).rfind("geoarrow.", 0) == 0;
}

Because the check lives on the server, it fixes the behavior for every client — JDBC, Go, C++, Python ADBC, the dbc CLI — not just the one-off client-side workaround shipped in adbc-driver-gizmosql 2.0.1, which this server version makes redundant. A new regression test (tests/integration/test_geoarrow.cpp) ingests WKB points into a fresh table (create), appends more into the same GEOMETRY column (append), then replaces the table outright (replace) — asserting typeof(geom) = 'GEOMETRY' and round-tripping every batch through ST_AsText to confirm the coordinates survived the trip.

The Bottleneck Moved: gRPC's Message Cap

With server-side conversion no longer the slow part, the client's Parquet decode and the gRPC transfer itself now dominate. That surfaces a limit that was always there but rarely hit: each Arrow batch travels as one Flight message, and gRPC's default cap is 16 MB. Even a broadband line has a frame size it won't exceed — this is GizmoSQL's. A batch of 1,000,000 TPC-H lineitem rows runs about 170 MB and gets rejected outright: trying to send message larger than max.

1with conn.cursor() as cur:
2    cur.adbc_ingest(
3        table_name="lineitem",
4        data=record_batch_reader,   # 50,000-row batches: comfortably
5                                     # under the 16 MB gRPC message cap
6        mode="create",
7    )

docs/bulk_ingestion.md now says this in as many words: 50,000–100,000 rows per batch is a safe default for typical tables. Widen that if your rows are unusually narrow, tighten it if they're unusually wide.

By the Numbers: What the Diff Actually Touched

The net line count barely moves — six lines shorter, total. What moved is where the complexity lives: duckdb_server.cpp shed the entire hand-written conversion switch and the interim-table dance, replaced by a call into two small, single-purpose files.

The code didn't get smaller because someone found a bigger pipe. It got smaller because the server stopped building its own modem.

Prove It Yourself

Numbers on a page are still just a story with a chart attached. So alongside this article there's a small, real project — bulk-ingest-demo — that runs the exact driver this piece is about against two live GizmoSQL servers at once: v1.36.1, the last release before this rewrite, and the current one, each on its own port.

# 1. start both servers, side by side
docker run --name gizmosql-v1361 --detach --rm --publish 31338:31337 \
    --env TLS_ENABLED=1 --env GIZMOSQL_PASSWORD=gizmosql_password \
    gizmodata/gizmosql:v1.36.1                    # before, port 31338

docker run --name gizmosql --detach --rm --publish 31337:31337 \
    --env TLS_ENABLED=1 --env GIZMOSQL_PASSWORD=gizmosql_password \
    gizmodata/gizmosql:latest                     # after,  port 31337

# 2. install the driver this article is actually about
pip install -r requirements.txt

# 3. the article's actual benchmark shape, against both
python bench_lineitem.py --scale-factor 1 --port 31338   # before
python bench_lineitem.py --scale-factor 1 --port 31337   # after

# 4. see the gRPC cap for yourself: force one giant batch
python bench_ingest.py --rows 2000000 --batch-size 0 --port 31337
#    -> trying to send message larger than max (16 MB gRPC cap)

Run just now, on this machine, at TPC-H lineitem scale factor 1 (6,001,215 rows): v1.36.1 loaded it in 9.0s, the current server in 1.1s — 8.2× faster, identical sum(l_quantity) checksum on both. That's not a number copied from a slide; it's a number from actually running it, which is the point.

A caveat, found by running it: try a narrower table the same way, against both ports, and the gap mostly closes — at 5,000,000 rows the two servers land within noise of each other. The old path's cost scales with column count and decimal conversions, and lineitem has both, in quantity. The headline number here is a lineitem-shaped number, not an every-table one.

v1.37.0 Changelog — 2026-08-24

• Changed: Bulk ingest (Flight SQL DoPut / ADBC adbc_ingest) is now streamed through DuckDB's Arrow scanner — a single INSERT INTO target BY NAME SELECT * FROM view over a registered arrow_scan, replacing per-cell duckdb::Value conversion and the interim append-mode table.
• Fixed: Geometry columns now bulk-ingest as GEOMETRY, not BLOB, server-side (adbc-driver-gizmosql#5) — for every client, in create, append, and replace mode.

Still the same trade, thirty years on: a connection that gets out of the way changes the way you work.

---

The benchmark project behind this article — bench_lineitem.py, bench_ingest.py, and instructions for running both GizmoSQL versions side by side — is available on GitHub.

GizmoSQL: github.com/gizmodata/gizmosql · commit b264f7c