5 ms·
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
by dtech 6y ago
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 6y agoUse 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 6y agoThis stackoverflow answer to a related question gives a better suggestion https://stackoverflow.com/a/6955055/3403369 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 6y ago> 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 6y agoEncrypt them on the backend before exposing them to the public. Decrypt in the backed on access. See https://hashids.org/ https://hashids.org/