Posts Tags Categories About

Optimasi Database Odoo dengan PostgreSQL Partitioning — 320 GB Jadi 7 Partisi

Arman included in Database
   1111 words   6 minutes 
/optimasi-database-odoo-dengan-postgresql-partitioning/armannurhidayat-blog-pengalaman-partitioning-db-postgres-odoo.jpg

Pendahuluan

Beberapa minggu yang lalu, saya menghadapi masalah klasik di proyek Odoo 12 klien: database membengkak hingga 320 GB untuk hanya 6 tabel. Ya, 6 tabel saja.

Akar masalahnya? Tabel-tabel ini menyimpan gambar desain produk (binary) yang di-upload untuk setiap proses produksi. Akumulasi bertahun-tahun.

Artikel ini menceritakan pengalaman saya melakukan PostgreSQL partitioning untuk menyelesaikan masalah ini. Untuk pemula yang ingin belajar partitioning, atau sedang mencari solusi untuk database besar.

Apa Itu Partitioning?

Bayangkan lemari arsip kantoran:

  • Tanpa partitioning: Semua dokumen dari 2010 sampai 2026 ditumpuk jadi satu tumpukan. Mau cari dokumen 2025? Ngobrak-abrik semua.
  • Dengan partitioning: Tumpukan dipecah per tahun. Masing-masing rak sendiri.

Di PostgreSQL:

1
2

Tanpa Partition:  [SEMUA DATA dalam 1 tabel besar]
Dengan Partition: [Tabel Induk] → [2023] [2024] [2025] [2026]

Data tetap bisa di-query seperti biasa. Tapi PostgreSQL otomatis hanya membaca partisi yang relevan.

Timeline Proyek

Kondisi awal database:

Tabel Ukuran Isi
tbl_or_line_doc 241 GB Gambar desain PO Garmen
tbl_sample_line_doc 22 GB Gambar sample
tbl_request_line_doc 19 GB Gambar request marketing
tbl_document_po_garmen_line 18 GB Dokumen PO
tbl_approval_desain 10 GB Gambar approval desain
mail_message 10 GB Email internal Odoo
Total 320 GB Hanya 6 tabel!

Persiapan

Analisa Tabel

Semua tabel memiliki pola yang sama:

1
2
3
4
5
6
7
8
9

\d tbl_or_line_doc

       Column       |       Type       | Nullable
--------------------+------------------+----------
 id                 | integer          | not null
 name               | character varying|
 doc                | bytea            | ← PENYEBABNYA!
 create_date        | timestamp        |
 ...

Kolom doc (bytea/binary) berisi gambar desain 2-4 MB per baris. Kali 100 ribu baris = 241 GB.

Pilih Partition Key

Pilihan paling masuk akal: create_date. Alasan:

  1. Data historis jarang diubah — cocok range partitioning
  2. Query Odoo sering filter tanggal
  3. Partisi per tahun — pas granularitasnya

Rencana Partisi

Setiap tabel dipecah jadi 7-8 partisi:

1
2
3
4
5
6
7

_p_legacy :  < 2023
_p2023    :  2023-01-01 s.d. 2024-01-01
_p2024    :  2024-01-01 s.d. 2025-01-01
_p2025    :  2025-01-01 s.d. 2026-01-01
_p2026    :  2026-01-01 s.d. 2027-01-01
_p2027    :  2027-01-01 s.d. 2028-01-01
_p_future :  ≥ 2028 (catch-all)

Script Partitioning

Saya menyusun 12 file SQL:

1
2
3
4
5
6
7
8

00_preparation.sql      → Setup tabel log, backup FK, cek NULL
01_tbl_or_line_doc.sql  → Partition 241 GB (tabel terberat)
02-05_*.sql             → 4 tabel dokumen lainnya
06_mail_message.sql     → Tabel email
07_auto_partition.sql   → Fungsi auto-create ke depan
08_cleanup.sql          → Verifikasi + drop tabel lama
09_restore_fk.sql       → Restore foreign key
fix_null.sql            → Perbaiki NULL values

Alur Partitioning

Setiap script 01-06 mengikuti pola yang sama:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

-- STEP 0: Cleanup
DROP TABLE IF EXISTS nama_tabel_new CASCADE;
DROP TABLE IF EXISTS nama_tabel_p2023 CASCADE;
-- ...dst

-- STEP 1: Buat partitioned table
CREATE TABLE nama_tabel_new (
    LIKE nama_tabel INCLUDING DEFAULTS
    EXCLUDING INDEXES EXCLUDING CONSTRAINTS
) PARTITION BY RANGE (create_date);

-- STEP 2: Buat partitions
CREATE TABLE _p2023 PARTITION OF nama_tabel_new
    FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');

-- STEP 3: Copy data + verifikasi
INSERT INTO nama_tabel_new SELECT * FROM nama_tabel;
-- Cek jumlah baris

-- STEP 4: Sync sequence ID
SELECT setval('nama_tabel_id_seq', max(id));

-- STEP 5: Buat index
CREATE INDEX ON nama_tabel_new USING btree (id);

