Kolom terhitung (generated columns) PostgreSQL untuk mempercepat filter admin

Mengapa layar admin cepat menjadi lambat dan berantakan

Layar admin biasanya dimulai sederhana: sebuah tabel, beberapa filter, mungkin satu pengurutan “terbaru dulu.” Lalu pekerjaan nyata dimulai. Tim support ingin pencarian yang menemukan pelanggan berdasarkan nama, email, dan telepon. Tim sales ingin pengurutan berdasarkan “aktivitas terakhir.” Tim finance ingin “saldo yang jatuh tempo.” Setiap permintaan menambah kondisi, join, dan perhitungan ekstra.

Kebanyakan daftar admin melambat karena alasan yang sama: setiap klik mengubah query. Penyaringan dan pengurutan bisa membuat database melakukan pemindaian banyak baris, terutama ketika query harus menghitung nilai untuk setiap baris sebelum menentukan mana yang cocok.

Titik balik yang umum terjadi adalah ketika WHERE dan ORDER BY dipenuhi ekspresi. Alih-alih memfilter pada kolom biasa, Anda memfilter pada lower(email), date_trunc('day', last_seen_at), atau pernyataan CASE yang memetakan banyak status ke satu “bucket”. Ekspresi-ekspresi itu bukan hanya lebih lambat. Mereka membuat SQL lebih susah dibaca, susah diindeks, dan lebih mudah salah.

SQL admin yang berantakan biasanya berasal dari beberapa pola berulang:

• Satu input “search” yang memeriksa beberapa field dengan aturan berbeda
• Pengurutan berdasarkan nilai turunan (nama lengkap, skor prioritas, “event bermakna terakhir”)
• Aturan bisnis yang disalin antar layar (aktif vs tidak aktif, bayar vs jatuh tempo)
• Penyesuaian kecil “pembantu” (trim, lower, coalesce) yang tersebar di mana-mana
• Nilai terhitung yang sama dipakai di list, filter, dan pengurutan

Tim sering mencoba menyembunyikan ini di lapisan aplikasi: pembangun query dinamis, conditional join, atau pra-hitung nilai di kode. Itu bisa bekerja, tapi memecah logika antara UI dan database, yang membuat debug query lambat menjadi menyakitkan.

Tujuannya sederhana: query cepat yang tetap mudah dibaca. Ketika sebuah nilai terhitung muncul berulang kali di layar admin, generated columns PostgreSQL bisa menjaga aturan itu di satu tempat sambil membiarkan database mengoptimalkannya.

Generated columns dalam bahasa sederhana

Generated column adalah kolom tabel biasa yang nilainya dihitung dari kolom lain. Anda tidak menulis nilainya sendiri. PostgreSQL mengisinya menggunakan ekspresi yang Anda definisikan.

Di PostgreSQL, generated columns disimpan. PostgreSQL menghitung nilai saat sebuah baris di-insert atau di-update, lalu menyimpannya di disk seperti kolom lainnya. Itu biasanya yang Anda inginkan untuk layar admin: pembacaan cepat, dan kemampuan untuk mengindeks nilai yang dihitung.

Ini berbeda dari melakukan perhitungan yang sama di tiap query. Jika Anda terus menulis WHERE lower(email) = lower($1) atau mengurutkan dengan last_name || ', ' || first_name, Anda membayar biaya itu berulang-ulang dan SQL Anda menjadi berantakan. Generated column memindahkan perhitungan berulang itu ke definisi tabel. Query Anda menjadi lebih sederhana, dan hasilnya konsisten di mana-mana.

Saat data sumber berubah, PostgreSQL akan memperbarui nilai generated itu secara otomatis untuk baris tersebut. Aplikasi Anda tidak perlu mengingat untuk menyinkronkannya.

Model mental yang berguna:

• Definisikan formula sekali.
• PostgreSQL menghitungnya saat penulisan.
• Query membacanya seperti kolom biasa.
• Karena disimpan, Anda bisa mengindeksnya.

Jika nanti Anda mengubah formula, Anda perlu perubahan skema. Rencanakan seperti migrasi apa pun, karena baris yang ada akan diperbarui agar cocok dengan ekspresi baru.

Kegunaan baik untuk field terhitung di filter dan pengurutan

Generated columns bersinar ketika nilai selalu diturunkan dari kolom lain dan Anda sering memfilter atau mengurutkannya. Mereka kurang membantu untuk laporan sekali pakai.

