We use Postgres and prisma for our Next.js app. Previous developers have used cuid for every table on our schema. For some reasons we are restructuring the tables and I was wondering would it be better to use int ids? Would it result in any performance gain?
What are the tradeoffs between using Int autoincrement id vs cuid for Postgres prisma client?
If you start comparing GUID vs Int ids for Postgres, please quote authentic reference proving that cuid is mapped to guid for Postgres.
The most important reasons for which CUID may be used, are 1. Obscure the creation order, and 2. to generate primary keys outside the database and in a distributed environment.
Unfortunately, if one is using CUID for security reasons, you end up exposing even more info. As per cuid2,
The original Cuid leaked details about the id, including very limited data from the host environment (via the host fingerprint), and the exact time that the id was created.
Cuid2 solves all these limitations.
Also, as per the discussion here, autoincrement() has substantial performance benefits over using string IDs.
Use cuid2 if you want to support horizontal scaling and/or too concerned about attackers being able to guess your IDs. It produces secure, collision-resistant IDs optimized for horizontal scaling and performance and also obscures the creation order.
But, cuid2 as the official document states, “But not too fast: If you can hash too quickly you can launch parallel attacks to find duplicates or break entropy-hiding. For unique IDs, the fastest runner loses the security race.”.
Thus, if you are not concerned about attackers being able to guess the next/previous IDs, autoincrement() may be the best choice.