PostgreSQL: Partitioning

Versi Bahasa Indonesia. Baca dalam Bahasa Inggris

Partitioning adalah proses membagi tabel besar menjadi bagian-bagian yang lebih kecil dan lebih mudah dikelola yang disebut partitions. Setiap partition menyimpan subset data, yang meningkatkan performa query dan menyederhanakan manajemen data.

Partitioning dapat memberikan beberapa manfaat:

• Performa query dapat ditingkatkan secara dramatis, terutama ketika sebagian besar baris yang sering diakses dari tabel berada di satu partition atau sejumlah kecil partition.
• Menghapus partition individu menggunakan DROP TABLE, atau melakukan ALTER TABLE DETACH PARTITION, jauh lebih cepat daripada operasi bulk. Perintah ini juga sepenuhnya menghindari overhead VACUUM yang disebabkan oleh DELETE massal.
• Data yang jarang digunakan dapat dipindahkan ke media penyimpanan yang lebih murah dan lebih lambat.

Jenis Partitioning di PostgreSQL

• Range Partitioning: Data dibagi berdasarkan rentang nilai (misalnya rentang tanggal).
• List Partitioning: Data dibagi berdasarkan daftar nilai spesifik (misalnya wilayah, kategori).
• Hash Partitioning: Data dibagi menggunakan fungsi hash (misalnya mendistribusikan secara merata berdasarkan ID pengguna).

Range Partitioning

CREATE TABLE transactions (
    id                  SERIAL  NOT NULL,
    customer_id         INT     NOT NULL,
    country             TEXT    NOT NULL,
    transaction_date    DATE    NOT NULL,
    CONSTRAINT PK_transaction PRIMARY KEY ("id", transaction_date)
) PARTITION BY RANGE (transaction_date);

Perintah di bawah ini akan membuat partition berdasarkan transaction_date. Satu untuk transaksi yang terjadi di tahun 2024 dan yang lainnya untuk 2025.

CREATE TABLE transactions_2024 PARTITION OF transactions
    FOR VALUES FROM ('2024-01-01') TO ('2024-12-31');

CREATE TABLE transactions_2025 PARTITION OF transactions
    FOR VALUES FROM ('2025-01-01') TO ('2025-12-31');

Dengan ini, ketika query Anda memiliki klausa where menggunakan transaction_date, maka akan memangkas partition yang tidak perlu.

Mari kita masukkan beberapa data dummy untuk tabel di atas dan coba query yang berbeda untuk melihat apakah ia memanfaatkan partition atau tidak.

INSERT INTO transactions (customer_id, country, transaction_date) 
VALUES 
    (101, 'ID', '2024-01-15'),
    (102, 'JP', '2024-03-22'),
    (103, 'ID', '2024-05-10'),
    (104, 'JP', '2024-07-30'),
    (105, 'ID', '2024-09-05'),
    (106, 'JP', '2025-02-18'),
    (107, 'ID', '2025-04-12'),
    (108, 'JP', '2025-06-25'),
    (109, 'ID', '2025-08-08'),
    (110, 'JP', '2025-11-14');

Pertama, mari kita coba memilih semua transaksi.

EXPLAIN ANALYZE 
SELECT * FROM transactions;

 Append  (cost=0.00..53.90 rows=2260 width=44) (actual time=0.027..0.049 rows=10 loops=1)
   ->  Seq Scan on transactions_2024 transactions_1  (cost=0.00..21.30 rows=1130 width=44) (actual time=0.025..0.027 rows=5 loops=1)
   ->  Seq Scan on transactions_2025 transactions_2  (cost=0.00..21.30 rows=1130 width=44) (actual time=0.019..0.019 rows=5 loops=1)
 Planning Time: 1.901 ms
 Execution Time: 0.076 ms

Seperti yang kita lihat di atas, jika kita tidak menggunakan klausa where dengan partition key, maka akan memindai semua partition.

Sekarang mari kita coba query semua transaksi yang terjadi di Februari 2025.

EXPLAIN ANALYZE 
SELECT * FROM transactions 
WHERE transaction_date >= '2025-02-01' 
AND transaction_date <= '2025-02-28';

 Seq Scan on transactions_2025 transactions  (cost=0.00..26.95 rows=6 width=44) (actual time=0.015..0.018 rows=1 loops=1)
   Filter: ((transaction_date >= '2025-02-01'::date) AND (transaction_date <= '2025-02-28'::date))
   Rows Removed by Filter: 4
 Planning Time: 0.401 ms
 Execution Time: 0.039 ms

