On: Pg-shortkey: YouTube-Like Short IDs as Postgres Pr...

[dtech]

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.

[rstarast → dtech]

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.)

[rstarast → rstarast]

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.

[dtech → rstarast]

> 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.

[opmac → dtech]

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