Partisi PostgreSQL untuk tabel event dalam audit logging

Mengapa tabel event dan audit menjadi masalah

Tabel event dan tabel audit terlihat mirip, tetapi ada untuk alasan yang berbeda.

Tabel event mencatat hal-hal yang terjadi: page view, email terkirim, panggilan webhook, eksekusi job. Tabel audit mencatat siapa mengubah apa dan kapan: perubahan status, pembaruan izin, persetujuan payout—sering dengan detail “sebelum” dan “sesudah”.

Keduanya tumbuh cepat karena sifatnya append-only. Anda jarang menghapus baris individual, dan baris baru datang setiap menit. Bahkan produk kecil bisa menghasilkan jutaan baris log dalam beberapa minggu ketika job latar dan integrasi dihitung.

Masalah terlihat dalam kerja sehari-hari. Panel admin butuh filter cepat seperti “error kemarin” atau “aksi oleh user ini.” Saat tabel berkembang, layar dasar itu mulai melambat.

Anda biasanya akan melihat beberapa gejala terlebih dahulu:

• Filter butuh beberapa detik (atau time out) bahkan dengan rentang tanggal yang sempit.
• Indeks tumbuh besar sehingga insert melambat dan biaya penyimpanan naik.
• VACUUM dan autovacuum memakan waktu lebih lama, dan Anda mulai memperhatikan pemeliharaan.
• Retensi jadi berisiko: menghapus baris lama lambat dan menciptakan bloat.

Partitioning adalah salah satu cara untuk menangani ini. Secara sederhana, ia membagi satu tabel besar menjadi banyak tabel kecil (partisi) yang berbagi nama logis. PostgreSQL mengarahkan setiap baris baru ke partisi yang tepat berdasarkan aturan, biasanya waktu.

Itulah mengapa tim melihat partitioning PostgreSQL untuk tabel event: ini bisa menjaga data terbaru di potongan-potongan yang lebih kecil, sehingga PostgreSQL dapat melewati seluruh partisi ketika kueri hanya butuh jendela waktu tertentu.

Partitioning bukanlah tombol ajaib. Ia bisa sangat membantu untuk kueri seperti “7 hari terakhir” dan membuat retensi lebih sederhana (meng-drop partisi lama cepat). Tapi ia juga dapat menciptakan masalah baru:

• Kueri yang tidak menggunakan partition key mungkin harus memeriksa banyak partisi.
• Lebih banyak partisi berarti lebih banyak objek untuk dikelola dan lebih banyak kemungkinan konfigurasi yang salah.
• Beberapa constraint unik dan indeks jadi lebih sulit ditegakkan di seluruh data.

Jika panel admin Anda sangat bergantung pada filter tanggal dan aturan retensi yang dapat diprediksi, partitioning bisa menjadi kemenangan nyata. Jika kebanyakan kueri adalah “cari semua aksi oleh user X di seluruh sejarah,” itu bisa menimbulkan masalah kecuali Anda merancang UI dan indeks dengan hati-hati.

Pola akses tipikal untuk log dan audit

Tabel event dan audit tumbuh ke satu arah: ke atas. Mereka mendapatkan aliran insert yang stabil dan hampir tidak ada update. Sebagian besar baris ditulis sekali, lalu dibaca nanti untuk dukungan, review insiden, atau pemeriksaan kepatuhan.

Bentuk "append-only" ini penting. Performa write menjadi perhatian konstan karena insert terjadi sepanjang hari, sementara performa baca penting pada momen tertentu (saat support atau ops butuh jawaban cepat).

Kebanyakan pembacaan adalah filter, bukan lookup acak. Di panel admin, seseorang biasanya memulai luas (24 jam terakhir) lalu menyempit ke user, entitas, atau aksi.

Filter umum meliputi:

