Sub-partitioning in PostgreSQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is sub-partitioning in PostgreSQL?

Sub-partitioning is dividing a partition of a table into smaller partitions. It helps organize data better and can improve query speed by narrowing down where data is stored.

intermediate

How do you create a sub-partitioned table in PostgreSQL?

You first create a partitioned table, then define partitions for it. Each partition can itself be partitioned further, creating sub-partitions using the PARTITION BY clause again.

intermediate

Why use sub-partitioning instead of just partitioning?

Sub-partitioning allows more detailed data grouping. For example, first by year, then by month inside each year. This makes data management and queries more efficient for large datasets.

advanced

Can you use different partitioning methods for sub-partitions?

Yes, PostgreSQL allows mixing partitioning methods. For example, you can partition a table by range, then sub-partition by list inside each range partition.

beginner

What happens if you query a sub-partitioned table without specifying partition keys?

PostgreSQL will scan all partitions and sub-partitions, which can be slower. Specifying partition keys helps the database skip irrelevant partitions and speeds up queries.

What is the main benefit of sub-partitioning in PostgreSQL?

A Automatic backups

B More disk space usage

C Slower data insertion

D Better data organization and faster queries

Sub-partitioning helps organize data into smaller groups, making queries faster by scanning fewer partitions.

Which SQL clause is used to define sub-partitions in PostgreSQL?

A GROUP BY

B ORDER BY

C PARTITION BY

D JOIN

The PARTITION BY clause is used to create partitions and sub-partitions.

Can you mix partitioning methods in sub-partitioning?

A Yes, different methods can be combined

B No, only one method per table

C Only hash partitioning is allowed

D Only range partitioning is allowed

PostgreSQL allows mixing partitioning methods like range and list in sub-partitions.

What happens if you query a sub-partitioned table without specifying partition keys?

A Only the first partition is scanned

B All partitions and sub-partitions are scanned

C Query fails with error

D Query runs instantly

Without partition keys, PostgreSQL scans all partitions and sub-partitions, which can slow down queries.

Which of these is a valid reason to use sub-partitioning?

A To organize data by year and then by month

B To reduce the number of tables

C To avoid indexing

D To disable constraints

Sub-partitioning helps organize data hierarchically, such as by year and then by month.

Explain what sub-partitioning is and why it is useful in PostgreSQL.

Think about how breaking data into smaller groups helps find data faster.

Describe how you would create a sub-partitioned table in PostgreSQL.

Start with a partitioned table, then add partitions inside partitions.