Postgresql Table Partitioning Django Project

问题:

I have a Django 1.7 project that uses Postgres 9.3. I have a table that will have rather high volume. The table will have anywhere from 13million to 40million new rows a month.

I would like to know what the best way to incorporate Postgres table partitioning with Django?

回答1:

As long as you use inheritance, and then only connect the parent table to your Django model, the partitions should be entirely transparent to Django. That is, a SELECT on the parent table will cascade down to the partitions, unless the ONLY keyword is explicitly used (where applicable).

Note that partitioning does add complexity in terms of needing to implement a programmatic method of determining when new partitions need to be created, and then creating them -- or doing this manually at certain intervals. Depending on your exact data and business logic, it is quite likely you may also need to implement triggers and rules to determine which partition to, say, INSERT something into (since you wouldn't want to INSERT into the parent table). These, too, should be abstracted from Django, however.

I have found that, depending on the exact circumstances, this may need to be done with your main apps shutdown, lest the new partition creation cause a deadlock.

Also worth considering is whether you need true partitions which get created over time, or if an inheritance model of, say, tables foo and foo_archive would suffice, where foo_archive inherits from foo, and periodically something (e.g. a script) moves older data into foo_archive to keep foo smaller.

回答2:

You can use Architect application for Postgresql Table Partitioning Django Project

PostgreSQL’s partitioning implementation in Architect is done purely at the database level. That means that Architect creates several triggers and functions and inserts them directly into the database, so even if you issue direct insert statement from database console and not from the ORM, everything will work as expected and record will be inserted into the correct partition, if partition doesn’t exist, it will be created for you automatically. Also partitions may be created in any order and not only from lower to higher.

It's new version of old Django DB Parti application

回答3:

If you are using newer version of PostgreSQL, you can try this

https://github.com/chaitin/django-pg-timepart

A Django extension that implements PostgreSQL tables for partitioning and management based on dates.