Virtual Columns in Modern Databases

Virtual columns (aka computed columns) are a powerful yet underutilized feature in many modern relational database systems like MySQL, MariaDB, PostgreSQL (via generated columns), Oracle, and even SQL Server. They offer a convenient way to derive values on-the-fly without duplicating logic in your application layer — but they also come with performance implications and caveats that developers should understand before embracing them fully.

Let’s dive in.

What is a Virtual Column?

In short, a virtual column is a column whose value is automatically calculated based on the value of other columns in the same row. The key distinction is that you don’t manually insert a value into this column — the database does the work for you.

Here’s a simple example using MySQL:

CREATE TABLE users (
  first_name VARCHAR(100),
  last_name VARCHAR(100),
  full_name VARCHAR(201) AS (CONCAT(first_name, ' ', last_name)) VIRTUAL
);

In this case, full_name is not physically stored in the table. Every time you query it, the database evaluates the expression in real-time. There’s also a variant:

... STORED

This means the column is persisted in storage and only recalculated when the source columns change. It’s faster for reads, but costs space.

Use Cases That Just Make Sense

Some solid use cases for virtual columns include:

Denormalized search columns
e.g. full name, slug, or combined address fields
Data validation or formatting
e.g. computed age from date of birth
Efficient indexing on computed expressions
In MySQL, STORED virtual columns can be indexed. That’s useful when your app filters on a computed field.
Migration-safe logic
Instead of duplicating business logic in multiple codebases (backend, frontend, etc.), centralize it in the DB schema.

PostgreSQL vs MySQL: Implementation Differences

MySQL / MariaDB

Supports VIRTUAL and STORED generated columns (since 5.7+). You can even add indexes — but only on STORED ones.

ALTER TABLE products
  ADD discounted_price DECIMAL(10,2)
  AS (price - (price * discount_rate / 100)) STORED,
  ADD INDEX idx_discounted_price (discounted_price);

PostgreSQL

Doesn’t have native virtual columns like MySQL. Instead, it supports generated columns (from version 12+):

ALTER TABLE users
  ADD full_name TEXT GENERATED ALWAYS AS (first_name || ' ' || last_name) STORED;

No virtual-only (non-stored) columns yet — everything is stored.

SQL Server

Offers PERSISTED computed columns. You can also define indexes on them:

ALTER TABLE users
  ADD full_name AS (first_name + ' ' + last_name) PERSISTED;

Practical Tips for Using Virtual Columns

Avoid logic bloat
Don’t try to implement complex business rules with virtual columns. Keep them deterministic and lightweight.
Check performance in large datasets
For VIRTUAL columns, every read incurs computation cost. For STORED columns, updates may get slower.
Know your database’s limitations
For instance, MySQL doesn’t allow virtual columns to reference other virtuals. Nesting is not supported.
Use with ORM cautiously
Not all ORMs (e.g. Laravel’s Eloquent) support virtual columns well. You may need to use raw queries or define accessors.

Virtual Columns in Laravel

---- wip

Summary: Should You Use Virtual Columns?

Use STORED virtual columns for performance-sensitive filtered searches.
Use VIRTUAL ones when space is more critical than speed.
Avoid overengineering — treat virtual columns as optimization tools, not business logic layers.
Check ORM compatibility — especially if you’re relying on schema migrations.

They’re not for every use case, but when used properly, virtual columns can dramatically reduce duplication, improve query performance, and simplify your data model.

Navigation