My Favorite PostgreSQL 18 Feature: Virtual Generated Columns
8 hours ago
- #SQL
- #PostgreSQL
- #Database
- PostgreSQL 18 introduces virtual generated columns, a new feature that computes values on the fly when queried, unlike stored generated columns which persist values on disk.
- Generated columns reduce boilerplate code by encapsulating logic close to the data, improving schema maintainability and reducing duplication.
- Virtual generated columns are ideal for lightweight expressions or when storage bloat is a concern, while stored generated columns are better for expensive expressions that benefit from indexing.
- A practical use case for generated columns is full-text search (FTS), where different language configurations can be stored as generated columns and indexed for efficient querying.
- Virtual generated columns are particularly useful for exposing JSON fields as regular columns without duplicating data on disk, offering a cleaner query surface with no storage overhead.
- Performance tradeoffs exist: stored columns add write overhead but speed up reads with indexes, while virtual columns keep writes cheap but require computation during reads.
- Schema evolution is easier with virtual columns, as they can be added instantly without rewriting the table, making them ideal for experimentation in production environments.
- Generated columns have limitations and security implications, especially when involving custom functions or user-defined types, requiring careful planning to avoid unexpected behaviors.