Partitioning has been introduced in Postgres 10. But, it wasn’t really working simply because the feature is there for some kind of introductions.

The community has been working on it and improved a lot when it was released last year on Postgres version 11, but not until Postgres 12, will be released between late September or early October, that everything is ready for production use where mostly used for OLTP.

But, there are still some things you need to know before you want to use it.

#1 Partition locking

Creating or attaching partition will only have ShareUpdateExclusiveLock on the partition parent, which means blocking any DML and DDL while still be acessible for SELECT statements. This is an improvement from pervious versions where attach and create partitions was locking on everything.

Dropping and detaching partitions however, we still need full AccessExclusiveLock on both partition and parent table. It means, everything will be blocked when you drop or detach any partition.

#2 Default partition

Postgres 11 introduced a feature where you can add a default partition as a fallback when you enter some data and they don’t belong to an existing partitions. It’s a good feature where some applications or someone were uninformed that you have bounds on the table.

If you have large default partition, every new partition that will be created will need to _scan that default partition to find records that might need to be put inside the new ones. And because creating new partition will require locks, your create partition might take longer that you expect.

#3 Partition bounds

If you have partitions referenced in order such as TIMESTAMP and use SELECT statements with ORDER BY the column that you use as a partition bounds, any sort process will not require the planner to sort the result because the data is sorted.

On the other hand, if you have an expression as partition bounds such as EXTRACT() functions, sorting is not being done in the partitions. What this means is that there is no optimization for your query and the planner needs another process to sort your statements.

#4 Functions

Because Postgres partitions are related to performance, you need to make sure that your functions or how you write your query with care in relation to volatile, stable or immutable. Because these functions will affect your performance depends on when the functions will be executed/read whether in planning time or execution time.

All in all, Postgres partitioning in version 12 is ready for your production use. But, you need to know about these things before you ended up in a situation where you will reflect on your decisions.