Wajib Tau Cara Membuat Partitioning yang Tepat pada PostgreSQL

Partitioning adalah teknik pemecahan tabel besar menjadi beberapa bagian (partisi) berdasarkan kriteria tertentu, seperti rentang nilai (range), daftar nilai (list), atau hash. Tujuan utama partitioning adalah untuk meningkatkan kinerja query dan mempermudah manajemen data dalam tabel besar. Namun, jika partitioning tidak dilakukan dengan benar, justru dapat menyebabkan penurunan kinerja query yang signifikan. Artikel ini akan membahas penyebab lambatnya akses data akibat partitioning yang salah pada PostgreSQL dan memberikan solusi beserta contoh implementasinya.

1. Penyebab Lambatnya Akses Data karena Partitioning yang Salah

Berikut adalah beberapa penyebab utama mengapa partitioning dapat menyebabkan kinerja query yang buruk:

a. Pemilihan Kolom Partition yang Tidak Tepat

Pemilihan kolom untuk partition yang tidak sesuai dengan pola akses query dapat membuat database tidak dapat memanfaatkan partitioning secara optimal. Sebagai contoh, jika query sering mengambil data berdasarkan bulan, tetapi kita mempartisi tabel berdasarkan tahun, maka database harus membaca semua partisi tahun tersebut.

b. Terlalu Banyak atau Terlalu Sedikit Partisi

Memiliki terlalu banyak partisi dapat meningkatkan overhead manajemen partisi, seperti indeks dan pengelolaan partisi itu sendiri. Sebaliknya, terlalu sedikit partisi dapat menyebabkan data yang tidak relevan dibaca, sehingga query harus memindai lebih banyak data daripada yang diperlukan.

c. Tidak Menggunakan Partition Pruning

Partition pruning adalah teknik di mana PostgreSQL hanya memindai partisi yang relevan dengan query, berdasarkan kondisi yang ada dalam klausa WHERE. Jika partitioning tidak diatur dengan benar, PostgreSQL mungkin tidak dapat melakukan pruning dan harus membaca semua partisi, yang menyebabkan query lebih lambat.

d. Tidak Memanfaatkan Indeks yang Tepat

Indeks yang buruk atau tidak adanya indeks pada kolom yang sering digunakan dalam query dapat memperburuk kinerja, terutama jika kolom tersebut adalah bagian dari klausa WHERE atau JOIN.

2. Contoh Kasus

Misalkan kita memiliki tabel transaksi penjualan yang sangat besar. Kita ingin mempartisi tabel tersebut berdasarkan tahun transaksi untuk mempermudah manajemen data dan meningkatkan kinerja query.

Struktur Tabel (Tanpa Partitioning)

CREATE TABLE sales (

    id SERIAL PRIMARY KEY,

    transaction_date DATE,

    product_category VARCHAR(50),

    amount NUMERIC

);

Setelah melihat pola query yang sering mengakses data berdasarkan tahun transaksi, kita memutuskan untuk menggunakan partitioning berdasarkan transaction_date.

Membuat Partitioning Berdasarkan Tahun

CREATE TABLE sales (

    id SERIAL PRIMARY KEY,

    transaction_date DATE,

    product_category VARCHAR(50),

    amount NUMERIC

) PARTITION BY RANGE (transaction_date);

CREATE TABLE sales_2020 PARTITION OF sales

    FOR VALUES FROM ('2020-01-01') TO ('2021-01-01');

CREATE TABLE sales_2021 PARTITION OF sales

    FOR VALUES FROM ('2021-01-01') TO ('2022-01-01');

Tabel sales sekarang terbagi menjadi beberapa partisi berdasarkan tahun. Namun, ini bisa menjadi masalah jika query sering mengakses data berdasarkan bulan atau kategori produk.

Contoh Query yang Sering Digunakan

SELECT SUM(amount)

FROM sales

WHERE transaction_date BETWEEN '2020-06-01' AND '2020-06-30'

AND product_category = 'Electronics';

Dalam query ini, PostgreSQL harus memindai seluruh partisi yang ada, bahkan jika hanya data untuk bulan Juni yang dibutuhkan, karena partitioning yang dilakukan berdasarkan tahun.

3. Masalah dan Solusi

Masalah 1: Partitioning yang Tidak Tepat Partitioning yang dilakukan berdasarkan transaction_date dengan granularity tahunan dapat menyebabkan masalah kinerja karena query lebih sering mengambil data berdasarkan bulan atau kategori produk.

