Add parameter to optimize inserts when using partitioning with PostgreSQL

[champ]

Insert performance, when using partioning with PostgreSQL, could be increased significantly by avoiding the extra insert and delete in the parent table. This can be achieved by returning NULL instead of NEW in the before insert trigger function, thus aborting the insert action in the parent table. The only downside to this is that subsequent triggers aren't fired. Because of this, a good solution would be to include this optimization as an option in the partition configuration.

[maxtepkeev]

Hi Hampus,

Yes, this is a known issue, I'm also aware or the return NULL approach. The reason why it's not used is because in the old days, Django ORM wasn't working with return NULL at all, because it was expecting to receive a newly created object from the database to get it's id.

I haven't tested how new Django versions behave with return NULL approach, maybe this is now fixed and we can drop support for old Django versions in favour of new ones and use the return NULL which as you said will give us an increase in performance. All other supported ORMs should be tested as well.

[jared-mackey]

I might be interested in taking this on as a learning experience. If you guys could provide an example of the optimized version and the original I would take a look at it. We are using this on our DB and I would like to optimize it as much as possible.

[maxtepkeev]

@mackeyja92 Contributions are always welcome. At first we need to check that most recent version of Django is capable of working with this approach, have a look here. This line should be changed to RETURN NULL;. After that rerun architect's partition command to refresh trigger in the database or alternatively you can just modify this line in trigger directly right in the database.

After that try to do some inserts through Django ORM and see if everything works as expected, i.e. you receive newly created object after the call to Model.objects.create(**kwargs), the partition is created successfully and so on. If everything works with Django then, ideally, you need to test the same with older Django versions to find out which of them doesn't support this method so we know which versions to drop, also other supported ORMs should be tested as well. After that let us know the results and I can tell what to do next.