Creating partitioned tables in PostgreSQL - Performance & Efficiency

Time Complexity: Creating partitioned tables

O(n)

Understanding Time Complexity

When we create partitioned tables, we want to understand how the work grows as the data grows.

How does splitting data into parts affect the time to insert or query data?

Scenario Under Consideration

Analyze the time complexity of creating a partitioned table and inserting data.

CREATE TABLE sales (
  id SERIAL PRIMARY KEY,
  sale_date DATE NOT NULL,
  amount NUMERIC
) PARTITION BY RANGE (sale_date);

CREATE TABLE sales_2023 PARTITION OF sales
  FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');

INSERT INTO sales (sale_date, amount) VALUES ('2023-06-15', 100);
    

This code creates a main table partitioned by date range and inserts a row into the correct partition.

Identify Repeating Operations

Look at what repeats when inserting many rows.

• Primary operation: Checking which partition a row belongs to.
• How many times: Once per inserted row.

How Execution Grows With Input

As you insert more rows, the system checks partitions for each row.

Input Size (n)	Approx. Operations
10	10 partition checks
100	100 partition checks
1000	1000 partition checks

Pattern observation: The number of partition checks grows directly with the number of rows inserted.

Final Time Complexity

Time Complexity: O(n)

This means the time to insert rows grows in a straight line with the number of rows.

Common Mistake

[X] Wrong: "Partitioning makes inserts instant no matter how many rows."

[OK] Correct: Each row still needs to be checked to find its partition, so time grows with rows.

Interview Connect

Understanding how partitioning affects time helps you design databases that handle large data smoothly.

Self-Check

"What if we added more partitions? How would that affect the time to find the right partition for each row?"