Today I Learned (TIL) that having a UUID as a database primary key can be a really bad idea

Trying out Simon Willison's idea of Today I Learned (TIL) as a way to get back into writing.

I was vaguely suspicious of UUID primary keys for reasons that I couldn't remember.

Turns out the reason is that some (many?) row-oriented database engines use a table's primary key to organise the row storage. (An example is InnoDB, which in 2023 is the default database engine for MySQL and MariaDB.) If the primary key is sequential, row order is maintained by adding the new row to the end of a page or, if that's full, creating a new page. If the table storage is organised around a (random) UUID primary key, the new row has to be inserted at whichever position maintains sort order. In the worst case scenario, the new row has to be first and the database moves every existing row to make room for it. Unsurprisingly, insert performance is terrible.

PostgreSQL is an interesting case: even the primary key is technically a "secondary index". Postgres always organises the row storage using an index defined by Postgres itself. The user-defined primary key doesn't affect that, so a UUID primary key is fine.