7 ms·
Better JIT for Postgres
- eru 7mo ago> 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. I don't know anything here, but this seems like a good case for ahead of time compilation? Or at least caching your JIT results? I can image much of the time, you are getting more or less the same query again and again?
- olau 7mo agoYes. Some years ago we ported some code from querying out the data and tallying in Python (how many are in each bucket) to using SQL to do that. It didn't speed up the execution. I was surprised by that, but I guess the Postgres interpreter is roughly the same speed as Python, which when you think about it perhaps isn't that surprising. But Python is truly general purpose while the core query stuff in SQL is really specialized (we were not using stored procedures). So if Pypy can get 5x speedup, it seems to me that it should be possible to get the same kind of speed up in Postgres. I guess it needs funding and someone as smart as the Pypy people.
- eru 7mo agoThat's curious. I regularly get speed ups when moving processing from Python to postgres. At least when using indices properly and when the shift reduces the amount of data carried back and forth.
- bob1029 7mo agoAt some level the application needs to participate in the performance conversation too. https://www.postgresql.org/docs/current/sql-prepare.html https://www.postgresql.org/docs/current/sql-prepare.html
- SigmundA 7mo agoUnless you cache query plans like other RDBMS's then the client manually managing that goes away and its not limited to a single connection. MS SQL still has prepared statements and they really haven't been used in 20 years since it gained the ability to cache plans based on statement text.
- masklinn 7mo agoPostgres’s PREPARE is per-connection so it’s pretty limited, and then connection poolers enter the fray and often can’t track SQL-level prepares. And then the issue is not dissimilar to Postgres’s planner issues.
- hinkley 7mo agoOracle’s wasn’t but I haven’t used it in a very long time so that may not be longer be true. The problem though was that it had a single shared pool for all queries and it could only run a query if it was in the pool, which is how out DB machine would max out at 50% CPU and bandwidth. We had made some mistakes in our search code that I told the engineer not to make.
- asah 7mo agoawesome! I wonder if it's possible to point AI at this problem and synthesize a bespoke compiler (per-architecture?) for postgresql expressions?
- kvdveer 7mo agoTwo things are holding back current LLM-style AI of being of value here: * Latency. LLM responses are measured in order of 1000s of milliseconds, where this project targets 10s of milliseconds, that's off by almost two orders of magnitute. * Determinism. LLMs are inherently non-deterministic. Even with temperature=0, slight variations of the input lead to major changes in output. You really don't want your DB to be non-deterministic, ever.
- simonask 7mo ago> 1000s of milliseconds Better known as "seconds"...
- olau 7mo agoThe suggestion was not to use an LLM to compile the expression, but to use an LLM to build the compiler.
- qeternity 7mo ago> LLMs are inherently non-deterministic. This isn't true, and certainly not inherently so. Changes to input leading to changes in output does not violate determinism.
- magicalhippo 7mo ago> This isn't true From what I understand, in practice it often is true[1]: Matrix multiplication should be “independent” along every element in the batch — neither the other elements in the batch nor how large the batch is should affect the computation results of a specific element in the batch. However, as we can observe empirically, this isn’t true. In other words, the primary reason nearly all LLM inference endpoints are nondeterministic is that the load (and thus batch-size) nondeterministically varies! This nondeterminism is not unique to GPUs — LLM inference endpoints served from CPUs or TPUs will also have this source of nondeterminism. [1]: https://thinkingmachines.ai/blog/defeating-nondeterminism-in-llm-inference/ https://thinkingmachines.ai/blog/defeating-nondeterminism-in...
- fabian2k 7mo agoThe last time I looked into it my impression was that disabling the JIT in PostgreSQL was the better default choice. I had a massive slowdown in some queries, and that doesn't seem to be an entirely unusual experience. It does not seem worth it to me to add such a large variability to query performance by default. The JIT seemed like something that could be useful if you benchmark the effect on your actual queries, but not as a default for everyone.
- pjmlp 7mo agoThat is quite strange, given that big boys RDMS (Oracle, SQL Server, DB2, Informix,...) all have JIT capabilities for several decades now.
- SigmundA 7mo agoThe big boys all cache query plans so the amount it time it take to compile is not really a concern.
- aengelke 7mo agoThat's not generally correct. Compile-time is a concern for several databases.
- SigmundA 7mo agoMost systems submit many of the same queries over and over again. Ad-hoc one off queries usually can accept higher initial up-front compile cost because the main results usually take much longer anyway, vs worrying about an extra 100ms of compile. Maybe it was too strong to say its not a concern at all, but nothing like PG where every single request needs to replan and potentially jit unless the client manually prepares and keeps the connection open.
- vladich 7mo agoPostgres caches query plans too, the problem is you can only cache what you can share, and if your planner works well, you can share very little, there can be a lot of unique plans even for the same query
- sourcegrift 7mo agoWe 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 agoThis is a neat idea. I want to take it further and precompile the entire DBMS binary for a specific schema.
- WJW 7mo agoHow will you handle ALTER TABLE queries without downtime?
- catlifeonmars 7mo agoThat 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 agoSomeone is already working on it: https://arxiv.org/pdf/2603.02081 https://arxiv.org/pdf/2603.02081
- catlifeonmars 7mo agoThat 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 agoIt 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
- larodi 7mo agosadly, no windows version yet AFAICT
- swaminarayan 7mo agoHave you tested this under high concurrency with lots of short OLTP queries? I’m curious whether the much faster compile time actually moves the point where JIT starts paying off, or if it’s still mostly useful for heavier queries.
- masklinn 7mo ago> By default, jit_above_cost 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).
- vladich 7mo agoIt's not useful for sub-millisecond queries like point lookups, or other simple ones that process only a few records. sljit option starts to pay off when you process (not necessarily return) hundreds of records. The more - the better. I'm still thinking about a caching option, that will allow to lift this requirement somewhat - for cached plans. For non-cached ones it will stay.
- the_biot 7mo agoWhat sort of things are people doing in their SQL queries that make them CPU bound? Admittedly I'm a meat-and-potatoes guy, but I like mine I/O bound. Really amazed to see not one but several generic JIT frameworks though, no idea that was a thing.
- throwaway140126 7mo agoPostgreSQL is Turing complete, so I guess they do what ever they want?
- martinald 7mo agoAnything jsonb in my experience is quickly CPU bound...
- jjice 7mo agoDefinitely. If you're doing regular queries with filters on jsonb columns, having the index directly on the JSON paths is really powerful. If I have a jsonb filter in the codebase at all, it probably needs an index, unless I know the result set is already very small.
- martinald 7mo agoYeah, the other problem is I've really struggled to have postgres use multiple threads/cores on one query. Often maxes out one CPU thread while dozens go unused. I constantly have to fight loads of defaults to get this to change and even then I never feel like I can get it working quite right (probably operator error to some extent). This compares to clickhouse where it constantly uses the whole hardware. Obviously it's easier to do that on a columnar database but it seems that postgres is actively designed to _not_ saturate multiple cores, which may be a good assumption in the past but definitely isn't a good one now IMO.
- d0100 7mo agoI've shaved off 30s of queries by transforming json columns into a string after the first CTE is done with it
- hinkley 7mo agoI’m always surprised to learn LLVM is so slow given that was one of the original motivations for developing it. I don’t know if that’s down to feature creep or intrinsic complexity being higher than people presumed was the case for GCC.
- Tanjreeve 7mo agoIt's a compiler backend for programming languages not a runtime JIT compiler. Especially inside a DBMS a lot of the assumptions it was built with don't hold. Some people in DBMS world (mostly at TUM with Umbra/CedarDB) have written their own and others tried multi pass approaches where you have an interpreter first then a more optimised LLVM pass later.
- hinkley 7mo agoIt was intended to solve the problem of interactive coding sessions such as with Language Servers, which GCC utterly fails at (because what we think of as modern IDEs did not exist in 1990). An awful lot of people have tried to use it as a JIT now and had to backpedal. I'm not sure how the one lead to the other but here we are.