Dealing with large tables with table partitioning
When we say that PostgreSQL supports table partitioning, we mean the division of a table into distinct independent tables. You want to do this because it makes large tables easier to manage, but also for performance reasons.
Postgres has had partitioning since version 8.1, and that was known as inheritance-based partitioning, which was complex and came with some serious limitations. Since version 10, declarative partitioning has become available, which lets you specify a partitioning method, the partitioning key, and the partition boundaries simply by creating tables through DDL.
How to do it…
Let’s create an example partitioned table. We want to partition our large table of transactions by their timestamp:
CREATE TABLE transactions (
tstamp TIMESTAMP WITH TIME ZONE PRIMARY KEY
,amount NUMERIC
)
PARTITION BY RANGE (tstamp);
Great, that’s the base table created; however, it has no partitions...