Search code examples
mysqlsqlpostgresqlprimary-keysqldatatypes

Right choice of data type for UUID as primary key

Prior Conditions are

  1. Table will have data in billions
  2. Table will have secondary indexes
  3. Table's primary key will be a foreign key of another table.
  4. Table will have a heavy data (another column may be Text).

Primary Key must be Unique, cause my database is replicated over machine's that's why I am choosing UUID.

PS: space is also a concern so I guess Varchar(36) might be the bad idea

Solution

  • I agree with BINARY(16). (16 bytes is better than 37.)

    But UUIDs are hopelessly inefficient for huge tables. (I assume your billion-row table will not fit in RAM.)

    I discuss those and more issues in http://mysql.rjweb.org/doc.php/uuid