Small Datum

Right, PG calls the REDO log the WAL (for most purposes they are the same thing). I did not know that 9.4 can do partial page writes to the WAL now. Guess I will have more reading to do, thanks for pointing it out! A nice blog post by a colleague recently showing how large writes to redo logs matter is (not about PG, but why it is significant in the context of size of entries):

http://smalldatum.blogspot.com/2014/03/redo-logs-in-mongodb-and-innodb.html

As far as when clustering a table is useful, see the CLUSTER command in PG. It is roughly the same places you would want to do it, except it is automatically maintained. You do need to realize what is going on to minimize impact on inserts, but in a lot of cases, data in inserted in generally ascending order so it mostly 'free'. This does make GUIDs really bad for PKs in InnoDB.

Clustered indexes are like covering indexes (which PG got recently). You don't quite realize how useful they are until you get access to it ;)

InnoDB compression for us is primarily for non-lob objects, so TOAST is quite a bit different than the cross-row compression that we get. We will normally do compression of large objects outside of the DB whenever possible.

I'm not saying that InnoDB is always better than PG, but in a lot of cases that I have tested it with, it is indeed better. PG has come a long way recently, including options such as covering indexes to close the gap.

Tbf, either can be made to be fast, or slow. MySQL used to have a huge advantage for range queries due to its clustering index (assuming you have designed a schema to exploit that capability), but as time has gone on that gap has narrowed [0] (despite the title, it's not just about inserts). My own benchmarks for strictly read-only queries have also borne that out.

The biggest difference, IMO, is if you _aren't_ aware of the clustering index, and so design your schema in a way that is suboptimal. For example, given a table with orders or something similar, with a PK of `(user_id, created_at)`, a query like `SELECT... FROM... WHERE user_id = ? ORDER BY created_at DESC LIMIT 1` can be quite fast in MySQL. With a monotonic integer as the PK, and a secondary index on those two columns, it can still be quite fast, depending on insert order. If however you invert the PK to `(created_at, user_id)`, while MySQL 8+ will still be able to use the index, it's not nearly as efficient – in my tests, I saw query speed go from 0.1 msec --> 10 msec.

In contrast, while there is a small difference in Postgres with all of those differences, it's just not that big of a difference, since it stores tuples in a heap. Again, in my tests, query speed went from 0.1 msec --> 0.7 msec. This is a point query, of course; with range queries (`WHERE created_at < ...`) Postgres suffered more, jumping up to ~25 - 50 msec.

[0]:…

A number of features stood out to me in this release:

1. Chipping away more at vacuum. Fundamentally Postgres doesn't have undo log and therefore has to have vacuum. It's a trade-off of fast recovery vs well.. having to vacuum. The unfortunate part about vacuum is that it adds load to the system exactly when the system needs all the resources. I hope one day people stop knowing that vacuum exists, we are one step closer, but not there.

2. Performance gets better and not worse. Mark Callaghan blogs about MySQL and Postgres performance changes over time and MySQL keep regressing performance while Postgres keeps improving.

https://x.com/MarkCallaghanDB https://smalldatum.blogspot.com/

3. JSON. Postgres keep improving QOL for the interop with JS and TS.

4. Logical replication is becoming a super robust way of moving data in and out. This is very useful when you move data from one instance to another especially if version numbers don't match. Recently we have been using it to move at the speed of 1Gb/s

5. Optimizer. The better the optimizer the less you think about the optimizer. According to the research community SQL Server has the best optimizer. It's very encouraging that every release PG Optimizer gets better.

I would say that that's pretty dubious claim with modern versions of Postgres and MySQL/InnoDB, running on modern hardware. See for example this recent comparative Benchmark from Mark Callaghan, a well known member of the MySQL community:

https://smalldatum.blogspot.com/2021/01/sysbench-postgres-vs-mysql-and-impact.html

I'm not claiming that this benchmark justifies the claim that Postgres broadly performs better than MySQL/InnoDB these days -- that would be highly simplistic. Just as it would be simplistic to claim that MySQL is clearly well ahead with OLTP stuff in some kind of broad and entrenched way. It's highly dependent on workload.

Note that Postgres really comes out ahead on a test called "update-index", which involves updates that modify indexed columns -- the write amplification is much worse on MySQL there. This is precisely the opposite of what most commentators would have predicted. Including (and perhaps even especially) Postgres community people.

First, this article is mostly (AI?) regurgitation. This is much better: https://smalldatum.blogspot.com/2026/01/common-prefix-skipping-adaptive-sort.html.

Second, I have independently invented this (quicksort on string prefixes) at my time at CWI, although I didn't end up publishing it, because...

Third, this was already published in the original 1961 Quicksort paper by Hoare: https://www.cs.ox.ac.uk/files/6226/H2006%20-%20Historic%20Quicksort.pdf. Near the end, the section on "Multi-word keys" describes a quicksort that partitions on just the first word, and only accesses the next word for the equality partition. And funnily enough this paper credits P. Shackleton for this, thus this idea was thought of even before the Quicksort paper came out.

So as is usual for software patents, this patent never should have been awarded.

This is very impressive, however...

See this tweet by @aphyr: https://twitter.com/aphyr/status/542755074380791809

(All credit for the idea in this comment is due to @aphyr)

Basically because the transactions modified keys selected from a uniform distribution, the probability of contention was extremely low. AKA this workload is basically a data-parallel problem, somewhat lessening the impressiveness of the high throughput. Would be interesting to see it with a Zipfian distribution (or even better, a Biebermark [0])

[0] - http://smalldatum.blogspot.co.il/2014/04/biebermarks.html