4 ms·
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 ther
by rstarast 6y ago
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 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.