Field pencarian yang benar-benar dipakai orang

Pencarian admin jarang “murni”. Orang mengharapkan kotak pencarian menangani teks berantakan, casing yang tidak konsisten, dan spasi ekstra. Jika Anda menyimpan "search key" ter-generate yang sudah ternormalisasi, klausa WHERE tetap mudah dibaca dan berperilaku sama di semua layar.

Kandidat yang baik termasuk nama lengkap gabungan, teks yang di-lowercase dan di-trim untuk pencarian case-insensitive, versi yang dibersihkan yang mereduksi whitespace, atau label status yang diturunkan dari beberapa field.

Contoh: alih-alih mengulang lower(trim(first_name || ' ' || last_name)) di setiap query, generate full_name_key sekali dan filter padanya.

Sort key yang cocok dengan cara manusia mengurutkan

Pengurutan adalah tempat di mana field terhitung sering cepat membayar kembali, karena pengurutan bisa memaksa PostgreSQL mengevaluasi ekspresi untuk banyak baris.

Sort key yang umum meliputi ranking numerik (paket plan dipetakan menjadi 1, 2, 3), satu timestamp “aktivitas terbaru” (seperti max dari dua timestamp), atau kode yang dipadding agar terurut dengan benar sebagai teks.

Saat sort key adalah kolom biasa yang diindeks, ORDER BY menjadi jauh lebih murah.

Flag turunan untuk filter cepat

Pengguna admin suka checkbox seperti “Jatuh tempo” atau “Nilai tinggi.” Ini bekerja baik sebagai generated columns jika logikanya stabil dan hanya berdasarkan data baris.

Misalnya, jika daftar pelanggan membutuhkan “Ada pesan belum dibaca” dan “Jatuh tempo”, boolean ter-generate has_unread (dari unread_count \u003e 0) dan is_overdue (dari due_date \u003c now() dan paid_at is null) membuat filter UI menjadi kondisi sederhana.

Memilih antara generated columns, indeks, dan opsi lain

Layar admin membutuhkan tiga hal: filter cepat, pengurutan cepat, dan SQL yang masih bisa dibaca beberapa bulan kemudian. Keputusan sebenarnya adalah di mana perhitungan sebaiknya diletakkan: di tabel, di indeks, di view, atau di kode aplikasi.

Generated columns cocok ketika Anda ingin nilai berperilaku seperti kolom nyata: mudah direferensi, terlihat di SELECT, dan sulit dilupakan saat menambah filter. Mereka juga berpadu alami dengan indeks biasa.

Expression indexes bisa lebih cepat ditambahkan karena Anda tidak mengubah definisi tabel. Jika Anda lebih peduli kecepatan dan tidak keberatan SQL yang kurang rapi, expression index sering cukup. Kekurangannya adalah keterbacaan, dan Anda mengandalkan planner mencocokkan ekspresi Anda secara persis.

Views membantu ketika Anda menginginkan “bentuk” data bersama, terutama jika daftar admin Anda meng-join banyak tabel. Tapi view kompleks bisa menyembunyikan pekerjaan mahal dan menambah tempat debugging.

Triggers bisa menjaga kolom biasa agar tetap sinkron, tapi mereka menambah bagian yang bergerak. Mereka bisa membuat bulk update lebih lambat dan mudah terabaikan saat troubleshooting.

Kadang pilihan terbaik adalah kolom biasa yang diisi oleh aplikasi. Jika pengguna bisa mengeditnya, atau formula berubah sering karena keputusan bisnis (bukan hanya data baris), membuatnya eksplisit lebih jelas.

Cara cepat memilih:

• Ingin query yang mudah dibaca dan formula stabil yang hanya berdasarkan data baris? Gunakan generated column.
• Ingin kecepatan untuk satu filter spesifik dan tidak keberatan SQL berantakan? Gunakan expression index.
• Butuh bentuk data join seperti laporan yang dipakai di banyak tempat? Pertimbangkan view.
• Butuh logika lintas-tabel atau efek samping? Utamakan logika di aplikasi dulu, trigger terakhir.

Langkah demi langkah: tambah generated column dan pakai di query

Mulai dengan satu query daftar admin yang lambat yang terasa di UI. Catat filter dan sort yang paling sering dipakai layar. Perbaiki satu query itu dulu.

