Kolom Generated PostgreSQL untuk Data Terhitung

Setiap backend menumpuk pajak kecil yang sama: sebuah nilai yang sebenarnya cuma fungsi dari kolom lain. Nama lengkap dari depan dan belakang, vektor pencarian dari judul dan isi, total dari harga kali kuantitas, email huruf kecil untuk pencarian tanpa peduli kapital. Jawaban malasnya adalah menghitungnya di kode aplikasi setiap kali menulis, dan berharap tak ada yang lupa. Jawaban yang lebih baik, hampir selalu, adalah membiarkan database yang memilikinya. Untuk itulah kolom generated ada.

Kolom generated hadir di PostgreSQL 12 pada 2019, dan sampai belum lama ini hanya ada satu rasa: STORED. PostgreSQL 18 menambah kolom VIRTUAL dan menjadikan virtual sebagai default ketika Anda tak menyebutkan mana yang diinginkan. Perubahan default itu penting, karena keduanya berperilaku sangat berbeda begitu Anda mencoba mengindeks atau mencarinya. Ini panduan praktis soal bagaimana saya benar-benar memakainya di layanan yang saya jalankan.

Apa sebenarnya kolom generated itu

Kolom generated didefinisikan dengan ekspresi GENERATED ALWAYS AS yang membaca kolom lain di baris yang sama. Anda tak pernah menulisnya langsung; PostgreSQL yang menghitungnya. Contoh klasiknya adalah total ternormalisasi di tabel baris item.

CREATE TABLE order_items (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  order_id    bigint NOT NULL,
  unit_price  numeric(12,2) NOT NULL,
  quantity    integer NOT NULL,
  -- computed on every write, physically stored on disk
  line_total  numeric(14,2) GENERATED ALWAYS AS (unit_price * quantity) STORED
);

INSERT INTO order_items (order_id, unit_price, quantity)
VALUES (1001, 19.90, 3);

-- line_total is 59.70 with no application code involved
SELECT unit_price, quantity, line_total FROM order_items;

Kemenangannya di sini bukan menghemat tiga karakter aritmetika. Melainkan line_total kini mustahil salah. Setiap penulis lewat setiap jalur kode, setiap migrasi SQL mentah, setiap sesi psql yang dijalankan anak magang jam 2 pagi menghasilkan nilai yang konsisten. Invariannya hidup di sebelah data yang ia batasi, bukan tersebar di layanan-layanan yang belum tentu sepakat soal rumusnya.

STORED versus VIRTUAL: pilih berdasarkan pola baca

Kedua jenis ini menukar biaya tulis dengan biaya baca. STORED membayar saat menulis dan di disk, sehingga baca dan indeks jadi gratis. VIRTUAL tak membayar apa pun saat menulis tapi menghitung ulang setiap kali dibaca, dan yang krusial, tak bisa diindeks per PostgreSQL 18. Jika Anda pernah memfilter, mengurutkan, join, atau mencari nilai turunan itu, hampir pasti Anda mau STORED.

Mengindeks kolom stored: kasus pencarian teks lengkap

Tempat kolom generated STORED paling keras membayar dirinya adalah pencarian teks lengkap. Alih-alih memelihara tsvector dengan tangan di sebuah trigger, Anda menghasilkannya dari kolom sumber dan mengindeks hasilnya dengan GIN. Vektor pencarian tetap seiring dengan konten, dan query tak pernah mengulang panggilan to_tsvector untuk memverifikasi kecocokan.

ALTER TABLE articles
  ADD COLUMN search_vector tsvector
  GENERATED ALWAYS AS (
    setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
    setweight(to_tsvector('english', coalesce(body,  '')), 'B')
  ) STORED;

CREATE INDEX articles_search_idx
  ON articles USING GIN (search_vector);

-- fast, and no need to name the config in the query
SELECT id, title
FROM   articles
WHERE  search_vector @@ to_tsquery('english', 'postgres & index')
ORDER  BY id DESC
LIMIT  20;

