PostgreSQL表分区Django项目 [英] Postgresql Table Partitioning Django Project

问题描述

我有一个使用Postgres 9.3的Django 1.7项目。我有一张桌子，桌子会很大。该表每月将有1300万到4000万新行。

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.

我想知道将Postgres表分区与Django结合的最佳方法是什么？

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

推荐答案

只要您使用 继承 ，然后仅将父表连接到您的 Django 模型，分区对 Django 应该是完全透明的。也就是说，除非明确使用 ONLY 关键字，否则父表上的 SELECT 将级联到分区。

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).

请注意，分区确实增加了复杂性，需要实施一种编程方法来确定何时需要创建新分区，然后再创建它们-或以一定间隔手动执行此操作。根据您的确切数据和业务逻辑，您很有可能还需要实现触发和规则来确定要划分到哪个分区，例如将INSERT 插入到其中（因为您不想将 INSERT 插入父表）。但是，这些也应该从 Django 中抽象出来。

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.

还值得考虑的是，您是否需要随着时间的推移而创建的真实分区，或者是否需要继承例如，表 foo 和 foo_archive 的模型就足够了，其中 foo_archive 继承自 foo ，并且定期地（例如脚本）将较旧的数据移动到 foo_archive 中以保持 foo 较小。

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.

这篇关于PostgreSQL表分区Django项目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！