take a uuid, for comparison
- you can add backdated entries, unlike autoincrement
- you can combine multiple tables without worrying about collisions in analytical queries, unlike autoincrement
- end users can't guess all ids in the table, or next id, unlike autoincrement
- it's easy to shard a uuid table and add entries independently
- a uuid forces you to use a timestamp for ordering, but you'll add a timestamp anyway because "it got created after record 43" ain't that useful
using autoincrement in a database table is a performance optimization, it works best when the table is entirely internal, and none of the ids will be visible to users. if you're using it for external facing tables, you need to be sure it's not going to wrap around, or need to be partitioned, combined with other tables, or have backdated entries.
you also need to encrypt the ids, or be sure that users being able to guess the next id, or list all ids, won't cause issues (spoiler: it always does, but these aren't insurmountable problems)
yes, for lots of little toy applications, or databases that never have more entries than ram, it's really not that big a deal—well, except for the security implications
either way, a uuid gives you flexibility that is unmatched by autoincrement, and therefore makes for a sensible, risk free, default choice. in truth, using an autoincrement is a performance optimization few meaningfully benefit from.
if you're not comfortable talking about clustered indexes and covering indexes, don't do it. if you've got a table of 20 entries and want easy, memorable keys, just give them memorable keys, rather than arbitrarily numbering them.
call it a slug, or a vanity key, but at least this way no-one works out they're customer #2
i'm not that fussed. you can either use uuids now, or you can add them later, when you backdate entries, combine tables, or partition your table, or, my favourite: when your HA database falls over, and autoincrement starts handing out already assigned keys, and all hell breaks loose.
enemies can let enemies use autoincrement, that's fine.
ps. if you reply to this with "yes, but have you considered it saves space, and it goes faster" yes that's what "performance optimization" means. if you consider replying to this with "but this one particular case benefits more from doing it" then i would like to remind you that (a) i'm talking about default choices and (b) we can be enemies, it's fine
