11 ms·

We have everything optimized, and yet somehow DB queries need to be "interpreted" at runtime. There's no reason for DB queries to not be precompiled.

catlifeonmars 7mo ago
This is a neat idea. I want to take it further and precompile the entire DBMS binary for a specific schema.
- WJW 7mo ago
  How will you handle ALTER TABLE queries without downtime?
  - catlifeonmars 7mo ago
    That would definitely present a bit of a challenge, but: - not all databases need migrations (or migrations without downtime) - alternatively, ship the migrations as part of the binary Adhoc modifications would still be more difficult but tbh that’s not necessarily a bug
- menaerus 7mo ago
  Someone is already working on it: https://arxiv.org/pdf/2603.02081 https://arxiv.org/pdf/2603.02081
  - catlifeonmars 7mo ago
    That looks interesting but it seems inefficient to put an LLM directly into the compilation pipeline, not to mention that it introduces nondeterministic behavior.
    - menaerus 7mo ago
      It has different limitations but inefficiency doesn't seem likely to be one of them. Did you read the Experimental Results section? > Figure 2 shows the experimental results, and GenDB outperforms all baselines on every query in both benchmarks. On TPC-H, GenDB achieves a total execution time of 214 ms across five representative queries. > This result is 2.8× faster than DuckDB (594 ms) and Umbra (590 ms), which are the two fastest baselines, and 11.2× faster than ClickHouse. > On SEC-EDGAR, GenDB achieves 328 ms, which is 5.0× faster than DuckDB and 3.9× faster than Umbra. > The performance gap increases with query complexity. For example, on TPC-H Q9, which is a five-way join with a LIKE filter, GenDB completes in 38 ms, which is 6.1× faster than DuckDB. GenDB uses iterative optimization with early stopping criteria. > On TPC-H, Q6 reaches a near-optimal time of 17 ms at iteration 0 with zone-map pruning and a branchless scan, and does not require further optimization. In contrast, Q18 starts at 12,147 ms and decreases to 74 ms by iteration 1, which is a 163× improvement. This gain comes from replacing a cache-thrashing hash aggregation with an index-aware sequential scan. > On SEC-EDGAR, Q4 decreases from 1,410 ms to 106 ms over three iterations, which is a 13.3× improvement, and Q6 decreases from 1,121 ms to 88 ms over four iterations, which is a 12.7× improvement. In Q6, the optimizer gradually fuses scan, compact, and merge operations into a single OpenMP parallel region, which removes three thread-spawn overheads. By iteration 1, GenDB already outperforms all baselines
      - vladich 7mo ago
        That's all great, but sadly impractical. I looked at one of the first statements: > GenDB is an LLM-powered agentic system that decomposes the complex end-to-end query processing and optimization task into a sequence of smaller and well-defined steps, where each step is handled by a dedicated LLM agent. And knowing typical LLM latency, it's outside of the realm of OLTP and probably even OLAP. You can't wait tens of seconds to minutes until LLM generates you some optimal code that you then compile and execute.
        menaerus 7mo ago
        No, that's not how I believe they intended it to work. They generate the workload-specific engine up-front and not when the query arrives.
