Kebijakan retensi 30 hari sering terlihat sederhana: simpan data audit atau log selama sebulan, lalu hapus. Di level database, keputusan ini berdampak langsung pada ukuran tabel, biaya index, performa query, strategi pagination, dan cara melakukan pembersihan data tanpa mengganggu traffic produksi.

Jika Anda menyimpan audit trail, event log, atau activity history, pertanyaan utamanya bukan hanya berapa lama data disimpan, tetapi juga bagaimana data itu di-query dan dihapus. Artikel ini fokus pada dampak retensi 30 hari terhadap desain tabel audit SQL, khususnya index pada created_at, tenant_id, dan status, serta trade-off antara TTL delete, scheduled purge, dan partitioning.

Sebagian layanan modern menerapkan kebijakan retensi yang tegas untuk data tertentu, misalnya 30 hari. Untuk sistem internal Anda, implikasi teknisnya serupa: semakin pendek retensi, semakin penting mekanisme pembuangan data yang konsisten, dapat diaudit, dan tidak merusak performa query aktif.

Mengapa retensi 30 hari memengaruhi performa database

Tabel audit cenderung memiliki pola write-heavy: banyak insert, sedikit update, dan query yang sering memfilter berdasarkan waktu. Dalam beberapa minggu saja, tabel ini dapat menjadi salah satu tabel terbesar di sistem, meskipun setiap baris relatif kecil.

Retensi 30 hari memengaruhi performa dalam beberapa area berikut:

  • Pertumbuhan tabel dan index: setiap row baru juga memperbesar index terkait.
  • Query audit makin lambat: terutama jika filter waktu, tenant, dan status tidak didukung index yang tepat.
  • Delete massal mahal: menghapus jutaan baris satu per satu dapat memicu vacuum, bloat, lock, dan I/O tinggi.
  • Pagination memburuk: offset besar pada data audit akan makin mahal seiring pertumbuhan tabel.

Masalah umum terjadi saat tim hanya memikirkan retensi sebagai job harian DELETE FROM audit_logs WHERE created_at < now() - interval '30 days', tanpa memikirkan dampaknya pada planner, index maintenance, dan fragmentasi data.

Desain tabel audit yang realistis

Struktur tabel audit biasanya memuat identitas tenant, waktu kejadian, tipe event, status, dan payload. Contoh sederhana di PostgreSQL:

CREATE TABLE audit_logs (
  id BIGSERIAL PRIMARY KEY,
  tenant_id BIGINT NOT NULL,
  actor_id BIGINT,
  action VARCHAR(100) NOT NULL,
  status VARCHAR(30) NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT NOW(),
  metadata JSONB
);

Dua karakteristik penting tabel audit:

  • created_at hampir selalu dipakai untuk retensi dan filter rentang waktu.
  • tenant_id sering menjadi filter utama pada sistem multi-tenant.

Karena itu, desain index sebaiknya mengikuti pola query nyata, bukan sekadar menambahkan index ke setiap kolom yang tampak penting.

Strategi index untuk created_at, tenant_id, dan status

1. Index tunggal pada created_at

Ini berguna untuk dua hal: query berdasarkan waktu dan proses purge data lama.

CREATE INDEX idx_audit_logs_created_at ON audit_logs (created_at);

Index ini membantu query seperti:

SELECT id, tenant_id, action, status, created_at
FROM audit_logs
WHERE created_at >= NOW() - INTERVAL '7 days'
ORDER BY created_at DESC
LIMIT 100;

Namun, jika hampir semua query selalu memfilter tenant_id, index tunggal pada created_at saja sering belum cukup optimal.

2. Composite index untuk tenant_id dan created_at

Pada aplikasi multi-tenant, ini biasanya index paling bernilai.

CREATE INDEX idx_audit_logs_tenant_created_at
ON audit_logs (tenant_id, created_at DESC);

Mengapa urutan kolom penting?

  • tenant_id diletakkan lebih dulu jika query umumnya mencari data per tenant.
  • created_at di posisi kedua membantu filter waktu sekaligus pengurutan terbaru ke terlama.

Query yang cocok:

SELECT id, action, status, created_at
FROM audit_logs
WHERE tenant_id = 42
  AND created_at >= NOW() - INTERVAL '30 days'
ORDER BY created_at DESC
LIMIT 100;