• Rentang waktu
• Actor (user ID, service account, alamat IP)
• Target (tipe entitas + entity ID, mis. Order #1234)
• Jenis aksi (created, updated, deleted, login failed)
• Status atau tingkat keparahan (success/error)

Rentang waktu adalah “pemotongan pertama” alami karena hampir selalu ada. Itulah wawasan kunci di balik partitioning PostgreSQL untuk tabel event: banyak kueri menginginkan irisan waktu, dan semua filter lain adalah penyaringan tambahan di dalam irisan itu.

Retensi adalah hal konstan lainnya. Log jarang hidup selamanya. Tim sering menyimpan event detail tinggi selama 30 atau 90 hari, lalu menghapus atau mengarsipkannya. Log audit mungkin punya kebutuhan lebih lama (365 hari atau lebih), tetapi bahkan kemudian Anda biasanya ingin cara yang dapat diprediksi untuk menghapus data lama tanpa memblokir database.

Audit logging juga datang dengan ekspektasi tambahan. Anda umumnya menginginkan riwayat yang immutabel, setiap record dapat ditelusuri (siapa/apa/kapan plus konteks request atau session), dan akses dikendalikan (tidak semua orang boleh melihat event terkait keamanan).

Pola-pola ini muncul langsung dalam desain UI. Filter yang diharapkan orang secara default — pemilih tanggal, selector user, pencarian entitas, dropdown aksi — adalah filter yang sama yang harus didukung tabel dan indeks Anda jika Anda ingin pengalaman admin tetap cepat saat volume tumbuh.

Cara mengetahui apakah partitioning layak

Partitioning bukan best practice default untuk log audit. Ia terasa menguntungkan ketika satu tabel menjadi sangat besar sehingga kueri sehari-hari dan maintenance rutin mulai saling bertentangan.

Petunjuk ukuran sederhana: setelah tabel event mencapai puluhan juta baris, layak mulai mengukur. Ketika tabel dan indeksnya tumbuh menjadi puluhan gigabyte, bahkan pencarian rentang tanggal “sederhana” bisa menjadi lambat atau tidak dapat diprediksi karena lebih banyak halaman data dibaca dari disk dan indeks jadi mahal untuk dipertahankan.

Sinyal kueri paling jelas adalah ketika Anda secara teratur meminta irisan waktu kecil (hari terakhir, minggu terakhir), tetapi PostgreSQL tetap menyentuh bagian besar tabel. Anda akan melihatnya sebagai layar “aktivitas terbaru” yang lambat, atau audit yang difilter menurut tanggal plus user, tipe aksi, atau entity ID. Jika rencana kueri menunjukkan scan besar atau buffer read konsisten tinggi, Anda membayar untuk data yang tidak bermaksud dibaca.

Sinyal operasional sama pentingnya:

• VACUUM dan autovacuum memakan waktu jauh lebih lama dari sebelumnya.
• Autovacuum tertinggal dan dead tuple (bloat) menumpuk.
• Indeks tumbuh lebih cepat dari perkiraan, terutama indeks multi-kolom.
• Kontensi lock menjadi lebih terasa ketika maintenance tumpang tindih dengan traffic.

Biaya operasional adalah tetesan lambat yang mendorong tim ke partitioning. Backup dan restore melambat saat satu tabel tumbuh, penyimpanan meningkat, dan job retensi menjadi mahal karena DELETE besar menciptakan bloat dan kerja vacuum ekstra.

Jika tujuan utama Anda adalah kebijakan retensi yang bersih dan kueri “periode terbaru” yang lebih cepat, partitioning biasanya layak dipertimbangkan. Jika tabel masih moderat dan kueri sudah cepat dengan indeks yang baik, partitioning dapat menambah kompleksitas tanpa manfaat jelas.

Opsi partitioning yang cocok untuk tabel event dan audit

Untuk sebagian besar data audit dan event, pilihan paling sederhana adalah range partitioning berdasarkan waktu. Log datang berurutan menurut waktu, kueri sering fokus pada “24 jam terakhir” atau “30 hari terakhir,” dan retensi biasanya berbasis waktu. Dengan partisi waktu, menghapus data lama bisa semudah menghapus partisi lama alih-alih menjalankan DELETE besar yang membuat bloat.

Partitioning berbasis rentang waktu juga menjaga indeks lebih kecil dan lebih fokus. Setiap partisi punya indeks sendiri, sehingga kueri untuk minggu lalu tidak perlu menelusuri satu indeks raksasa yang meliputi sejarah bertahun-tahun.

Gaya partitioning lain ada, tetapi cocok untuk lebih sedikit kasus log dan audit:

• List (tenant atau customer) bisa bekerja ketika Anda punya sejumlah kecil tenant yang sangat besar dan kueri biasanya tetap di dalam satu tenant. Ini menjadi menyulitkan ketika Anda punya ratusan atau ribuan tenant.
• Hash (penyebaran write merata) bisa membantu ketika Anda tidak punya kueri window waktu dan ingin men-distribusikan penulisan. Untuk audit log kurang umum karena membuat retensi dan penelusuran berdasarkan waktu jadi lebih sulit.
• Subpartitioning (waktu plus tenant) bisa sangat kuat, tetapi kompleksitas tumbuh cepat. Ini terutama untuk sistem ber-volume sangat tinggi dengan kebutuhan isolasi tenant yang ketat.

Jika Anda memilih rentang waktu, pilih ukuran partisi yang sesuai dengan cara Anda menelusuri dan meretensi data. Partisi harian masuk akal untuk tabel ber-volume sangat tinggi atau retensi yang ketat. Partisi bulanan lebih mudah dioperasikan pada volume moderat.

Contoh praktis: jika tim admin memeriksa percobaan login yang gagal setiap pagi dan memfilter 7 hari terakhir, partisi harian atau mingguan berarti kueri hanya menyentuh partisi paling baru. PostgreSQL bisa mengabaikan sisanya.

Apa pun pendekatannya, rencanakan bagian membosankan: membuat partisi masa depan, menangani event yang datang terlambat, dan mendefinisikan apa yang terjadi di setiap batas (akhir hari, akhir bulan). Partitioning membayar ketika rutinitas itu tetap sederhana.

Cara memilih partition key yang tepat

Partition key yang baik mencocokkan cara Anda membaca tabel, bukan bagaimana data terlihat di diagram.

Untuk log dan audit, mulai dari panel admin Anda: filter apa yang orang gunakan pertama kali, hampir setiap saat? Untuk kebanyakan tim itu rentang waktu (24 jam terakhir, 7 hari terakhir, tanggal kustom). Jika itu benar untuk Anda, partitioning berbasis waktu biasanya memberi kemenangan terbesar dan paling dapat diprediksi karena PostgreSQL bisa melewati seluruh partisi di luar rentang yang dipilih.

Anggap key itu sebagai janji jangka panjang. Anda mengoptimalkan untuk kueri yang akan terus dijalankan selama bertahun-tahun.

Mulai dengan “filter pertama” yang digunakan orang

Sebagian besar layar admin mengikuti pola: rentang waktu plus opsional user, aksi, status, atau resource. Partisi berdasarkan hal yang mempersempit hasil lebih awal dan konsisten.

Pemeriksaan realita singkat:

• Jika tampilan default adalah “event terbaru,” partisi berdasarkan timestamp.
• Jika tampilan default adalah “event untuk satu tenant/account,” tenant_id bisa masuk akal, tapi hanya jika tenant cukup besar untuk dibenarkan.
• Jika langkah pertama selalu “pilih user,” user_id mungkin menggoda, tetapi biasanya menghasilkan terlalu banyak partisi untuk dikelola.

Hindari kunci dengan cardinality tinggi

Partitioning bekerja terbaik ketika setiap partisi adalah potongan data yang bermakna. Kunci seperti user_id, session_id, request_id, atau device_id bisa menghasilkan ribuan atau jutaan partisi. Itu menambah overhead metadata, mempersulit maintenance, dan sering memperlambat perencanaan.

Partisi berbasis waktu menjaga jumlah partisi dapat diprediksi. Anda memilih harian, mingguan, atau bulanan berdasarkan volume. Terlalu sedikit partisi (satu per tahun) tidak banyak membantu. Terlalu banyak (satu per jam) menambah overhead dengan cepat.

Pilih timestamp yang tepat: created_at vs occurred_at

Jelaskan apa arti waktu:

• occurred_at: saat event terjadi dalam produk.
• created_at: saat database merekamnya.

Untuk audit, “occurred” sering kali yang diinginkan admin. Namun pengiriman terlambat (klien mobile offline, retry, antrean) berarti occurred_at bisa datang terlambat. Jika kedatangan terlambat umum, mempartisi berdasarkan created_at dan mengindeks occurred_at untuk penyaringan bisa lebih stabil secara operasional. Opsi lain adalah mendefinisikan kebijakan backfill yang jelas dan menerima bahwa partisi lama kadang mendapat event terlambat.

Juga putuskan bagaimana Anda menyimpan waktu. Gunakan tipe yang konsisten (sering timestamptz) dan perlakukan UTC sebagai sumber kebenaran. Format untuk timezone pengguna di UI. Itu menjaga batas partisi tetap stabil dan menghindari masalah daylight saving.

Langkah demi langkah: rencanakan dan jalankan partitioning

Partitioning paling mudah ketika Anda memperlakukannya seperti proyek migrasi kecil, bukan tweak cepat. Tujuannya adalah penulisan sederhana, pembacaan yang dapat diprediksi, dan retensi yang menjadi operasi rutin.

Rencana rollout praktis

1. Pilih ukuran partisi yang sesuai dengan volume Anda. Partisi bulanan biasanya cukup untuk beberapa ratus ribu baris per bulan. Jika Anda memasukkan puluhan juta baris per bulan, partisi mingguan atau harian biasanya menjaga indeks lebih kecil dan pekerjaan vacuum lebih terkendali.

2. Rancang key dan constraint untuk tabel terpartisi. Di PostgreSQL, unique constraint harus menyertakan partition key (atau ditegakkan dengan cara lain). Pola umum adalah (created_at, id), di mana id dihasilkan dan created_at adalah partition key. Ini menghindari kejutan ketika Anda menemukan constraint yang Anda harapkan ternyata tidak diizinkan.

3. Buat partisi masa depan sebelum dibutuhkan. Jangan tunggu insert gagal karena tidak ada partisi yang cocok. Tentukan seberapa jauh ke depan membuatnya (misalnya 2-3 bulan) dan jadikan itu pekerjaan rutin.

4. Jaga indeks per-partisi kecil dan selektif. Partitioning tidak membuat indeks gratis. Sebagian besar tabel event butuh partition key plus satu atau dua indeks yang cocok dengan filter admin nyata, seperti actor_id, entity_id, atau event_type. Lewatkan indeks “untuk berjaga-jaga”. Anda bisa menambahkannya nanti pada partisi baru dan backfill partisi lama jika perlu.

5. Rencanakan retensi dengan menghapus partisi, bukan menghapus baris. Jika Anda menyimpan 180 hari log, menghapus partisi lama itu cepat dan menghindari DELETE besar yang berjalan lama dan bloat. Tulis aturan retensi, siapa yang menjalankannya, dan bagaimana Anda memverifikasi hasilnya.

Contoh kecil

Jika tabel audit Anda mendapat 5 juta baris per minggu, partisi mingguan pada created_at adalah awal yang masuk akal. Buat partisi 8 minggu ke depan dan pertahankan dua indeks per partisi: satu untuk pencarian umum berdasarkan actor_id dan satu untuk entity_id. Ketika window retensi berakhir, drop partisi mingguan terlama alih-alih menghapus jutaan baris.

Jika Anda membangun alat internal dengan AppMaster, membantu untuk memutuskan partition key dan constraint lebih awal supaya model data dan kode yang digenerasikan mengikuti asumsi yang sama sejak hari pertama.

Apa yang berubah untuk filter panel admin

Setelah Anda mempartisi tabel log, filter panel admin berhenti menjadi “hanya UI.” Mereka menjadi faktor utama yang menentukan apakah kueri menyentuh beberapa partisi saja atau memindai berbulan-bulan data.

Perubahan praktis terbesar: waktu tidak bisa lagi menjadi opsional. Jika pengguna bisa menjalankan pencarian tanpa batas (tanpa rentang tanggal, hanya “tampilkan semua untuk user X”), PostgreSQL mungkin harus memeriksa setiap partisi. Meskipun setiap pemeriksaan cepat, membuka banyak partisi menambah overhead dan halaman terasa lambat.

Aturan yang bekerja baik: wajibkan rentang waktu untuk pencarian log dan audit, dan tetapkan default yang masuk akal (misalnya 24 jam terakhir). Jika seseorang benar-benar membutuhkan “seluruh waktu”, buat itu pilihan yang disengaja dan beri peringatan bahwa hasil mungkin lebih lambat.

Buat filter sesuai dengan partition pruning

Partition pruning hanya membantu ketika klausa WHERE menyertakan partition key dalam bentuk yang bisa digunakan PostgreSQL. Filter seperti created_at BETWEEN X AND Y mem-prune dengan rapi. Pola yang sering memecah pruning termasuk casting timestamp ke date, membungkus kolom dalam fungsi, atau memfilter terutama berdasarkan kolom waktu yang berbeda dari partition key.

Di dalam setiap partisi, indeks harus cocok dengan cara orang benar-benar memfilter. Dalam praktiknya, kombinasi yang sering penting adalah waktu ditambah satu kondisi lain: tenant/workspace, user, aksi/tipe, entity ID, atau status.

Sorting dan paginasi: jaga tetap dangkal

Partitioning tidak akan memperbaiki paginasi lambat sendiri. Jika panel admin mengurutkan berdasarkan terbaru dan pengguna lompat ke halaman 5000, paginasi OFFSET yang dalam tetap memaksa PostgreSQL melewati banyak baris.

Paginasi berbasis cursor cenderung lebih baik untuk log: “muat event sebelum timestamp/id ini.” Ini membuat database menggunakan indeks ketimbang melewatkan offset besar.

Preset membantu juga. Beberapa opsi biasanya sudah cukup: 24 jam terakhir, 7 hari terakhir, hari ini, kemarin, rentang kustom. Preset mengurangi pencarian “scan semuanya” yang tidak sengaja dan membuat pengalaman admin lebih dapat diprediksi.

Kesalahan umum dan jebakan

Kebanyakan proyek partitioning gagal karena alasan sederhana: partitioning bekerja, tetapi kueri dan UI admin tidak sesuai. Jika Anda ingin partitioning memberikan manfaat, desainlah berdasarkan filter nyata dan retensi nyata.

1) Mempartisi berdasarkan kolom waktu yang salah

