Range partitioning by date in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Querying data from a date range partitioned table

Given a table sales partitioned by date ranges on the sale_date column, what will be the output of this query?

SELECT COUNT(*) FROM sales WHERE sale_date >= '2023-01-01' AND sale_date < '2023-04-01';

Assume the partitions are:

• sales_2023_q1: 2023-01-01 to 2023-03-31
• sales_2023_q2: 2023-04-01 to 2023-06-30

The sales_2023_q1 partition contains 1500 rows, and sales_2023_q2 contains 2000 rows.

A 0

B 3500

C 2000

D 1500

💡 Hint

Think about which partitions the query will scan based on the date range.

The query filters for dates from 2023-01-01 up to but not including 2023-04-01. Only the sales_2023_q1 partition covers this range, so only its 1500 rows are counted.

🧠 Conceptual

intermediate

Understanding range partition boundaries

Which statement correctly describes how PostgreSQL range partitions handle boundary values for date partitions?

A The partition includes rows where the partition key is >= the start boundary and <= the end boundary.

B The partition includes rows where the partition key is > the start boundary and <= the end boundary.

C The partition includes rows where the partition key is >= the start boundary and < the end boundary.

D The partition includes rows where the partition key is < the start boundary and > the end boundary.

💡 Hint

Think about how ranges are usually defined in PostgreSQL partitions.

PostgreSQL range partitions include rows where the partition key is greater than or equal to the start boundary and less than the end boundary. This avoids overlap between partitions.

📝 Syntax

advanced

Creating a range partitioned table by date

Which of the following SQL statements correctly creates a table events partitioned by range on the event_date column?

A CREATE TABLE events (id SERIAL PRIMARY KEY, event_date DATE NOT NULL) PARTITION BY RANGE (event_date);

B CREATE TABLE events (id SERIAL PRIMARY KEY, event_date DATE NOT NULL) PARTITION BY LIST (event_date);

C CREATE TABLE events (id SERIAL PRIMARY KEY, event_date DATE NOT NULL) PARTITION BY HASH (event_date);

D CREATE TABLE events (id SERIAL PRIMARY KEY, event_date DATE NOT NULL) PARTITION BY RANGE event_date;

💡 Hint

Check the syntax for partitioning by range in PostgreSQL.

The correct syntax uses PARTITION BY RANGE (column_name). Options A and B use wrong partition types, and D has incorrect syntax missing parentheses.

🔧 Debug

advanced

Diagnosing a partition constraint violation error

You created a range partition orders_2023_q1 for orders table with range from '2023-01-01' to '2023-04-01'. When inserting a row with order_date = '2023-04-01', you get a constraint violation error. Why?

A Because the partition range is inclusive of the start date but exclusive of the end date, so '2023-04-01' does not belong in this partition.

B Because the partition range is inclusive of both start and end dates, so '2023-04-01' is duplicated in two partitions.

C Because the partition was created with a wrong data type for the date column.

D Because the partition key column is missing from the insert statement.

💡 Hint

Recall how PostgreSQL defines range partition boundaries.

PostgreSQL range partitions include the start boundary but exclude the end boundary. So '2023-04-01' belongs to the next partition, not this one.

❓ optimization

expert

Optimizing queries on range partitioned tables by date

You have a large logs table partitioned by month on log_date. You want to optimize a query that fetches logs for March 2024. Which approach will best improve query performance?

A Remove the partitioning and store all logs in a single table with an index on <code>log_date</code>.

B Add a WHERE clause filtering <code>log_date &gt;= '2024-03-01' AND log_date &lt; '2024-04-01'</code> to enable partition pruning.

C Use a JOIN with the partition table <code>logs_2024_03</code> directly instead of querying the parent table.

D Add an index on <code>log_date</code> in the parent table only.

💡 Hint

Think about how PostgreSQL uses partition pruning with WHERE clauses.

Adding a WHERE clause that matches the partition boundaries allows PostgreSQL to scan only the relevant partition, improving performance. Indexes on the parent table do not help partitions. Querying partitions directly is not recommended. Removing partitioning loses partition benefits.