4 ms·
A much simpler solution is to keep your tables as they are (with an integer primary key), but add a non sequential public identifier too. id => 123, public_id
by grugdev42 10mo ago
A much simpler solution is to keep your tables as they are (with an integer primary key), but add a non sequential public identifier too.
id => 123,
public_id => 202cb962ac59075b964b07152d234b70
There are many ways to generate the public_id. A simple MD5 with a salt works quite well for extremely low effort.
Add a unique constraint on that column (which also indexes it), and you'll be safe and performant for hundreds of millions of rows!
Why do we developers like to overcomplicate things? ;)
- Denvercoder9 10mo agoThis misses the point. The reason not to use UUIDv4 is that having an index on random values is slow(er), because sequential inserts into the underlying B-tree are faster than random inserts. You're hitting the same problem with your `public_id` column, that it's not the primary key doesn't change that.
- hnfong 10mo agoInts as pk would be quicker for joins etc though.
- grugdev42 10mo agoExactly
- sgarland 10mo agoFor InnoDB-based DBs that are not Aurora, and if the secondary index isn’t UNIQUE, it solves the problem, because secondary non-unique index changes are buffered and written in batches to amortize the random cost. If you’re hashing a guaranteed unique entity, I’d argue you can skip the unique constraint on this index. For Aurora MySQL, it just makes it worse either way, since there’s no change buffer.
- Retr0id 10mo agoPer https://news.ycombinator.com/item?id=46273325 https://news.ycombinator.com/item?id=46273325, if you use a block cipher rather than a hash then you don't even need to store it anywhere.