PostgreSQL → Partitioning

Sumber: Luca Ferrari, Enrico Pirozzi - Learn PostgreSQL Use, manage, and build secure and scalable databases with PostgreSQL 16 (2023) - Chapter 9

Pertama-tama, mari kita coba memahami mengapa kita harus melakukan partisi data. Kita harus mulai dengan mengatakan bahwa ukuran / size database itu akan selalu bertambah. Oleh karena itu, mungkin saja sebuah database, setelah beberapa bulan growth, dapat mencapai ukuran gigabyte, terabyte, atau bahkan petabyte.

Hal lain yang harus selalu kita ingat adalah bahwa tidak semua tabel itu grow / tumbuh pada tingkat yang sama. Beberapa tabel lebih besar dari tabel lain dan beberapa index juga lebih besar dari index lainnya. Kita juga perlu tahu bahwa ada bagian dari RAM server kita, yang digunakan bersama oleh semua proses PostgreSQL, yang digunakan untuk mengelola data yang ada dalam tabel. Bagian dari RAM server ini disebut shared_buffers.

Cara kerja PostgreSQL adalah sebagai berikut:

1. Data diambil dari hard disk.

2. Data ditempatkan di shared_buffers.

3. Data diproses di shared_buffers.

4. Data diunduh ke disk.

Biasanya, di server khusus hanya untuk PostgreSQL, ukuran shared_buffers adalah sekitar sepertiga atau seperempat dari total RAM server. Berikut referensi untuk mengatur beberapa parameter konfigurasi PostgreSQL (termasuk ukuran yang disarankan untuk shared_buffers) https://pgtune.leopard.in.ua.

PGTune

Ketika volume data dari sebuah tabel grow secara berlebihan dibandingkan dengan ukuran shared_buffers, ada kemungkinan kinerja akan menurun. Dalam hal ini, partitioning data dapat membantu kita. Partitioning berarti memecah tabel yang sangat besar menjadi tabel-tabel yang lebih kecil. Program client akan berpikir bahwa server masih hanya memiliki satu tabel, tetapi memiliki tabel yang lebih kecil juga berarti memiliki index yang lebih kecil yang memiliki peluang lebih tinggi untuk tetap berada di memori yang akan meningkatkan kinerja data. Selain itu, memiliki tabel yang lebih kecil berarti bahwa proses vacuum bekerja pada tabel yang lebih kecil, yang meminimalkan waktu eksekusi proses vacuum. Ketika menjalankan vacuum full, ruang disk yang digunakan oleh tabel menjadi dua kali lipat, oleh karena itu memiliki banyak tabel kecil daripada satu tabel besar secara signifikan mengurangi dampak dari masalah ini. Partitioning data dapat dilakukan dengan dua cara:

• Menggunakan table inheritance (satu-satunya cara yang mungkin untuk versi PostgreSQL < 10)

• Menggunakan declarative partitioning (cara terbaik mulai dari versi 10)

Setelah mengetahui kapan disarankan untuk melakukan partisi data, mari kita lihat jenis-jenis partitioning tabel apa saja yang mungkin. PostgreSQL mendukung tiga jenis declarative partitioning:

• Range partitioning

• List partitioning

• Hash partitioning

Sebelum kita menjelaskan ketiga metode ini secara detail, mari kita bahas terlebih dahulu tentang vacuum .

Dalam PostgreSQL, VACUUM dan VACUUM FULL adalah dua perintah penting untuk menjaga kinerja dan efisiensi database. Keduanya digunakan untuk membersihkan dead tuples (baris yang sudah tidak berlaku lagi karena operasi UPDATE atau DELETE), namun memiliki perbedaan mendasar dalam cara kerja dan dampaknya terhadap sistem.

PostgreSQL menggunakan sistem penyimpanan Multi-Version Concurrency Control (MVCC), yang memungkinkan banyak transaksi untuk membaca data tanpa saling mengganggu. Setiap kali ada operasi UPDATE atau DELETE, baris lama tidak dihapus langsung, tapi ditandai sebagai dead tuple. Baris baru dibuat dalam kasus UPDATE.

Karena baris lama tetap disimpan, database akan membengkak seiring waktu. Di sinilah VACUUM berperan.

Fungsi:

• Membersihkan dead tuples yang sudah tidak dibutuhkan.

• Membebaskan ruang agar bisa digunakan kembali oleh query baru.

• Memperbarui statistik internal PostgreSQL (jika pakai ANALYZE).

• Menghindari pembengkakan ukuran tabel dan index.

Contoh:

VACUUM my_table;
VACUUM ANALYZE my_table;

VACUUM FULL melakukan semua yang dilakukan oleh VACUUM, ditambah dengan:

• Mengembalikan ruang kosong ke sistem operasi (bukan cuma untuk reuse internal).

• Memampatkan tabel (tabel di-rewrite dari nol).

• Mengurangi ukuran file tabel secara fisik.

