Format function for safe formatting in PostgreSQL - Time & Space Complexity

Time Complexity: Format function for safe formatting

O(n)

Understanding Time Complexity

We want to understand how the time to run a query using the format function changes as the input grows.

Specifically, how does formatting many values safely affect execution time?

Scenario Under Consideration

Analyze the time complexity of the following code snippet.

DO $$
DECLARE
  n integer := 1000;
  i integer;
  formatted_text text;
BEGIN
  FOR i IN 1..n LOOP
    formatted_text := format('Value: %s', i);
  END LOOP;
END $$;
    

This code formats a string with a number inside a loop that runs n times.

Identify Repeating Operations

• Primary operation: The format function call inside the loop.
• How many times: It runs once for each number from 1 to n, so n times.

How Execution Grows With Input

Each time we add one more number to format, the loop runs one more time, doing one more format operation.

Input Size (n)	Approx. Operations
10	10 format calls
100	100 format calls
1000	1000 format calls

Pattern observation: The number of operations grows directly with n, so doubling n doubles the work.

Final Time Complexity

Time Complexity: O(n)

This means the time to run the code grows in a straight line as the input size increases.

Common Mistake

[X] Wrong: "The format function runs in constant time no matter how many times it is called."

[OK] Correct: Each call to format does some work, so calling it more times means more total work.

Interview Connect

Understanding how repeated formatting affects performance helps you write efficient queries and scripts that handle many inputs safely.

Self-Check

"What if we replaced the loop with a single format call that formats all values at once? How would the time complexity change?"