Optimasi Database Postgresql untuk meningkatkan performa

Sumber: https://stackify.com/postgresql-performance-tutorial/

Halo semuanya pada kali ini saya ingin membagikan cerita saya dalam melakukan optimasi database, dalam hal ini menggunakan postgresql. Postgresql adalah database relational (RDMS) yang cukup populer digunakan, beberapa perbandingan fitur postgres dibanding mysql dapat dilihat pada postingan saya sebelumnya disini.

Walaupun secara fitur postgresql jauh lebih unggul, dalam beberapa kasus, terutama penggunaan data yang besar, perlu dilakukan beberapa optimasi untuk mendapatkan hasil performa yang lebih baik.

Gunakan Connection Pooling

Connection Pooling adalah salah satu langkah awal yang bisa kita lakukan untuk meningkatkan performa database. Dengan connection pooling, koneksi yang telah terhubung dapat digunakan berkali-kali, mengurangi jumlah koneksi klien ke database.

Jumlah koneksi yang tinggi dapat meningkatkan penggunaan memori database & membuat beban database tinggi. Dengan connection pool, koneksi yang terhubung akan berkurang dan menurunkan beban database.

Beberapa library database yang populer biasanya sudah dilengkapi fitur connection pool, seperti GORM pada golang atau Sequelize pada NodeJS, tinggal kita gunakan saja fitur connection pool pada aplikasi terkait.

Optimasi Indexing query

Indeks adalah salah satu komponen penting dalam sebuah database untuk mempercepat pencarian data. Index yang tepat akan mempercepat database dalam melakukan proses query.

Bagaimana cara menentukan kolom mana yang harus diindexing? Kita bisa membuat indeks pada kolom yang sering diakses dan digunakan dalam operasi pencarian dan pengurutan data.

SELECT id, name, role_id, created_at FROM users 
WHERE order_id = 1 
AND ORDER BY created_at DESC

Pada query diatas, untuk mempercepat query kita dapat membuat indexing pada kolom order_id dan created_at, karena kedua kolom tersebut digunakan untuk search & order.

Kita juga dapat melakukan analisa dari query yang ada, dengan menggunakan EXPLAIN query.

EXPLAIN SELECT id, name, role_id, created_at FROM users 
WHERE order_id = 1 
AND ORDER BY created_at DESC

EXPLAIN akan memberikan keterangan index key yang digunakan dan baris yang telah dicari.

Optimasi Config Postgresql

Salah satu cara untuk meningkatkan performa database postgres adalah dengan melakukan perubahan konfigurasi pada postgres. Beberapa parameter yang perlu diperhatikan antara lain max_conection, shared_buffers, work_mem, effective_cache_size, dan maintenance_work_mem.

max_conection, digunakan untuk menentukan maximum koneksi yang dapat terhubung ke database, dapat diatur sesuai dengan kebutuhan sistem & ketersediaan server.

shared_buffers, jumlah memori yang digunakan untuk menyimpan data cached. Semakin besar nilainya, semakin banyak memori yang dapat digunakan untuk menyimpan data, yang dapat meningkatkan kinerja database karena mengurangi kebutuhan untuk I/O disk. Namun, perlu diingat shared_buffers menggunakan alokasi ram yang tersedia pada hardware. Kita bisa menggunakan 25% dari total RAM tersedia, atau lebih jika dibutuhkan.

effective_cache_size, digunakan oleh query planner PostgreSQL untuk memperkirakan jumlah memori yang tersedia untuk caching data di dalam memory, seperti index dan data tabel yang sering diakses.

work_mem, digunakan untuk menentukan jumlah memori yang dialokasikan oleh PostgreSQL untuk setiap operasi sort, hash table, atau join yang dilakukan dalam sebuah query.

maintenance_work_mem, menentukan jumlah memori yang dialokasikan oleh PostgreSQL untuk operasi pemeliharaan, seperti VACUUM, ANALYZE, CREATE INDEX.

Setiap parameter ini sangat bergantung pada spesifikasi server, beban kerja database, dan ukuran data yang diolah. Oleh karena itu, disarankan untuk melakukan pengujian dan pemantauan secara berkala, kemudian melakukan optimasi parameter sesuai dengan kebutuhan sistem & ketersediaan hardware untuk mendapatkan hasil yang optimal.

Monitor performa

Monitoring performa salah satu aspek penting untuk dapat meng-indenfifikasi masalah dan optimasi pada database. Salah satu cara kita melakukan monitoring database, seperti pg_stat_activity & pg_stat_database.

SELECT * FROM pg_stat_activity;

Kita juga dapat menambah tools tambahan yang bisa menampilkan grafik lebih baik secara visual seperti Grafana.

Contoh tampilan grafik monitoring postgresql di Grafana