Meningkatkan Performance UPDATE Statement di PostgreSQL

Kendala utama yang sering ditemukan ketika berurusan dengan data yang memiliki jumlah sangat besar adalah ketika ada kebutuhan untuk melakukan update secara keseluruhan pada suatu tabel. Pada PostgreSQL, melakukan update row satu per satu akan mengakibatkan execution time yang tinggi, seperti ditulis di PostgreSQL.org:

“If you allow each insertion to be committed separately, PostgreSQL is doing a lot of work for each row that is added. An additional benefit of doing all insertions in one transaction is that if the insertion of one row were to fail then the insertion of all rows inserted up to that point would be rolled back, so you won’t be stuck with partially loaded data.”

Di KMKLabs, kami memiliki tabel besar yang menyimpan lebih dari 1 juta rows data, jika melakukan update secara sekuensial untuk setiap row, waktu yang dibutuhkan mencapai 4 jam. Dengan melakukan multi-rows update, waktu yang dibutuhkan dapat dikurangi hingga kurang dari satu jam. Selain itu, seperti yang tertulis pada quote di atas, dengan multi-rows update maka resiko partial update dapat dihindari karena jika terjadi kegagalan update pada satu row saja, semua update pada satu eksekusi akan di-rollback.

Contoh penerapan multi-rows update dimana setiap row akan memiliki value yang berbeda-beda:

UPDATE table1 AS t SETfield1 = C.field1FROM (VALUES (1, ‘value1’),(2, ‘value2’),…) AS c (id, field1)WHERE c.id = t.id