Partitioning best practices in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

🧠 Conceptual

intermediate

Why use partitioning in PostgreSQL?

Which of the following is the main reason to use table partitioning in PostgreSQL?

A To automatically create indexes on all columns

B To improve query performance by scanning only relevant partitions

C To reduce the size of the database backups

D To enforce foreign key constraints across partitions

💡 Hint

Think about how partitioning helps with large datasets and query speed.

Partitioning helps by dividing a large table into smaller pieces, so queries can scan only the needed partitions, improving performance.

❓ query_result

intermediate

Query result with range partitioning

Given a table sales partitioned by range on sale_date, which query will return sales only from 2023?

PostgreSQL

SELECT * FROM sales WHERE sale_date >= '2023-01-01' AND sale_date < '2024-01-01';

A Returns all sales from 2023 only

B Returns sales from all years

C Returns sales only from 2022

D Returns no rows due to syntax error

💡 Hint

Check the date range in the WHERE clause.

The query filters sales between 2023-01-01 (inclusive) and 2024-01-01 (exclusive), so only 2023 sales are returned.

📝 Syntax

advanced

Correct syntax for creating a list partition

Which of the following CREATE TABLE statements correctly creates a list partition for a table orders partitioned by region?

A CREATE TABLE orders_east PARTITION OF orders FOR VALUES IN ('East');

B CREATE TABLE orders_east PARTITION OF orders VALUES LIST ('East');

C CREATE TABLE orders_east PARTITION OF orders FOR VALUES ('East');

D CREATE TABLE orders_east PARTITION OF orders PARTITION BY LIST ('East');

💡 Hint

Look for the correct syntax to specify list partition values.

The correct syntax uses FOR VALUES IN ('value') to define list partitions.

❓ optimization

advanced

Best practice for partition key choice

Which is the best practice when choosing a partition key for a large table?

A Choose a column that is not indexed

B Choose a column with very few distinct values to reduce partitions

C Choose a column that is rarely used in WHERE clauses

D Choose a column with high cardinality and frequent filtering in queries

💡 Hint

Think about how partition pruning works with query filters.

Choosing a column with many distinct values and often used in filters helps queries scan fewer partitions, improving performance.

🔧 Debug

expert

Why does this partitioned table query fail?

Consider a partitioned table events partitioned by LIST on event_type. The query SELECT * FROM events WHERE event_type = 'login'; returns no rows, but data exists. What is the most likely cause?

A The event_type column is not part of the partition key

B The query syntax is invalid for partitioned tables

C The partition for 'login' was not created or attached properly

D PostgreSQL does not support filtering on partition keys

💡 Hint

Think about how partitions must be created and attached for data to be found.

If the partition for a specific list value is missing or not attached, queries filtering on that value return no rows even if data exists elsewhere.