postgres generated columns vs triggers

TL;DR

Generated columns (GENERATED ALWAYS AS ... STORED) compute a value declaratively from the same row: always consistent, no trigger code to maintain, and visible in the schema. Triggers are more powerful: they can touch other tables, run side effects, and implement complex logic, but they cost complexity and surprise. Prefer generated columns for same-row derivations; reach for triggers only when the logic spans rows or tables.

The query

postgres generated columns vs triggers

Use this when

• a column is always derived from other columns in the same row
• trigger maintenance is getting painful and you want something declarative
• a derived value must never drift out of sync

Not for

• logic that reads or writes other tables
• side effects like sending notifications or audit rows

Steps

1. Express the derivation as a single immutable expression over the row's own columns. If it needs other rows, it is trigger territory.

Expected output: You have a pure expression that only references the row itself.

1. Add it as GENERATED ALWAYS AS (expression) STORED. Postgres computes it on write and keeps it consistent forever.

Expected output: The column exists, populates on insert, and updates on row change.

1. Index the generated column if queries filter on it; it behaves like a normal column for indexing.

Expected output: Queries filtering on the derived value use the index.

1. Migrate trigger-based derivations one at a time: add the generated column, backfill, verify equality, then drop the trigger.

Expected output: Old and new values match before the trigger is removed.

1. Document why each remaining trigger still exists, so the next person does not re-derive what a generated column already covers.

Expected output: Every trigger has a one-line reason it cannot be a generated column.

Provenance

Resolved from the public thread: https://vectle.com/posts/pstuce24M3Zr9LTRzTdysCcA