Namun, VACUUM FULL mengunci tabel selama proses berlangsung, sehingga tidak cocok untuk tabel yang sering diakses secara langsung di sistem produksi.

Fungsi:

• Digunakan untuk reclaim space ketika tabel sudah terlalu besar karena banyak UPDATE/DELETE.

• Mengatasi pembengkakan file .rel yang tidak bisa diatasi oleh VACUUM biasa.

Perlu Diperhatikan:

• Lebih lambat dari VACUUM.

• Eksklusif lock (tidak bisa diakses selama proses berlangsung).

• Harus digunakan secara terencana/offline maintenance.

Contoh:

VACUUM FULL my_table;

Perbandingan VACUUM vs VACUUM FULL

Aspek	VACUUM	VACUUM FULL
Membersihkan dead tuples	✅	✅
Mengembalikan ruang ke OS	❌ (hanya internal reuse)	✅ (kompak file fisik)
Lock Tabel	Tidak eksklusif	Eksklusif (tidak bisa diakses)
Kecepatan	Cepat	Lebih lambat
Risiko gangguan sistem	Minimal	Tinggi (harus maintenance)
Ukuran file tabel	Tidak mengecil	Ukuran fisik bisa mengecil

PostgreSQL juga memiliki autovacuum daemon yang secara otomatis melakukan VACUUM pada tabel yang banyak berubah. Ini mengurangi kebutuhan untuk menjalankan VACUUM manual, tapi tidak melakukan VACUUM FULL secara otomatis.

Konfigurasi penting:

• autovacuum_vacuum_threshold

• autovacuum_vacuum_scale_factor

• autovacuum_naptime

Tips Praktis

• Gunakan VACUUM ANALYZE secara berkala untuk memperbarui statistik query planner.

• Untuk mengidentifikasi bloat, gunakan ekstensi seperti pgstattuple atau query dari pg_stat_user_tables.

• Jangan sering-sering pakai VACUUM FULL kecuali benar-benar perlu.

“Ketika menjalankan vacuum full, ruang disk yang digunakan oleh tabel menjadi dua kali lipat.” benar dalam konteks proses sementara. Berikut adalah penjelasan makna teknis dan mengapa ini terjadi:

• VACUUM FULL tidak membersihkan data pada tempatnya (in-place).

• PostgreSQL akan:

  1. Membuat salinan baru dari tabel di file baru.

  2. Menyalin hanya data yang masih aktif/valid (tanpa dead tuples) ke salinan baru.

  3. Setelah selesai, PostgreSQL akan:

     • Menghapus file lama.

     • Mengganti pointer tabel ke file baru yang bersih dan lebih kecil.

Apa yang Terjadi di Disk? Misalkan kamu punya tabel:

Kondisi awal	Ukuran tabel
Tabel lama	1 GB

Lalu kamu menjalankan:

VACUUM FULL my_table;

Selama proses berlangsung, PostgreSQL akan:

Langkah	Ukuran Disk
Copy tabel lama → tabel baru	1 GB tambahan
Total sementara	2 GB
Setelah selesai (tabel lama dihapus)	~1 GB (lebih kecil karena sudah dibersihkan)

Jadi:

• Selama VACUUM FULL berlangsung, dua versi tabel akan ada di disk:

  1. File tabel lama (yang masih ada).

  2. File tabel baru (yang sedang dibangun).

• Setelah selesai, file lama dihapus, dan hanya file bersih yang tersisa.

Catatan

• Jika tabel besar (misalnya 50 GB), dan disk kamu hanya tersisa 30 GB, maka VACUUM FULL bisa gagal karena tidak cukup ruang untuk membuat salinan baru.

• Ini kenapa VACUUM FULL harus dilakukan dengan hati-hati di production system.

Kalau kamu ingin alternatif yang lebih hemat disk, bisa gunakan:

• VACUUM reguler (tidak reclaim ke OS tapi cepat).

• pg_repack (tool eksternal yang melakukan vacuum-like proses tanpa full lock dan dengan efisien ruang disk). Mau dijelaskan juga?

Sebelum lanjut, Kita akan membuat docker container bernama belajar-partitioning, seperti yang ditunjukkan pada file docker compose bawah ini:

services:
  db:
    container_name: belajar-partition
    image: postgres:17-alpine
    restart: always
    ports:
      - "5432:5432"
    environment:
      POSTGRES_USER: ${DB_USER:-postgres}
      POSTGRES_PASSWORD: ${DB_PASSWORD:-password}
      POSTGRES_DB: ${DB_NAME:-password}
    volumes:
      - postgres_data:/var/lib/postgresql/data
volumes:
  postgres_data:

Range partitioning

Range partitioning adalah di mana tabel dibagi menjadi "interval." Interval tidak boleh tumpang tindih dan rentang didefinisikan melalui penggunaan field atau sekumpulan field. Untuk informasi lebih lanjut, lihat https://www.postgresql.org/docs/current/ddl-partitioning.html.

