Creating partitioned tables in PostgreSQL - Practice Exercises

Challenge - 5 Problems

❓ query_result

intermediate

Output of inserting data into a range partitioned table

Consider the following PostgreSQL partitioned table setup:

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');

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

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

SELECT tableoid::regclass, sale_date, amount FROM sales ORDER BY sale_date;

What will be the output of the SELECT query?

PostgreSQL

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');

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

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

SELECT tableoid::regclass, sale_date, amount FROM sales ORDER BY sale_date;

A

sales_2023 | 2024-03-10 | 200
sales_2024 | 2023-06-15 | 100

B

sales_2023 | 2023-06-15 | 100
sales_2024 | 2024-03-10 | 200

C

sales | 2023-06-15 | 100
sales | 2024-03-10 | 200

D Error: no partition for value

💡 Hint

Remember that data inserted into a partitioned table is stored in the matching partition table.

The data is routed to the correct partition based on the sale_date. The first row goes to sales_2023 because its date is in 2023, the second to sales_2024. The tableoid::regclass shows the actual partition table name.

🧠 Conceptual

intermediate

Choosing partition key type for performance

You want to create a partitioned table in PostgreSQL to store user logs. The logs have a timestamp column and a user_id column. You expect queries mostly filter by date ranges and sometimes by user_id.

Which partitioning strategy is best for optimizing query performance?

A Partition by RANGE on the timestamp column

B Partition by LIST on the user_id column

C Partition by HASH on the user_id column

D Partition by RANGE on the user_id column

💡 Hint

Think about which column is most commonly used in WHERE clauses and how partition pruning works.

Since queries mostly filter by date ranges, partitioning by RANGE on the timestamp column allows PostgreSQL to skip irrelevant partitions efficiently. Partitioning by user_id would not help range queries on dates.

📝 Syntax

advanced

Identify the syntax error in partition creation

Which of the following CREATE TABLE statements for a partitioned table is syntactically correct in PostgreSQL?

A

CREATE TABLE orders (
  id INT,
  order_date DATE
) PARTITION BY RANGE (order_date);

B

CREATE TABLE orders (
  id INT,
  order_date DATE
) PARTITION BY LIST (order_date);

C

CREATE TABLE orders (
  id INT,
  order_date DATE
) PARTITIONED BY RANGE (order_date);

D

CREATE TABLE orders (
  id INT,
  order_date DATE
) PARTITION BY RANGE order_date;

💡 Hint

Check the exact syntax for partitioning in PostgreSQL.

Option A is correct as it uses PARTITION BY RANGE (order_date). Option A is correct syntax for LIST partitioning. Option A uses invalid keyword PARTITIONED BY. Option A misses parentheses after RANGE.

🔧 Debug

advanced

Why does this insert fail on a partitioned table?

Given this partitioned table setup:

CREATE TABLE events (
  id SERIAL PRIMARY KEY,
  event_type TEXT NOT NULL
) PARTITION BY LIST (event_type);

CREATE TABLE events_login PARTITION OF events FOR VALUES IN ('login');
CREATE TABLE events_logout PARTITION OF events FOR VALUES IN ('logout');

INSERT INTO events (event_type) VALUES ('signup');

Why does the INSERT statement fail?

A The partition tables are missing primary keys

B The event_type column cannot be NULL

C No partition exists for the value 'signup' in event_type

D The PARTITION BY LIST syntax is invalid

💡 Hint

Think about how PostgreSQL routes data to partitions.

Since there is no partition defined for the value 'signup' in the event_type column, PostgreSQL cannot find a matching partition and raises an error.

❓ optimization

expert

Optimizing query performance on a large partitioned table

You have a large PostgreSQL table partitioned by RANGE on a date column with monthly partitions. You notice queries filtering on date ranges are slow.

Which of the following actions will most likely improve query performance?

A Drop all indexes and rely on sequential scans

B Create a global index on the parent partitioned table

C Convert the partitioning to LIST on the date column

D Create indexes on the partition key column in each partition

💡 Hint

Consider how indexes work with partitioned tables in PostgreSQL.

PostgreSQL does not support global indexes on partitioned tables. Indexes must be created on each partition individually to speed up queries filtering on the partition key.