Hacker News Reader

Comment by Kwpolska | Hacker News Reader

> your database is spending all it's time shuffling everything around to insert your new rows in the middle of everything

Is it? The order of data on disk tends to be the insertion order. What might get shuffled around is the index for the primary key, but indexes can handle that quite well.

View on HN

And the answer is "depends on the database tech".

Here are some nice benchmarks: https://www.toomanyafterthoughts.com/uuids-are-bad-for-datab...

I learned the lexically-sortable-id trick from wizened DBAs in the 90s. It's an old trick. I've applied it myself to speed things up and have used both the ULID types described in the article and UUIDv7 etc.

I even advocate using UUID7 on BigQuery where there are no indexes. Having sortable keys is nice even if it doesn't add speed.

You should definitely bench your storage to confirm that it likes hotspots (some don't!)

You should, but B-tree is the most common data structure used for database indexes, and sequential order performs better than random order for index keys.

You often can't bottleneck the entire workload on the capacity of one cluster node and one storage device.

I can't really understand what you said, could you elaborate? :)

If one machine can only handle a hundred transactions per second, and you might ever receive more than that, you have to spread the work across several machines, probably by assigning primary keys that aren't (mostly) sequential.

usually in sharding schemes its a few bits of a hash of the id that are used to determine shard; the system or library you are using will take care of this transparently for you.

Reply on news.ycombinator.com