Pilih field terhitung yang menghapus pekerjaan berulang, dan beri nama yang jelas dalam snake_case sehingga orang lain bisa menebak isinya tanpa membaca ulang ekspresi.

1) Tambah generated column (STORED)

ALTER TABLE customers
ADD COLUMN full_name_key text
GENERATED ALWAYS AS (
  lower(concat_ws(' ', last_name, first_name))
) STORED;

Validasi pada baris nyata sebelum menambah indeks:

SELECT id, first_name, last_name, full_name_key
FROM customers
ORDER BY id DESC
LIMIT 5;

Jika output salah, perbaiki ekspresinya sekarang. STORED berarti PostgreSQL akan memperbaruinya untuk setiap insert dan update.

2) Tambah indeks yang cocok dengan layar admin Anda

Jika layar admin memfilter berdasarkan status dan mengurutkan berdasarkan nama, indeks pola itu:

CREATE INDEX customers_status_full_name_key_idx
ON customers (status, full_name_key);

3) Perbarui query admin untuk memakai kolom baru

Sebelumnya Anda mungkin punya ORDER BY yang berantakan. Setelahnya, jadi jelas:

SELECT id, status, first_name, last_name
FROM customers
WHERE status = 'active'
ORDER BY full_name_key ASC
LIMIT 50 OFFSET 0;

Gunakan generated columns untuk bagian yang sering difilter dan diurutkan setiap hari, bukan untuk layar yang jarang dibuka.

Pola pengindeksan yang cocok dengan layar admin nyata

Layar admin mengulang beberapa perilaku: memfilter dengan beberapa field, mengurutkan oleh satu kolom, dan paginasi. Pengaturan terbaik jarang “indeks semua kolom.” Yang terbaik adalah mengindeks bentuk query yang paling umum.

Aturan praktis: taruh kolom filter paling umum di depan, dan kolom sort paling umum di akhir. Jika Anda multi-tenant, workspace_id (atau sejenis) sering jadi yang pertama: (workspace_id, status, created_at).

Pencarian teks punya masalah sendiri. Banyak kotak pencarian berakhir menjadi ILIKE '%term%', yang sulit dipercepat dengan btree biasa. Pola yang membantu adalah mencari pada kolom pembantu yang ternormalisasi alih-alih teks mentah (lowercase, trim, mungkin digabung). Jika UI Anda bisa menggunakan prefix search (term%), indeks btree pada kolom ternormalisasi itu bisa membantu. Jika harus contains search (%term%), pertimbangkan mengetatkan perilaku UI untuk tabel besar (misalnya “email starts with”), atau batasi pencarian ke subset yang lebih kecil.

Juga cek selektivitas sebelum menambah indeks. Jika 95% baris berbagi nilai yang sama (misalnya status = 'active'), mengindeks kolom itu sendiri tidak banyak membantu. Gabungkan dengan kolom yang lebih selektif, atau gunakan partial index untuk kasus minoritas.

Contoh realistis: daftar pelanggan yang tetap cepat

Bayangkan halaman admin pelanggan tipikal: kotak pencarian, beberapa filter (nonaktif, rentang saldo), dan kolom “Last seen” yang bisa diurutkan. Seiring waktu SQL menjadi susah dibaca: LOWER(), TRIM(), COALESCE(), matematika tanggal, dan blok CASE yang diulang di banyak layar.

Salah satu cara menjaga cepat dan mudah dibaca adalah mendorong ekspresi berulang itu ke generated columns.

Tabel dan generated columns

Asumsikan tabel customers dengan name, email, last_seen, dan balance. Tambah tiga field terhitung:

• search_key: blob teks ternormalisasi untuk pencarian sederhana
• is_inactive: boolean yang bisa difilter tanpa mengulang logika tanggal
• balance_bucket: label untuk segmentasi cepat

ALTER TABLE customers
  ADD COLUMN search_key text
    GENERATED ALWAYS AS (
      lower(trim(coalesce(name, ''))) || ' ' || lower(trim(coalesce(email, '')))
    ) STORED,
  ADD COLUMN is_inactive boolean
    GENERATED ALWAYS AS (
      last_seen IS NULL OR last_seen \u003c (now() - interval '90 days')
    ) STORED,
  ADD COLUMN balance_bucket text
    GENERATED ALWAYS AS (
      CASE
        WHEN balance \u003c 0 THEN 'negative'
        WHEN balance \u003c 100 THEN '0-99'
        WHEN balance \u003c 500 THEN '100-499'
        ELSE '500+'
      END
    ) STORED;

