Primary key best practices

[CTOJoe]

I have not found any clear documentation on best practices for performance when choosing primary keys for PlanetScale. I am curious if PlanetScale suffers the same performance issues as MySQL when using completely random (not lexicographical generated) primary keys. Would using a nanoid for a primary key affect high volume insert table performance due to the id having to be randomly inserted into the btree instead of appended to the end?

Thanks in advance for any insight on this matter.

[jgreet]

Hi @CTOJoe ,

PlanetScale databases are Vitess clusters of Innodb backed MySQL 8 instances so the same general guidelines apply.

Use integers and AUTO_INCREMENT for primary keys where possible. If you have to use strings, they should be as short as possible and sequential. Entirely random primary keys have the potential for performance problems on large or busy tables.

We use NanoIDs in our own application as external identifiers but the primary keys for those records are integers:
https://planetscale.com/blog/why-we-chose-nanoids-for-planetscales-api

[CTOJoe]

Thank you for your insight to this matter. That's what we figured, but wanted to get confirmation.

[aMacDawg]

hi @jgreet , I was following the planetscale Nano ID tutorial here and was wondering if there was an approach for generating the Nano ID thru the database trigger engine as opposed to putting this in application logic?

I think I understand the idea of why application logic might be the ideal place for something like this, especially with some of the model-wide controls for limiting collision risk but I would appreciate your confirmation!

[jgreet]

Unfortunately that won't be an option on PlanetScale databases today since they don't support creating triggers:

https://planetscale.com/docs/reference/mysql-compatibility

[205g0]

they should be as short as possible

but any kind of ULID with be minimum 26 bytes, this shouldn't have a huge perf impact along as they are created lexicographically (like ULID), right?