Logo PostgreSQL

Semakin banyak data, semakin lambat ketika PostgreSQL harus melakukan update suatu baris. Hal ini wajar karena banyak hal yang dilakukan oleh PostgreSQL untuk memastikan ACID (atomicity, consistency, isolation, durability) terhadap data. Berbeda dengan MongoDB yang hingga Mei 2020 masih bermasalah mengenai ACID.

In order to support rollback and crash-safety, PostgreSQL must write a new copy of every modified row, rather than modifying the row in-place. Twice, actually, because it must be written to WAL (a sequential log for crash recovery) then to the table.

Due to MVCC, an update in Postgres consists of finding the row being updated, and inserting a new version of the row back into the database. The main downside to doing this is the need to readd the row to every index.

Alhasil saat PostgreSQL update baris, ada 3 hal yang dilakukan:

  • Soft-delete atau tandai baris yang akan dihapus dimana akan benar-benar terhapus & free space saat vacuum / autovacuum berjalan
  • Buat baris baru
  • Menulis ulang index meski update kolom yang bukan termasuk dalam index.

Ketiga langkah diatas juga dilakukan pada Write-Ahead Logging (WAL) PostgreSQL, sehingga total ada 6 langkah ketika suatu baris akan dilakukan update.

Cara untuk mempercepat update & upsert PostgreSQL dapat dilakukan dengan

  1. Ubah Query dan Kurangi Update
  2. Kurangi Index
  3. Matikan synchronous_commit
  4. Kurangi Fillfactor
  5. Ubah Konfigurasi PostgreSQL
  6. Gunakan UNLOGGED Table (tidak direkomendasikan)

Ubah Query dan Kurangi Update

Seperti yang telah dijelaskan sebelumnya, operasi update pada dasarnya melakukan hapus baris dan tulis baris baru dengan data baru.

Untuk data yang sedikit, ini tidak menjadi masalah. Namun ketika data mencapai jutaan baris dan perlu sering melakukan update hal ini akan bermasalah.

Perlu cek ulang query apakah memungkinkan operasi update dikurangi, misal dengan cara melihat kolom updated_at. Jika tanggal updated_at sama, maka tidak perlu diubah.

Kurangi Index

Index pada database dapat mempercepat query untuk membaca tabel, namun memperlambat proses create dan update. Jika memiliki index, maka selain harus enam langkah diatas juga harus melakukan update pada tabel berisi index.

Matikan synchronous_commit

Secara default, synchronous_commit pada PostgreSQL adalah on. Jika on artinya setelah melakukan create, update, atau delete, maka harus menunggu commit pada Write Ahead Logging (WAL) juga selesai baru suatu transaksi database dianggap selesai.

Dengan mematikan konfigurasi tersebut, maka akan membuat perilaku dan performance PostgreSQL create atau update mirip MongoDB dimana tidak perlu menunggu commit dari WAL karena commit dilakukan secara async.

Berikut ini adalah contoh SQL yang digunakan untuk mematikan synchronous_commit.

SET LOCAL synchronous_commit TO OFF

Sayangnya, konfigurasi ini tidak bisa dimatikan jika menggunakan Cloud PostgreSQL dari Google.

Kurangi Fillfactor

Fillfactor adalah parameter tabel di PostgreSQL berupa persen dari 10-100 dan terkait aspek performansi, parameter ini yang paling penting. Default value pada Fillfactor adalah 100 yang artinya complete packing.

PostgreSQL menyimpan data berupa per block bernama page dimana satu block berukuran 8 kB. Jika menggunakan default value 100 atau complete packing, maka ketika terjadi satu update, yang dilakukan PostgreSQL adalah menandai tuple atau data lama sebagai terhapus dan membuat data baru pada page yang lain walaupun data yang disimpan berukuran 1 kB.

Agar tidak selalu melakukan re-writing index untuk membaca page baru, maka perlu mengecilkan ukuran fillfactor sehingga jika ada update, pointer tetap menunjuk ke page yang lama sehingga tidak ada re-writing.

