ENUM types in PostgreSQL - Time & Space Complexity

Time Complexity: ENUM types

O(n)

Understanding Time Complexity

When using ENUM types in PostgreSQL, it's important to understand how the system handles these values internally.

We want to know how the time to work with ENUM values changes as the number of ENUM labels grows.

Scenario Under Consideration

Analyze the time complexity of querying a table with an ENUM column.

CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');

CREATE TABLE person (
  id SERIAL PRIMARY KEY,
  name TEXT,
  current_mood mood
);

SELECT * FROM person WHERE current_mood = 'happy';
    

This code defines an ENUM type, creates a table using it, and queries rows matching a specific ENUM value.

Identify Repeating Operations

Look at what happens when the query runs:

• Primary operation: Comparing ENUM values during row filtering.
• How many times: Once per row scanned in the table.

How Execution Grows With Input

As the number of rows grows, the database compares the ENUM value for each row to the target.

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

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

Final Time Complexity

Time Complexity: O(n)

This means the time to find matching rows grows in a straight line as the table gets bigger.

Common Mistake

[X] Wrong: "ENUM types make comparisons instant no matter how many rows there are."

[OK] Correct: While ENUM values are stored efficiently, the database still checks each row's value when scanning, so time grows with row count.

Interview Connect

Understanding how ENUM types affect query time helps you explain data type choices and performance in real projects.

Self-Check

What if we added an index on the ENUM column? How would the time complexity change?