DO blocks for anonymous code in PostgreSQL - Time & Space Complexity

Time Complexity: DO blocks for anonymous code

O(n)

Understanding Time Complexity

We want to understand how the time it takes to run a DO block changes as the amount of work inside it grows.

How does the number of operations inside the block affect the total time?

Scenario Under Consideration

Analyze the time complexity of the following DO block in PostgreSQL.

DO $$
DECLARE
  i INTEGER := 1;
BEGIN
  WHILE i <= 1000 LOOP
    PERFORM pg_sleep(0); -- simulate work
    i := i + 1;
  END LOOP;
END $$;

This block runs a loop 1000 times, doing a small action each time.

Identify Repeating Operations

Look for repeated actions inside the block.

• Primary operation: The WHILE loop running the PERFORM statement.
• How many times: The loop runs once for each number from 1 to 1000, so 1000 times.

How Execution Grows With Input

As the number of loop iterations increases, the total work grows in a straight line.

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

Pattern observation: Doubling the number of iterations roughly doubles the total work.

Final Time Complexity

Time Complexity: O(n)

This means the time grows directly in proportion to the number of loop iterations.

Common Mistake

[X] Wrong: "The DO block runs instantly no matter how many times the loop runs."

[OK] Correct: Each loop iteration takes some time, so more iterations mean more total time.

Interview Connect

Understanding how loops inside anonymous code blocks affect performance helps you write efficient database scripts and answer questions about code speed.

Self-Check

"What if we replaced the WHILE loop with a nested loop inside the DO block? How would the time complexity change?"