Partition pruning hanya terjadi jika klausa WHERE cocok dengan partition key. Kesalahan umum adalah mempartisi berdasarkan created_at sementara panel admin memfilter berdasarkan event_time (atau sebaliknya). Jika tim support selalu bertanya “apa yang terjadi antara 10:00 dan 10:15”, tetapi tabel dipartisi berdasarkan waktu ingestion, Anda masih bisa menyentuh lebih banyak data daripada yang diharapkan.

2) Membuat terlalu banyak partisi kecil

Partisi per jam (atau lebih kecil) terlihat rapi, tetapi menambah overhead: lebih banyak objek untuk dikelola, lebih banyak kerja planner, dan lebih banyak kemungkinan indeks hilang atau permission tidak cocok.

Kecuali Anda punya write volume sangat tinggi dan retensi yang ketat, partisi harian atau bulanan biasanya lebih mudah dioperasikan.

3) Menganggap “unik global” tetap bekerja

Tabel terpartisi punya batasan: beberapa indeks unik harus menyertakan partition key, kalau tidak PostgreSQL tidak bisa menegakkannya di seluruh partisi. Ini sering mengejutkan tim yang mengira event_id unik selamanya. Jika Anda butuh identifier unik, gunakan UUID dan buat unik bersama key waktu, atau tegakkan keunikan di lapisan aplikasi.

