First, we will take a closer look at the outdated method of partitioning data. Keep in mind that understanding this technique is important to gain a deeper overview of what PostgreSQL really does behind the scenes.

Before digging deeper into the advantages of partitioning, I want to show you how partitions can be created. The entire thing starts with a parent table that we can create by using the following command:

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 partitioning, but we'll cover more on that a bit later.

Now that the parent table is in place, the child tables can be created. This is how it works:

test=# CREATE TABLE t_data_2016 () INHERITS (t_data);
CREATE TABLE
test=# d t_data_2016
                       Table "public.t_data_2016" 
 Column  | Type    | Modifiers 
---------+---------+----------------------------------------------------- 
 id      | integer | not null default 
                     nextval('t_data_id_seq'::regclass) 
 t       | date    | 
 payload | text    | 
Inherits: t_data

The table is called t_data_2016 and inherits from t_data. (). This means that no extra columns are added to the child table. As you can see, inheritance means that all the columns from the parents are available in the child table. Also note that the id column will inherit the sequence from the parent so that all the children can share the very same numbering.

Let's create some more tables:

test=# CREATE TABLE t_data_2015 () INHERITS (t_data);
CREATE TABLE
test=# CREATE TABLE t_data_2014 () INHERITS (t_data);
CREATE TABLE

So far, all of the tables are identical and just inherit from the parent. However, there's more: child tables can actually have more columns than parents. Adding more fields is simple:

test=# CREATE TABLE t_data_2013 (special text) INHERITS (t_data);
CREATE TABLE

In this case, a special column has been added. It has no impact on the parent; it just enriches the children and makes them capable of holding more data.

After creating a handful of tables, a row can be added:

test=# INSERT INTO t_data_2015 (t, payload) 
        VALUES ('2015-05-04', 'some data');
INSERT 0 1

The most important thing now is that the parent table can be used to find all the data in the child tables:

test=# SELECT * FROM t_data;
 id |     t      | payload 
----+------------+----------- 
  1 | 2015-05-04 | some data 
(1 row)

Querying the parent allows you to gain access to everything below the parent in a simple and efficient manner.

To understand how PostgreSQL does partitioning, it makes sense to take a look at the plan:

test=# EXPLAIN SELECT * FROM t_data;
                              QUERY PLAN 
----------------------------------------------------------------- 
 Append (cost=0.00..84.10 rows=4411 width=40) 
   -> Seq Scan on t_data (cost=0.00..0.00 rows=1 width=40) 
   -> Seq Scan on t_data_2016 
         (cost=0.00..22.00 rows=1200 width=40) 
   -> Seq Scan on t_data_2015 
         (cost=0.00..22.00 rows=1200 width=40) 
   -> Seq Scan on t_data_2014 
         (cost=0.00..22.00 rows=1200 width=40) 
   -> Seq Scan on t_data_2013 
         (cost=0.00..18.10 rows=810 width=40) 
(6 rows) 

Actually, the process is quite simple. PostgreSQL will simply unify all the tables and show us all the content from all the tables inside and below the partition we are looking at. Note that all the tables are independent and are just connected logically through the system catalog.