Index ini biasanya lebih bermanfaat daripada membuat banyak index tunggal terpisah pada tenant_id dan created_at, karena planner dapat memanfaatkan urutan kolom untuk filtering dan sorting sekaligus.

3. Kapan menambahkan status ke index

Jangan otomatis membuat index terpisah pada status. Kolom status sering memiliki cardinality rendah, misalnya hanya success, failed, dan pending. Index pada kolom seperti ini kadang tidak selektif dan justru menambah biaya write.

Pertimbangkan index gabungan hanya jika query nyata memang sering seperti ini:

SELECT id, action, created_at
FROM audit_logs
WHERE tenant_id = 42
  AND status = 'failed'
  AND created_at >= NOW() - INTERVAL '30 days'
ORDER BY created_at DESC
LIMIT 100;

Maka index berikut bisa masuk akal:

CREATE INDEX idx_audit_logs_tenant_status_created_at
ON audit_logs (tenant_id, status, created_at DESC);

Tetapi ada trade-off:

  • Insert menjadi lebih mahal karena lebih banyak index yang harus diperbarui.
  • Ukuran storage naik.
  • Planner bisa bingung jika terlalu banyak index mirip dengan kombinasi kolom yang tumpang tindih.

4. Hindari index berlebih

Kesalahan umum pada tabel audit:

  • Mengindex semua kolom filter secara terpisah tanpa melihat pola query.
  • Membuat index (created_at), (tenant_id), (status), (tenant_id, created_at), dan (tenant_id, status, created_at) sekaligus tanpa evaluasi.
  • Melupakan bahwa setiap index memperlambat insert dan delete.

Untuk banyak sistem audit, kombinasi awal yang wajar sering cukup:

  • PRIMARY KEY (id)
  • INDEX (created_at) untuk purge global atau query lintas tenant
  • INDEX (tenant_id, created_at DESC) untuk query utama per tenant

Tambahkan index lain hanya jika ada bukti dari query lambat atau hasil EXPLAIN.

TTL delete vs scheduled purge vs partitioning

Tiga pendekatan ini sering dibahas saat menerapkan retensi 30 hari. Tidak ada satu jawaban untuk semua sistem.

1. TTL delete

Dalam konteks umum SQL, TTL berarti ada mekanisme otomatis yang menghapus row setelah umur tertentu. Beberapa database atau ekstensi mendukung TTL secara native, tetapi pada relational database tradisional seperti PostgreSQL, Anda biasanya perlu mensimulasikannya dengan job terjadwal.

Kelebihan:

  • Mudah dipahami secara operasional.
  • Cocok untuk volume kecil hingga menengah.

Kekurangan:

  • Jika implementasinya berupa delete besar, bisa memicu bloat dan vacuum berat.
  • Kurang terkontrol jika waktu eksekusi berbarengan dengan beban puncak.

2. Scheduled purge

Ini pendekatan paling umum: cron, scheduler, atau worker menjalankan delete berkala.

Contoh buruk:

DELETE FROM audit_logs
WHERE created_at < NOW() - INTERVAL '30 days';

Masalahnya, satu transaksi besar bisa:

  • memakan waktu lama,
  • menghasilkan banyak dead tuples,
  • membebani replika,
  • mengganggu query lain.

Pendekatan yang lebih aman adalah batch delete:

DELETE FROM audit_logs
WHERE id IN (
  SELECT id
  FROM audit_logs
  WHERE created_at < NOW() - INTERVAL '30 days'
  ORDER BY created_at
  LIMIT 5000
);

Jalankan berulang sampai tidak ada row tersisa. Ukuran batch perlu disesuaikan dengan kapasitas sistem Anda.

Kelebihan scheduled purge:

  • Mudah diterapkan.
  • Dapat dikontrol jadwal, batch size, dan retry.
  • Tidak membutuhkan perubahan arsitektur tabel yang besar.

Kekurangan:

  • Tetap menghasilkan overhead delete dan vacuum.
  • Semakin besar volume data, semakin terasa biaya maintenance-nya.

3. Partitioning

Untuk tabel audit yang terus tumbuh, partitioning berdasarkan waktu sering menjadi pilihan paling sehat. Misalnya partisi harian atau mingguan berdasarkan created_at.

Keuntungan utama partitioning adalah penghapusan data lama dapat dilakukan dengan drop partition alih-alih delete row satu per satu. Ini jauh lebih murah secara operasional.

