Written by the people
who built Opteryx.
Engine internals, query planning, storage, and the occasional field note. No fluff.
How a Python SQL Engine Got to the Top of ClickBench
Opteryx's planner is Python and its execution is native. How we measure performance, A/B test across architectures, try ideas to see if they work, and tune every layer from file layout to cache lines to cost estimates.
Justin Joyce·2026-10-24More posts
Time Travel and Temporal Tables
Opteryx doesn't keep a history table. Every commit is a snapshot, and time travel is choosing which one to read. How a snapshot is built, what its timeline does and doesn't mean, and how to replay the data behind a past decision and compare it with what you know now.
Iceberg Redux
Opteryx's catalog started life as an Apache Iceberg catalog, then grew away from the format. Here's why it diverged, what we had to change to read Iceberg tables again, and how native Avro reading (and now writing) in Rugo made the integration fast.
Avro Deserves Better Than a Dict per Record
Avro is everywhere data moves - Iceberg manifests, Kafka sinks, event pipelines - yet Python's readers all hand it back one dictionary at a time. Here's what Rugo and Opteryx's new Avro reader means if you use Iceberg, keep Avro files in a bucket, or just read Avro in Python.
One Number, Two Jobs
Row-group size in Parquet was doing two jobs for Opteryx - setting how many requests we make to object storage, and setting the size of every morsel the engine works on. We changed the file layout so the two can be set separately.
To Shred or Not to Shred
On JSONBench, Opteryx queries raw NDJSON with no load step and ranks fourth of nine at 100 million documents. Here's what the engines above it do before the first query, and how we get close without it.
Bronze Is the File — A Medallion Pipeline for Our Own Logs
Our platform logs feed billing, auditing and health dashboards. We rebuilt the ingest as a medallion — raw lines in bronze, SQL tasks for silver and gold — and found two bugs that had been losing data in the old one.
Debugging Object Storage Read Failures Without the Egress Bill
A query over a 260 GB table started failing in production. Rather than pay egress to scan it again and again, we reproduced the failure on a laptop with hadro, a small read-only S3 server that can pretend to be a slow network.
IP Addresses Are Just Integers — And That's the Whole Design
Opteryx now has a native IPV4 type, CIDR containment, IP_TRUNC and CIDR_AGG. The type is a uint32 with a label, which is why almost none of the engine had to learn anything.
Replacing a Magic Number
Replacing one hardcoded LIKE selectivity constant with a real estimator.
Monitoring opteryx.app — A Faster Way to Search Web Traffic Logs
We needed to calculate rolling response times for opteryx.app from 1 GB of JSONL logs. Rugo did it in 1 second where pandas took 13 and grep couldn't do the stats at all.
Rugo Ships Standalone — The Work Behind the Second Wheel
Rugo — Opteryx's Parquet/CSV/JSONL file engine — is now publishable as its own PyPI package, built from the same source tree as opteryx_core, with opteryx_core left completely untouched.
A Weekend With RISC-V
We borrowed an Orange Pi, pointed Opteryx at it, and found out what "builds on my Mac" actually assumes — including some SIMD code that had never met a real compiler.
The Last Place Arrow Lived
Opteryx now writes Parquet, CSV and JSONL entirely on its own. Removing the final PyArrow dependency wasn't about cleanliness — it was about owning the part of a query engine that decides how fast tomorrow's reads will be.
From Tree-Walking to Bytecode — Expression Evaluation Redesigned
How Opteryx replaced a recursive expression evaluator with a three-layer bytecode system to remove Python overhead and unlock new optimizations.
A JSONL Reader That Only Reads What You Ask For
How Opteryx's new JSONL reader pushes projections and filters into the document scan itself, and why that changes the cost profile for log queries.
Redesigning the String Header — Prefix or Hash?
Why we deviated from the textbook long-string layout and moved to a hybrid 16-byte header tuned for equality-heavy SQL workloads.
A Toolbox of Hash Structures — Why One Hash Table Isn't Enough
How replacing a single general-purpose hash table with a family of specialised structures — including a direct-addressed bit-array — drove meaningful performance gains across joins, filters, and deduplication in Opteryx.
Five Benchmarks, One Engine
How we run five industry benchmarks to close blind spots exposed by our execution-layer rewrite and measure Opteryx against other engines.
100x Aggregates — making aggregation faster
How we replaced Python-materialised aggregates with native methods and saw 10–100x speedups.
When we Stopped Using Regex for REGEXP_REPLACE
REGEXP_REPLACE dominated query time. Swapping regex engines didn't help. We built a specialised DFA instead.
Rewriting the Memory Model: Moving Beyond Arrow
Why we replaced Arrow in Opteryx to break through a fundamental performance barrier.
Making LIKE Faster: From 93 Seconds to Single Digits
How optimising the LIKE operator — turned a 93-second query into sub-10-second execution through algorithmic improvements and GIL-aware design.
10x Faster Memory Management: Optimising Opteryx's Core Memory Pool
How a targeted change to the MemoryPool implementation produced a 10x improvement by moving metadata into C++ while keeping the Python API unchanged.
Building a specialized hash table to beat Abseil
Our custom hash table achieves faster build times than Abseil; here's what the actual benchmarks show on the probe path.
What If the Docs Wrote Themselves?
Why Opteryx is shifting documentation generation closer to the code.
Rewriting the I/O Stack
The new I/O layer and why it matters.