Unique constraint on jsonb type

Using Django Using the ORM
1

Hey there!

I have a model that contains a JSONField which I’m trying to add to a UniqueTogether constraint like this:

class AppCommand(models.Model):
  guild_id = models.BigIntegerField(null=True)
  data = models.JSONField()

  class Meta:
    constraints = (
      models.UniqueConstraint(fields=("guild_id", "data"), name="guild_data_unique_together"),
    )

However, the resulting index from Postgres is "guild_data_unique_together" UNIQUE CONSTRAINT, btree (guild_id, data), B-tree isn’t an appropriate index type for jsonb, and I’m of course hit with the following error when trying to do something:

django.db.utils.OperationalError: index row requires 8280 bytes, maximum size is 8191

Is there a way to specify to Django which index type to use for its constraint?
I’ve also read online[1] that you can use a hashing function instead, but I’m worried about the performance cost of doing this on large JSON objects? Postgres seems to already have a very good equality operator on jsonb objects.

Of course if there are better ways to do this, feel free to suggest alternatives!

  1. MD5-based uniqueness constraints in Django · Applied Cartography ↩︎

2

After further testing, I understood that Postgres doesn’t support any other index type than B-tree for unique constraints. It turns out the recommended solution is to use hashing, so I went for the following solution:

class AppCommand(models.Model):
  guild_id = models.BigIntegerField(null=True)
  data = models.JSONField()
  data_hash = models.GeneratedField(expression=Func(F("data"), function="jsonb_hash"), output_field=models.BigIntegerField())

  class Meta:
    constraints = (
      models.UniqueConstraint(fields=("guild_id", "data_hash"), name="guild_data_unique_together"),
    )

# and then this works
AppCommand.objects.bulk_create(objects, update_conflicts=True, unique_fields=("guild_id", "data_hash"), update_fields=("data",))

jsonb_hash[1] takes advantage of the data structure without casting to text, but it’s undocumented, so be careful when using it. A change of implementation would break uniqueness.

  1. PostgreSQL Source Code: src/backend/utils/adt/jsonb_op.c Source File ↩︎