3 ms·

For the number 2, I think one issue is that you are going to be semi-frequently whacking the db to do a mapping of that random string id back to the real id.

For the number 2, I think one issue is that you are going to be semi-frequently whacking the db to do a mapping of that random string id back to the real id. OK for smaller entities but might be a pain if there's a lot of those ids to wrangle. You can throw a secondary index on it, but that will still have some minor fragmentation issues. One benefit of a random id is if you are working with more complex data models it can make creating those easier/faster. Instead of having a centralized location to get new ids from (the DB) you can create ids on the fly from the application which can turn the write into a single action from the application rather than a dance of inserting the main table, getting the new id, then inserting to the normalized tables.

caeril 2y ago
That's why the Good Lord invented caching. In most applications, 90% of your workload will be over ids less than a week old, so your hit rate is likely to be pretty high for this sort of mapping.
- cogman10 2y ago
  First hit can be a beast. It's workload/entity determinant if caching is enough for this. Not great if you are spending 1 minute on the first lookup just to do the mapping.
  - reissbaker 2y ago
    Although I think it'll be fine even without a cache for most workloads (it's super easily indexed since the cardinality of random data is extremely high), I'm curious what cache setup you've got that it would take a minute to do a KV lookup — Memcached or Redis for example should be single digit milliseconds or better.
reissbaker 2y ago
Assuming you've set up an index on the public ID string, that should be a super fast query even on a table with billions of rows. Beyond billions of rows you're looking at something a lot more custom than a standard SQL DB, but assuming you shard the data, it should still be a very fast query regardless of dataset size. Worst case you can cache, as others have mentioned, but TBH I think you don't need to for a query that simple and should save the cache space for something more complicated. Most SQL DBs are excellent for ready-heavy workloads; it's writes that tend to make them fall over.

caeril 2y ago

That's why the Good Lord invented caching. In most applications, 90% of your workload will be over ids less than a week old, so your hit rate is likely to be pretty high for this sort of mapping.

cogman10 2y ago
First hit can be a beast. It's workload/entity determinant if caching is enough for this. Not great if you are spending 1 minute on the first lookup just to do the mapping.
- reissbaker 2y ago
  Although I think it'll be fine even without a cache for most workloads (it's super easily indexed since the cardinality of random data is extremely high), I'm curious what cache setup you've got that it would take a minute to do a KV lookup — Memcached or Redis for example should be single digit milliseconds or better.

reissbaker 2y ago

Assuming you've set up an index on the public ID string, that should be a super fast query even on a table with billions of rows. Beyond billions of rows you're looking at something a lot more custom than a standard SQL DB, but assuming you shard the data, it should still be a very fast query regardless of dataset size. Worst case you can cache, as others have mentioned, but TBH I think you don't need to for a query that simple and should save the cache space for something more complicated. Most SQL DBs are excellent for ready-heavy workloads; it's writes that tend to make them fall over.