Postgresql – error query did explode the postgres database

I had something similar happen recently with a table of 3.5 million rows. My update would never finish. After a lot of experimenting and frustration, I finally found the culprit. It turned out to be the indexes on the table being updated.

The solution was to drop all indexes on the table being updated before running the update statement. Once I did that, the update finished in a few minutes. Once the update completed, I re-created the indexes and was back in business. This probably won't help you at this point but it may someone else looking for answers.

I'd keep the indexes on the table you are pulling the data from. That one won't have to keep updating any indexes and should help with finding the data you want to update. It ran fine on a slow laptop.

First, I would suggest reading this: http://ledgersmbdev.blogspot.com/2012/08/postgresql-or-modelling-part-3-table.html because although it doesn't cover partitioning, you are doing partitioning using table inheritance and this will cover a lot of details.

In general there are three ways you can handle this. The first is to use specialized key tables (managed with triggers) and reference these tables in your fkey constraints. This is often the simplest approach but it has significant costs in some cases.

The second possibility is you can partition your joining table by the foreign key. This can lead to major complexity problems though and so aside from rare cases, it is my least-favored approach.

A final possibility is you can write your own constraint triggers to manage foreign key enforcement.

Which approach you choose will be dependent on the complexity of joining information, particularly transitively joining information. The second approach allows you to do things entirely declaratively at a significant (possibly major) complexity cost, while the first and third move away from declarative approaches, meaning you may also want to use pgTAP to run test cases on your triggers.....

None of these solutions are without significant complexity. You may want to rethink whether partitioning is the best way to go here and whether partial indexes and other approaches will get you where you need to go.