Value fillfactor dihitung berdasarkan ukuran tuple atau data per baris, namun pada umumnya pada 50-90 persen.

Berikut ini adalah SQL untuk cek ukuran bytes per baris untuk PostgreSQL 9.3 keatas, jika ukuran per baris lebih dari 4 kB maka lebih baik biarkan fillfactor 100 karena akan tetap membuat pointer menunjuk page baru tidak berpengaruh terhadap performansi update.

SELECT l.metric, l.nr AS "bytes/ct"
     , CASE WHEN is_size THEN pg_size_pretty(nr) END AS bytes_pretty
     , CASE WHEN is_size THEN nr / NULLIF(x.ct, 0) END AS bytes_per_row
FROM  (
   SELECT min(tableoid)        AS tbl      -- = 'public.tbl'::regclass::oid
        , count(*)             AS ct
        , sum(length(t::text)) AS txt_len  -- length in characters
   FROM   public.tbl t                     -- provide table name *once*
   ) x
 , LATERAL (
   VALUES
      (true , 'core_relation_size'               , pg_relation_size(tbl))
    , (true , 'visibility_map'                   , pg_relation_size(tbl, 'vm'))
    , (true , 'free_space_map'                   , pg_relation_size(tbl, 'fsm'))
    , (true , 'table_size_incl_toast'            , pg_table_size(tbl))
    , (true , 'indexes_size'                     , pg_indexes_size(tbl))
    , (true , 'total_size_incl_toast_and_indexes', pg_total_relation_size(tbl))
    , (true , 'live_rows_in_text_representation' , txt_len)
    , (false, '------------------------------'   , NULL)
    , (false, 'row_count'                        , ct)
    , (false, 'live_tuples'                      , pg_stat_get_live_tuples(tbl))
    , (false, 'dead_tuples'                      , pg_stat_get_dead_tuples(tbl))
   ) l(is_size, metric, nr);

Sedangkan berikut ini adalah contoh ubah fillfactor menjadi 50 persen pada tabel yang sudah ada.

ALTER TABLE table_name SET ( fillfactor = 50);
VACUUM FULL table_name;

Ubah Konfigurasi PostgreSQL

Ada beberapa konfigurasi yang bisa diubah untuk meningkatkan performansi jika poin 1-4 dirasa masih belum cukup, bisa ubah konfigurasi dari PostgreSQL berikut ini.

  • commit_delay - setiap transaksi tidak akan langsung dilakukan commit pada WAL, tapi tunggu atau delay beberapa saat.
  • shared_buffers - konfigurasi ini menentukan berapa banyak memori yang didedikasikan untuk PostgreSQL untuk caching data.
  • effective_io_concurrency - jumlah concurrent disk IO yang dijalankan PostgreSQL. Default value adalah 1.
  • max_worker_processes - jumlah maksimum background proses. Default value adalah 8.
  • min_wal_size & max_wal_size - soft-limit untuk menulis WAL pada disk. Default value min_wal_size adalah 80 MB dan max_wal_size adalah 1 GB.

Untuk konfigurasi lain dapat dilihat pada PostgreSQL Server Configuration.

Gunakan UNLOGGED Table

If specified, the table is created as an unlogged table. Data written to unlogged tables is not written to the write-ahead log (see Chapter 29), which makes them considerably faster than ordinary tables. However, they are not crash-safe: an unlogged table is automatically truncated after a crash or unclean shutdown. The contents of an unlogged table are also not replicated to standby servers. Any indexes created on an unlogged table are automatically unlogged as well.

Solusi ini merupakan alternatif lain jika poin 1-6 masih belum cukup, namun tidak direkomendasikan.

Suatu unlogged table lebih cepat dalam operasi create, update, dan delete karena tidak perlu harus menuliskan ke Write-Ahead Logging (WAL) setiap transaksi.

Namun risiko tabel jenis ini adalah tidak crash-safe, jika terjadi crash atau unclean shutdown maka tabel akan dilakukan truncate pada tabel tersebut.

Referensi