3 ms·
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 inser
by jeltz 6y ago
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.
- dtech 6y agoBut 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/