1

Using PostgreSQL database and django-postgres-extra for creating a table partitioning in Django 4.2 application.

The database settings are

{
    "default": {
        "ENGINE": "psqlextra.backend",
        "NAME": "example1",
        "USER": "example1",
        "PASSWORD": "example1",
        "HOST": "database",
        "PORT": "5432",
        "ATOMIC_REQUESTS": False,
        "AUTOCOMMIT": True,
        "CONN_MAX_AGE": 0,
        "CONN_HEALTH_CHECKS": False,
        "TIME_ZONE": None,
    }
}

PSQLEXTRA_PARTITIONING_MANAGER = 'app.partitioning.manager'

and the model to be partitioned

class TrackingData(PostgresPartitionedModel):
    
    id = models.UUIDField(default=uuid.uuid4, primary_key=True, editable=False)
    dynamic_url_object_id = models.IntegerField(blank=True, null=True)
    ip_address = models.GenericIPAddressField(blank=False)
    user_agent = models.TextField(null=True, blank=True)

    scan_time = models.DateTimeField(blank=True, default=datetime.now)

    created = models.DateTimeField(auto_now_add=True)
    modified = models.DateTimeField(auto_now=True)

    class Meta:
        verbose_name_plural = 'Tracking Data'

    class PartitioningMeta:
        method = PostgresPartitioningMethod.RANGE
        key = ['scan_time']

then generated migration using the command python manage.py pgmakemigrations

but when running python manage.py migrate, it is giving error

django.db.utils.NotSupportedError: unique constraint on the partitioned table must include all partitioning columns
DETAIL:  UNIQUE constraint on table "tracking_trackingdata" lacks column "scan_time" which is part of the partition key.

Also tried the following update in the Meta class

class Meta:
    verbose_name_plural = 'Tracking Data'
    constraints = [
        models.UniqueConstraint(
            fields=['id', 'scan_time'],
            name='unique_scan_time_per_partition'
        )
    ]
Anuj TBE
  • 9,198
  • 27
  • 136
  • 285
  • Pls check similar answer [here](https://stackoverflow.com/a/75896058/4808122) PostgreSQL partitioning requires the PK of the entity to be a part of the partition key. To partition independently on the time (`scan_time`) you'll have to drop the primary key in `id` (which will lead to problems with *django*(?). Now you can partition on `id, scan_time` which probably makes less sense. – Marmite Bomber Apr 14 '23 at 09:26

0 Answers0