Mari kita lihat contoh definisi range partitioning. Misalkan kita memiliki tabel ini:

Tabel sebelum range partitioning

field_date	field_value
2023-03-01	1
2023-03-02	10
2023-04-01	12
2023-04-15	1

Sekarang anggaplah kita ingin membagi tabel ini menjadi dua tabel. Tabel pertama (TABEL A) akan berisi semua record dengan nilai field_date antara 2023-03-01 dan 2023-03-31, dan tabel kedua (TABEL B) akan berisi semua record dengan nilai field_date antara 2023-04-01 dan 2023-04-30. Jadi, tujuan kita adalah memiliki dua tabel sebagai berikut:

Tabel A

field_date	field_value
2023-03-01	1
2023-03-02	10

Tabel B

field_date	field_value
2023-04-01	12
2023-04-15	1

Apa yang telah kita lihat adalah contoh partitioning berdasarkan range. Ini berguna ketika kita memiliki tabel besar di mana data dapat dibagi berdasarkan rentang waktu, misalnya, omset, tabel audit, atau tabel log.

List partitioning

Dalam list partitioning, tabel akan dipartisi menggunakan daftar nilai.

Mari kita lihat contoh definisi list partitioning. Misalkan kita memiliki tabel ini:

Tabel sebelum list partitioning

field_state	field_city
United States	Washington
United States	San Francisco
Italy	Rome
Japan	Tokyo

Misalkan sekarang kita ingin membagi tabel ini menjadi n tabel, dengan satu tabel untuk setiap negara bagian. Tabel pertama (TABEL A) akan berisi semua record dengan nilai field_state sama dengan United States, tabel kedua (TABEL B) akan berisi semua record dengan nilai field_state sama dengan Italy, dan tabel ketiga (TABEL C) akan berisi record dengan nilai field_state sama dengan Japan. Jadi, tujuan kita adalah memiliki tiga tabel sebagai berikut:

Tabel A

field_state	field_city
United States	Washington
United States	San Francisco

Tabel B

field_state	field_city
Italy	Rome

Tabel C

field_state	field_city
Japan	Tokyo

Ini adalah contoh partitioning berdasarkan list. Ini berguna ketika kita memiliki tabel besar di mana data dapat dibagi oleh satu field, seperti field kota atau negara bagian dalam buku telepon, atau dalam daftar pelanggan.

Hash partitioning

Dengan menggunakan hash partitioning, tabel akan dipartisi menggunakan nilai hash untuk membagi data ke dalam tabel yang berbeda. Mari kita lihat contoh hash partitioning. Misalkan kita memiliki tabel ini:

Tabel sebelum hash partitioning

field_date	field_value
2023-03-01	1
2023-03-02	1
2023-04-01	2
2023-04-15	2

Misalkan sekarang kita memiliki fungsi hash yang mengubah tanggal menjadi nilai hash, misalnya:

• hash(1) = 1

• hash(1) = 1

• hash(2) = 0

• hash(2) = 0

Jadi, setelah proses partitioning, kita akan memiliki dua tabel:

Tabel A

field_date	field_value
2023-03-01	1
2023-03-02	1

Tabel B

field_date	field_value
2023-04-01	2
2023-04-15	2

Ini adalah contoh partitioning berdasarkan hash.

Di bagian selanjutnya kita akan melihat bagaimana PostgreSQL mengimplementasikan list, range, dan hash partitioning, tetapi sebelum itu, mari kita luangkan waktu untuk berbicara tentang table inheritance.

Untuk informasi lebih lanjut tentang partitioning, lihat https://www.postgresql.org/docs/current/ddl-partitioning.html.

Table inheritance

Topik lain yang harus kita lihat adalah table inheritance. PostgreSQL menggunakan konsep inheritance dari database ke objek. Konsepnya sangat sederhana dan dapat diringkas sebagai berikut: misalkan kita memiliki dua tabel, TABEL A dan TABEL B. Jika kita mendefinisikan TABEL A sebagai tabel parent dan TABEL B sebagai tabel child, ini berarti bahwa semua record di TABEL B akan dapat diakses dari TABEL A.

Mari sekarang kita coba memberikan contoh dari apa yang baru saja kita jelaskan:

1. Mari kita definisikan dua tabel.

   Tabel pertama, tabel parent, didefinisikan sebagai berikut:

   create table table_a (
       pk integer not null primary key,
       tag text,
       parent integer
   );

   Dan tabel kedua, tabel child, didefinisikan sebagai berikut:

   create table table_b () inherits (table_a);
   alter table table_b add constraint table_b_pk primary key(pk);

2. Tabel child mewarisi semua field dari tabel parent. Tabel parent seperti yang terlihat di sini:

   \d table_a;
   Table "table_a"
   Column  | Type    |
   --------+---------+
   pk      | integer |
   tag     | text    |
   parent  | integer |
   Indexes:
   "table_a_pkey" PRIMARY KEY, btree (pk)
   Number of child tables: 1 (Use \d+ to list them.)

   Dan untuk detail lebih lanjut, kita bisa gunakan perintah \d+