SigmundA 7mo ago
Postgresql uses a process per connection model and it has no way to serialize a query plan to some form that can be shared between processes, so the time it takes to make the plan including JIT is very important. Most other DB's cache query plans including jitted code so they are basically precompiled from one request to the next with the same statement.
- zaphirplane 7mo ago
  What do you mean ? Cause the obvious thing is a shared cache and if there is one thing the writers of a db know it is locking
  - SigmundA 7mo ago
    Sharing executable code between processes it not as easy as sharing data. AFAIK unless somethings changed recently PG shares nothing about plans between process and can't even share a cached plan between session/connections.
    - llm_nerd 7mo ago
      Executable code is literally just data that you mark as executable. It did the JIT code, and the idea that it can't then share it between processes is incomprehensible. I was actually confused by this submission as it puts so much of an emphasis on initial compilation time, when every DB (apparently except for pgsql) caches that result and shares it/reuses it until invalidation. Invalidation can occur for a wide variety of reasons (data composition changing, age, etc), but still the idea of redoing it on every query, where most DBs see the same queries endlessly, is insane.
      - SigmundA 7mo ago
        No a lot of jitted code has pointers to addresses specific to that process which makes no sense in another process. To make code shareable between processes takes effort and will have tradeoff in performance since it is not specialized to the process. If the query plan where at least serializable which is more like a AST then at least that part could be reused and then maybe have jitted code in each processes cached in memory that the plan can reference by some key. DB's like MSSQL avoid the problem because they run a single OS process with multiple threads instead. This is also why it can handle more connections easily since each connection is not a whole process.
Asm2D 7mo ago
Many SQL engines have JIT compilers. The problems related to PostgreSQL are pretty much all described here. It's very difficult to do low-latency queries if you cannot cache the compiled code and do it over and over again. And once your JIT is slow you need a logic to decide whether to interpret or compile. I think it would be the best to start interpreting the query and start compilation in another thread, and once the compilation is finished and interpreter still running, stop the interpreter and run the JIT compiled code. This would give you the best latency, because there would be no waiting for JIT compiler.
- aengelke 7mo ago
  > It's very difficult to do low-latency queries if you cannot cache the compiled code This is not too difficult, it just requires a different execution style. Salesforce's Hyper for example very heavily relies on JIT compilation, as does Umbra [1], which some people regard as one of the fastest databases right now. Umbra doesn't cache any IR or compiled code and still has an extremely low start-up latency; an interpreter exists but is practically never used. Postgres is very robust and very powerful, but simply not designed for fast execution of queries. Disclosure: I work in the group that develops Umbra. [1]: https://umbra-db.com/ https://umbra-db.com/
  - Asm2D 7mo ago
    If I recall research papers regarding Umbra it's also using AsmJit as a JIT backend, which means that theoretically the compilation times would be comparable if you only consider code emitting overhead. The problem will always be queries where the compilation is orders of magnitude more expensive than the query itself. I can imagine indexed lookup of 1 or few entries, etc... Accessing indexed entries like these are very well optimized by SQL query engines and possibly make no sense JIT optimizing.
    - vladich 7mo ago
      Interesting... AsmJit is pretty fast for compilation, but about 3x than sljit. The only way I can see how to make it fast enough, in theory (i.e. without slowing down point-lookup queries and such) would be to fuse planning with code generation - i.e. a single pass plan builder + compiler essentially. Not sure if Umbra tries to do that, and AsmJit is not the best choice for it anyway, but with sljit it could be on par with interpreter even for fastest queries I believe. Pretty hard (likely impossible) to implement though, planning is inherently a non-linear process...
levkk 7mo ago
See prepared statements.
jpfr 7mo ago
The "byte-code" coming from the query planner typically only has a handful of steps in a linear sequence. Joins, filters, and such. But the individual steps can be very costly. So there is not much to gain from JITing the query plan execution only. JITing begins to make more sense, when the individual query plan steps (join, filter, ...) themselves be specialized/recompiled/improved/merged by knowing the context of the query plan.
array_key_first 7mo ago
DB queries do get pre compiled and cached if you use prepared statements. This is why you should always use prepared statements if you can.
- kbolino 7mo ago
  It is not always necessary to explicitly use prepared statements, though. For example, the pgx library for Go [1] and the psycopg3 library for Python [2] will automatically manage prepared statements for you. [1]: https://pkg.go.dev/github.com/jackc/pgx/v5#hdr-Prepared_Statements https://pkg.go.dev/github.com/jackc/pgx/v5#hdr-Prepared_Stat... [2]: https://www.psycopg.org/psycopg3/docs/advanced/prepare.html https://www.psycopg.org/psycopg3/docs/advanced/prepare.html