4) Membiarkan UI admin menjalankan pencarian tanpa batas

Panel admin sering dikirim dengan kotak pencarian yang ramah yang berjalan tanpa filter. Pada tabel log terpartisi, itu bisa berarti memindai setiap partisi.

Pencarian full-text pada payload pesan sangat berisiko. Tambahkan pengaman: wajibkan rentang waktu, batasi rentang default, dan buat “seluruh waktu” menjadi pilihan yang disengaja.

5) Tidak ada rencana retensi (dan tidak ada rencana untuk partisi)

Partitioning tidak otomatis menyelesaikan retensi. Tanpa kebijakan, Anda akan punya tumpukan partisi lama, penyimpanan berantakan, dan maintenance yang semakin lambat.

Aturan operasi sederhana biasanya mencegah ini: definisikan berapa lama event mentah disimpan, otomatisasi pembuatan partisi masa depan dan penghapusan yang lama, terapkan indeks konsisten, pantau jumlah partisi dan tanggal batasnya, dan uji filter admin paling lambat terhadap volume data realistis.

Daftar periksa cepat sebelum Anda berkomitmen

Partitioning bisa menjadi kemenangan besar untuk log audit, tetapi menambah kerja rutin. Sebelum mengubah skema, cek kembali bagaimana orang benar-benar menggunakan tabel.

