Because of supporting offline clients I unfortunately have to replace some of my integer Primary Key’s for UUID’s (but not necessarily the 128-bit RFC 4122 version). I’m on Postgres 12 (could upgrade to 13, but there seems nothing in the changelog related to this). What would be the best performing option? I have some thoughts written below. Any insights/answers?
To improve overall performance, I am assuming that shorter is better for the PK.
As I have a degree of control over the application, for example there cannot be more than one ID per second, I can have a key that is shorter than the typical UUID, which I think is pretty long.
I have been informing myself on Postgres PK’s, and learned that they are B-Tree’s, and may (if I understand it correctly) benefit from monotonically increasing values.
I’m thinking of making a Base36 key composed of three parts:
The Unix epoch in Base36 (6 characters, monotonic) (for ex. right now it’s feb-7 2021, or 1612725396 seconds since 1-1-1970 => QO6AVO)
..followed by a 4 character Base36 client fingerprint, which is reoccurring, but still a random number.
..and a Base36 random number of 5-7 positions (still contemplating the length) which would not be monotonic at all.
The resulting key will thus not be entirely monotonous, but still somewhat.
A key could look like for example: QO6AVO2N9C5O6AQT1 and could be saved as VARCHAR(17) or as BIGINT or BIGSERIAL. What would be more performant, and why? I read different opinions on it on various blogs…
Alternatively, I could use the CUID provided by https://www.npmjs.com/package/cuid. Also a Base36, but it is slightly larger, but has the same level of monotony. Allegedly there are DB’s that use that package for there native ID generation.
A benefit of UUID’s is that they cannot be guessed. My application has no need of that.