catlifeonmars 7mo ago

This is a neat idea. I want to take it further and precompile the entire DBMS binary for a specific schema.

WJW 7mo ago
How will you handle ALTER TABLE queries without downtime?
- catlifeonmars 7mo ago
  That would definitely present a bit of a challenge, but: - not all databases need migrations (or migrations without downtime) - alternatively, ship the migrations as part of the binary Adhoc modifications would still be more difficult but tbh that’s not necessarily a bug
menaerus 7mo ago
Someone is already working on it: https://arxiv.org/pdf/2603.02081 https://arxiv.org/pdf/2603.02081
- catlifeonmars 7mo ago
  That looks interesting but it seems inefficient to put an LLM directly into the compilation pipeline, not to mention that it introduces nondeterministic behavior.
  - menaerus 7mo ago
    It has different limitations but inefficiency doesn't seem likely to be one of them. Did you read the Experimental Results section? > Figure 2 shows the experimental results, and GenDB outperforms all baselines on every query in both benchmarks. On TPC-H, GenDB achieves a total execution time of 214 ms across five representative queries. > This result is 2.8× faster than DuckDB (594 ms) and Umbra (590 ms), which are the two fastest baselines, and 11.2× faster than ClickHouse. > On SEC-EDGAR, GenDB achieves 328 ms, which is 5.0× faster than DuckDB and 3.9× faster than Umbra. > The performance gap increases with query complexity. For example, on TPC-H Q9, which is a five-way join with a LIKE filter, GenDB completes in 38 ms, which is 6.1× faster than DuckDB. GenDB uses iterative optimization with early stopping criteria. > On TPC-H, Q6 reaches a near-optimal time of 17 ms at iteration 0 with zone-map pruning and a branchless scan, and does not require further optimization. In contrast, Q18 starts at 12,147 ms and decreases to 74 ms by iteration 1, which is a 163× improvement. This gain comes from replacing a cache-thrashing hash aggregation with an index-aware sequential scan. > On SEC-EDGAR, Q4 decreases from 1,410 ms to 106 ms over three iterations, which is a 13.3× improvement, and Q6 decreases from 1,121 ms to 88 ms over four iterations, which is a 12.7× improvement. In Q6, the optimizer gradually fuses scan, compact, and merge operations into a single OpenMP parallel region, which removes three thread-spawn overheads. By iteration 1, GenDB already outperforms all baselines
    - vladich 7mo ago
      That's all great, but sadly impractical. I looked at one of the first statements: > GenDB is an LLM-powered agentic system that decomposes the complex end-to-end query processing and optimization task into a sequence of smaller and well-defined steps, where each step is handled by a dedicated LLM agent. And knowing typical LLM latency, it's outside of the realm of OLTP and probably even OLAP. You can't wait tens of seconds to minutes until LLM generates you some optimal code that you then compile and execute.
      - menaerus 7mo ago
        No, that's not how I believe they intended it to work. They generate the workload-specific engine up-front and not when the query arrives.

SigmundA 7mo ago

Postgresql uses a process per connection model and it has no way to serialize a query plan to some form that can be shared between processes, so the time it takes to make the plan including JIT is very important. Most other DB's cache query plans including jitted code so they are basically precompiled from one request to the next with the same statement.

