PostgreSQL table inheritance

[Yennick Schepers]

The Django ORM multi-table inheritance approach works by creating a different table for every subclass. For working with these subclasses the ORM uses SQL table joins to query the database. This makes sense for databases such as MySQL and SQLite but not for PostgreSQL.

I suggest adding a PostgreSQL specific model that optimizes the ORM behaviour when developing for a PostgreSQL database. This allows for using the PostgreSQL object-relational table approach by the ORM. When implemented, a table used by a subclass inherits from the table used by the superclass, avoiding for example the usage of joins.

Another approach is optimizing the default behaviour of the Django ORM but this might impact existing Django implementations that expect traditional multi-table inheritance.

PostgreSQL 9.4 manual about table inheritance:
​http://www.postgresql.org/docs/9.4/static/ddl-inherit.html

[Tim Graham]

Seems worth exploring.

[Simon Charette]

I think it might be worth exploring a way to specify how model inheritance should be handled at the database level.

This could allow us to support MTI (the actual), STI (Single Table Inheritance) and PostgreSQL table inheritance.

IMHO it would be more worthwhile to abstract this whole concept with documented caveats and limitations because it's going to be hard to mimic the actual MTI implementation (Fk's as primary keys) with PostgreSQL table inheritance.

For example, constraints are not inherited by children tables so the following scenario would fail if we simply replaced the existing MTI model on PostgreSQL by table inheritance.

class Parent(models.Model):
    pass

class Child(Parent):
    pass

class Referent(models.Model):
    parent = models.ForeignKey(Parent)

CREATE TABLE parent (id serial PRIMARY KEY);
CREATE TABLE child () INHERITS (parent);
CREATE TABLE referent (
    parent_id int REFERENCES parent 
);

>>> child = Child.objects.create()
>>> ref = ParentReferent(parent=child)
IntegrityError ...

ERROR:  insert or update on table "referent" violates foreign key constraint "referent_parent_id_fkey"
DETAIL:  Key (parent_id)=(1) is not present in table "parent".

[Marc Tamlyn]

For the record, I did actually consider this when working out which features to include in contrib.postgres and ultimately rejected it as I'm really not sure how we can handle it. I also spoke to some postgres people who suggested that in most cases the way Django does inheritance is better anyway. This is of course all up for discussion, but I don't intend to try to implement this myself.

[Asif Saifuddin Auvi]

what about postgresql foreign table inheritence?

[Thomas Güttler]

Just for the records. There is a third party app which gives you single-table-inheritance: ​https://github.com/craigds/django-typed-models

This works for all database engines, not just PostgreSQL.

Maybe this is a solution if the other two types of inheritance (abstract or multi-table) don't fit your needs.

[Brecht Verhoeve]

I came across this ticket while exploring some more advanced PostgreSQL features in combination with Django.

I was wondering if the ticket is still open? Is it still worth exploring or were other solutions deemed more viable? Or is this a feature that isn't used much in practice?

[Simon Charette]

Based on the above discussions I think it might be better to invest time in adding an entry point in how Django model inheritance is represented at the database level rather then focusing on getting PostgreSQL multi-table inheritance working by default which would be highly backward incompatible.

It would be large undertaking but an API along the following lines would be interesting.

from django.db.models.inheritance import single_table_inheritance  # default is MTI
from django.contrib.postgres.inheritance import builtin_inheritance, partition_inheritance

class Event(models.Model):
    datetime = models.DateTimeField()

class SportEvent(Event, inheritance=builtin_inheritance):
    pass

class Event2020(
    Event, inheritance=partition_inheritance(
        field='datetime',
        range=(datetime(2020, 0, 0), datetime(2021, 0, 0)),
    ):
    pass

class STIEvent(models.Model, inheritance=single_table_inheritance):
    pass

That would allow different inheritance models to be used and implemented in third-party applications while maintaining support for the current abstract, proxy, and MTI based inheritance scheme.