Hacker News Reader

Comment by the_biot | Hacker News Reader

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.

View on HN

Most databases in practice are sub-terabyte and even sub-100Gb, their active dataset is almost fully cached. For most databases I worked with, cache hit rate is above 95% and for almost all of them it's above 90%. In that situation, most queries are CPU-bound. It's completely different from typical OLAP in this sense.

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.

I've shaved off 30s of queries by transforming json columns into a string after the first CTE is done with it

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.

PostgreSQL is Turing complete, so I guess they do what ever they want?

Reply on news.ycombinator.com