Can GROUP BY still be optimized after all these years? (pivotlake.io)

The query is:

  SELECT item_id, COUNT(*) AS c
  FROM orders
  GROUP BY item_id
  ORDER BY c DESC
  LIMIT 10;

They are getting the top 10 most frequent values of item_id. It's possible for one item_id to be the 11th most popular item across every partition and not the 10th. Then when you sum the counts across all partitions, it becomes the 10th most common.

The problem is that “faster” and “slower” compare speed, which is x units per y time. But here, we’re only comparing time. The semi-legit interpretation is “if one unit takes 22.2ms, how many units can I complete in 35.6ms”, and actually the answer to that is indeed the 1.6 number they mention.

It’s not sensationalist, it’s just a bit sloppy with the word “faster”.

It's 60% faster (0.6x faster, 1.6x as fast). Throughput (“how much stuff you can do per unit of time”) is the only reasonable way to describe performance[1], otherwise large speedups are basically impossible to describe in an intuitive way. (If something that took 10s now takes 1s, that's “ten times as fast”, not “zero point nine times faster”.)

[1] Even latency changes are best described in terms of throughput.

1.6x faster is completely acceptable in natural language.

MySQL GROUP BY is not broken by default (ONLY_FULL_GROUP_BY, the standards-conforming choice, is default for a decade or more). But there is basically no team left on the MySQL optimizer, after a long series of layoffs; it's gone from something like 40 to 3, give or take. Try Postgres :-)

group by is useful with other aggregates like sum, max, and so on.

At least they have useful technical information in it. Personally really like reading these blog posts.

Measurements and context is so situational that it would be useless anyway imo.

It is not like any DB company is writing blog posts with benchmarks compared to hardware limits. DuckDB had blog posts that has sentences like "You are doing it wrong if you are not using HTTP api on your database in 2026", last time I saw their blog.

I also think object storage is bad in this case. And rust is no a no-no for a database project like this, because it is not ergonomic to do static allocation and arena allocators in rust. And parquet is trash for performance because of many reasons like supported compression formats etc.

You're not wrong: if you know how much memory you need for a single query and can keep the data in attached SSD (so ephemeral SSD on AWS), scaling vertically will give you the fastest execution time.

However horizontal scaling has advantages in that it's easier to scale up and down incrementally. It gives you more aggregate network bandwidth for accessing disaggregated data. Disaggregation allows for independent scaling of compute and storage. Losing a single machine has a marginal impact on the cluster, which can enable you to use cheaper spot resources.

Basically, it's often easier to operate a system designed around horizontal scaling if you need resiliency.