Hacker News Reader

Comment by mcqueenjordan | Hacker News Reader

Apologies for being the grumpy guy this morning.

I don’t think “we added an in memory cache to reduce roundtrips to the DB” is “solving an interesting performance problem.”

View on HN

They even mention CTEs at the bottom of the article, but just as a throwaway idea. I'm not too familiar with them in MySQL, but they're the obvious choice for doing something like this with other databases.

Sigh. Fucking kids on my lawn.

My understanding is that CTEs are an optimization fence in some databases so aren't great for web application queries? I think that this is no longer the case in Postgres, but I recall learning that like ~6 years ago when working with other databases. Or is that total nonsense?

What is an optimization fence?

The database and query planner can't look past it to see that it can simplify the operations tthat the query will do.

I most commonly use CTEs for splitting ranges into individual values (1-5 into 1,2,3,4,5). It’s an order of magnitude faster than joining some utility table, which some still recommended.

As of PostgreSQL 12, whether the optimization fence is used or not is controlled with MATERIALIZED and NOT MATERIALIZED.

By default it’s an optimization fence if a CTE is referenced 2+ times, and not an optimization fence if referenced 0-1 times. The MATERIALIZED and NOT MATERIALIZED overrides the default.

They couldn‘t use CTEs because PlanetScale, which is built on top of Vitess, does not support them. So the most straightforward solution is just not available for them.

What you're missing:

The famous serverless data hosting company uses rails

What's ironic here is that there's an awesome_nested_set gem for Rails which (if I've understood the problem right) makes the solution trivial.

Reply on news.ycombinator.com