SQLite Extension in Practice: C vs Rust Performance Comparison for parse_number

Starting from a Simple Idea

While walking his dog, Simon Willison had a thought: how convenient would it be to add a parse_number() function to SQLite that converts strings like -$1,234.56 or 12.5% directly into numeric values that can be summed and sorted? This seemingly simple need turned out to be a perfect entry point for observing the performance and complexity of SQLite extensions implemented in different languages.

He ran the experiment with Claude chat (Opus 5.5), first having the AI write extensions in C and Rust respectively, then iterating on performance bottlenecks, and finally compiling the process into a reproducible research report. The exploration path is clear: prototype → measure → optimize → review, offering a reference framework for everyday tool selection decisions.

A SQLite extension (loadable extension) is a dynamic library mechanism officially supported by SQLite since version 3.9.0: developers can compile a shared library (.so on Linux, .dylib on macOS, .dll on Windows) and load it in a SQLite session with SELECT load_extension('path/to/ext'), thereby registering new scalar functions, aggregate functions, collations, or even virtual tables into the SQL language. Extensions register callbacks via C APIs such as sqlite3_create_function, and SQLite invokes these callbacks during SQL execution to perform computation. The parse_number() in this article is exactly such a registered scalar function. Because extensions interact with the SQLite core through the C ABI, C is naturally the most direct implementation language; other languages require FFI wrapper layers (such as Rusqlite, sqlite-loadable-rs) to expose the same interface, which is one important source of the size differences.

Round One: A Stark Contrast in Code Size and Performance

The initial implementation offered an intuitive comparison: the shared library produced by the Rust version was far larger than the C version. The extension based on Rusqlite was 321 KB, the version using sqlite-loadable-rs was 298 KB, while the C extension was only 14 KB—a gap of nearly an order of magnitude.

But the performance tests showed a reversal: the Rust extension was actually faster. The reason isn't mysterious—the parsing function in the Rust standard library equivalent to C's strtod is itself about twice as fast. This exposes a common misconception: small size doesn't equal good performance; the implementation quality of the underlying parsing function is the key variable.

strtod is the C standard library function that parses a string into a double (string to double), declared in <stdlib.h>. It must handle signs, leading whitespace, decimal and scientific notation, inf/nan, localized decimal points, and many other edge cases, while conforming to C standard and IEEE 754 rounding requirements, so its implementation contains numerous branches and validation logic. The scenario faced by parse_number() is much narrower—it only needs to strip currency symbols, thousands separators, and percent signs, then parse a number of limited format; no support for hexadecimal floats, extremely long significands, or locale differences is required. Because the input domain is greatly narrowed, a custom parser can skip the overhead strtod pays for generality, which is the fundamental reason the new C implementation described below can cut decimal parsing from 106 ns to 18 ns.

Round Two: Letting C Catch Up and Overtake

Since the bottleneck was in number parsing itself, Simon had Claude try writing a C parsing function by hand, with the goal of matching or surpassing the Rust implementation. The results were quite significant: the per-row net overhead (lower is better) on 2 million rows of data is compared below:

Data Type	First C	New C	rusqlite	sqlite-loadable
Mixed data	67 ns	19 ns	46 ns	41 ns
Pure integers	25 ns	16 ns	36 ns	32 ns
Pure decimals	106 ns	18 ns	55 ns	47 ns

The new C implementation took the lead across all three data types, with the most striking improvement being decimal parsing dropping from 106 ns to 18 ns. This shows that standard library functions are not always the optimal solution—customizing parsing logic for a specific format (stripping signs, removing thousands separators, handling percent signs) can bypass the extra branching and validation overhead of general-purpose functions.

Trade-offs Beyond Performance: Complexity and Maintainability

Looking at the numbers alone, the C approach wins on both size and speed. But the code complexity account can't be judged by runtime alone. The Rust version, via Rusqlite or sqlite-loadable-rs, has clearer memory safety boundaries and more structured error handling; whereas a custom C parser pursuing extreme performance must manually manage buffers and handle boundary conditions, increasing maintenance and audit costs.

For this kind of pure string conversion function, the input domain is relatively controllable and the logic is self-contained, so C's risk remains within acceptable bounds. But if the extension grows to involve dynamic memory allocation, concurrency, or complex state machines, Rust's safety guarantees become significantly more valuable. When choosing, performance benchmarks are only one dimension of the decision; the team's language stack and long-term maintenance costs matter equally.

Rusqlite is a relatively mature SQLite binding library in the Rust ecosystem, wrapping APIs for connections, statements, transactions, and user-defined functions, but it is mainly aimed at using SQLite within Rust programs rather than specifically for writing loadable extensions. sqlite-loadable-rs has a different positioning: it is based on the loadable extension API of sqlite3ext.h, providing macros and type wrappers that let developers register functions in a way close to native extensions, while handling entry point symbols, memory allocators, and other details. Both statically link part of the Rust standard library or runtime into the artifact, which explains the several-hundred-KB size; a C extension, by contrast, can rely directly on libc symbols already loaded by the host SQLite, so the artifact is naturally much smaller. Understanding this layer of difference is what allows "size" and "performance" to be compared on the correct dimensions.

Workflow Insights from AI-Assisted Development

This experiment also demonstrated an efficient mode of AI collaboration: first have the model produce a runnable prototype, use real benchmark data to expose problems, then issue optimization instructions targeting specific bottlenecks, and finally have the model organize the process into a structured report. Each step has clear acceptance criteria, avoiding blind trust in AI output.

Starting from the small parse_number() function, what we get is not just a set of C vs Rust comparison data, but a reusable methodology: measurement-driven optimization, comparison-driven trade-off clarification. For developers who frequently write SQLite extensions or make low-level tooling decisions, this research report is worth a read.

Project address: simonw/research - sqlite-parse-number-c-vs-rust

One-line Summary

Simon Willison uses the parse_number() extension to compare C and Rust in size, performance, and maintenance cost.

Paragraph Summary

The article documents Simon Willison's experiment using the addition of a parse_number() function to SQLite as an entry point to compare C and Rust for writing SQLite extensions. In the first round, Rust was about 300 KB while C was only 14 KB, but Rust led on performance because its underlying parsing function was faster; next, Claude was asked to hand-write a custom C parser, which on 2 million rows of data surpassed Rust across mixed, integer, and decimal scenarios, cutting decimal parsing from 106 ns to 18 ns. The article further discusses trade-offs in complexity and maintainability, and summarizes an AI-assisted development workflow of "prototype—measure—targeted optimization—review."