Lawrence Jones, a software engineer and early incident.io employee formerly at GoCardless, writes about lessons from startup engineering, Postgres, and infrastructure.
The author describes his startup engineering background; a commenter discusses his ULID post, noting insert performance rather than ID generation is the slower part.
I don't think that's right. They show in the section titled "Generating" that the performance of calling the ULID function from SQL is only very slightly slower. It's the INSERT that performs worse.
Generally, inserting sorted values (like sequential integers or in this case, ULIDs) into a B-tree index is much faster than inserting random values. This is because inserted values go into the same, highly packed B-tree nodes, whereas random inserts will need to create a lot of scattered B-tree nodes, resulting in more pages written. Random values are generally faster to query, but slower to insert.
In this case I think the insert speed differences may come down to the sizes of the keys. Postgres's native UUID type is 128 bits, or 16 bytes, whereas the ULID is stored as the "text" type, encoded as base32, resulting in a string that is 26 bytes, plus a 32-bit string length header, so 240 bits in total, or 1.87x longer. In the benchmark, the ULID insert is about 3x that of the UUID. So the overhead may be not just the extra space but the overhead of string comparisons compared to just comparing 128-bit ints.
Edit: The article doesn't actually say which ULID implementation they use. The one implemented in PL/PGSQL mentioned in one of the article's links [1] is very slow. The other [2] is quite fast, but doesn't use base32. However, this [3] native C extension is fast, about 15% faster than the UUID function on my machine.
On my machine, using pg-ulid, inserting 1M rows was on…
Mine is https://blog.lawrencejones.dev/
I’ve been working as a software engineer for about a decade now, primarily at startups. Recently the first employee at incident.io, before then working at GoCardless.
Tend to write about lessons I’ve learned that others find useful, or stories I think will be enjoyable. Helps me collect my thoughts and practice my writing!
Examples would be;
- Want to found a start-up? Work at one first (https://blog.lawrencejones.dev/learn-at-scale-up/)
- Adding latency: one step, two step, oops (https://blog.lawrencejones.dev/latency/)
- My most impactful code (https://blog.lawrencejones.dev/most-impactful/)
- On working too hard: finding balance, and lessons learned from others (https://blog.lawrencejones.dev/working-too-hard/)
Occasionally get a popular share on HN or people mentioning they read my blog in real life, which makes it feel worth it.