Using a UUID as a primary key in Django models (generic relations impact)
The real problem with UUID as a PK is the disk fragmentation and insert degradation associated with non-numeric identiifers. Because the PK is a clustered index (in virtually every RDBMS except PostgreSQL), when it's not auto-incremented, your DB engine will have to resort your physical drive when inserting a row with an id of lower ordinality, which will happen all the time with UUIDs. When you get lots of data in your DB, it may take many seconds or even minutes just to insert one new record. And your disk will eventually become fragmented, requiring periodic disk defragmentation. This is all really bad.
To solve for these, I recently came up with the following architecture that I thought would be worth sharing.
The UUID Pseudo-Primary-Key
This method allows you to leverage the benefits of a UUID as a Primary Key (using a unique index UUID), while maintaining an auto-incremented PK to address the fragmentation and insert performance degredation concerns of having a non-numeric PK.
How it works:
- Create an auto-incremented primary key called
pkid
on your DB Models. - Add a unique-indexed UUID
id
field to allow you to search by a UUID id, instead of a numeric primary key. - Point the ForeignKey to the UUID (using
to_field='id'
) to allow your foreign-keys to properly represent the Pseudo-PK instead of the numeric ID.
Essentially, you will do the following:
First, create an abstract Django Base Model
class UUIDModel(models.Model):
pkid = models.BigAutoField(primary_key=True, editable=False)
id = models.UUIDField(default=uuid.uuid4, editable=False, unique=True)
class Meta:
abstract = True
Make sure to extend the base model instead of models.Model
class Site(UUIDModel):
name = models.CharField(max_length=255)
Also make sure your ForeignKeys point to the UUID id
field instead of the auto-incremented pkid
field:
class Page(UUIDModel):
site = models.ForeignKey(Site, to_field='id', on_delete=models.CASCADE)
If you're using Django Rest Framework (DRF), make sure to also create a Base ViewSet class to set the default search field:
class UUIDModelViewSet(viewsets.ModelViewSet):
lookup_field = 'id'
And extend that instead of the base ModelViewSet for your API views:
class SiteViewSet(UUIDModelViewSet):
model = Site
class PageViewSet(UUIDModelViewSet):
model = Page
More notes on the why and the how in this article: https://www.stevenmoseley.com/blog/uuid-primary-keys-django-rest-framework-2-steps
A UUID primary key will cause problems not only with generic relations, but with efficiency in general: every foreign key will be significantly more expensive—both to store, and to join on—than a machine word.
However, nothing requires the UUID to be the primary key: just make it a secondary key, by supplementing your model with a uuid field with unique=True
. Use the implicit primary key as normal (internal to your system), and use the UUID as your external identifier.
As seen in the documentation, from Django 1.8 there is a built in UUID field. The performance differences when using a UUID vs integer are negligible.
import uuid
from django.db import models
class MyUUIDModel(models.Model):
id = models.UUIDField(primary_key=True, default=uuid.uuid4, editable=False)
You can also check this answer for more information.