Jika masalah utama Anda adalah halaman admin time out ketika seseorang membuka “24 jam terakhir” atau “minggu ini”, Anda hampir memenuhi syarat. Jika kebanyakan kueri adalah “user ID di seluruh sejarah”, partitioning mungkin kurang membantu kecuali Anda juga mengubah cara UI mengarahkan pencarian.

Daftar periksa singkat yang menjaga tim jujur:

• Rentang waktu adalah filter default. Sebagian besar kueri admin menyertakan jendela jelas (dari/ke). Jika pencarian tanpa batas umum, pruning partisi kurang membantu.
• Retensi ditegakkan dengan menghapus partisi, bukan menghapus baris. Anda nyaman menghapus partisi lama dan punya aturan jelas berapa lama data harus disimpan.
• Jumlah partisi tetap masuk akal. Perkirakan partisi per tahun (harian, mingguan, bulanan). Terlalu banyak partisi kecil menambah overhead. Terlalu sedikit partisi besar mengurangi manfaat.
• Indeks sesuai dengan filter yang sebenarnya digunakan. Selain partition key, Anda masih butuh indeks per-partisi yang tepat untuk filter umum dan urutan.
• Partisi dibuat otomatis dan dimonitor. Job rutin membuat partisi masa depan, dan Anda tahu saat job itu gagal.