Pola yang sama bekerja untuk kasus yang lebih murah. Kolom STORED lower(email) dengan indeks unik memberi Anda keunikan tanpa peduli kapital tanpa indeks fungsional yang harus diingat oleh setiap jalur insert. Kolom STORED date_trunc('month', created_at) memungkinkan Anda membangun b-tree untuk rollup bulanan yang cepat. Apa pun yang berulang kali Anda filter adalah kandidat.

Aturan yang akan menggigit Anda

Kolom generated bukan sihir; ekspresinya harus memenuhi batasan nyata, dan PostgreSQL menegakkannya saat DDL. Pelajari ini sebelum Anda merancang di sekitar sebuah kolom, bukan setelah migrasi gagal di staging.

• Ekspresinya harus IMMUTABLE. now(), random(), dan apa pun yang bergantung zona waktu ditolak — nilainya harus fungsi murni dari baris yang tersimpan.
• Ia bisa mereferensikan kolom lain di baris yang sama, tapi bukan kolom generated lain, dan bukan kolom sistem kecuali tableoid.
• Kolom generated tak bisa punya DEFAULT atau definisi identity, dan tak bisa jadi bagian dari partition key.
• Anda tak bisa menghapus kolom yang menjadi sandaran kolom generated tanpa lebih dulu menghapus atau mengubah kolom generated itu.
• Di PostgreSQL 18, kolom virtual tambahan tak bisa membawa batasan unik atau foreign-key, batasan not-null, atau ikut dalam replikasi logis.

Memindahkan logika turunan dengan aman

Mendorong sebuah rumus dari kode aplikasi ke database adalah migrasi nyata, bukan sakelar konfigurasi. Bagian berbahayanya adalah ALTER: Anda tak bisa mengubah ekspresi kolom generated STORED atau terindeks di tempatnya. Anda harus menghapus kolomnya dan menambahkannya kembali, yang menulis ulang tabel dan menghapus indeks apa pun padanya. Di tabel besar itu operasi berat yang mengunci. Berikut urutan yang saya ikuti.

1. Tambahkan kolom generated STORED dalam satu migrasi. Menambah kolom stored menulis ulang tabel sekali, jadi jadwalkan di jendela lalu lintas rendah.
2. Bangun indeks secara CONCURRENTLY setelahnya agar tabel tetap bisa ditulis selama pembuatan indeks.
3. Verifikasi kesetaraan: bandingkan nilai generated dengan nilai lama yang dihitung aplikasi di seluruh tabel sebelum memercayainya.
4. Hapus komputasi sisi aplikasi hanya setelah kolom dan indeks aktif serta terverifikasi di produksi.
5. Untuk mengubah rumus nanti, hapus dan tambahkan ulang kolom di jendela pemeliharaan — perlakukan setiap perubahan ekspresi sebagai penulisan ulang tabel.

-- migration up
ALTER TABLE articles
  ADD COLUMN search_vector tsvector
  GENERATED ALWAYS AS (to_tsvector('english', coalesce(body, ''))) STORED;

-- separate step, outside the transaction:
CREATE INDEX CONCURRENTLY articles_search_idx
  ON articles USING GIN (search_vector);

-- changing the expression later is a drop + re-add, NOT an ALTER:
ALTER TABLE articles DROP COLUMN search_vector;
ALTER TABLE articles
  ADD COLUMN search_vector tsvector
  GENERATED ALWAYS AS (
    setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
    setweight(to_tsvector('english', coalesce(body,  '')), 'B')
  ) STORED;

Aturan praktis saya: ambil kolom generated STORED saat nilai turunan adalah invarian keras yang tak pernah ingin Anda biarkan salah oleh aplikasi, dan Anda membaca atau mengindeksnya jauh lebih sering daripada menulis ulang rumusnya. Ambil VIRTUAL saat nilainya murah dihitung, jarang Anda baca, dan tulisan mendominasi. Dan apa pun pilihan Anda, tulis kata kuncinya dengan lantang di DDL agar migrasi berikutnya tak mewarisi kejutan dari default versi.