3. Dalam tabel terakhir ini, kita dapat melihat bahwa table_b adalah tabel child dari table_a.

   Mari kita lakukan hal yang sama untuk tabel bernama table_b:

   \d table_b;
   Table "table_b"
   Column  | Type  
   --------+-----
   pk      | integer  
   tag     | text 
   parent  | integer
   Indexes:
   "table_b_pk" PRIMARY KEY, btree (pk)
   Inherits: table_a

   Di sini, kita dapat melihat bahwa table_b adalah tabel child dari table_a.

4. Sekarang mari kita lihat bagaimana kedua tabel ini berperilaku jika kita memasukkan, mengubah, atau menghapus data. Misalnya, mari kita lakukan beberapa insert sebagai berikut:

   insert into table_a (pk,tag,parent) values (1,'Operating Systems',0);
   insert into table_b (pk,tag,parent) values (2,'Linux',0);

5. Mari kita lihat bagaimana data kita bereaksi jika kita mengeksekusi perintah select:

   select * from table_b;
   
   pk  | tag  | parent
   ----+-------+--------
   2   | Linux | 0
   (1 row)

   Kita dapat melihat bahwa table_b memiliki satu record.

6. Sekarang kita jalankan perintah berikut:

   select * from table_a;
   
   pk  | tag               | parent
   ----+-------------------+--------
   1   | Operating Systems | 0
   2   | Linux             | 0
   (2 rows)

7. Tampaknya table_a memiliki dua record. Ini terjadi karena tabel ini mewarisi atribut tabel lain. Jika kita mengeksekusi perintah SELECT pada tabel parent, kita akan melihat semua record yang dimiliki oleh tabel parent dan semua record yang dimiliki oleh tabel child.

   Jika kita ingin melihat semua record yang hanya dimiliki oleh table_a, kita harus menggunakan klausa ONLY, seperti yang terlihat di sini:

   select * from only table_a;
   
   pk  | tag               |  parent
   ----+-------------------+--------
   1   | Operating Systems | 0
   (1 row)

8. Mari kita lihat apa yang terjadi jika kita UPDATE beberapa record, misalnya, jika kita mengeksekusi yang berikut:

   update table_a set tag='BSD Unix' where pk=2;

   Kita melakukan operasi update pada table_a, tetapi update ini secara fisik dilakukan pada table_b melalui inheritance tabel, seperti yang dapat kita lihat di sini:

   select * from table_b;
   
   pk  | tag      | parent
   ----+----------+--------
   2   | BSD Unix | 0
   (1 row)

9. Hal yang sama terjadi jika kita menggunakan statement delete sebagai berikut:

   delete from table_a where pk=2;

   Di sini, sekali lagi, operasi delete yang dilakukan pada table_a memiliki efek pada table_b; seperti yang dapat kita lihat di sini, table_a akan memiliki record ini:

   select * from table_a;
   
   pk  | tag               | parent
   ----+-------------------+--------
   1   | Operating Systems | 0
   (1 row)

   Dan table_b sekarang tidak akan memiliki record:

   select * from table_b;
   
   pk  | tag | parent
   ----+-----+--------
   (0 rows)

   Di PostgreSQL, inheritance menyebarkan operasi yang dilakukan pada tabel parent ke tabel child.

Dropping tables

Untuk menyimpulkan topik inheritance, kita perlu membahas cara menghapus tabel. Jika kita ingin menghapus tabel child, misalnya, untuk drop table_b, kita harus menjalankan statement berikut:

drop table table_b;

Jika kita ingin drop tabel parent dan semua tabel child yang terhubung dengannya, kita harus menjalankan yang berikut:

drop table table_a cascade;

Meskipun inheritance telah digunakan dan masih dapat digunakan untuk mengimplementasikan partitioning tabel, sejak versi 10 declarative partitioning telah menjadi metode yang lebih disukai. Kita membahas declarative partitioning di bagian selanjutnya.

Exploring declarative partitioning

Di bagian ini, kita akan berbicara tentang declarative partitioning. Ini telah tersedia di PostgreSQL sejak versi 10, tetapi kinerjanya telah meningkat di versi yang lebih baru. Kita sekarang akan melihat contoh partitioning berdasarkan range dan contoh partitioning berdasarkan list.

List partitioning

Dalam contoh pertama declarative partitioning, kita akan menggunakan contoh yang sama yang kita lihat ketika kita memperkenalkan partitioning menggunakan inheritance. Kita akan melihat bahwa hal-hal menjadi jauh lebih sederhana menggunakan metode declarative partitioning:

Sekarang mari kita buat tabel parent kita:

