GENERATED columns (stored and virtual) in PostgreSQL - Time & Space Complexity

Time Complexity: GENERATED columns (stored and virtual)

O(n)

Understanding Time Complexity

We want to understand how the time to compute generated columns changes as the table grows.

How does adding more rows affect the work done to create these columns?

Scenario Under Consideration

Analyze the time complexity of this table with a generated column.

CREATE TABLE sales (
  price numeric,
  quantity int,
  total numeric GENERATED ALWAYS AS (price * quantity) STORED
);

INSERT INTO sales (price, quantity) VALUES
  (10, 2), (15, 3), (7, 5);

SELECT * FROM sales;

This code creates a table where the total is automatically calculated by multiplying price and quantity for each row.

Identify Repeating Operations

Look at what repeats when the table grows.

• Primary operation: Calculating the generated column expression (price * quantity) for each row.
• How many times: Once per row inserted or updated.

How Execution Grows With Input

Each new row requires one calculation of the generated column.

Input Size (n)	Approx. Operations
10	10 calculations
100	100 calculations
1000	1000 calculations

Pattern observation: The work grows directly with the number of rows; double the rows, double the calculations.

Final Time Complexity

Time Complexity: O(n)

This means the time to compute generated columns grows linearly with the number of rows.

Common Mistake

[X] Wrong: "The generated column calculation happens only once for the whole table, so it's constant time."

[OK] Correct: Each row's generated column is calculated separately when inserted or updated, so the total work grows with the number of rows.

Interview Connect

Understanding how generated columns scale helps you explain database behavior clearly and shows you can reason about query costs in real projects.

Self-Check

What if the generated column used a subquery instead of a simple expression? How would the time complexity change?