Contoh konsep di PostgreSQL:

CREATE TABLE audit_logs (
  id BIGSERIAL,
  tenant_id BIGINT NOT NULL,
  actor_id BIGINT,
  action VARCHAR(100) NOT NULL,
  status VARCHAR(30) NOT NULL,
  created_at TIMESTAMP NOT NULL,
  metadata JSONB
) PARTITION BY RANGE (created_at);

Lalu buat partisi per hari atau per minggu. Saat data lebih tua dari 30 hari, Anda bisa melepas atau menghapus partisi lama.

Kelebihan:

  • Purge data lama sangat cepat.
  • Membantu query time-range jika planner melakukan partition pruning dengan baik.
  • Mengurangi kebutuhan delete massal.

Kekurangan:

  • Operasional lebih kompleks.
  • Manajemen partisi harus disiplin: pembuatan partisi baru, monitoring, dan fallback saat partisi belum tersedia.
  • Perlu desain index per partisi atau strategi index yang konsisten.

Kapan memilih yang mana?

  • TTL/scheduled purge sederhana: cocok jika volume audit masih moderat dan beban delete tidak menimbulkan masalah nyata.
  • Scheduled purge bertahap: cocok sebagai langkah transisi saat volume mulai besar tetapi belum ingin merombak tabel.
  • Partitioning: tepat jika audit log adalah tabel besar, retensi ketat, dan delete massal sudah menjadi bottleneck operasional.

Dampak offset pagination pada data audit besar

Audit log hampir selalu ditampilkan sebagai daftar kronologis: terbaru dulu, lalu pengguna menelusuri halaman lama. Di sinilah offset pagination sering menjadi sumber query lambat.

Masalah offset pagination

Contoh query umum:

SELECT id, tenant_id, action, status, created_at
FROM audit_logs
WHERE tenant_id = 42
ORDER BY created_at DESC
LIMIT 100 OFFSET 10000;

Meskipun hanya mengambil 100 row, database tetap harus melewati banyak row sebelum mencapai offset yang diminta. Semakin besar offset, semakin mahal query tersebut.

Pada tabel audit besar, masalah ini terlihat sebagai:

  • latensi halaman lama jauh lebih tinggi,
  • pemakaian CPU dan I/O meningkat,
  • hasil tidak stabil jika data baru terus masuk selama user mem-paginate.

Kapan keyset pagination lebih tepat

Jika urutan data jelas dan stabil, misalnya berdasarkan created_at DESC, id DESC, gunakan keyset pagination.

SELECT id, tenant_id, action, status, created_at
FROM audit_logs
WHERE tenant_id = 42
  AND (created_at, id) < ('2026-06-20 10:00:00', 987654)
ORDER BY created_at DESC, id DESC
LIMIT 100;

Mengapa lebih baik?

  • Database tidak perlu menghitung offset besar.
  • Latensi lebih konsisten antar halaman.
  • Lebih stabil untuk data yang terus bertambah.

Agar efisien, Anda butuh index yang sejalan:

CREATE INDEX idx_audit_logs_tenant_created_id
ON audit_logs (tenant_id, created_at DESC, id DESC);

Trade-off keyset pagination:

  • Tidak ideal jika UI harus lompat langsung ke halaman ke-200.
  • Implementasi API biasanya memakai cursor, bukan nomor halaman sederhana.

Untuk halaman audit dan activity log, ini biasanya trade-off yang layak.

Checklist diagnosis bottleneck pada query audit SQL

Jika query audit mulai lambat, gunakan checklist berikut sebelum menambah index baru secara membabi buta.

Periksa pola query nyata

  • Apakah query selalu memfilter tenant_id?
  • Apakah ada filter rentang waktu yang konsisten?
  • Apakah status benar-benar selektif atau hanya sedikit variasi nilai?
  • Apakah query memakai ORDER BY created_at DESC?

Periksa rencana eksekusi

  • Gunakan EXPLAIN atau EXPLAIN ANALYZE di lingkungan aman.
  • Lihat apakah planner memakai sequential scan atau index scan.
  • Periksa apakah sorting terjadi eksplisit karena index tidak cocok dengan urutan query.

Periksa ukuran dan kesehatan tabel

  • Apakah tabel audit jauh lebih besar dari perkiraan?
  • Apakah ada bloat setelah delete besar?
  • Apakah vacuum atau autovacuum tertinggal?

