Table Partitions di PostgreSQL

Horasss, apa kabar blogger?, baru jumpa lagi ni, tapi memang si kalau niat nge-blog itu suasana hatinya harus pas, jadi gak bisa dipaksain, dan dilanjutin. 😀 #curhatDikit.

Oke langsung saja, sekarang kita akan coba sharing bagaimana sebuah table partition di PostgreSQL bekerja, dan bagaimana membuatnya. Pertama-tama tools yang harus disiapkan adalah:

  1. Database Server PostgreSQL 8.4 or later, rekomendasinya si PostgreSQL 9.1
  2. DBMS, disini saya pakai EMS PgManager 2011
  3. Mental dan pengertian ditambah kopi dan musik =))

A. PENGERTIAN

Filosofi partisi adalah memecah tabel ke dalam beberapa segment (partisi atau subpartisi), di mana tabel konvensional hanya mempunyai satu segment.

B. FUNGSI

Manfaat lain dari partitioning adalah tiap-tiap segment (partisi atau subpartisi) bisa ditaruh di tablespace yang berbeda, sehingga kita mendapat manfaat dari spreading(menyebar) tablespace, yaitu penyebaran I/O dan mengurangi resiko loss data karena tablespace corrupt.

C. PERSIAPAN CHILD TABLE

Prinsipnya adalah kita akan membuat beberapa table turunan dari table utama. Anggaplah kita akan membuat partisi satu table saja bernama pos_bill_master, child table kita buat sebanyak 12 sesuai dengan jumlah bulan dalam 1 tahun.

CREATE TABLE pos_bill_master_2014_01(
 PRIMARY KEY (bill_code,bill_counter, bill_datetime),
 CHECK (bill_datetime >= DATE '2014-01-01' AND bill_datetime < DATE '2014-02-01' )
) INHERITS (pos_bill_master);

CREATE TABLE pos_bill_master_2014_02(
 PRIMARY KEY (bill_code,bill_counter, bill_datetime),
 CHECK (bill_datetime >= DATE '2014-02-01' AND bill_datetime < DATE '2014-03-01' )
) INHERITS (pos_bill_master);

CREATE TABLE pos_bill_master_2014_03(
 PRIMARY KEY (bill_code,bill_counter, bill_datetime),
 CHECK (bill_datetime >= DATE '2014-03-01' AND bill_datetime < DATE '2014-04-01' )
) INHERITS (pos_bill_master);

CREATE TABLE pos_bill_master_2014_04(
 PRIMARY KEY (bill_code,bill_counter, bill_datetime),
 CHECK (bill_datetime >= DATE '2014-04-01' AND bill_datetime < DATE '2014-05-01' )
) INHERITS (pos_bill_master);

CREATE TABLE pos_bill_master_2014_05(
 PRIMARY KEY (bill_code,bill_counter, bill_datetime),
 CHECK (bill_datetime >= DATE '2014-05-01' AND bill_datetime < DATE '2014-06-01' )
) INHERITS (pos_bill_master);

CREATE TABLE pos_bill_master_2014_06(
 PRIMARY KEY (bill_code,bill_counter, bill_datetime),
 CHECK (bill_datetime >= DATE '2014-06-01' AND bill_datetime < DATE '2014-07-01' )
) INHERITS (pos_bill_master);

CREATE TABLE pos_bill_master_2014_07(
 PRIMARY KEY (bill_code,bill_counter, bill_datetime),
 CHECK (bill_datetime >= DATE '2014-07-01' AND bill_datetime < DATE '2014-08-01' )
) INHERITS (pos_bill_master);

CREATE TABLE pos_bill_master_2014_08(
 PRIMARY KEY (bill_code,bill_counter, bill_datetime),
 CHECK (bill_datetime >= DATE '2014-08-01' AND bill_datetime < DATE '2014-09-01' )
) INHERITS (pos_bill_master);

CREATE TABLE pos_bill_master_2014_09(
 PRIMARY KEY (bill_code,bill_counter, bill_datetime),
 CHECK (bill_datetime >= DATE '2014-09-01' AND bill_datetime < DATE '2014-10-01' )
) INHERITS (pos_bill_master);

CREATE TABLE pos_bill_master_2014_10(
 PRIMARY KEY (bill_code,bill_counter, bill_datetime),
 CHECK (bill_datetime >= DATE '2014-10-01' AND bill_datetime < DATE '2014-11-01' )
) INHERITS (pos_bill_master);

CREATE TABLE pos_bill_master_2014_11(
 PRIMARY KEY (bill_code,bill_counter, bill_datetime),
 CHECK (bill_datetime >= DATE '2014-11-01' AND bill_datetime < DATE '2014-12-01' )
) INHERITS (pos_bill_master);

CREATE TABLE pos_bill_master_2014_12(
 PRIMARY KEY (bill_code,bill_counter, bill_datetime),
 CHECK (bill_datetime >= DATE '2014-12-01' AND bill_datetime < DATE '2015-01-01' )
) INHERITS (pos_bill_master);

