Integer types (smallint, integer, bigint) in PostgreSQL - Time & Space Complexity

Time Complexity: Integer types (smallint, integer, bigint)

O(n)

Understanding Time Complexity

When working with integer types in PostgreSQL, it's helpful to understand how operations on these types scale as data grows.

We want to see how the time to process integers changes when using smallint, integer, or bigint types.

Scenario Under Consideration

Analyze the time complexity of inserting and querying integer values of different sizes.

-- Create a table with an integer column
CREATE TABLE numbers (
  id SERIAL PRIMARY KEY,
  value INTEGER
);

-- Insert n integer values
INSERT INTO numbers (value)
SELECT generate_series(1, n);

-- Query all values
SELECT * FROM numbers WHERE value > 0;
    

This code inserts n integer values and then queries all rows with a simple condition.

Identify Repeating Operations

Look for repeated actions that affect performance.

• Primary operation: Inserting and scanning n rows in the table.
• How many times: Each row is inserted once and scanned once during the query.

How Execution Grows With Input

As the number of rows n increases, the work to insert and query grows roughly in proportion.

Input Size (n)	Approx. Operations
10	About 10 insert and 10 scan operations
100	About 100 insert and 100 scan operations
1000	About 1000 insert and 1000 scan operations

Pattern observation: The operations increase linearly as n grows.

Final Time Complexity

Time Complexity: O(n)

This means the time to insert and query grows directly with the number of rows.

Common Mistake

[X] Wrong: "Using bigint instead of integer will slow down queries significantly because bigger numbers are harder to process."

[OK] Correct: The difference in processing time between smallint, integer, and bigint is very small and usually does not affect query time noticeably. The main factor is the number of rows, not the integer size.

Interview Connect

Understanding how data size affects query time helps you write efficient database code and explain your reasoning clearly in interviews.

Self-Check

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