CODEWITHSUNDEEP
sqlpostgresqlindexing
Foobar
Foobar

Reputation: 8467

Are there any downsides to using nanoid for primary key?

I know that UUIDs and incrementing integers are often used for primary keys. I'm thinking of nanoids instead because those are URL friendly without being guessable / brute-force scrapeable (like incrementing integers).

Would there be any reason not to use nanoids as primary keys in a database like Postgres? (For example: Maybe they drastically increase query time since they aren't ... aligned or something?)

https://github.com/ai/nanoid

Upvotes: 17

Views: 12728

Answers (4)

user1843640
user1843640

Reputation: 3949

If you need to generate primary keys in a distributed system (not in the database), and decide that you are going to use NanoIDs or UUIDs as the primary key in a database like SQL Server (probably Postgres too), then you will want to make the primary key NONCLUSTERED and then have a CLUSTERED index on a different column with a sequential data type like INT, BIGINT or DATETIME2. This will help avoid fragmentation and the associated performance problems. Note: SQL Server creates a clustered index on the primary key by default. Might be the same with Postgres.

Upvotes: 0

SystematicFrank
SystematicFrank

Reputation: 17261

UUIDs are standardized by the Open Software Foundation (OSF) and described by the RFC 4122. That means that there will be far more chances for other tools to give you some perks around it.

Some examples:

  • MongoDB has a special type to optimize the storage of UUIDs. Not only a NanoID string will take more space, but even the binary takes more bits (126 in Nano ID and 122 in UUID)

  • Once saw a logging tool extracting the timestamp from the uids, can't remember which, but is is available

Also the long, non reduced version of UUIDs are very easy to identify visually. When the end user is a developer, it might help to understand the nature/source of the ID (like clearly not a database auto-increment key)

Upvotes: 0

Bill Karwin
Bill Karwin

Reputation: 562378

Most databases use incrementing id's because it's more efficient to insert a new value onto the end of a B-tree based index.

If you insert a new value into a random place in the middle of a B-tree, it may have to split the B-tree nonterminal node, and that could cause the node at the next higher level to split, and so on up to the top of the B-tree.

This also has a greater risk of causing fragmentation, which means the index takes more space for the same number of values.

Read https://www.percona.com/blog/2015/04/03/illustrating-primary-key-models-in-innodb-and-their-impact-on-disk-usage/ for a great visualization about the tradeoff between using an auto-increment versus UUID in a primary key.

That blog is about MySQL, but the same issue applies to any B-tree based data structure.

Upvotes: 14

Foobar
Foobar

Reputation: 8467

I'm not sure if there is a disadvantage to using nanoids, but they are often unnecessary. While UUIDs are long, they can be translated to a shorter format without losing entropy.

See the NPM package (https://www.npmjs.com/package/short-uuid).

Upvotes: 2

Related Questions