Привет.
Это топик о том, как относительно быстро и безболезненно настроить секционирование (партицирование) таблицы по месяцам, если вы используете Django+PostgreSQL. Многое из описанного подойдёт и для других фреймворков и ORM.

О том, что такое секционирование и зачем оно нужно, можно почитать, например, здесь, здесь и здесь.

Итак, есть проект на Django и таблица одной из моделей должна быть очень большой. Если чтение из этой таблицы происходит часто, и всегда известен период времени, в который запись была сделана, секционирование ускорит работу базы данных.

Каждый раз писать запросы для включения секционирования не очень хочется, так что попробуем автоматизировать. Хорошо, если на выходе получится что-то, что может использовать и не сильно знакомый с SQL человек. I've read the docs, so you don't have to.

Сперва я расскажу, как быстро попробовать мои наработки, а потом о том, что под капотом. Действуем так:

Ловим syncdb, чтобы можно было добавить команды секционирования.
Подключаем SQL, который вытащит установленные индексы, создаст секции, активирует индексы на них, добавит функции и триггеры.
Активируем секционирование в конечных приложениях и моделях.

Сначала устанавливаем пакет из репозитория

pip install git+https://github.com/podshumok/django-post-syncdb-hooks

и подключаем пару приложений:

INSTALLED APPS = (
    # ...
    'post_syncdb_hooks',
    'post_syncdb_hooks.partitioning',

    'your.app',
    # ...
)

Пусть есть модель в yourapp/models.py:

from django.db import models

class MyModel(models.Model):
    date_created = models.DateTimeField()
    my_data = models.IntegerField(db_index=True)

добавляем файл yourapp/sql/post_syncdb-hook.postgresql_psycopg2.sql (можно подредактировать интервалы под нужды):

SELECT month_partition_creation(
    date_trunc('MONTH', NOW())::date,
    date_trunc('MONTH', NOW() + INTERVAL '1 year' )::date,
    'yourapp_mymodel', 'date_created');

запускаем syncdb:

./manage.py syncdb

… и секции созданы.

Если это первый syncdb, то в созданных секциях не будет индексов. Чтобы исправить это, нужно запустить syncdb ещё раз.

Что ж, база теперь готова, а вот Django — ещё нет. Дело в том, что, начиная с версии 1.3 Django конструирует для PostgreSQL запросы INSERT INTO, добавляя в них RETURNING..., чтобы получить id вставленной записи. А метод секционирования, который мы используем, не поддерживает этой возможности.

Мы можем грубо заставить Django не использовать RETURNING нигде:

from django.db import connections, DEFAULT_DB_ALIAS

connections[DEFAULT_DB_ALIAS].features.can_return_id_from_insert = False

А можем отредактировать нашу модель так, чтобы RETURNING не использовался только с ней:

from post_syncdb_hooks.partitioning import to_partition

class MyModel(models.Model):
    "..."
    #...

    @to_partition
    def save(self, *args, **kwargs):
        return super(MyModel, self).save(*args, **kwargs)

Итак, база готова, Django готов, но готовы ли мы? Чтобы запросы на чтение не приводили к опросу базой всех секций, мы должны фильтровать QuerySet'ы по полю, для которого реализовано секционирование (в примере — date_created):

qs = MyModel.objects.filter(date_created__lte=..., date_created__gt=...)

Также стоит позаботиться о том, чтобы нигде не происходил, без особой надобности, count() всех записей: например, paginator в админке любит это делать.

Теперь все готовы. Всё.

Подкапотное про `post_syncdb_hooks`

post_syncdb_hooks содержит management.py, в котором подключается ресивер для сигнала post_syncdb. Этот ресивер или хук вызывается для всех установленных приложений. Он просматривает, не лежит ли рядом с файлом models.py того или иного приложения папка sql, и, если лежит, нет ли там файлов post_syncdb-hook.sql или post_syncdb-hook.(backend).sql, которые можно запустить.

post_syncdb_hooks.partitioning как раз и содержит такой SQL файл. В нём мы создаём SQL-функцию, которая принимает четыре параметра: дату начала, дату конца, имя таблицы и имя поля. При вызове функции, для указанной таблицы создаются секции по выбранному полю, начиная с даты начала до даты конца с шагом в месяц, а также триггер для INSERT INTO и индексы таблиц-секций (если индексы заданы для основной таблицы).

Подкапотное про индексы

Самым скучным и сложным для меня оказалось создание индексов для секций. Когда есть написанный SQL для создания таблицы и мы переходим к схеме с секционированием, то проблем нет — копипаст. А вот, когда Django создаёт таблицы, не очень понятно как заставить его не создавать индексы для мастер-таблицы, но сохранить соответствующий SQL. В итоге, я решил: пусть Django таки создаст индексы (они всё равно будут пустыми), а я их скопирую в секции.

Осталось решить, как это проделать.

У psql есть опция -E, которая заставляет его выводить SQL, генерируемый внутренними командами. Так что,

sudo -u postgres psql -E db

db=# \di
********* QUERY **********
SELECT n.nspname as "Schema",
  c.relname as "Name",
  CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' WHEN 'f' THEN 'foreign table' END as "Type",
  pg_catalog.pg_get_userbyid(c.relowner) as "Owner",
 c2.relname as "Table"
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
     LEFT JOIN pg_catalog.pg_index i ON i.indexrelid = c.oid
     LEFT JOIN pg_catalog.pg_class c2 ON i.indrelid = c2.oid
WHERE c.relkind IN ('i','')
      AND n.nspname <> 'pg_catalog'
      AND n.nspname <> 'information_schema'
      AND n.nspname !~ '^pg_toast'
  AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;
**************************
db=#

Немного копипаста и у нас есть всё для того, чтобы создать индексы для таблиц-секций.

Спасибо!

Представленный здесь пакет используется мною в паре проектов и довольно успешно справляется с поставленными перед ним задачами.
Спасибо всем, кто это прочитал. Надеюсь, сделанное кому-нибудь пригодится.
Также прошу отмечать в комментариях и на гитхабе найденные недостатки, предлагать улучшения и расширения.

Подкапотное про post_syncdb_hooks

Подкапотное про индексы

Спасибо!

Подкапотное про `post_syncdb_hooks`

Секционирование таблиц моделей в Django с PostgreSQL