JSONB modification functions in PostgreSQL - Time & Space Complexity

Time Complexity: JSONB modification functions

O(n)

Understanding Time Complexity

When working with JSONB data in PostgreSQL, we often modify parts of the data using special functions.

We want to understand how the time to modify JSONB grows as the JSONB data gets bigger.

Scenario Under Consideration

Analyze the time complexity of the following JSONB modification query.

UPDATE my_table
SET data = jsonb_set(data, '{address,city}', '"New York"')
WHERE id = 1;
    

This query updates the city inside the address object of a JSONB column for one row.

Identify Repeating Operations

Look for repeated work inside the JSONB modification.

• Primary operation: Traversing the JSONB tree to find the path and replace the value.
• How many times: The traversal happens once per update, touching nodes along the path.

How Execution Grows With Input

The time depends on how deep and large the JSONB data is.

Input Size (n)	Approx. Operations
10 keys	About 10 steps to find and update
100 keys	About 100 steps
1000 keys	About 1000 steps

Pattern observation: The work grows roughly in direct proportion to the size of the JSONB data touched.

Final Time Complexity

Time Complexity: O(n)

This means the time to modify JSONB grows linearly with the size of the JSONB data involved.

Common Mistake

[X] Wrong: "Modifying one key in JSONB is always instant no matter the size."

[OK] Correct: Even changing one key requires walking through the JSONB structure, so bigger data takes more time.

Interview Connect

Understanding how JSONB modification scales helps you explain performance in real projects using JSON data in databases.

Self-Check

"What if we updated multiple keys at once using jsonb_set multiple times? How would the time complexity change?"