Upsert functions spinlock when doing insert first.

PostgreSQL version: 9.4.1
Operating system: CentOS 6.6

We have simple upsert functions part of our ETL process. These upsert functions execute an insert and update from staging to datawarehouse tables and finally mark all these staged rows as processed, all within the same transaction.

In our tests and production we have encountered problems where these functions get stuck at full CPU with zero disk I/O. We were able to repro the problem on small dataset (<100'000 rows) with a single thread. We fiddled with settings following best practices, adding large amount of memory, increasing WAL segments, etc... to no avail.

We identified the problem to be the update statement even though our tests insert tens of thousands of rows (millions in production) but only update a handful of rows each time. We thought it might have been a bad query plan due to outdated statistics but a full analyze doesn't fix the issue, sometime a postgresql restart fixes it but not always.

We might have identified a work around by having the update be done first followed by the insert which is semantically equivalent as they do not operate on the same rows. This has been the only reliable fix to avoid this problem. However this is not a fix and it is concerning to us as it is such a simple use case.

- Upsert function: http://pgsql.privatepaste.com/a547f35aa0

- Staging Table: http://pgsql.privatepaste.com/faeafdeb3d

- Datawarehouse Table: http://pgsql.privatepaste.com/07a53910e8
Thanks,
Alexander Porte
Sr. Director of Engineering, Analytics
Agilysys, Inc.<http://www.agilysys.com/>
Alexander(dot)Porte(at)agilysys(dot)com<mailto:Alexander(dot)Porte(at)agilysys(dot)com>
(o) 425.378.2295 * (m) 425.638.2220
Facebook<https://www.facebook.com/Agilysys?fref=ts> | Twitter<https://twitter.com/Agilysys> | LinkedIn<http://www.linkedin.com/company/6142?trk=tyah> | Agilysys Blog<http://news.agilysys.com/>