Apa perbedaan antara kolom generated STORED dan VIRTUAL di PostgreSQL?

Kolom generated STORED menghitung nilainya setiap INSERT atau UPDATE dan menulisnya ke disk, sehingga memakai storage dan bisa diindeks. Kolom generated VIRTUAL tak menyimpan apa pun dan menghitung ulang saat dibaca, membuat tulisan lebih murah tapi berarti ia tak bisa diindeks per PostgreSQL 18. STORED adalah satu-satunya opsi sebelum PostgreSQL 18, yang memperkenalkan VIRTUAL sebagai default baru.

Bisakah membuat indeks pada kolom generated PostgreSQL?

Anda bisa mengindeks kolom generated STORED persis seperti kolom biasa, termasuk indeks b-tree dan GIN. Anda tak bisa mengindeks kolom generated VIRTUAL, karena nilainya tak tersimpan di mana pun untuk ditunjuk indeks. Jika Anda berencana memfilter, mengurutkan, atau mencari nilai turunan, definisikan kolomnya sebagai STORED.

Mengapa ekspresi kolom generated saya gagal dengan 'is not immutable'?

PostgreSQL mensyaratkan ekspresi generasi bersifat IMMUTABLE, artinya ia fungsi murni dari baris saat ini dan selalu mengembalikan hasil yang sama. Fungsi seperti now(), random(), atau cast yang bergantung zona waktu tidak immutable dan ditolak. Kasus halusnya adalah to_tsvector(body): kunci konfigurasinya sebagai to_tsvector('english', body) agar immutable.

Bagaimana cara mengubah ekspresi kolom generated yang sudah ada?

Anda tak bisa meng-ALTER ekspresi kolom generated STORED atau terindeks di tempatnya. Anda harus DROP kolomnya dan ADD kembali dengan ekspresi baru, yang menulis ulang tabel dan menghapus indeks apa pun padanya. Jadwalkan ini di jendela pemeliharaan dan bangun ulang indeks secara CONCURRENTLY setelahnya pada tabel besar.

Kapan sebaiknya memakai kolom generated ketimbang menghitung di kode aplikasi?

Pakai kolom generated saat nilai turunan adalah invarian yang tak pernah ingin Anda biarkan salah oleh penulis mana pun, seperti total baris, email ternormalisasi, atau vektor pencarian. Menaruhnya di database menjamin setiap jalur kode, migrasi, dan query manual menghasilkan nilai yang sama. Utamakan STORED saat sering membaca atau mengindeks nilainya, dan VIRTUAL saat tulisan mendominasi dan baca jarang.

Kolom Generated PostgreSQL: STORED, VIRTUAL, dan Indeks

Kapan mendorong logika turunan ke kolom generated STORED, cara mengindeksnya, dan batasan yang menggigit di produksi.

Ringkasan Utama

Kolom generated PostgreSQL menghitung nilainya dari kolom lain di baris yang sama memakai ekspresi immutable. Kolom STORED menulis nilai itu ke disk sehingga bisa diindeks dan dibaca gratis; kolom VIRTUAL menghitung saat dibaca. Pakai STORED plus indeks yang cocok saat Anda memfilter atau mencari data turunan.

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.

Dimensi	STORED	VIRTUAL
Kapan dihitung	Saat INSERT / UPDATE	Setiap kali dibaca
Pemakaian disk	Memakai storage seperti kolom biasa	Nol — tak ada yang disimpan
Bisa diindeks	Ya	Tidak (PostgreSQL 18)
Kecepatan tulis	Lebih lambat — hitung ulang dan tulis	Lebih cepat — tak ada kerja saat tulis
Paling cocok untuk	Data yang banyak dibaca, difilter, dicari	Data banyak ditulis, jarang dibaca
Default bila DDL tak menyebut	Satu-satunya opsi sebelum v18	Default baru di v18+

Karena VIRTUAL adalah default baru di PostgreSQL 18, skrip CREATE TABLE lama yang tak menyertakan kata kunci kini menghasilkan kolom virtual, bukan stored. Jika migrasi berikutnya mencoba mengindeksnya, pembuatan indeks itu gagal. Selalu tulis STORED atau VIRTUAL secara eksplisit di DDL Anda ketimbang percaya pada default.

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;

Satu keunggulan kolom generated dibanding indeks ekspresi biasa adalah query tak perlu mengulang ekspresi persis untuk mengenai indeks. Indeks GIN pada search_vector dipakai oleh query mana pun yang mereferensikan search_vector, yang menjaga kode aplikasi tetap bersih dan ORM Anda senang.

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.

Immutability lebih ketat dari kelihatannya. to_tsvector('english', body) dengan konfigurasi dinamai sebagai literal bersifat immutable dan diizinkan; to_tsvector(body), yang membaca konfigurasi default sesi, tidak. Selalu kunci argumen konfigurasi di ekspresi kolom generated.

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.

Tambahkan kolom generated STORED dalam satu migrasi. Menambah kolom stored menulis ulang tabel sekali, jadi jadwalkan di jendela lalu lintas rendah.
Bangun indeks secara CONCURRENTLY setelahnya agar tabel tetap bisa ditulis selama pembuatan indeks.
Verifikasi kesetaraan: bandingkan nilai generated dengan nilai lama yang dihitung aplikasi di seluruh tabel sebelum memercayainya.
Hapus komputasi sisi aplikasi hanya setelah kolom dan indeks aktif serta terverifikasi di produksi.
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.

Sumber

Kolom Generated PostgreSQL untuk Data Terhitung