Dan kita dapat melihat di atas bahwa postgres memangkas partition yang tidak perlu. Dalam kasus ini kita hanya membutuhkan data di partition transactions_2025 sehingga akan melewati pemindaian pada partition lain. Dan seperti yang Anda lihat, planning time dan execution time juga berkurang.

List Partitioning

CREATE TABLE transactions (
    id                  SERIAL  NOT NULL,
    customer_id         INT     NOT NULL,
    country             TEXT    NOT NULL,
    transaction_date    DATE    NOT NULL,
    CONSTRAINT PK_transaction PRIMARY KEY ("id", country)
) PARTITION BY LIST (country);

Perintah di bawah ini akan membuat 3 partition untuk ID, JP, dan lainnya.

CREATE TABLE transactions_id PARTITION OF transactions
    FOR VALUES IN ('ID');

CREATE TABLE transactions_jp PARTITION OF transactions
    FOR VALUES IN ('JP');

CREATE TABLE transactions_other PARTITION OF transactions
    DEFAULT; -- Catch-all untuk negara lain

Kita perlu membuat partition DEFAULT karena jika kita memasukkan negara lain selain ID dan JP maka akan mengembalikan error. Jadi kita perlu mendefinisikan partition di mana postgres dapat menyimpan negara selain ID dan JP.

Konsep optimasi query sama dengan jenis partition lainnya. Jika kita menyertakan field country di klausa where, maka akan memangkas partition yang tidak perlu.

Hash Partitioning

CREATE TABLE transactions (
    id                  SERIAL  NOT NULL,
    customer_id         INT     NOT NULL,
    country             TEXT    NOT NULL,
    transaction_date    DATE    NOT NULL,
    CONSTRAINT PK_transaction PRIMARY KEY ("id", customer_id)
) PARTITION BY HASH (customer_id);

Perintah di bawah ini akan membuat 4 partition berdasarkan MODULUS 4. Jika Anda ingin membuat partition yang lebih kecil atau lebih besar, Anda dapat mengubah MODULUS dan REMAINDER sesuai kebutuhan.

CREATE TABLE transactions_p0 PARTITION OF transactions
    FOR VALUES WITH (MODULUS 4, REMAINDER 0);

CREATE TABLE transactions_p1 PARTITION OF transactions
    FOR VALUES WITH (MODULUS 4, REMAINDER 1);

CREATE TABLE transactions_p2 PARTITION OF transactions
    FOR VALUES WITH (MODULUS 4, REMAINDER 2);

CREATE TABLE transactions_p3 PARTITION OF transactions
    FOR VALUES WITH (MODULUS 4, REMAINDER 3);

Untuk setiap record yang kita masukkan, postgres akan menghitung nilai hash untuk customer_id dan kemudian menempatkannya di partition yang benar.

Ketika query kita memiliki customer_id di klausa where, postgres akan menghitung nilai hash dan memindai hanya partition tertentu.

Contoh:

Nilai hash dari customer_id = 104 adalah -1470666514, maka remainder-nya adalah 0.

SELECT pg_catalog.hashint4(104);

  hashint4   
-------------
 -1470666514

Jadi postgres hanya akan memindai partition transactions_p0 dan mengabaikan partition lainnya.

EXPLAIN ANALYZE
SELECT * FROM transactions 
WHERE customer_id = 104;

 Seq Scan on transactions_p0 transactions  (cost=0.00..24.12 rows=6 width=44) (actual time=0.032..0.036 rows=1 loops=1)
   Filter: (customer_id = 104)
   Rows Removed by Filter: 3
 Planning Time: 0.230 ms
 Execution Time: 0.066 ms

Fungsi Hash PostgreSQL

Best Practices

• Filter query yang paling umum harus selaras dengan partition key untuk memastikan partition pruning di mana postgres hanya memindai partition yang relevan, bukan seluruh tabel.
  • Query berbasis waktu -> Gunakan Range Partitioning pada order_date.
  • Pencarian berbasis wilayah -> Gunakan List Partitioning pada country.
  • Akses acak (misalnya user_id) -> Gunakan Hash Partitioning pada user_id.
• Partitioning bekerja paling baik ketika data terdistribusi secara merata di seluruh partition. Data yang tidak seimbang (misalnya satu partition sangat besar sementara yang lain kecil) dapat menyebabkan hotspot dan mengurangi manfaat partitioning.
• Hindari sering memperbarui partition key karena memicu perpindahan baris.

Referensi

• https://www.postgresql.org/docs/current/ddl-partitioning.html