Hacker News Reader

> And yes, absolutely _never_ make it your primary key.

If I dont have lots of range queries, why not then? Only because of bloating (I think fragmentation is a more precise term here)?

View on HN

Because there's a good chance down the line you will need to do some sort of range query. Let's say you want to add and backill a column. Not too bad, you create a partial index where the column is null and use that for backfilling data.

But at a certain scale that starts taking too long and a bigint column would be quicker. Or you decide you need to periodically scan the table in batches for some reason. Perhaps to export the contents to a data warehouse as part of an initial snapshot.

You can skip enumerating these possibilities by having a bigint surrogate key from the get go. There's other advantages as well like better joins and temporal locality when the bigint index can be used rather than the uuid.

Reply on news.ycombinator.com