CREATE TABLE part_tags (
    pk SERIAL NOT NULL ,
    level INTEGER NOT NULL DEFAULT 0,
    tag VARCHAR (255) NOT NULL,
    primary key (pk,level)
) PARTITION BY LIST (level);

Seperti yang dapat kita lihat dari contoh sebelumnya, kita harus mendefinisikan jenis partitioning apa yang ingin kita terapkan. Dalam hal ini, ini adalah LIST PARTITIONING. Hal penting lain yang perlu diperhatikan adalah bahwa field yang digunakan untuk mempartisi data harus menjadi bagian dari primary key.

Selanjutnya, mari kita definisikan tabel child:

CREATE TABLE part_tags_level_0 PARTITION OF part_tags FOR VALUES IN (0);
CREATE TABLE part_tags_level_1 PARTITION OF part_tags FOR VALUES IN (1);
CREATE TABLE part_tags_level_2 PARTITION OF part_tags FOR VALUES IN (2);

Dalam contoh ini, kita telah membuat tiga tabel child yang mewarisi dari tabel parent. Tabel part_tags_level_0 akan berisi semua record dengan level sama dengan 0, part_tags_level_1 akan berisi semua record dengan level sama dengan 1, dan part_tags_level_2 akan berisi semua record dengan level sama dengan 2.

Mari kita coba memasukkan beberapa data:

insert into part_tags (level,tag) values (0,'Operating Systems');
insert into part_tags (level,tag) values (1,'Linux');
insert into part_tags (level,tag) values (2,'BSD Unix');

Mari kita lihat apa yang terjadi jika kita menjalankan perintah SELECT pada tabel parent:

select * from part_tags;
pk  | level | tag
----+-------+-------------------
1   | 0     | Operating Systems
2   | 1     | Linux
3   | 2     | BSD Unix
(3 rows)

Tabel parent berisi semua data dari semua tabel child.

Sekarang mari kita lihat apa yang terjadi jika kita menjalankan perintah SELECT pada tabel child:

select * from part_tags_level_0 ;
pk  | level | tag
----+-------+-------------------
1   | 0     | Operating Systems
(1 row)

select * from part_tags_level_1 ;
pk  | level | tag
----+-------+---------
2 | 1       | Linux
(1 row)

select * from part_tags_level_2 ;
pk  | level | tag
----+-------+----------
3.  | 2     | BSD Unix
(1 row)

Setiap tabel child berisi record yang sesuai dengan level yang ditentukan dalam perintah CREATE TABLE.

Jika kita mencoba memasukkan record dengan level yang tidak ditentukan, kita akan mendapatkan kesalahan:

insert into part_tags (level,tag) values (3,'Windows');
ERROR: no partition of relation "part_tags" matches the row
DETAIL: Failing row contains (4, 3, Windows).

Range partitioning

Sekarang mari kita lihat contoh lain, kali ini menggunakan range partitioning.

Mari kita buat tabel parent kita:

CREATE TABLE part_tags_date (
    pk SERIAL NOT NULL ,
    tag VARCHAR (255) NOT NULL,
    create_date DATE NOT NULL DEFAULT now():: DATE ,
    primary key (create_date, pk)
)
PARTITION BY RANGE (create_date);

Seperti yang dapat kita lihat dari contoh sebelumnya, kita harus menentukan jenis partitioning apa yang ingin kita terapkan. Dalam hal ini, ini adalah RANGE PARTITIONING. Hal penting lain yang perlu diperhatikan adalah bahwa field yang digunakan untuk mempartisi data harus menjadi bagian dari primary key.

Selanjutnya, mari kita definisikan tabel child:

CREATE TABLE part_tags_date_01 PARTITION OF part_tags_date FOR VALUES FROM ('2023-01-01') TO ('2023-06-30');
CREATE TABLE part_tags_date_02 PARTITION OF part_tags_date FOR VALUES FROM ('2023-07-01') TO ('2023-12-31');

Dalam contoh ini, kita telah membuat dua tabel child yang mewarisi dari tabel parent. Tabel part_tags_date_01 akan berisi semua record dengan create_date antara 2023-01-01 dan 2023-06-30, dan part_tags_date_02 akan berisi semua record dengan create_date antara 2023-07-01 dan 2023-12-31.

Mari kita coba memasukkan beberapa data:

insert into part_tags_date (tag,create_date) values ('Operating Systems','2023-01-01');
insert into part_tags_date (tag,create_date) values ('Linux','2023-07-01');
insert into part_tags_date (tag,create_date) values ('BSD Unix','2023-12-31');

Mari kita lihat apa yang terjadi jika kita menjalankan perintah SELECT pada tabel parent:

select * from part_tags_date;
pk  | tag | create_date
----+-------------------+-------------
1  | Operating Systems  | 2023-01-01
2  | Linux              | 2023-07-01
3  | BSD Unix           | 2023-12-31
(3 rows)

Tabel parent berisi semua data dari semua tabel child.

