Domain types for validation in PostgreSQL - Time & Space Complexity

Time Complexity: Domain types for validation

O(n)

Understanding Time Complexity

We want to understand how the time it takes to check data using domain types grows as we add more data.

How does validation time change when the number of rows increases?

Scenario Under Consideration

Analyze the time complexity of the following domain type validation.

CREATE DOMAIN positive_int AS INTEGER
  CHECK (VALUE > 0);

CREATE TABLE orders (
  order_id SERIAL PRIMARY KEY,
  quantity positive_int
);

INSERT INTO orders (quantity) VALUES (5), (10), (0); -- last will fail

This code creates a domain type that only allows positive integers and uses it to validate data on insert.

Identify Repeating Operations

Look at what happens when many rows are inserted or checked.

• Primary operation: Checking the domain constraint for each row inserted or updated.
• How many times: Once per row, for every row being inserted or updated.

How Execution Grows With Input

Each new row adds one more check to do.

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

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

Final Time Complexity

Time Complexity: O(n)

This means the time to validate grows in a straight line as you add more rows.

Common Mistake

[X] Wrong: "The domain validation happens once for the whole table, so time stays the same no matter how many rows."

[OK] Correct: Each row is checked separately when inserted or updated, so more rows mean more checks and more time.

Interview Connect

Understanding how validation scales helps you design databases that stay fast as data grows. This skill shows you think about real-world data handling.

Self-Check

"What if the domain check was more complex, like checking a pattern or multiple conditions? How would the time complexity change?"