String aggregation with STRING_AGG in PostgreSQL - Time & Space Complexity

Time Complexity: String aggregation with STRING_AGG

O(n log n)

Understanding Time Complexity

When we combine many text values into one string using STRING_AGG, it takes some time to do this work.

We want to understand how the time needed grows as we add more text values.

Scenario Under Consideration

Analyze the time complexity of the following code snippet.

SELECT department_id, STRING_AGG(employee_name, ', ' ORDER BY employee_name) AS employees
FROM employees
GROUP BY department_id;
    

This query groups employees by their department and joins their names into one string per department.

Identify Repeating Operations

Identify the loops, recursion, array traversals that repeat.

• Primary operation: Scanning each employee record and concatenating their names per group.
• How many times: Once for each employee in the table, grouped by department.

How Execution Grows With Input

As the number of employees grows, the work to combine their names grows roughly in proportion.

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

Pattern observation: The time grows roughly in a straight line as more names are combined.

Final Time Complexity

Time Complexity: O(n log n)

This means the time to combine strings grows proportionally to n log n due to the ORDER BY sorting inside STRING_AGG.

Common Mistake

[X] Wrong: "STRING_AGG runs instantly no matter how many rows there are."

[OK] Correct: Each name must be read and added to the result, so more rows mean more work and more time.

Interview Connect

Understanding how aggregation functions like STRING_AGG scale helps you explain query performance clearly and confidently.

Self-Check

"What if we added an ORDER BY inside STRING_AGG? How would that affect the time complexity?"