D. PERSIAPAN INDEX DAN TAMBAH CONSTRAINT

Untuk mempercepat akses terhadap table, maka dibuatkan masing-masing 3 buah index disetiap masing-masing child table. Juga menambahkan constraint dengan unique index 3 kolom.

CREATE INDEX pos_bill_master_2014_01_bill_datetime ON pos_bill_master_2014_01 (bill_datetime);
create index pos_bill_master_2014_01_bill_counter on pos_bill_master_2014_01 (bill_counter);
create index pos_bill_master_2014_01_bill_code on pos_bill_master_2014_01 (bill_code);
alter table pos_bill_master_2014_01 add constraint pos_bill_master_2014_01_uniq_idx unique (bill_datetime,bill_counter,bill_code);

CREATE INDEX pos_bill_master_2014_02_bill_datetime ON pos_bill_master_2014_02 (bill_datetime);
create index pos_bill_master_2014_02_bill_counter on pos_bill_master_2014_02 (bill_counter);
create index pos_bill_master_2014_02_bill_code on pos_bill_master_2014_02 (bill_code);
alter table pos_bill_master_2014_02 add constraint pos_bill_master_2014_02_uniq_idx unique (bill_datetime,bill_counter,bill_code);

CREATE INDEX pos_bill_master_2014_03_bill_datetime ON pos_bill_master_2014_03 (bill_datetime);
create index pos_bill_master_2014_03_bill_counter on pos_bill_master_2014_03 (bill_counter);
create index pos_bill_master_2014_03_bill_code on pos_bill_master_2014_03 (bill_code);
alter table pos_bill_master_2014_03 add constraint pos_bill_master_2014_03_uniq_idx unique (bill_datetime,bill_counter,bill_code);

CREATE INDEX pos_bill_master_2014_04_bill_datetime ON pos_bill_master_2014_04 (bill_datetime);
create index pos_bill_master_2014_04_bill_counter on pos_bill_master_2014_04 (bill_counter);
create index pos_bill_master_2014_04_bill_code on pos_bill_master_2014_04 (bill_code);
alter table pos_bill_master_2014_04 add constraint pos_bill_master_2014_04_uniq_idx unique (bill_datetime,bill_counter,bill_code);

CREATE INDEX pos_bill_master_2014_05_bill_datetime ON pos_bill_master_2014_05 (bill_datetime);
create index pos_bill_master_2014_05_bill_counter on pos_bill_master_2014_05 (bill_counter);
create index pos_bill_master_2014_05_bill_code on pos_bill_master_2014_05 (bill_code);
alter table pos_bill_master_2014_05 add constraint pos_bill_master_2014_05_uniq_idx unique (bill_datetime,bill_counter,bill_code);

CREATE INDEX pos_bill_master_2014_06_bill_datetime ON pos_bill_master_2014_06 (bill_datetime);
create index pos_bill_master_2014_06_bill_counter on pos_bill_master_2014_06 (bill_counter);
create index pos_bill_master_2014_06_bill_code on pos_bill_master_2014_06 (bill_code);
alter table pos_bill_master_2014_06 add constraint pos_bill_master_2014_06_uniq_idx unique (bill_datetime,bill_counter,bill_code);

CREATE INDEX pos_bill_master_2014_07_bill_datetime ON pos_bill_master_2014_07 (bill_datetime);
create index pos_bill_master_2014_07_bill_counter on pos_bill_master_2014_07 (bill_counter);
create index pos_bill_master_2014_07_bill_code on pos_bill_master_2014_07 (bill_code);
alter table pos_bill_master_2014_07 add constraint pos_bill_master_2014_07_uniq_idx unique (bill_datetime,bill_counter,bill_code);

CREATE INDEX pos_bill_master_2014_08_bill_datetime ON pos_bill_master_2014_08 (bill_datetime);
create index pos_bill_master_2014_08_bill_counter on pos_bill_master_2014_08 (bill_counter);
create index pos_bill_master_2014_08_bill_code on pos_bill_master_2014_08 (bill_code);
alter table pos_bill_master_2014_08 add constraint pos_bill_master_2014_08_uniq_idx unique (bill_datetime,bill_counter,bill_code);

CREATE INDEX pos_bill_master_2014_09_bill_datetime ON pos_bill_master_2014_09 (bill_datetime);
create index pos_bill_master_2014_09_bill_counter on pos_bill_master_2014_09 (bill_counter);
create index pos_bill_master_2014_09_bill_code on pos_bill_master_2014_09 (bill_code);
alter table pos_bill_master_2014_09 add constraint pos_bill_master_2014_09_uniq_idx unique (bill_datetime,bill_counter,bill_code);

CREATE INDEX pos_bill_master_2014_10_bill_datetime ON pos_bill_master_2014_10 (bill_datetime);
create index pos_bill_master_2014_10_bill_counter on pos_bill_master_2014_10 (bill_counter);
create index pos_bill_master_2014_10_bill_code on pos_bill_master_2014_10 (bill_code);
alter table pos_bill_master_2014_10 add constraint pos_bill_master_2014_10_uniq_idx unique (bill_datetime,bill_counter,bill_code);

