Partitioning data

Given default 8,000 blocks, PostgreSQL can store up to 32 TB of data inside a single table. If you compile PostgreSQL with 32,000 blocks, you can even put up to 128 TB into a single table. However, large tables like that are not necessarily too convenient anymore and it can make sense to partition tables to make processing easier and in some cases a bit faster.

Starting with PostgreSQL 10.0, we will most likely have improved partitioning, which will offer end users significantly easier handling of data partitioning.

At the time this chapter was written, PostgreSQL 10.0 had not been released yet and therefore the old means of partitioning are covered.

Creating partitions

Before digging deeper into the advantages of partitioning, I want to show how partitions can be created. The entire thing starts with a parent table:

test=# CREATE TABLE t_data (id serial, t date, payload text); 
CREATE TABLE

In this example, the parent table has three columns. The date column will be used for...