Sekarang query admin terbaca seperti UI.

Filter + sorting yang mudah dibaca

“Pelanggan tidak aktif, aktivitas terbaru dulu” menjadi:

SELECT id, name, email, last_seen, balance
FROM customers
WHERE is_inactive = true
ORDER BY last_seen DESC NULLS LAST
LIMIT 50;

Dan pencarian dasar menjadi:

SELECT id, name, email, last_seen, balance
FROM customers
WHERE search_key LIKE '%' || lower(trim($1)) || '%'
ORDER BY last_seen DESC NULLS LAST
LIMIT 50;

Keuntungan nyata adalah konsistensi. Field yang sama memberi tenaga ke banyak layar tanpa menulis ulang logika:

• Kotak pencarian daftar pelanggan menggunakan search_key
• Tab “Inactive customers” menggunakan is_inactive
• Chip filter saldo menggunakan balance_bucket

Kesalahan umum dan jebakan

Generated columns bisa terlihat sebagai kemenangan sederhana: tempatkan perhitungan di tabel dan bersihkan query Anda. Mereka hanya membantu ketika cocok dengan cara layar memfilter dan mengurutkan, dan ketika Anda menambahkan indeks yang tepat.

Kesalahan paling umum:

• Menganggap ini mempercepat tanpa mengindeks. Nilai terhitung tetap butuh indeks untuk filter atau sort cepat pada skala besar.
• Memasukkan terlalu banyak logika ke satu field. Jika generated column menjadi semacam program mini, orang berhenti mempercayainya. Jaga singkat dan beri nama jelas.
• Menggunakan fungsi non-immutable. PostgreSQL mengharuskan ekspresi untuk stored generated column bersifat immutable. Hal seperti now() dan random() melanggar dan sering tidak diizinkan.
• Mengabaikan biaya tulis. Insert dan update harus mempertahankan nilai terhitung. Pembacaan lebih cepat tidak sebanding jika import dan integrasi menjadi terlalu lambat.
• Membuat duplikat yang hampir sama. Standarisasikan satu atau dua pola (mis. satu normalized key) daripada menumpuk lima kolom serupa.

Jika daftar admin Anda menggunakan contains search (seperti ILIKE '%ann%'), generated column saja tidak akan menyelamatkannya. Anda mungkin butuh pendekatan pencarian berbeda. Tapi untuk query kerja sehari-hari "filter dan sort", generated columns ditambah indeks yang tepat biasanya membuat performa lebih dapat diprediksi.

Daftar periksa cepat sebelum meluncurkan

Sebelum mendorong perubahan ke daftar admin, pastikan nilai terhitung, query, dan indeks selaras.

• Formulanya stabil dan mudah dijelaskan dalam satu kalimat.
• Query Anda benar-benar menggunakan generated column di WHERE dan/atau ORDER BY.
• Indeks cocok dengan penggunaan nyata, bukan tes sekali saja.
• Anda membandingkan hasil dengan logika lama pada kasus tepi (NULL, string kosong, spasi ganjil, campuran huruf).
• Anda menguji performa tulis jika tabel sibuk (imports, background updates, integrasi).

Langkah berikutnya: terapkan ini ke layar admin Anda

Pilih titik awal kecil yang berdampak besar: 2-3 layar admin yang dibuka tiap hari (orders, customers, tickets). Catat yang terasa lambat (filter rentang tanggal, pengurutan “last activity”, pencarian oleh nama gabungan, filter label status). Lalu standarkan sekumpulan field terhitung singkat yang bisa dipakai ulang di banyak layar.

Rencana rollout yang mudah diukur dan mudah dibatalkan:

• Tambahkan generated column dengan nama yang jelas.
• Jalankan lama dan baru berdampingan sebentar jika Anda mengganti logika yang ada.
• Tambah indeks yang cocok dengan filter utama atau sort.
• Ganti query layar untuk memakai kolom baru.
• Ukur sebelum dan sesudah (waktu query dan baris yang dipindai), lalu hapus solusi lama.

Jika Anda membangun tools admin internal di AppMaster (appmaster.io), field terhitung ini cocok masuk ke model data bersama: database memegang aturannya, dan filter UI bisa menunjuk ke nama field yang jelas alih-alih mengulang ekspresi di banyak layar.