CREATE INDEX pos_bill_master_2014_11_bill_datetime ON pos_bill_master_2014_11 (bill_datetime);
create index pos_bill_master_2014_11_bill_counter on pos_bill_master_2014_11 (bill_counter);
create index pos_bill_master_2014_11_bill_code on pos_bill_master_2014_11 (bill_code);
alter table pos_bill_master_2014_11 add constraint pos_bill_master_2014_11_uniq_idx unique (bill_datetime,bill_counter,bill_code);

CREATE INDEX pos_bill_master_2014_12_bill_datetime ON pos_bill_master_2014_12 (bill_datetime);
create index pos_bill_master_2014_12_bill_counter on pos_bill_master_2014_12 (bill_counter);
create index pos_bill_master_2014_12_bill_code on pos_bill_master_2014_12 (bill_code);
alter table pos_bill_master_2014_12 add constraint pos_bill_master_2014_12_uniq_idx unique (bill_datetime,bill_counter,bill_code);

 E. TRIGGER UNTUK EXECUTE FUNGSI

Sebuah trigger yang dipasang pada table master akan men-eksekusi sebuah fungsi yang berfungsi untuk memecah data sesuai tanggal transaksinya.

CREATE OR REPLACE FUNCTION ftgr_pos_bill_master_bi()
RETURNS TRIGGER AS $$
BEGIN

IF ( NEW.bill_datetime >= DATE '2015-01-01' AND NEW.bill_datetime < DATE '2015-02-01' ) THEN INSERT INTO pos_bill_master_2015_01 VALUES (NEW.*); ELSIF ( NEW.bill_datetime >= DATE '2015-02-01' AND NEW.bill_datetime < DATE '2015-03-01' ) THEN INSERT INTO pos_bill_master_2015_02 VALUES (NEW.*); ELSIF ( NEW.bill_datetime >= DATE '2015-03-01' AND NEW.bill_datetime < DATE '2015-04-01' ) THEN INSERT INTO pos_bill_master_2015_03 VALUES (NEW.*); ELSIF ( NEW.bill_datetime >= DATE '2015-04-01' AND NEW.bill_datetime < DATE '2015-05-01' ) THEN INSERT INTO pos_bill_master_2015_04 VALUES (NEW.*); ELSIF ( NEW.bill_datetime >= DATE '2015-05-01' AND NEW.bill_datetime < DATE '2015-06-01' ) THEN INSERT INTO pos_bill_master_2015_05 VALUES (NEW.*); ELSIF ( NEW.bill_datetime >= DATE '2015-06-01' AND NEW.bill_datetime < DATE '2015-07-01' ) THEN INSERT INTO pos_bill_master_2015_06 VALUES (NEW.*); ELSIF ( NEW.bill_datetime >= DATE '2015-07-01' AND NEW.bill_datetime < DATE '2015-08-01' ) THEN INSERT INTO pos_bill_master_2015_07 VALUES (NEW.*); ELSIF ( NEW.bill_datetime >= DATE '2015-08-01' AND NEW.bill_datetime < DATE '2015-09-01' ) THEN INSERT INTO pos_bill_master_2015_08 VALUES (NEW.*); ELSIF ( NEW.bill_datetime >= DATE '2015-09-01' AND NEW.bill_datetime < DATE '2015-10-01' ) THEN INSERT INTO pos_bill_master_2015_09 VALUES (NEW.*); ELSIF ( NEW.bill_datetime >= DATE '2015-10-01' AND NEW.bill_datetime < DATE '2015-11-01' ) THEN INSERT INTO pos_bill_master_2015_10 VALUES (NEW.*); ELSIF ( NEW.bill_datetime >= DATE '2015-11-01' AND NEW.bill_datetime < DATE '2015-12-01' ) THEN INSERT INTO pos_bill_master_2015_11 VALUES (NEW.*); ELSIF ( NEW.bill_datetime >= DATE '2015-12-01' AND NEW.bill_datetime < DATE '2016-01-01' ) THEN
INSERT INTO pos_bill_master_2015_12 VALUES (NEW.*);

ELSE
RAISE EXCEPTION 'Date out of range. Something wrong with the ftgr_pos_bill_master_bi() function!';
END IF;
RETURN NULL;
END;
$$
LANGUAGE plpgsql;

Berikut adalah trigger yang memanggil fungsi diatas.

DROP TRIGGER IF EXISTS tgr_pos_bill_master_bi ON trobex.pos_bill_master;

CREATE TRIGGER tgr_pos_bill_master_bi BEFORE INSERT 
ON trobex.pos_bill_master FOR EACH ROW 
EXECUTE PROCEDURE trobex.ftgr_pos_bill_master_bi();

Demikian sharingnya, keuntungan table partisi baru akan dirasakan apabila data sudah sangat besar. Sebenarnya si ini bukan sharing, tapi tepatnya catatan aja. Mumpung dikantor tidak ada kerjaan dan boss lagi hilang entah kemana. HAHAHAHAHA..

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s