Sekarang mari kita lihat apa yang terjadi jika kita menjalankan perintah SELECT pada tabel child:

select * from part_tags_date_01;
pk  | tag               | create_date
----+-------------------+-------------
1   | Operating Systems | 2023-01-01
(1 row)

select * from part_tags_date_02;
pk  | tag       | create_date
----+-----------+-------------
2   | Linux     | 2023-07-01
3   | BSD Unix  | 2023-12-31
(2 rows)

Setiap tabel child berisi record yang sesuai dengan rentang yang ditentukan dalam perintah CREATE TABLE.

Jika kita mencoba memasukkan record dengan tanggal yang tidak ditentukan, kita akan mendapatkan kesalahan:

insert into part_tags_date (tag,create_date) values ('Windows','2024-01-01');
ERROR: no partition of relation "part_tags_date" matches the row
DETAIL: Failing row contains (4, Windows, 2024-01-01).

Partition Maintenance

Jika kita ingin membuat / attach partisi baru ke parent table, kita bisa jalankan perintah:

CREATE TABLE part_tags_date_05_2023 PARTITION OF part_tags FOR VALUES FROM ('2023-05-01') TO ('2023-05-30');

Jika kita ingin detach / melepaskan partisi bisa jalankan perintah:

ALTER TABLE part_tags DETACH PARTITION part_tags_date_05_2023;

The default partition

Default partition memastikan bahwa tidak ada data yang hilang; jika PostgreSQL tidak menemukan tabel child mana pun untuk menyimpan record, maka record tersebut disimpan di default partition.

Mari kita lihat contohnya:

Mari kita buat tabel parent kita:

CREATE TABLE part_tags_date_default (
    pk SERIAL NOT NULL ,
    tag VARCHAR (255) NOT NULL,
    create_date DATE NOT NULL DEFAULT now():: DATE ,
    primary key (create_date, pk)
) PARTITION BY RANGE (create_date);

Selanjutnya, mari kita definisikan tabel child kita:

CREATE TABLE part_tags_date_01 PARTITION OF part_tags_date_default FOR VALUES FROM ('2023-01-01') TO ('2023-06-30');
CREATE TABLE part_tags_date_02 PARTITION OF part_tags_date_default FOR VALUES FROM ('2023-07-01') TO ('2023-12-31');
CREATE TABLE part_tags_date_default_03 PARTITION OF part_tags_date_default DEFAULT;

Dalam contoh ini, kita telah membuat default partition.

Mari kita coba memasukkan beberapa data:

insert into part_tags_date_default (tag,create_date) values ('Operating Systems','2023-01-01');
insert into part_tags_date_default (tag,create_date) values ('Linux','2023-07-01');
insert into part_tags_date_default (tag,create_date) values ('BSD Unix','2023-12-31');
insert into part_tags_date_default (tag,create_date) values ('Windows','2024-01-01');

Mari kita lihat apa yang terjadi jika kita menjalankan perintah SELECT pada tabel parent:

select * from part_tags_date_default;

pk  | tag               | create_date
----+-------------------+-------------
1   | Operating Systems | 2023-01-01
2   | Linux             | 2023-07-01
3   | BSD Unix          | 2023-12-31
4   | Windows           | 2024-01-01
(4 rows)

Tabel parent berisi semua data dari semua tabel child.

Sekarang mari kita lihat apa yang terjadi jika kita menjalankan perintah SELECT pada tabel child:

select * from part_tags_date_01;
pk  | tag               | create_date
----+-------------------+-------------
1   | Operating Systems | 2023-01-01
(1 row)

select * from part_tags_date_02;
pk  | tag       | create_date
----+-----------+-------------
2   | Linux     | 2023-07-01
3   | BSD Unix  | 2023-12-31
(2 rows)

select * from part_tags_date_default_03;
pk  | tag     | create_date
----+---------+-------------
4   | Windows | 2024-01-01
(1 row)

Setiap tabel child berisi record yang sesuai dengan rentang yang ditentukan dalam perintah CREATE TABLE.

Karena kita memiliki default partition, kita tidak lagi mendapatkan kesalahan jika kita mencoba memasukkan record dengan tanggal yang tidak ditentukan.

Partitioning dan tablespace

Tablespace adalah lokasi di sistem file tempat PostgreSQL menyimpan data. Secara default, PostgreSQL menyimpan semua data dalam satu tablespace. Namun, kita dapat membuat tablespace tambahan dan menyimpan data tabel yang berbeda di tablespace yang berbeda. Ini dapat berguna jika kita memiliki beberapa disk dan ingin menyebarkan data di berbagai disk.

Mari kita lihat contohnya:

Pertama, kita akan membuat dua tablespace baru:

CREATE TABLESPACE part_tags_date_01_space LOCATION '/var/lib/postgresql/15/main/part_tags_date_01_space';
CREATE TABLESPACE part_tags_date_02_space LOCATION '/var/lib/postgresql/15/main/part_tags_date_02_space';