Periksa pola delete

  • Apakah purge dijalankan dalam satu transaksi besar?
  • Apakah purge berjalan di jam sibuk?
  • Apakah replika ikut tertinggal saat job purge aktif?

Periksa pagination

  • Apakah endpoint memakai offset besar?
  • Apakah halaman lama jauh lebih lambat daripada halaman pertama?
  • Apakah keyset pagination lebih cocok untuk use case ini?

Kesalahan umum saat menerapkan retensi 30 hari

  • Mengira retensi pendek otomatis membuat tabel kecil. Jika purge tidak konsisten atau gagal beberapa hari, ukuran tabel tetap membengkak.
  • Menambah banyak index untuk semua kemungkinan query. Ini memperlambat insert dan delete.
  • Menghapus jutaan row sekaligus. Efek sampingnya bisa lebih mahal daripada manfaat retensi itu sendiri.
  • Tidak menguji query audit dengan data realistis. Query cepat di staging kecil belum tentu cepat di produksi.
  • Mengandalkan offset pagination untuk histori panjang. Ini sering baru terasa saat data sudah besar.
  • Tidak menyelaraskan index dengan urutan ORDER BY. Akibatnya database tetap harus sort di memori atau disk.

Langkah migrasi aman di produksi

Perubahan pada tabel audit biasanya menyentuh area sensitif: ukuran besar, trafik tinggi, dan proses latar belakang. Lakukan migrasi secara bertahap.

1. Ukur beban saat ini

  • Catat query audit paling sering dan paling lambat.
  • Identifikasi ukuran tabel, pertumbuhan harian, dan volume row yang dipurge per hari.
  • Pastikan ada baseline sebelum mengubah schema.

2. Tambahkan index baru secara hati-hati

Di PostgreSQL, untuk tabel besar dan aktif, pertimbangkan pembuatan index yang meminimalkan blocking write, sesuai kemampuan database Anda dan prosedur operasional yang berlaku.

Contoh:

CREATE INDEX CONCURRENTLY idx_audit_logs_tenant_created_at
ON audit_logs (tenant_id, created_at DESC);

Setelah itu, validasi dengan EXPLAIN bahwa query utama benar-benar memakai index tersebut.

3. Ubah endpoint dari offset ke keyset secara kompatibel

Jangan memutus klien lama secara mendadak. Anda bisa:

  • menambahkan parameter cursor baru,
  • mempertahankan offset untuk sementara pada rentang kecil,
  • mengarahkan UI audit besar ke mode cursor-based pagination.

4. Ganti delete besar menjadi purge bertahap

Jika saat ini job harian melakukan satu delete masif, pecah menjadi batch kecil dengan jeda antar batch jika perlu. Pantau:

  • durasi transaksi,
  • lag replika,
  • dead tuples atau bloat,
  • efek ke query pengguna.

5. Evaluasi partitioning sebagai fase berikutnya

Jangan langsung mempartisi tanpa alasan. Biasanya partitioning layak jika:

  • volume audit sangat besar,
  • purge harian sudah menjadi beban tetap,
  • retensi berbasis waktu adalah aturan utama query dan penghapusan.

Migrasi ke partitioning sebaiknya dilakukan lewat tabel baru, backfill bertahap, dual-write atau cutover terencana, lalu validasi data sebelum trafik penuh diarahkan ke tabel baru.

Rekomendasi praktis

Untuk banyak aplikasi multi-tenant dengan kebijakan retensi 30 hari, pendekatan pragmatis yang sering efektif adalah:

  1. Mulai dengan schema audit sederhana.
  2. Tambahkan index (tenant_id, created_at DESC) sebagai index utama query audit.
  3. Pertahankan index (created_at) jika purge global berbasis waktu sering dilakukan.
  4. Gunakan scheduled purge bertahap, bukan delete masif.
  5. Hindari index status kecuali ada query yang benar-benar membutuhkan dan terbukti selektif.
  6. Pindahkan endpoint histori besar dari offset pagination ke keyset pagination.
  7. Naikkan ke partitioning saat biaya purge dan ukuran tabel sudah menjadi masalah operasional nyata.

Intinya, retensi bukan hanya aturan penyimpanan data. Pada tabel audit, retensi adalah keputusan desain performa. Jika Anda menentukan 30 hari, pastikan query, index, dan mekanisme purge dirancang sejak awal untuk horizon waktu tersebut.