We are running some nightly data sync jobs which move data from an external database into postgres using an upsert statement. The data is updated in batches and using parallel workers (10 currently).

The problem we are having is that even when no data is changed (e.g. when running two consecutive syncs with no changes in between), there is a high amount of disk io (100% disk usage, 200 MB/s is writes). Our monitoring reports that there are no updated rows and only reads are performed on the table itself.

The expected behavior would be a high amount of reads but close to zero write operations on the disk. We can reproduce this consistently, even when running two syncs on a previously empty table.

What can be the reason for this high amount of disk write operations?

Some more details on our setup:

  • Postgres 12.5 running on Kubernetes with 25 GB of memory
  • 1TB disk with 3000 IOPS gp3 volumes (AWS)
  • Table has around 570 millions rows (120 GB)
  • Primary key index is 43 GB

Upsert-Statements used:

INSERT INTO ... (...)
VALUES (...)
ON CONFLICT (primayKeys...)
WHERE (tableColumns...) IS DISTINCT FROM (excludedColumns...)
  • I assume you did confirm there are no triggers on the table that might fire.
    – mustaccio
    Jan 11, 2021 at 16:08
  • @mustaccio yes, there are no triggers on the table
    – Birne94
    Jan 11, 2021 at 16:15

1 Answer 1


The DO UPDATE locks the "victim" tuple before it evaluates the WHERE part. This dirties the page containing the tuple, even if the WHERE ends up short-circuiting the UPDATE.

  • So if I understand this correctly, these locks are what cause the high amount of disk writes? Is there any way to avoid locking the row? (This should not be a problem since the sync job is the only writer in the system).
    – Birne94
    Jan 12, 2021 at 7:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.