-- STEP 6: Swap tabel (transaksi, rollback jika gagal)
BEGIN;
ALTER TABLE nama_tabel RENAME TO nama_tabel_old;
ALTER TABLE nama_tabel_new RENAME TO nama_tabel;
COMMIT;

-- STEP 7: VACUUM ANALYZE
VACUUM ANALYZE nama_tabel_p2023;

Kenapa Metode Copy + Swap?

Karena tabel existing sudah punya data dan index. Dengan copy + swap:

  1. Copy data di background
  2. Verifikasi row count
  3. Swap dalam 1 transaksi (< 1 detik downtime)
  4. Jika gagal, rollback — data aman

Eksekusi

Persiapan

  1. Stop Odoo — tidak boleh ada koneksi lain
  2. Cek free disk — perlu duplikasi. 241 GB → butuh ~300 GB free
  3. Gunakan screen — proses berjam-jam
1
2
3
4

screen -S partitioning
psql -U odoo -h 192.168.1.75 -p 5432 -d TERATAI_WIDJAJA \
  -f addons/teratai/script_partitioning_teratai/00_preparation.sql
# Ctrl+A, D untuk detach

00_preparation.sql — Persiapan

Berjalan mulus — membuat tabel log, backup FK, cek NULL. Semua 0 NULL.

01_tbl_or_line_doc.sql — 241 GB

Ini yang terberat. 7,5 jam kemudian:

1
2
3

NOTICE:  Copy completed in 27317 seconds
NOTICE:  Verified: 101683 rows OK
NOTICE:  Sequence synced from 103254 to 103254

7,5 jam hanya untuk copy data. Kenapa? Setiap baris berisi binary 2-4 MB. PostgreSQL harus baca, tulis, kompres TOAST, tulis WAL.

Yang Tidak Berjalan Mulus

Koneksi SSH terputus di tengah copy! Saya belum pakai screen saat itu. PostgreSQL otomatis rollback — data aman. Tapi 241 GB harus di-copy ulang.

Pelajaran: Screen itu WAJIB.

1
2
3

screen -S partitioning   # Buat session
Ctrl+A, D               # Detach (biarkan jalan)
screen -r partitioning   # Re-attach

Analisa I/O Disk

Dari metrik PostgreSQL:

1

SELECT * FROM pg_stat_bgwriter;

1
2
3

checkpoints_req  : 157 (terlalu sering)
maxwritten_clean : 21976 (background writer kewalahan)
buffers_backend  : 33,474,638 (~255 GB)

Throughput hanya 12,5 MB/s — idealnya 80-160 MB/s untuk HDD. Server mengalami I/O bottleneck. Tapi partitioning tetap berhasil.

Hasil

Sebelum Partitioning

1
2
3

Tabel                  | Ukuran | Query Tahun 2025
-----------------------|--------|------------------
tbl_or_line_doc        | 241 GB | FULL SCAN 241 GB

Sesudah Partitioning

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

Tabel                  | Partisi                    | Query 2025
-----------------------|----------------------------|-----------
tbl_or_line_doc        | 7 partisi (19-83 GB)       | Scan 83 GB
  ├─ _p_legacy (<2023) | 19 GB
  ├─ _p2023            | 49 GB
  ├─ _p2024            | 48 GB
  ├─ _p2025            | 83 GB ← TERBESAR
  ├─ _p2026            | 16 GB
  ├─ _p2027            | 0 bytes
  └─ _p_future (≥2028) | 0 bytes

Verifikasi

1
2
3
4
5

\d tbl_or_line_doc

Partitioned table "public.tbl_or_line_doc"
Partition key: RANGE (create_date)
Number of partitions: 7

Yang Saya Pelajari

1. Data Binary Sangat Berat

241 GB yang saya pikir adalah “baris” ternyata 99% adalah binary di TOAST storage. Operasi copy TOAST jauh lebih lambat.

2. I/O Disk adalah Raja

Untuk database besar, SSD bukan pilihan — keharusan. HDD dengan 12,5 MB/s membuat copy 241 GB butuh 7,5 jam.

3. Screen Itu Wajib

Satu perintah screen -S partitioning menyelamatkan dari frustrasi koneksi putus.

4. Script Idempotent = Tenang

Dengan DROP TABLE IF EXISTS, script bisa dijalankan ulang kapan saja tanpa error.

Automation

Untuk maintenance ke depan:

1
2

-- Panggil tiap awal tahun
SELECT auto_create_all_partitions();

Fungsi ini otomatis:

  1. Deteksi tahun terakhir dari partisi yang ada
  2. Drop partisi _p_future (catch-all)
  3. Buat partisi baru
  4. Re-create _p_future dengan batas baru

Kesimpulan

Apa yang saya dapatkan:

Sebelum Sesudah
1 tabel 241 GB 7 partisi 0-83 GB
Query FULL SCAN Query partition pruning
VACUUM lock 241 GB VACUUM per partisi kecil
Backup semua data Backup per tahun

Apa yang saya korbankan:

  • 7,5 jam waktu copy
  • ~500 GB IOPs selama proses

Apakah sepadan? Sangat. Untuk database yang terus bertambah, partitioning adalah investasi jangka panjang.

Updated on 2024-10-30
 postgresql, Odoo, database, partitioning, tutorial
Back | Home