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

11–20 of 116 posts

Re: Better JIT for Postgres

#11

Post by qeternity »

kvdveer wrote: post #4
asah wrote: post #3

awesome! I wonder if it's possible to point AI at this problem and synthesize a bespoke compiler (per-architecture?) for postgresql expressions?

Two 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 n…

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

Re: Better JIT for Postgres

#14

Post by swaminarayan »

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.

Re: Better JIT for Postgres

#15

Post by SigmundA »

pjmlp wrote: post #9
fabian2k wrote: post #8

The 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, b…

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.

Re: Better JIT for Postgres

#16

Post by SigmundA »

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

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

Re: Better JIT for Postgres

#17

Post by catlifeonmars »

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.

Re: Better JIT for Postgres

#18

Post by SigmundA »

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.

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.

Re: Better JIT for Postgres

#19

Post by zaphirplane »

SigmundA wrote: post #18
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.

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.

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

Re: Better JIT for Postgres

#20

Post by the_biot »

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.

Post reply on HN