Hacker News Reader

Comment by jeltz | Hacker News Reader

Because inserting at the end of a btree (the data structured used by most databases for indexes) is much cheaper than doing random inserts, and as a bonus inserting at the end does not create index bloat since you can mostly avoid page splits. So you will get faster inserts and smaller indexes.

View on HN

But is there an alternative if you need these hard-to-guess public identifiers? They will always have some randomness and you'll need to index them so it seems unavoidable.

Use integers as primary keys and don't expose them, and add a (unique) random ID column that's used to interface with the rest of the world.

(I understand there'll still be an index for the public key -- the argument would be that you don't bear the cost for that for most queries.)

This stackoverflow answer to a related question gives a better suggestion https://stackoverflow.com/a/6955055/3403369:

Create a separate table mapping integers to unique IDs, and join with this. You can generate that in advance, so can avoid gradually building the index.

> the argument would be that you don't bear the cost for that for most queries

Your INSERT becomes even more expensive because you have 2 update 2 indexes, and your lookup doesn't change.

I get the benefit if you use the id as a FK or if the database must have data on disk in PK order like MySQL or MSSQL (?). On PostgreSQL if you just insert & read on that key only it seems objectively worse.

Encrypt them on the backend before exposing them to the public. Decrypt in the backed on access. See https://hashids.org/

Reply on news.ycombinator.com