Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Better JIT for Postgres

github.com

Post reply on HN
Print view View original

31–40 of 116 posts

Re: Better JIT for Postgres

#31

Post by Asm2D »

sourcegrift wrote: post #12

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.

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.

Re: Better JIT for Postgres

#32

Post by WJW »

catlifeonmars wrote: post #17
sourcegrift wrote: post #12

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.

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

How will you handle ALTER TABLE queries without downtime?

Re: Better JIT for Postgres

#33

Post by catlifeonmars »

WJW wrote: post #32
catlifeonmars wrote: post #17

Earlier quoted context omitted.

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

How will you handle ALTER TABLE queries without downtime?

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

Re: Better JIT for Postgres

#34

Post by wreath »

the_biot wrote: post #20

What 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.

I think reading queries that are always served from cache are CPU bound because it also involves locking the buffers etc and there is no I/O involved.

Re: Better JIT for Postgres

#35

Post by masklinn »

bob1029 wrote: post #10
eru wrote: post #2

> 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?

At some level the application needs to participate in the performance conversation too. https://www.postgresql.org/docs/current/sql-prepare.html

Postgres’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.

Re: Better JIT for Postgres

#36

Post by masklinn »

swaminarayan wrote: post #14

Have 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.

> 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).

Re: Better JIT for Postgres

#37

Post by _flux »

SigmundA wrote: post #28
zaphirplane wrote: post #19

Earlier quoted context omitted.

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

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.

Write the binary to a file, call it `libquery-id1234.so`, and link that to whichever processes that need it?

Re: Better JIT for Postgres

#38

Post by aengelke »

Asm2D wrote: post #31
sourcegrift wrote: post #12

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.

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…

> 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/

Re: Better JIT for Postgres

#39

Post by aengelke »

SigmundA wrote: post #15
pjmlp wrote: post #9

Earlier quoted context omitted.

That is quite strange, given that big boys RDMS (Oracle, SQL Server, DB2, Informix,...) all have JIT capabilities for several decades now.

The big boys all cache query plans so the amount it time it take to compile is not really a concern.

That's not generally correct. Compile-time is a concern for several databases.

Re: Better JIT for Postgres

#40

Post by chrisaycock »

Asm2D wrote: post #31
sourcegrift wrote: post #12

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.

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…

> I think it would be the best to start interpreting the query and start compilation in another thread

This technique is known as a "tiered JIT". It's how production virtual machines operate for high-level languages like JavaScript.

There can be many tiers, like an interpreter, baseline compiler, optimizing compiler, etc. The runtime switches into the faster tier once it becomes ready.

More info for the interested:

https://ieeexplore.ieee.org/document/10444855

Post reply on HN