Why CRUD operations are fundamental in PostgreSQL - Performance Analysis

Time Complexity: Why CRUD operations are fundamental

O(n)

Understanding Time Complexity

CRUD operations are the basic actions we perform on data in a database. Understanding their time complexity helps us see how the work grows as data grows.

We want to know how the time to do these operations changes when the amount of data changes.

Scenario Under Consideration

Analyze the time complexity of the following SQL commands for CRUD operations.

-- Create (Insert)
INSERT INTO users (name, email) VALUES ('Alice', '[e-mail]');

-- Read (Select)
SELECT * FROM users WHERE id = 5;

-- Update
UPDATE users SET email = '[e-mail]' WHERE id = 5;

-- Delete
DELETE FROM users WHERE id = 5;
    

These commands add, retrieve, change, or remove a single user record by id.

Identify Repeating Operations

Each operation works on the database rows.

• Primary operation: Searching for the row by id (for Read, Update, Delete) or adding a new row (for Create).
• How many times: Usually once per operation, but searching may scan multiple rows if no index exists.

How Execution Grows With Input

As the number of rows grows, the time to find a row can grow too if no index is used.

Input Size (n)	Approx. Operations
10	About 10 checks if no index
100	About 100 checks if no index
1000	About 1000 checks if no index

Pattern observation: Without an index, searching grows linearly with data size; with an index, it stays fast.

Final Time Complexity

Time Complexity: O(n)

This means the time to find or modify a row grows roughly in direct proportion to the number of rows when no index is used.

Common Mistake

[X] Wrong: "CRUD operations always take the same time no matter how much data there is."

[OK] Correct: Without indexes, the database may check many rows, so more data means more work and longer time.

Interview Connect

Knowing how CRUD operations scale helps you explain database performance clearly and shows you understand how data size affects speed.

Self-Check

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