Sekarang mari kita buat tabel parent kita:

CREATE TABLE part_tags_date_tablespace (
    pk SERIAL NOT NULL ,
    tag VARCHAR (255) NOT NULL,
    create_date DATE NOT NULL DEFAULT now():: DATE ,
    primary key (create_date, pk)
) PARTITION BY RANGE (create_date);

Selanjutnya, mari kita definisikan tabel child kita dan menentukan tablespace tempat kita ingin menyimpannya:

CREATE TABLE part_tags_date_01 PARTITION OF part_tags_date_tablespace FOR VALUES FROM ('2023-01-01') TO ('2023-06-30') TABLESPACE part_tags_date_01_space;
CREATE TABLE part_tags_date_02 PARTITION OF part_tags_date_tablespace FOR VALUES FROM ('2023-07-01') TO ('2023-12-31') TABLESPACE part_tags_date_02_space;

Dalam contoh ini, tabel part_tags_date_01 akan disimpan di tablespace part_tags_date_01_space, dan tabel part_tags_date_02 akan disimpan di tablespace part_tags_date_02_space.

Mari kita coba memasukkan beberapa data:

insert into part_tags_date_tablespace (tag,create_date)
values ('Operating Systems','2023-01-01');

insert into part_tags_date_tablespace (tag,create_date)
values ('Linux','2023-07-01');

insert into part_tags_date_tablespace (tag,create_date)
values ('BSD Unix','2023-12-31');

Mari kita lihat apa yang terjadi jika kita menjalankan perintah SELECT pada tabel parent:

select * from part_tags_date_tablespace;
pk  | tag               | create_date
----+-------------------+-------------
1   | Operating Systems | 2023-01-01
2   | Linux             | 2023-07-01
3   | BSD Unix          | 2023-12-31
(3 rows)

Tabel parent berisi semua data dari semua tabel child.

Sekarang mari kita lihat apa yang terjadi jika kita menjalankan perintah SELECT pada tabel child:

select * from part_tags_date_01 ;
pk  | tag               | create_date
----+-------------------+-------------
1   | Operating Systems | 2023-01-01
(1 row)

select * from part_tags_date_02 ;
pk  | tag       | create_date
----+-----------+-------------
2   | Linux     | 2023-07-01
3   | BSD Unix  | 2023-12-31
(2 rows)

Setiap tabel child berisi record yang sesuai dengan rentang yang ditentukan dalam perintah CREATE TABLE.

Indexes pada tabel yang dipartisi

PostgreSQL mengelola index pada tabel yang dipartisi; jika kita membuat index pada tabel parent, index tersebut secara otomatis dibuat pada tabel child.

Mari kita lihat contohnya:

Mari kita buat tabel parent kita:

CREATE TABLE part_tags_index (
    pk SERIAL NOT NULL ,
    tag VARCHAR (255) NOT NULL,
    create_date DATE NOT NULL DEFAULT now():: DATE ,
    primary key (create_date, pk)
) PARTITION BY RANGE (create_date);

Selanjutnya, mari kita definisikan tabel child kita:

CREATE TABLE part_tags_index_01 PARTITION OF part_tags_index FOR VALUES FROM ('2023-01-01') TO ('2023-06-30');
CREATE TABLE part_tags_index_02 PARTITION OF part_tags_index FOR VALUES FROM ('2023-07-01') TO ('2023-12-31');

Mari kita buat index pada tabel parent:

CREATE INDEX part_tags_index_tag ON part_tags_index (tag);

Mari kita lihat index pada tabel parent:

\d part_tags_index
.....
"part_tags_index_pkey" PRIMARY KEY, btree (create_date, pk)
"part_tags_index_tag" btree (tag)
Number of child tables: 2 (Use \d+ to list them.)
Partition key: RANGE (create_date)

Mari kita lihat index pada tabel child:

\d part_tags_index_01
....
Indexes:
"part_tags_index_pkey" PRIMARY KEY, btree (create_date, pk)
"part_tags_index_tag" btree (tag)
Partition of: "part_tags_index" FOR VALUES FROM ('2023-01-01') TO ('2023-06-30')

Seperti yang dapat kita lihat, PostgreSQL mengelola index pada tabel yang dipartisi.

Triggers dan partitioning

Kita juga dapat menggunakan triggers untuk melakukan partitioning. Ini adalah metode yang sangat fleksibel, tetapi juga lebih lambat daripada declarative partitioning.

Mari kita lihat contohnya:

Mari kita buat tabel parent kita:

CREATE TABLE part_tags_trigger (
    pk SERIAL NOT NULL ,
    tag VARCHAR (255) NOT NULL,
    create_date DATE NOT NULL DEFAULT now():: DATE ,
    primary key (pk)
);

Selanjutnya, mari kita definisikan tabel child kita:

CREATE TABLE part_tags_trigger_01 (
    pk SERIAL NOT NULL,
    tag VARCHAR (255) NOT NULL,
    create_date DATE NOT NULL DEFAULT now():: DATE,
    primary key (pk)
);

CREATE TABLE part_tags_trigger_02 (
    pk SERIAL NOT NULL,
    tag VARCHAR (255) NOT NULL,
    create_date DATE NOT NULL DEFAULT now():: DATE,
    primary key (pk)
);

Sekarang kita akan membuat sebuah fungsi yang akan memutuskan tabel child mana yang akan menyimpan record:

CREATE OR REPLACE FUNCTION part_tags_trigger_function()
RETURNS TRIGGER AS $$
BEGIN
    IF (NEW.create_date >= DATE '2023-01-01' AND NEW.create_date <= DATE '2023-06-30') THEN
        INSERT INTO part_tags_trigger_01 VALUES (NEW.*);
    
    ELSIF (NEW.create_date >= DATE '2023-07-01' AND NEW.create_date <= DATE '2023-12-31') THEN
        INSERT INTO part_tags_trigger_02 VALUES (NEW.*);
    
    ELSE
        RAISE EXCEPTION 'Invalid date %', NEW.create_date;
    END IF;

    RETURN NULL;
END;
$$
LANGUAGE plpgsql;

Sekarang kita akan membuat trigger yang akan memanggil fungsi tersebut:

CREATE TRIGGER part_tags_trigger_trigger
BEFORE INSERT ON part_tags_trigger
FOR EACH ROW EXECUTE PROCEDURE part_tags_trigger_function();

Mari kita coba memasukkan beberapa data:

insert into part_tags_trigger (tag,create_date) values ('Operating Systems','2023-01-01');
insert into part_tags_trigger (tag,create_date) values ('Linux','2023-07-01');
insert into part_tags_trigger (tag,create_date) values ('BSD Unix','2023-12-31');
insert into part_tags_trigger (tag,create_date) values ('Windows','2024-01-01');
ERROR: Invalid date 2024-01-01
CONTEXT: PL/pgSQL function part_tags_trigger_function() line 8 at
RAISE

Mari kita lihat apa yang terjadi jika kita menjalankan perintah SELECT pada tabel parent:

select * from part_tags_trigger ;
pk | tag | create_date
----+-----+-------------
(0 rows)

Tabel parent tidak berisi data apa pun.

Sekarang mari kita lihat apa yang terjadi jika kita menjalankan perintah SELECT pada tabel child:

select * from part_tags_trigger_01;
pk | tag | create_date
----+-------------------+-------------
1 | Operating Systems | 2023-01-01
(1 row)

select * from part_tags_trigger_02;
pk | tag | create_date
----+-----------+-------------
2 | Linux | 2023-07-01
3 | BSD Unix | 2023-12-31
(2 rows)

Setiap tabel child berisi record yang sesuai dengan rentang yang ditentukan dalam trigger. Karena kita menggunakan trigger, kita mendapatkan kesalahan jika kita mencoba memasukkan record dengan tanggal yang tidak ditentukan.

Batasan

Ada beberapa batasan pada partitioning:

• Semua tabel child harus mewarisi dari tabel parent.

• Field yang digunakan untuk mempartisi data harus menjadi bagian dari primary key.

• Kita tidak dapat menggunakan UPDATE untuk memindahkan baris dari satu partisi ke partisi lain. Kita harus menggunakan DELETE dan INSERT.

• Kita tidak dapat menggunakan TRUNCATE pada tabel parent. Kita harus menggunakan TRUNCATE pada setiap tabel child.

• Kita tidak dapat menggunakan FOREIGN KEY pada tabel yang dipartisi.

Kapan menggunakan partitioning

Partitioning adalah teknik yang sangat berguna, tetapi tidak selalu diperlukan. Berikut adalah beberapa pedoman tentang kapan menggunakan partitioning:

• Gunakan partitioning ketika tabel Anda sangat besar.

• Gunakan partitioning ketika Anda memiliki query yang hanya mengakses sebagian kecil dari data.

• Gunakan partitioning ketika Anda memiliki operasi yang perlu dilakukan pada sebagian kecil dari data.

• Jangan gunakan partitioning jika tabel Anda kecil.

• Jangan gunakan partitioning jika Anda memiliki query yang mengakses sebagian besar data.

• Jangan gunakan partitioning jika Anda tidak memiliki operasi yang perlu dilakukan pada sebagian kecil dari data.

Ringkasan

Kita telah melihat bagaimana kita dapat melakukan partisi data, dan, dalam beberapa kasus, bagaimana kita dapat menggunakan rules dan triggers yang telah dipelajari di bab sebelumnya untuk memungkinkan partitioning. Kita mulai dengan memperkenalkan konsep dasar partitioning, dan kemudian kita melihat kemungkinan yang ditawarkan PostgreSQL untuk mengimplementasikan partitioning.