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

Making Postgres 300x faster for analytics: batching, operator fusion, and SIMD

malisper.me

Post reply on HN
Print view View original

141–150 of 167 posts

Re: Making Postgres 300x faster for analytics: batching, operator fusion, and SIMD

#141

Post by insanitybit »

ForHackernews wrote: post #40
appplication wrote: post #7

Earlier quoted context omitted.

Oh bummer. I was really excited about pgrust but AGPL is a dealbreaker. Not for me personally, but it will never see wide adoption because it’s a banned license in most corporate environments. Lack of path to wide adoption means it’s dead in the water. It’s weird because those who actually care about optimized pg gains are most likely large corporate customers. Why make a product targeting them and license it in such…

> It’s weird because those who actually care about optimized pg gains are most likely large corporate customers. Why make a product targeting them and license it in such a way they’ll never use it? Wait, sorry, you're asking why make something enterprise customers might pay for, and then not give it away to them for free?

It's not that companies won't pay for it, it's that it is banned. Legal teams at these companies set the policy.

Re: Making Postgres 300x faster for analytics: batching, operator fusion, and SIMD

#142

Post by dwattttt »

wffurr wrote: post #61
jnwatson wrote: post #15

Earlier quoted context omitted.

The floating point comparison bug is nightmare fuel. I could look at that for years and never spot the mistake.

Fuzzers are brilliant at this and produce all kinds of insane floating point inputs.

If you're only taking one float as input, we've long been at a point where you can just test them all: https://randomascii.wordpress.com/2014/01/27/theres-only-fou...

Re: Making Postgres 300x faster for analytics: batching, operator fusion, and SIMD

#143

Post by NautilusWave »

sgt wrote: post #53

Cool project but .. reality is that people will generally not choose pgrust over Postgres, even 5-10 years from now. The problem is not that it may be technically superior and faster by then, it's that it's not built by the trusted Postgres team. There's a lot more to trust than development velocity or performance. It's also about the longevity and continuity of a critical piece of technology.

The possibility of competition, and an existing proof of concept, could motivate the Postgres team to pursue performance initiatives that they otherwise wouldn't.

Re: Making Postgres 300x faster for analytics: batching, operator fusion, and SIMD

#145

Post by mdkdog »

wanted to test this but no joy.

"ERROR: convert_string_datum (selfuncs.c): pg_strxfrm leg; C-collation lane only" "thread 'pg:backend:1572' (11650) panicked at crates/backend/optimizer/plan/planner/src/selfuncs.rs:966:9:"

i would say is not ready yet.

Re: Making Postgres 300x faster for analytics: batching, operator fusion, and SIMD

#146

Post by inigyou »

marginalia_nu wrote: post #63
malisper wrote: post #57

Earlier quoted context omitted.

I would probably dig into the reasons for the differences in the benefit on the test machine and in prod I had an issue like this for optimizing pgrust. I had an optimization that showed no impact on my test machine (c8g.4xl) and showed a 20% improvement when ran on my mac. It turns out the issue was the instruction cache on the c8g.4xl was being saturated on the test machine but not on my laptop, moving the bottlene…

I'm pretty sure the reason for the difference is that production machine exists in a state of mixed memory residency and low grade resource contention that is incredibly hard to replicate in a test scenario (as the moment you start making queries the pages warm up, and the test becomes unreliable). The hard part about optimizing this type of code, IMO, is that there are so many cache layers, both in the CPU and the O…

Have you tried running the test in a cgroup with a small memory limit? This should force pages to get swapped out earlier than normal.

Re: Making Postgres 300x faster for analytics: batching, operator fusion, and SIMD

#147

Post by k_bx »

We went this path: pg -> peerdb -> clickhouse replica, then pg -> another pg with pg_clickhouse extension -> clickhouse

This way we keep querying without changing the query language (use same postgres syntax), but switch connection port for analytics query. Experience so far: made 3 PRs to pg_clickhouse (1 merged), otherwise works pretty well.

If something like pgrust would work even better/easier – would definitely check it out instead.

Re: Making Postgres 300x faster for analytics: batching, operator fusion, and SIMD

#149

Post by rastignack »

malisper wrote: post #29
rastignack wrote: post #26

I would be interested about a more detailed architecture overview of the io scheduler (like this: https://www.scylladb.com/2021/04/06/scyllas-new-io-scheduler... ) and the thread scheduler. PostgreSQL has historically been bad at managing the noisy neighbor problem, but with thread pools, and io priorities, it can be solved. Has this been tackled here ?

I'll need to write up how the scheduler works at some point, but it's heavily based on these papers[0][1]. It solves two different problems. First, it lets us throttle resource-intensive queries. Second, it enables work stealing. If you have idle cores on your machine, we'll assign those cores to running queries to help speed them up. That means if you have an over-provisioned machine, we'll make use of the extra cap…

Interesting. It would need some work to handle all the workloads I’ve faced where you have two applications with different priorities (ie oltp or grid workloads and analytics). In those cases you want to make sure that BI users do not interrupt the transaction processing by assigning them a set of cores, a priority, and work mem limits for example. Looks doable without major changes.
Post reply on HN