Tes praktis: lihat tiga filter yang paling sering digunakan tim support atau ops Anda. Jika dua dari mereka biasanya dipenuhi oleh “rentang waktu + satu kondisi lagi”, partitioning PostgreSQL untuk tabel event sering layak dipertimbangkan.

Contoh realistis dan langkah praktis selanjutnya

Tim support membuka dua layar sepanjang hari: “Login events” (berhasil dan gagal) dan “Security audits” (reset password, perubahan peran, update API key). Saat pelanggan melaporkan aktivitas mencurigakan, tim memfilter berdasarkan user, memeriksa beberapa jam terakhir, dan mengekspor laporan singkat.

Sebelum partisi, semuanya berada di satu tabel events besar. Tabel tumbuh cepat, dan bahkan pencarian sederhana mulai melambat karena database harus melalui banyak baris lama. Retensi juga menyulitkan: pekerjaan nightly menghapus baris lama, tetapi DELETE besar memakan waktu lama, menciptakan bloat, dan bersaing dengan traffic normal.

Setelah mempartisi berdasarkan bulan (menggunakan timestamp event), alur kerja membaik. Panel admin mewajibkan filter waktu, sehingga sebagian besar kueri hanya menyentuh satu atau dua partisi. Halaman memuat lebih cepat karena PostgreSQL bisa mengabaikan partisi di luar rentang yang dipilih. Retensi menjadi rutin: alih-alih menghapus jutaan baris, Anda drop partisi lama.

Satu hal tetap sulit: pencarian full-text “seluruh waktu.” Jika seseorang mencari alamat IP atau frase samar tanpa batas tanggal, partitioning tidak membuat itu murah. Perbaikannya biasanya di perilaku produk: default pencarian ke jendela waktu dan jadikan “24 jam / 7 hari / 30 hari” jalur yang jelas.

Langkah praktis yang cenderung berhasil:

• Peta filter panel admin Anda dulu. Tuliskan field mana yang digunakan orang dan mana yang harus diwajibkan.
• Pilih partisi yang sesuai dengan cara Anda menelusuri. Partisi bulanan sering kali awal yang baik; beralih ke mingguan hanya ketika volume memaksanya.
• Jadikan rentang waktu filter kelas utama. Jika UI memperbolehkan “tanpa tanggal”, harapkan halaman lambat.
• Selaraskan indeks dengan filter nyata. Ketika waktu selalu ada, strategi indeks yang menempatkan waktu di depan biasanya baseline yang tepat.
• Tetapkan aturan retensi yang sesuai batas partisi (misalnya simpan 13 bulan dan hapus apa pun yang lebih lama).

Jika Anda membangun panel admin internal dengan AppMaster (appmaster.io), layak memodelkan asumsi-asumsi ini sejak awal: perlakukan filter berbatas waktu sebagai bagian dari model data, bukan sekadar pilihan UI. Keputusan kecil itu melindungi kinerja kueri saat volume log tumbuh.