RE: Query Crashes PG 10.3 using partitions, works on 9.6

Hi Cory,

You are running the query (DELETE statement) as if the table is not partitioned which is causing the server to crash.
Please run that query for each partitions separately in a loop with dynamic query and you should see the improvement. It should be pretty quick.

Regards,
Virendra.

From: Kumar, Virendra
Sent: Wednesday, March 28, 2018 5:57 PM
To: Cory Tucker; pgsql-general(at)postgresql(dot)org
Subject: RE: Query Crashes PG 10.3 using partitions, works on 9.6

Would be nice if you can attach explain plan of course, explain analyze is not going to work if server is crashing.

Regards,
Virendra

From: Cory Tucker [mailto:cory(dot)tucker(at)gmail(dot)com]
Sent: Wednesday, March 28, 2018 5:49 PM
To: pgsql-general(at)postgresql(dot)org<mailto:pgsql-general(at)postgresql(dot)org>
Subject: Query Crashes PG 10.3 using partitions, works on 9.6

Hey guys, I am in the middle of testing out a database migration from 9.6 to 10.3. We have a quasi-multi tenant based application and so are utilizing native partitions on some relations to help improve some performance.

I was issuing a query on both databases to cleanup some duplicates in preparation of applying new indexes. On the 9.6 database with all the data in one table, the query runs fine in about 6 min. On 10.3, with a work_mem setting of 1GB the query runs for about 7 minutes and then gets terminated with an out of memory error. If I bump the work_mem up fairly high (12GB out of 52GB available) the server actually crashes.

On the both databases, the total dataset size is exactly the same, ~29M records. The table looks like this:

Table "candidate_person"
Column | Type | Collation | Nullable | Default
--------------+--------------------------+-----------+----------+---------
created_at | timestamp with time zone | | not null | now()
modified_at | timestamp with time zone | | not null | now()
account_id | bigint | | not null |
candidate_id | character varying(40) | C | not null |
first_name | text | | |
middle_name | text | | |
last_name | text | | |
spouse | boolean | | not null | false
suffix | text | | |
salutation | text | | |
Partition key: LIST (account_id)

With the only difference being on 9.6 there obviously isn't any partitions. On 10.3 there are ~250 partition tables.

I have attached the server log that shows the first out of memory and then the server crash and recovery.

________________________________

This message is intended only for the use of the addressee and may contain
information that is PRIVILEGED AND CONFIDENTIAL.

If you are not the intended recipient, you are hereby notified that any
dissemination of this communication is strictly prohibited. If you have
received this communication in error, please erase all copies of the message
and its attachments and notify the sender immediately. Thank you.

________________________________

This message is intended only for the use of the addressee and may contain
information that is PRIVILEGED AND CONFIDENTIAL.

If you are not the intended recipient, you are hereby notified that any
dissemination of this communication is strictly prohibited. If you have
received this communication in error, please erase all copies of the message
and its attachments and notify the sender immediately. Thank you.