PostgreSQL Partitioning 101
We can define a before and an after about Partitioning in Postgres. The seismic shift occurred in PG 10 (2017) when the declarative partitioning approach first appeared.
The approach for partitioning before PG 10 was very cumbersome to implement and to maintain, that is not the case for the new approach.
From PG 10 till now, every PG release has improved the DDL and the functionalities that we’ll cover now.
Getting to know the Basic partitioning DDL
This was the new approach that PG 10 allowed us to use, which permit us to partition according to a range, for example a date:
Given the previous table we could have two partition strategies for the Range: a Range and a List.
(PG 11, gave us another option “Hash” that we’ll be reviewing in a future post)
Let’s check those two strategies from PostgreSQL 10:
RANGE: range of values for every partitioned column
LIST: list of values for every partitioned column.
RANGE Partitioning
First let’s see how to make the partition with Range, in the following screenshot note that here the partitions have a name meaning they are not an anonymous object.
Each partition is assigned a range of values for the partition. In the following case you’re creating a partition for the month of January of 2019.
Another option is defining a partition by attaching a table that may already have data:
Important to note: The interval “From — to” is closed on the left, open on the right, meaning a microsecond before that quantity that you put in the To value. This approach is especially useful to avoid leap second problems and other edge cases.
If you happen to know partitioning before PG 10, nothing more is needed in order to get the tuple in the partition, no triggers no rules no nothing!, that’s a great improvement not only in simplicity but also in performance, giving a huge 10x improvement by some metrics.
You can also make partitions for 2 columns, but it -in my knowledge- is not very useful, as there is no common use case known to use that functionality and by virtue of being brief here, we will not check that use case in more detail.
LIST partitioning
We’ll use a ENUM as a list give its succinct DDL and other functionalities (that you can check in my articles about ENUM)
An easy to make clients table where every client is from a South American country.
As you can see in the next two tables in the illustration you can easily define a partition for every client in Colombia or a partition that mixes the clients from Argentina and Chile.
That’s all for today, Next time we’ll review the partitioning by hash :-)
Thanks to Alvaro Herrera for providing all the base for this article. Follow him @alvherre
