vladich / pg_jitter

A lightweight JIT compilation provider for PostgreSQL that adds three alternative JIT backends - **sljit**, **AsmJit** and **MIR** - delivering faster compilation and competitive query execution across PostgreSQL 14–18. Why? JIT compilation was introduced in Postgres 11 in 2018. It solves a problem of Postgres having to interpret expressions and use inefficient per-row loops in run-time in order to do internal data conversions (so-called tuple deforming). On expression-heavy workloads or just wide tables, it can give a significant performance boost for those operations. However, standard LLVM-based JIT is notoriously slow at compilation. When it takes tens to hundreds of milliseconds, it may be suitable only for very heavy, OLAP-style queries, in some cases. For typical OLTP queries, LLVM's JIT overhead can easily exceed the execution time of the query itself. pg_jitter provides native code generation with microsecond-level compilation times instead of milliseconds, making JIT worthwhile for a much wider range of queries. Performance Typical compilation time: • **sljit**: tens to low hundreds of microseconds • **AsmJIT**: hundreds of microseconds • **MIR**: hundreds of microseconds to single milliseconds • **LLVM (Postgres default)**: tens to hundreds of milliseconds (seconds to tens(!) of seconds in worst cases) In reality, the effect of JIT compilation is broader - execution can slow down for up to ~1ms even for sljit, because of other related things, mostly cold processor cache and effects of increased memory pressure (rapid allocations / deallocations related to code generation and JIT compilation). Therefore, on systems executing a lot of queries per second, it's recommended to avoid JIT compilation for very fast queries such as point lookups or queries processing only a few records. By default, parameter is set to a very high number (100'000). This makes sense for LLVM, but doesn't make sense for faster providers. It's recommended to set this parameter value to something from ~200 to low thousands for pg_jitter (depending on what specific backend you use and your specific workloads). • **sljit** is the most consistent: 5–25% faster than the interpreter across all workloads. This, and also its phenomenal compilation speed, make it the best choice for most scenarios. • **AsmJIT** excels on wide-row/deform-heavy queries (up to 32% faster) thanks to specialized tuple deforming • **MIR** provides solid gains while being the most portable backend • **LLVM** was supposed to be fast at execution time, due to clang optimization advantages, but in fact, in most cases, it's slower than all 3 pg_jitter backends, even not counting compilation performance differences. This is due to zero-cost inlining using compile-time pre-extracted code and manual instruction-level optimization. Benchmarks There are several scripts in the folder to run different types of benchmarks, one of them is tests/bench_comprehensive.sh, another tests/gen_cross_version_benchmarks.py. Here are some results for ARM64 (Apple Silicon M1 Pro) and x86_64 (Ryzen AI 9 HX PRO 370) for different versions of Postgres and different backends. Some of them are pretty interesting, for example the "super wide table" section for both ARM and x86, where LLVM's performance is simply atrocious (10x-30x of the baseline). ARM64 -> PG14 | PG15 | PG16 | PG17 | PG18 * sljit * AsmJit * MIR x86_64 -> PG14 | PG15 | PG16 | PG17 | PG18 * sljit * AsmJit * MIR Features • **Simple-configuration** - set and go • **Three independent backends** with different strengths • **Runtime backend switching** via (no restart) • **PostgreSQL 14–18** support from one codebase • **Two-tier function optimization** - hot-path PG functions compiled as direct native calls • **No LLVM dependency** - pure C/C++ with small, embeddable libraries • **Precompiled function blobs** - optional build-time native code extraction for zero-cost inlining • **Supported platforms** - aside from AsmJit, other providers (in theory) can be used on most platforms supported by Postgres. But **pg_jitter** was only tested on Linux/MacOS (ARM64) and Linux/Windows (x86_64) so far. Testing it on other platforms is planned, but if you had success (or issues) running it, please let me know at vladimir@churyukin.com. Stability The current source code can be considered beta-quality. It passes all standard Postgres regression tests and shows good improvements in performance tests. But it lacks large-scale production verification (yet). Stay tuned. Quick Start Prerequisites • PostgreSQL 14–18 (with development headers) • CMake >= 3.16 • C11 and C++17 compilers • Backend libraries as sibling directories: SLJIT | AsmJit | MIR For MIR, use the patched version from MIR-patched - it has a few changes about tracking the size of the generated native code per function, and per-function memory management. Build Install Configure Configuration All parameters are user-settable ( in session, for persistent) and take effect without a restart unless noted. Backend Selection | Parameter | Type | Default | Description | |-----------|------|---------|-------------| | | enum | (if 2+ backends installed, else the single available one) | Active JIT backend: , , , or . The mode is **experimental** — it uses adaptive statistics to select the fastest backend per expression profile. | Parallel Execution | Parameter | Type | Default | Description | |-----------|------|---------|-------------| | | enum | | Controls JIT in parallel workers. — workers use the PG interpreter. — each worker JIT-compiles independently. — leader compiles once, workers reuse code via DSM (saves compilation time, slightly higher per-row overhead). The mode is **experimental** | | | integer (KB) | (4 MB) | Maximum DSM segment size for shared JIT code. Range: 64 KB – 1 GB. | Expression Tuning | Parameter | Type | Default | Description | |-----------|------|---------|-------------| | | integer | | Minimum expression step count fo…