How to add partitioning into an existing model? pgmakemigrations command does not create the required migration

[kaletvintsev]

Can't add partitioning to existing models. In my app I have the model, which I want to optimize by partitioning. My old model:

class ProjectKeyword(models.Model):
    collected_at = models.DateTimeField(default=timezone.now, db_index=True)
    ...

Partitioned model:

class StatRecord(PostgresPartitionedModel):
    class PartitioningMeta:
        method = PostgresPartitioningMethod.RANGE
        key = ["collected_at"]
    collected_at = models.DateTimeField(default=timezone.now, db_index=True)

I have a partitioning manager in app/partitioning.py. And PSQLEXTRA_PARTITIONING_MANAGER param in settings.

from dateutil.relativedelta import relativedelta

from psqlextra.partitioning import (
    PostgresPartitioningManager,
    PostgresCurrentTimePartitioningStrategy,
    PostgresTimePartitionSize,
    partition_by_current_time,
)
from psqlextra.partitioning.config import PostgresPartitioningConfig

from core.models import StatRecord, USP

manager = PostgresPartitioningManager([
    PostgresPartitioningConfig(
        model=StatRecord,
        strategy=PostgresCurrentTimePartitioningStrategy(
            size=PostgresTimePartitionSize(weeks=1),
            count=4*12,
        ),
    ),
])

But python manage.py pgmakemigrations make strange migrations, which do not allow working with partitions. Generated migration:

# Generated by Django 3.2.13 on 2023-09-13 14:37

from django.db import migrations
import psqlextra.manager.manager

class Migration(migrations.Migration):

    dependencies = [
        ('core', '0012_auto_20230629_1704'),
    ]

    operations = [
        migrations.AlterModelManagers(
            name='statrecord',
            managers=[
                ('objects', psqlextra.manager.manager.PostgresManager()),
            ],
        ),
    ]

And after applying this migration I can't run python manage.py pgpartition I have this error

Traceback (most recent call last):
  File "/Users/orphey/Coding/skm/manage.py", line 22, in <module>
    main()
  File "/Users/orphey/Coding/skm/manage.py", line 18, in main
    execute_from_command_line(sys.argv)
  File "/Users/orphey/Coding/skm/venv/lib/python3.11/site-packages/django/core/management/__init__.py", line 419, in execute_from_command_line
    utility.execute()
  File "/Users/orphey/Coding/skm/venv/lib/python3.11/site-packages/django/core/management/__init__.py", line 413, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/Users/orphey/Coding/skm/venv/lib/python3.11/site-packages/django/core/management/base.py", line 354, in run_from_argv
    self.execute(*args, **cmd_options)
  File "/Users/orphey/Coding/skm/venv/lib/python3.11/site-packages/django/core/management/base.py", line 398, in execute
    output = self.handle(*args, **options)
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/orphey/Coding/skm/venv/lib/python3.11/site-packages/psqlextra/management/commands/pgpartition.py", line 72, in handle
    plan = partitioning_manager.plan(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/orphey/Coding/skm/venv/lib/python3.11/site-packages/psqlextra/partitioning/manager.py", line 52, in plan
    model_plan = self._plan_for_config(
                 ^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/orphey/Coding/skm/venv/lib/python3.11/site-packages/psqlextra/partitioning/manager.py", line 84, in _plan_for_config
    table = self._get_partitioned_table(connection, config.model)
            ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/orphey/Coding/skm/venv/lib/python3.11/site-packages/psqlextra/partitioning/manager.py", line 121, in _get_partitioned_table
    raise PostgresPartitioningError(
psqlextra.partitioning.error.PostgresPartitioningError: Model StatRecord, with table core_statrecord does not exists in the database. Did you run `python manage.py migrate`?

[gabriel-v]

scrolled through the closed issues a bit and https://github.com/SectorLabs/django-postgres-extra/issues/164

i guess our next steps are:

• add new partitioned model with different name
• write RunPython migration to copy data over
• write another migration to drop old table
• write another migration to rename table into the old name

And if you have foreign keys pointing to the table you want to partition, there'll be complications when dropping the old table - the partitioned table will probably have different primary keys now (because they will include the partition keys) -- so you will have to rebuild all the FKs by doing the above create/copy/drop/rename for those too

and don't forget about the reverse migrations :D

good luck!

[sknutsonsf]

See this article about how to do it: https://rodoq.medium.com/partition-an-existing-table-on-postgresql-480b84582e8d Seems to be working so far, but I am not yet in production with the change.

Note: Postgres will want the primary key as (id, timestamp) if timestamp is your partitioning column. ID must be first or update won't work quickly.