zaphirplane 7mo ago
What do you mean ? Cause the obvious thing is a shared cache and if there is one thing the writers of a db know it is locking
- SigmundA 7mo ago
  Sharing executable code between processes it not as easy as sharing data. AFAIK unless somethings changed recently PG shares nothing about plans between process and can't even share a cached plan between session/connections.
  - llm_nerd 7mo ago
    Executable code is literally just data that you mark as executable. It did the JIT code, and the idea that it can't then share it between processes is incomprehensible. I was actually confused by this submission as it puts so much of an emphasis on initial compilation time, when every DB (apparently except for pgsql) caches that result and shares it/reuses it until invalidation. Invalidation can occur for a wide variety of reasons (data composition changing, age, etc), but still the idea of redoing it on every query, where most DBs see the same queries endlessly, is insane.
    - SigmundA 7mo ago
      No a lot of jitted code has pointers to addresses specific to that process which makes no sense in another process. To make code shareable between processes takes effort and will have tradeoff in performance since it is not specialized to the process. If the query plan where at least serializable which is more like a AST then at least that part could be reused and then maybe have jitted code in each processes cached in memory that the plan can reference by some key. DB's like MSSQL avoid the problem because they run a single OS process with multiple threads instead. This is also why it can handle more connections easily since each connection is not a whole process.

Asm2D 7mo ago

Many SQL engines have JIT compilers. The problems related to PostgreSQL are pretty much all described here. It's very difficult to do low-latency queries if you cannot cache the compiled code and do it over and over again. And once your JIT is slow you need a logic to decide whether to interpret or compile. I think it would be the best to start interpreting the query and start compilation in another thread, and once the compilation is finished and interpreter still running, stop the interpreter and run the JIT compiled code. This would give you the best latency, because there would be no waiting for JIT compiler.

aengelke 7mo ago
> It's very difficult to do low-latency queries if you cannot cache the compiled code This is not too difficult, it just requires a different execution style. Salesforce's Hyper for example very heavily relies on JIT compilation, as does Umbra [1], which some people regard as one of the fastest databases right now. Umbra doesn't cache any IR or compiled code and still has an extremely low start-up latency; an interpreter exists but is practically never used. Postgres is very robust and very powerful, but simply not designed for fast execution of queries. Disclosure: I work in the group that develops Umbra. [1]: https://umbra-db.com/ https://umbra-db.com/
- Asm2D 7mo ago
  If I recall research papers regarding Umbra it's also using AsmJit as a JIT backend, which means that theoretically the compilation times would be comparable if you only consider code emitting overhead. The problem will always be queries where the compilation is orders of magnitude more expensive than the query itself. I can imagine indexed lookup of 1 or few entries, etc... Accessing indexed entries like these are very well optimized by SQL query engines and possibly make no sense JIT optimizing.
  - vladich 7mo ago
    Interesting... AsmJit is pretty fast for compilation, but about 3x than sljit. The only way I can see how to make it fast enough, in theory (i.e. without slowing down point-lookup queries and such) would be to fuse planning with code generation - i.e. a single pass plan builder + compiler essentially. Not sure if Umbra tries to do that, and AsmJit is not the best choice for it anyway, but with sljit it could be on par with interpreter even for fastest queries I believe. Pretty hard (likely impossible) to implement though, planning is inherently a non-linear process...

levkk 7mo ago

See prepared statements.

jpfr 7mo ago

The "byte-code" coming from the query planner typically only has a handful of steps in a linear sequence. Joins, filters, and such. But the individual steps can be very costly. So there is not much to gain from JITing the query plan execution only. JITing begins to make more sense, when the individual query plan steps (join, filter, ...) themselves be specialized/recompiled/improved/merged by knowing the context of the query plan.

array_key_first 7mo ago

DB queries do get pre compiled and cached if you use prepared statements. This is why you should always use prepared statements if you can.

kbolino 7mo ago
It is not always necessary to explicitly use prepared statements, though. For example, the pgx library for Go [1] and the psycopg3 library for Python [2] will automatically manage prepared statements for you. [1]: https://pkg.go.dev/github.com/jackc/pgx/v5#hdr-Prepared_Statements https://pkg.go.dev/github.com/jackc/pgx/v5#hdr-Prepared_Stat... [2]: https://www.psycopg.org/psycopg3/docs/advanced/prepare.html https://www.psycopg.org/psycopg3/docs/advanced/prepare.html