How to integrate postgresql 10/11 declarative table partitioning (i.e. PARTITION BY clause) in a Django model?

Question

PostgreSQL 10 introduces declarative table partitioning with the PARTITION BY clause, and I would like to use it to a Django model.

In principle all what I would need to do is introduce the PARTITION BY clause at the end of the CREATE TABLE statement that the Django ORM creates.

CREATE TABLE measurement (
    city_id         int not null,
    logdate         date not null,
    peaktemp        int,
    unitsales       int
) PARTITION BY RANGE (logdate);

Is it possible to insert this clause into the model? I thought that maybe there is a way to somehow append custom SQL to the query that the ORM generates, e.g. using the Meta:

class Measurement(models.Model):
    ...
    class Meta:
        append = "PARTITION BY RANGE (logdate)"

As far as I am concern the above is not possible. I have also look into the architect library, but it does not use the new PARTITION BY clause. Instead, it uses inheritance and triggers so the code does not suggest any way in which I could append the clause (neither it does for other databases, e.g. MySQL).

I have also though of customizing the migrations, by adding an ALTER TABLE... operation, e.g.:

operations = [
    migrations.RunSQL(
        "ALTER TABLE measurement PARTITION BY RANGE (logdate)",
    ),
] 

Unfortunately, the above (or similar) doesn't seem to be supported in PostgreSQL ALTER TABLE statement(at least not yet).

A final idea would be to retrieve the CREATE TABLE statement that the Django model generates, before sending the query, e.g. sql = Measurement.get_statement() where Measurement is the model. Then, I could append the PARTITION BY clause, and send the query directly. I couldn't find any method that returns the statement. I went through the Django create_model code and the sql is generated and directly send to the database, so it would not be easy to extract the statement from there.

Does anybody has a clue how this could be achieved in a way in which I can I still use the benefits of the Django ORM?

Answer 1

An approach I suggest trying is to use a SQL capturing schema editor to collect the SQL necessary to perform the create_model. That's what powers the sqlmigrate feature by the way.

from django.db.migrations import CreateModel

class CreatePartitionedModel(CreateModel):
    def __init__(self, name, fields, partition_sql, **kwargs):
        self.partition_sql = partition_sql
        super().__init__(name, fields, **kwargs)

    def database_forwards(self, app_label, schema_editor, from_state, to_state):
        collector = type(schema_editor)(
            schema_editor.connection, collect_sql=True, atomic=False
        )
        with collector:
            super().database_forwards(
                app_label, collector, from_state, to_state
            )
        collected_sql = collector.collected_sql
        schema_editor.deferred_sql.extend(
            collector.deferred_sql
        )

        model = to_state.apps.get_model(app_label, self.name)
        create_table = 'CREATE TABLE %s' % schema_editor.quote_name(
            model._meta.db_table
        )
        for sql in collected_sql:
            if str(sql).startswith(create_table):
                sql = '%s PARTITION BY %s' % (sql.rstrip(';'), self.partition_sql)
            schema_editor.execute(sql)

From that point you should simply have to replace your makemigrations auto-generated CreateModel with a CreatePartitionedModel operation and make sure to specify partition_sql='RANGE (logdate)'.