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

51–60 of 116 posts

Re: Better JIT for Postgres

#51

Post by jjice »

martinald wrote: post #22
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.

Anything jsonb in my experience is quickly CPU bound...

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

Re: Better JIT for Postgres

#52

Post by array_key_first »

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.

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.

Re: Better JIT for Postgres

#53

Post by SigmundA »

vladich wrote: post #45
SigmundA wrote: post #15

Earlier quoted context omitted.

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

Postgres 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

No it cannot cache query plans between processes (connections) and the only way it can cache in the same process in the same connection is by the client manually preparing it, this was how the big boys did it 30 years ago, not anymore.

Was common guidance back in the day to use stored procedures for all application access code because they where cached in MSSQL (which PG doesn't even do). Then around 2000 it started caching based on statement text and that became much less important.

You would only used prepared statements if doing a bunch of inserts in a loop or something and it has a very small benefit now days only because its not sending the same text over the network over and over and hashing to lookup plan.

Re: Better JIT for Postgres

#54

Post by vladich »

_flux wrote: post #37
SigmundA wrote: post #28

Earlier quoted context omitted.

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?

Won't work well if it executes 20k+ queries per second. Filesystem will be a bottleneck among other things.

Re: Better JIT for Postgres

#55

Post by martinald »

jjice wrote: post #51
martinald wrote: post #22

Earlier quoted context omitted.

Anything jsonb in my experience is quickly CPU bound...

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

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

Re: Better JIT for Postgres

#56

Post by menaerus »

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.

Someone is already working on it: https://arxiv.org/pdf/2603.02081

Re: Better JIT for Postgres

#57

Post by kbolino »

array_key_first wrote: post #52
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.

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.

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

[2]: https://www.psycopg.org/psycopg3/docs/advanced/prepare.html

Re: Better JIT for Postgres

#58

Post by qeternity »

magicalhippo wrote: post #23
qeternity wrote: post #11

Earlier quoted context omitted.

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

> 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 endpoi…

Yes, lots of things can create indeterminism. But nothing is inherent.

Re: Better JIT for Postgres

#59

Post by qeternity »

yomismoaqui wrote: post #25
qeternity wrote: post #11

Earlier quoted context omitted.

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

Quoting: "But why aren’t LLM inference engines deterministic? One common hypothesis is that some combination of floating-point non-associativity and concurrent execution leads to nondeterminism based on which concurrent core finishes first." From https://thinkingmachines.ai/blog/defeating-nondeterminism-in...

Yes, lots of things can create indeterminism. But nothing is inherent.

Re: Better JIT for Postgres

#60

Post by vladich »

SigmundA wrote: post #53
vladich wrote: post #45

Earlier quoted context omitted.

Postgres 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

No it cannot cache query plans between processes (connections) and the only way it can cache in the same process in the same connection is by the client manually preparing it, this was how the big boys did it 30 years ago, not anymore. Was common guidance back in the day to use stored procedures for all application access code because they where cached in MSSQL (which PG doesn't even do). Then around 2000 it started…

I didn't say it can cache between processes. The problem is not caching between processes, it's that caching itself is not very useful, because the planner creates different plans for different input parameters of the same query in the general case. So you can reliably cache plans only for the same sets of parameters. Or you can cache generic plans, which Postgres already does as well (and sharing that cache won't solve much of the problem too).
Post reply on HN