Optimize PostgreSQL Performance with pg_jitter JIT Compilation

PostgreSQL’s built-in just-in-time (JIT) compilation revolutionized query performance for analytics workloads, but many engineering teams still avoid it on OLTP systems due to the notorious startup overhead of the default LLVM engine. pg_jitter—an alternative JIT provider for Postgres—changes the game with microsecond-level code generation, making JIT acceleration practical even for transactional and mixed workloads. This post details how to deploy, tune, and benchmark pg_jitter for production, using only configuration steps and performance data from primary sources.

Key Takeaways:

You landed the Cloud Storage of the future internet. 

Use it NOW and forever!

Support the growth of a Team File sharing system that works for people in China, USA, Europe, APAC and everywhere else.

• pg_jitter offers sljit, AsmJit, and MIR as alternative JIT backends for PostgreSQL, delivering microsecond-level compilation compared to the millisecond-to-second startup times of LLVM.
• This speedup enables JIT benefits for OLTP and mixed workloads, not just heavy analytics queries.
• Real-world performance depends on tuning cost thresholds, selecting the optimal backend, and profiling with your actual workload.
• All JIT approaches have trade-offs in maturity, compatibility, and ecosystem support—evaluate thoroughly before production use.

Why JIT Matters in Production Postgres

Just-in-time compilation (JIT) in PostgreSQL allows SQL expressions and user-defined functions to be compiled into native code at execution time, significantly speeding up CPU-bound queries. According to the official PostgreSQL documentation, JIT is most effective for long-running, computationally intensive queries—typical in analytics and reporting workloads:

-- Example: JIT not triggered for low-cost queries
EXPLAIN ANALYZE SELECT SUM(relpages) FROM pg_class;
-- Output: No JIT used; execution time is too low for JIT overhead to pay off

JIT activation is governed by settings like jit_above_cost, meaning only queries whose estimated execution cost exceeds the threshold will trigger compilation (Postgres docs). For short or simple queries, the default LLVM engine’s startup latency—often tens to hundreds of milliseconds—can outweigh any benefit. But for complex workloads, JIT can dramatically reduce execution time.

OpenAI’s global-scale deployment of PostgreSQL, handling millions of queries per second for ChatGPT, underscores how every microsecond of query execution matters (InfoQ). Their architectural choices, such as tuning at the application level and offloading write-heavy workloads to sharded systems, demonstrate that aligning database features with workload patterns is critical. For most teams, however, the high cost of LLVM JIT means it’s only enabled for rare OLAP workloads. That’s exactly the bottleneck pg_jitter addresses.

JIT Decision Logic in PostgreSQL

The PostgreSQL planner uses cost-based heuristics—parameters like jit_above_cost, jit_inline_above_cost, and jit_optimize_above_cost—to decide if and when to apply JIT. If a query’s cost falls below these thresholds, or JIT is disabled, the query is interpreted instead. This means you need to tune these settings and validate with your workload to see real benefit. For details, refer to Section 30.2 of the docs.

Real-World Implications

In production, teams often disable JIT for OLTP workloads because LLVM’s compilation latency can eclipse the total query runtime. pg_jitter’s faster compilation allows you to enable JIT for a much wider array of queries, including those that run in microseconds to milliseconds—opening up performance gains that were previously unattainable in transactional databases.

Introducing pg_jitter: Better JIT for Postgres

pg_jitter is an open-source JIT provider supporting PostgreSQL 14–18. It introduces three alternative JIT backends—sljit, AsmJit, and MIR—all engineered to deliver far lower compilation overhead than LLVM. According to the README:

• LLVM (default): Compilation time is “tens to hundreds of milliseconds” (may be several seconds for complex queries).
• pg_jitter (sljit, AsmJit, MIR): “Tens to low hundreds of microseconds.”

This is not a marginal improvement—it’s a step change in overhead, making JIT practical for workloads where LLVM’s latency was previously a dealbreaker.

How pg_jitter Changes the Equation

pg_jitter registers as a pluggable JIT provider, letting you select the backend that best fits your workload. This enables:

• JIT acceleration for smaller, faster queries in OLTP and mixed environments.
• Backend selection (sljit, AsmJit, MIR) for tuning performance or compatibility.
• Support across PostgreSQL versions 14–18, covering most modern deployments.

In the words of the project: “pg_jitter provides native code generation with microsecond-level compilation times instead of milliseconds, making JIT worthwhile for a much wider range of queries.”

Real-World Setup, Configuration, and Examples

Installation

To install pg_jitter, you need PostgreSQL 14–18 and standard build tools (CMake, PostgreSQL dev headers):

# Clone the repository
git clone https://github.com/vladich/pg_jitter.git
cd pg_jitter

# Build and install (requires CMake and PostgreSQL development headers)
./build.sh
sudo ./install.sh

Always check the pg_jitter README for platform-specific notes and updates.

Configuration

Enable JIT and point PostgreSQL to pg_jitter in your postgresql.conf:

# postgresql.conf
jit = on
jit_provider = 'pg_jitter'
jit_backend = 'sljit'         # or 'asmjit', 'mir'
jit_above_cost = 1000         # Lower to enable JIT on more queries
jit_inline_above_cost = 2000
jit_optimize_above_cost = 3000

Restart PostgreSQL and confirm the provider is active.

Example: JIT for Transactional Queries

# Lower jit_above_cost to include more transactional queries
SET jit_above_cost = 500;

EXPLAIN (ANALYZE, VERBOSE) SELECT customer_id, SUM(amount)
FROM transactions
WHERE processed_at > NOW() - INTERVAL '1 day'
GROUP BY customer_id;

-- Look for the 'JIT' section in the plan to verify provider and compilation time

This configuration enables JIT for queries that would be too small or fast for LLVM to benefit. Always use EXPLAIN (ANALYZE, VERBOSE) to check that JIT is active and measure the effect.

Advanced Tuning

• Test different jit_backend options (sljit, asmjit, mir) to see which backend best matches your workload characteristics.
• Monitor compilation and execution times with pg_stat_statements and Postgres JIT statistics views to correlate JIT usage with real-world performance.
• Include cost threshold tuning in your CI/CD release process to ensure JIT continues to deliver value as your schema and query patterns evolve.

Integration in Large-Scale Environments

If you manage mission-critical or regulated workloads, pilot pg_jitter in staging first. Ensure your DBA or SRE teams review upgrade and compatibility processes. For context on scaling strategies at the highest levels, study OpenAI’s Postgres scaling for ChatGPT and how they balanced JIT, sharding, and application-level optimizations.

Considerations, Trade-offs, and Alternatives

Limitations and Caveats

• Workload Sensitivity: JIT delivers the most benefit for CPU-bound, expression-heavy queries. Even pg_jitter’s speedup is unnecessary for very simple queries. Always profile against your own workload.
• Maturity and Compatibility: pg_jitter is newer than LLVM JIT, and while it supports versions 14–18, edge-case incompatibilities (with custom datatypes, extensions, or unusual query patterns) are possible. Validate in a staging environment, especially after upgrades or when using nonstandard extensions.
• Release Lag: PostgreSQL’s development pace means third-party JIT providers may lag behind core releases. Regularly check the pg_jitter repo and include compatibility checks in your upgrade processes.

Alternatives

• LLVM (default): The most mature and widely tested JIT provider for PostgreSQL, preferred for very heavy analytical workloads where compilation cost is negligible compared to total runtime (PostgreSQL docs).
• Disabling JIT: For workloads dominated by trivial, short queries (e.g., microservices), set jit = off for maximal predictability and zero compilation overhead.
• Distributed Architectures: As highlighted in OpenAI’s ChatGPT Postgres scaling, when scaling beyond a single instance, sharding or distributed systems become necessary regardless of JIT settings. JIT is not a substitute for distributed architecture at extreme scale.

For configuration and best practices, see the official documentation.

When Should You Use pg_jitter?

If your database handles CPU-intensive, expression-heavy queries and you want to enable JIT for both analytics and transactional use cases, pg_jitter is worth serious consideration. If your workload is mostly simple queries or relies on many custom extensions, proceed with caution and validate thoroughly.

Common Pitfalls and Pro Tips

• Over-tuning: Setting jit_above_cost too low can trigger JIT for queries that don’t benefit. Always benchmark and review EXPLAIN (ANALYZE, VERBOSE) output.
• Assuming JIT Is Active: JIT may not be used even when enabled—always check the ‘JIT’ section in your query plan for provider and backend details.
• Ignoring Backend Options: While sljit is generally a safe starting point, try asmjit or mir if you encounter performance or compatibility issues. Different backends can yield different results depending on the workload.
• Neglecting Monitoring: Regularly monitor JIT stats and performance over time to catch regressions or missed optimization opportunities.
• Skipping Staging: Always stage new JIT configurations—especially with custom workloads or extensions—to avoid subtle bugs or regressions.

Pro Tip: Automate JIT Validation in CI/CD

Integrate JIT configuration checks into your test pipelines. After schema or index changes, run representative workloads with EXPLAIN (ANALYZE, VERBOSE) and parse output for JIT usage and backend. This reduces the risk of silent regressions and ensures you get the intended performance benefit as your application evolves.

Conclusion and Next Steps

pg_jitter represents a major leap for teams needing just-in-time acceleration in PostgreSQL without the prohibitive overhead of LLVM. With careful configuration and proper workload profiling, it can deliver meaningful speedups for OLTP and mixed environments that previously couldn’t justify enabling JIT. No JIT provider is universally optimal—validate against your schema and monitor compatibility as PostgreSQL evolves.

For further detail, see the official PostgreSQL JIT documentation and the pg_jitter source repository. For broader context on scaling, see the OpenAI ChatGPT Postgres scaling story for lessons in performance tuning and infrastructure design at global scale.