Back to Devoured - May 07, 2026
DEVOURED
How We Accelerated Transpilation by Compiling SQLGlot with mypyc

How We Accelerated Transpilation by Compiling SQLGlot with mypyc

Data databaseperformanceopensourcepythonsql
Fivetran compiled SQLGlot (the pure-Python SQL parser supporting 34 dialects) into C extensions using mypyc, achieving 5x faster parsing while shipping both compiled and pure Python versions in parallel.
What: Fivetran accelerated SQLGlot by compiling it with mypyc, a tool that converts type-annotated Python to C extensions. They ship the compiled version as an optional package (pip install "sqlglot[c]") with 5x faster parsing, 2.5x faster SQL generation, and 2-2.5x faster optimization. The team fixed 6 mypyc compiler bugs including a memory exhaustion issue with large modules (SQLGlot has ~950 expression classes in one file), dictionary comprehension crashes, and broken property vtables. Evangelos Danias from Fivetran published the detailed implementation on May 1, 2026.
Why it matters: This demonstrates a viable middle path between pure Python and compiled extensions that previous approaches (Rust bindings, Cython, PyPy) couldn't provide. By contributing fixes upstream to mypyc rather than maintaining workarounds, Fivetran improved the entire Python ecosystem's ability to get C-level performance from well-typed Python code without abandoning compatibility.
Takeaway: If you use SQLGlot at scale for parsing millions of queries, install the compiled version with pip install "sqlglot[c]". If you maintain a heavily-typed, CPU-bound Python library, try mypyc on your hottest modules (start with the most frequently called code).
Deep dive
  • Fivetran ships SQLGlot in two forms: pure Python (default) and optional C extensions (sqlglotc package) that users install with pip install "sqlglot[c]", auto-loading the compiled modules when available
  • Previously tried Rust with sqlglotrs tokenizer for over a year but abandoned it due to separate build/test pipelines, versioning headaches, and the need for Rust expertise on the team
  • Rejected Cython (requires Cython-specific syntax) and PyPy (different runtime, non-starter for production) in favor of mypyc which works with pure Python source code
  • Uses cibuildwheel to build pre-compiled wheels for Python 3.9-3.14 on Linux, macOS, and Windows, eliminating the Rust cross-compilation problems
  • Compiles 100+ modules including tokenizer, parser, generator, all 950 expression AST classes, 33+ dialect parsers, 32+ dialect generators, schema module, and critical optimizer passes (type annotation, simplification)
  • Deliberately leaves some modules as pure Python: less-used optimizer passes and the executor, since they change frequently and don't run often enough to justify compilation overhead
  • Found and fixed 6 mypyc compiler bugs: memory exhaustion on modules with 950 classes due to redundant instruction processing, dictionary comprehensions with lambdas crashing, method resolution breaking with deep inheritance, property getter/setter vtable mixups, class variable initialization ordering, and init_subclass running before constants were set
  • Made extensive source code adaptations: added ClassVar annotations to 100+ class-level dictionaries, replaced metaclasses with init_subclass, converted lazy imports to qualified access, moved instance variables into init, and fixed type annotations that mypyc's strict runtime caught as incorrect
  • Optimized parser hot loop by replacing Optional[Token] with a sentinel token (special TokenType.SENTINEL where bool returns False), eliminating None checks that added overhead in compiled code
  • Used i64 type annotation for parser index fields to use native 64-bit integers instead of Python's arbitrary-precision integers, compounding gains across millions of increment/compare operations
  • Inlined hot paths by replacing generator-based tree traversal (expression.walk()) with direct loops, achieving 1.8x speedup on scope analysis, and adding fast paths for common token patterns like column references
  • Replaced generator's string concatenation dispatch (expression.key + "_sql" with getattr()) with pre-built dispatch dictionary mapping expression types to generation methods, yielding 6-23% speedup
  • Contributed 5 string operation primitives to mypyc: str.isspace() (1.3x faster), str.isalnum() (3.2x faster), str.isdigit() (3.5x faster), str.lower()/upper() (2.6x faster), and ASCII character caching for str[i] indexing (3.9x faster by reusing CPython's internal cache)
  • Compiled classes have constraints that matter: can't monkey-patch, can't add runtime attributes (no dict), subclassing requires special decorator, functions don't expose code for inspection
  • Runs full test suite in both pure Python and compiled modes, treating any divergence as a bug
  • Benchmarks against diverse query shapes: TPC-H queries, 20,000-item IN clauses, 500 levels of nested arithmetic, 200 JOINs, 500 UNIONs, 1,000-branch CASE statements to ensure consistent speedups
Decoder
  • mypyc: A transpiler built on mypy that converts type-annotated Python code into C extension modules, using type hints to generate efficient C code that bypasses Python's dynamic dispatch for operations like attribute lookups and method calls
  • cibuildwheel: Tool for building pre-compiled Python wheels across multiple platforms (Linux, macOS, Windows) and Python versions without manual cross-compilation setup
  • SQLGlot: Pure-Python SQL parser, transpiler, and optimizer supporting 34 dialects with zero dependencies, powers projects like SQLMesh and Apache Superset
  • TPC-H: Industry-standard database performance benchmark consisting of complex analytical queries used to test optimization and generation performance
  • sentinel pattern: Using a special marker value instead of None to avoid Optional type overhead, where the sentinel has bool returning False so if checks still work naturally
  • vtable: Virtual method table used in compiled languages to dispatch method calls on objects, which mypyc generates for Python classes
Original article

Fivetran dramatically accelerated SQLGlot (the popular pure-Python SQL parser, transpiler, and optimizer) by compiling it with mypyc, a tool that turns well-typed Python code into fast C extensions. They ship the compiled version as an optional package that delivers ~5x faster parsing, ~2.5x faster SQL generation, and 2-2.5x faster optimization, while keeping the original pure-Python version as the default for maximum compatibility.

Explore all 73 articles in today's digest

Fresh dev news, AI-digested daily. Never miss a trend at devoured.fyi