Partition types (range, list, hash) in PostgreSQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is range partitioning in PostgreSQL?

Range partitioning divides a table into parts where each part holds rows with values within a specific range. For example, a sales table can be partitioned by year ranges like 2020-2021, 2022-2023.

beginner

How does list partitioning work in PostgreSQL?

List partitioning splits a table into parts based on a list of discrete values. For example, a table can be partitioned by country names like 'USA', 'Canada', 'Mexico'. Each partition holds rows matching those values.

intermediate

Explain hash partitioning in PostgreSQL.

Hash partitioning distributes rows into a fixed number of partitions using a hash function on the partition key. It balances data evenly without ranges or lists, useful when data distribution is unknown.

beginner

Which partition type would you use to split data by months?

Range partitioning is best for splitting data by months because months form continuous ranges of dates.

intermediate

What is a key benefit of hash partitioning?

Hash partitioning evenly distributes data across partitions, which helps balance load and improve query performance when data values are unpredictable.

Which partition type divides data based on continuous value ranges?

A Range partitioning

B List partitioning

C Hash partitioning

D None of the above

Range partitioning splits data into partitions based on continuous ranges of values.

If you want to partition a table by specific countries, which partition type should you use?

A Range partitioning

B Composite partitioning

C Hash partitioning

D List partitioning

List partitioning is used to split data by specific discrete values like country names.

What does hash partitioning use to assign rows to partitions?

A A hash function

B Lists of values

C Value ranges

D Random assignment

Hash partitioning uses a hash function on the partition key to distribute rows evenly.

Which partition type is best when you want to balance data evenly but don't know the data distribution?

A Range partitioning

B Hash partitioning

C List partitioning

D No partitioning

Hash partitioning evenly distributes data without needing to know value ranges or lists.

Which partition type would you NOT use to split data by date ranges?

A Hash partitioning

B List partitioning

C Both B and C

D Range partitioning

Date ranges are best handled by range partitioning; list and hash partitioning are not suitable for continuous ranges.

Describe the differences between range, list, and hash partitioning in PostgreSQL.

Think about how data is divided in each type.

When would you choose hash partitioning over range or list partitioning?

Consider data balance and predictability.