Try Opteryx

What is Opteryx

Opteryx is an open-source SQL query engine for analytics. It runs as a Python library — pip install opteryx-core and you have a full SQL engine in your process — and is built to query data where it already lives: Parquet files on object storage, local files, and a range of other sources, without copying it into a warehouse first.

Overview

Opteryx sits in the same conceptual space as DuckDB or Trino: an analytical (OLAP) engine that reads columnar data and runs SQL over it. It is not a database — there is no server, no storage layer of its own, and nothing to administer. You point it at data, hand it a query, and it returns results.

The design target is the kind of workload that used to require spinning up a cluster: tens or hundreds of millions of rows, columnar files in object storage, and ad-hoc analytical SQL. Opteryx aims to make that runnable from a laptop or a single cloud worker, with cold-query performance close enough to a warehouse that the trip to a warehouse often isn't worth it.

Key Characteristics

Performance

The execution engine is vectorised — it processes data in batches of values rather than row-by-row — and the I/O layer is built around the structure of the files it reads. For Parquet on object storage that means scheduling fine-grained byte-range reads against column-chunk metadata and pipelining reads, decompression, and decoding so the engine isn't sitting idle waiting for bytes.

The internal memory model is purpose-built for the engine rather than borrowed from a general-purpose interchange format, which keeps hot-path costs (allocations, copies, format conversions) low.

Flexibility

Opteryx queries data across a range of sources from a single SQL surface:

  • Parquet, JSONL, and Skene (the engine's own native format) as dataset formats; CSV, JSONL, Parquet, Avro and ORC files by path through the READ_* table functions
  • Object storage (Google Cloud Storage, Amazon S3), plain HTTP(S), and local disk
  • In-process Python data structures (lists of dicts, pandas, Polars, PyArrow)
  • A growing set of operational stores accessed through connectors

The same query can join across sources — for example, a Parquet dataset on GCS against a dictionary held in Python — without staging the data anywhere first.

Scalability

Scale here means up, not out. Opteryx is single-node by design: one process, on whatever machine you have. The work to handle larger datasets has gone into making that single process efficient — vectorised operators, predicate and projection pushdown, careful memory management — rather than into a distributed runtime.

In practice that covers a wide band of workloads. When it doesn't, the right answer is usually a different engine, not a bigger Opteryx.

Use Cases

Opteryx is a good fit for:

  • Ad-hoc analysis over datasets that are too large to load into pandas but don't justify a warehouse.
  • Embedded analytics inside Python applications and notebooks, where shipping a SQL engine alongside the app is simpler than calling out to one.
  • Data lake querying — running SQL directly against Parquet on object storage without an intermediate ETL step.
  • Lightweight serving of analytical results from a single process, particularly where startup cost and operational overhead matter.

For a more detailed breakdown of where Opteryx does and doesn't fit, see When to Use Opteryx.

Architecture Overview

A query in Opteryx moves through four broad stages:

  1. Parsing and planning — SQL is parsed into a logical plan describing what to compute.
  2. Optimisation — the plan is rewritten by a series of rules (predicate pushdown, projection pruning, constant folding, and others) to do less work.
  3. Execution — the optimised plan runs as a pipeline of vectorised operators that pass batches of columnar data between stages.
  4. I/O — readers fetch only the column chunks and row groups the query actually needs, in parallel with execution.

Each stage is covered in more detail under Architecture and Core Concepts.

Why Opteryx Is Fast explains how each stage avoids work, with published benchmark results.

What We Guarantee

Before you commit to Opteryx, check where it stops as well as what it does. Compatibility lists the Python versions, platforms and formats verified against the current release. Known Limits covers the architectural gaps, and SQL Conformance shows which parts of standard SQL are supported.