Solusi: Agar lebih optimal, kita bisa menggunakan partitioning berdasarkan bulan atau kategori produk.

Memperbaiki Partitioning dengan Partitioning Berdasarkan Bulan dan Kategori Produk

Jika query sering mengakses data berdasarkan bulan, kita bisa mempartisi berdasarkan bulan, atau menggunakan kombinasi partitioning yang lebih tepat seperti menggunakan product_category dan transaction_date:

CREATE TABLE sales (

    id SERIAL PRIMARY KEY,

    transaction_date DATE,

    product_category VARCHAR(50),

    amount NUMERIC

) PARTITION BY RANGE (transaction_date);

-- Partisi berdasarkan tahun dan bulan

CREATE TABLE sales_2020_01 PARTITION OF sales

    FOR VALUES FROM ('2020-01-01') TO ('2020-02-01');

CREATE TABLE sales_2020_02 PARTITION OF sales

    FOR VALUES FROM ('2020-02-01') TO ('2020-03-01');

Dengan partitioning seperti ini, query berdasarkan bulan akan lebih efisien karena PostgreSQL hanya perlu memindai partisi yang relevan.

Memperbaiki dengan Partitioning Berdasarkan Kategori Produk

Jika query sering menggunakan product_category untuk filter data, lebih baik menggunakan list partitioning untuk memisahkan data berdasarkan kategori produk.

CREATE TABLE sales (

    id SERIAL PRIMARY KEY,

    transaction_date DATE,

    product_category VARCHAR(50),

    amount NUMERIC

) PARTITION BY LIST (product_category);

CREATE TABLE sales_electronics PARTITION OF sales

    FOR VALUES IN ('Electronics');

CREATE TABLE sales_furniture PARTITION OF sales

    FOR VALUES IN ('Furniture');

Dengan cara ini, query yang mencari produk berdasarkan kategori seperti Electronics atau Furniture akan lebih cepat, karena PostgreSQL hanya memindai partisi yang relevan.

4. Optimasi Query dengan Indeks

Selain memperbaiki partitioning, penting untuk menambahkan indeks pada kolom yang sering digunakan dalam query.

Menambahkan Indeks pada Kolom yang Sering Digunakan

CREATE INDEX idx_sales_transaction_date ON sales (transaction_date);

CREATE INDEX idx_sales_product_category ON sales (product_category);

Indeks ini membantu mempercepat pencarian data berdasarkan tanggal transaksi dan kategori produk.

Memanfaatkan Partition Pruning

Pastikan query ditulis dengan kondisi yang dapat memanfaatkan partition pruning, di mana PostgreSQL hanya membaca partisi yang relevan.

SELECT SUM(amount)

FROM sales

WHERE transaction_date BETWEEN '2020-06-01' AND '2020-06-30'

AND product_category = 'Electronics';

Dengan partitioning yang benar dan query yang sesuai, PostgreSQL dapat melakukan pruning dan hanya membaca data pada partisi yang relevan.

5. Memantau dan Menyesuaikan Jumlah Partisi

Terlalu banyak partisi dapat memperburuk kinerja karena meningkatkan overhead dalam pengelolaan partisi. Sebaliknya, terlalu sedikit partisi dapat menyebabkan pembacaan data yang tidak relevan.

Jika Anda memiliki banyak data tetapi tidak terlalu sering memodifikasi data, mungkin lebih baik untuk mempartisi data per kuartal atau tahun, bukan per bulan.

Repartitioning

Jika Anda merasa partitioning yang ada tidak optimal, Anda bisa melakukan repartitioning dengan membuat tabel baru yang lebih optimal dan memindahkan data ke tabel tersebut.

Partitioning yang salah dapat menyebabkan lambatnya akses data di PostgreSQL. Beberapa penyebab utama masalah ini meliputi pemilihan kolom partition yang tidak tepat, terlalu banyak atau terlalu sedikit partisi, dan ketidakmampuan PostgreSQL untuk melakukan partition pruning. Untuk memperbaikinya, Anda perlu memilih kolom partition yang tepat, menambahkan indeks yang relevan, dan memastikan query memanfaatkan partition pruning. Dengan memantau dan menyesuaikan jumlah partisi secara bijak, Anda bisa meningkatkan kinerja database PostgreSQL secara signifikan. Semoga informasi ini bermanfaat dan mohon maaf jika ada informasi yang tidak sesuai.