Indexing a PostgreSQL 18 generated column requires writing STORED

The post's example is one line of DDL: `line_total numeric(12,2) GENERATED ALWAYS AS (unit_price * quantity) STORED` ^[16]. Delete the last word and PostgreSQL 18 makes the column virtual ^[1]. It comes from the blog of Schemity, a desktop ERD tool its author builds, and every statement in it ran on PostgreSQL 18.3 in a throwaway container ^[2][3]. On its insert figures, the trigger's overhead is about 4.5 times the stored column's ^[1].

Those figures are the average of three runs of one INSERT ... SELECT over generate_series(1, 1000000) on a laptop, and the author says to read them as ratios ^[6]. For the ratios to transfer, your writes would have to resemble that batch, where a per-row function call is a large share of each row's work. I'd expect single-row inserts into a table with several indexes to show smaller percentages, since more of each insert's time goes to other work. The timings are all for inserts. A virtual column writes nothing, so it is computed when a query reads it ^[5].

In my view the index rule decides most cases. The post's own list of good generated columns includes lower(email), a tsvector from to_tsvector('english', body), and a field pulled out of a jsonb document ^[7]. In my experience, columns like those exist to be searched. On 18, each one needs STORED if the index is to sit on the column itself, because a virtual column cannot be indexed directly ^[1]. The post tested definitions written on 18.3 and does not cover what an upgrade does to columns created on earlier versions ^[3]. After an upgrade, the columns I would check are the ones in migrations written since, where a missing STORED produces a virtual column ^[1].

PostgreSQL enforces the same-row, immutable-only rule when the column is created ^[7][9]. I like this design. A bad expression fails in the migration while its author is still at the keyboard. The check is strict: the one-argument to_tsvector(body) is refused because its result depends on a server setting ^[8]. The rejection of one generated column reading another has an easy fix: repeat the other column's expression inline ^[10].

Triggers keep the cases a generated column cannot express: an updated_at set to now(), an orders.total summed from order_lines, and a customer's name stamped onto an invoice ^[11]. They fail without an error. In the bypass test, the trigger fired only on updates OF unit_price and quantity, and a statement setting line_total touched neither column ^[12]. Removing UPDATE OF closes that gap and adds a function call to updates that never touch the price ^[13]. A later maintainer then inherits a column that looks exactly like one the application writes ^[14]. "If a generated column can express the value, the trigger buys nothing but risk," the author wrote ^[15].

I think that holds on PostgreSQL 18 with one amendment. I would write STORED on every generated column that will carry an index and leave the rest virtual, where the post measured no insert cost ^[5].