DEFAULT values and expressions in PostgreSQL - Time & Space Complexity

Time Complexity: DEFAULT values and expressions

O(n)

Understanding Time Complexity

We want to understand how the time to insert data with DEFAULT values changes as we add more rows.

How does the database handle setting default values when many rows are inserted?

Scenario Under Consideration

Analyze the time complexity of this INSERT statement using DEFAULT values.

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  username TEXT NOT NULL,
  created_at TIMESTAMP DEFAULT now()
);

INSERT INTO users (username) VALUES ('alice'), ('bob'), ('carol');
    

This code creates a table with a default timestamp and inserts multiple rows without specifying the timestamp.

Identify Repeating Operations

Look for repeated actions during the insert process.

• Primary operation: For each row inserted, the database evaluates the DEFAULT expression (now()) to set the timestamp.
• How many times: Once per row inserted, so the number of evaluations grows with the number of rows.

How Execution Grows With Input

As you insert more rows, the database must run the DEFAULT expression for each one.

Input Size (n)	Approx. Operations
10	10 evaluations of now()
100	100 evaluations of now()
1000	1000 evaluations of now()

Pattern observation: The work grows directly with the number of rows inserted.

Final Time Complexity

Time Complexity: O(n)

This means the time to insert rows with DEFAULT values grows linearly with how many rows you add.

Common Mistake

[X] Wrong: "DEFAULT expressions run only once no matter how many rows are inserted."

[OK] Correct: Each row needs its own value, so the DEFAULT expression runs separately for every row inserted.

Interview Connect

Understanding how DEFAULT values affect insert performance shows you know how databases handle data behind the scenes.

Self-Check

"What if the DEFAULT expression was a constant value instead of a function call? How would the time complexity change?"