A lot of what has been added in PostgreSQL 10 and 11 will make sure that what you have seen in the "old world" is automated. This is especially true for indexing, tuple routing on insertion, and so on. However, let's go through those things in a more organized way.

For many years, the PostgreSQL community has been working on built-in partitioning. Finally, PostgreSQL 10.0 now offers the first implementation of in-core partitioning, which will be covered in this chapter. In PostgreSQL 10, the partitioning functionality was still pretty busy and therefore a lot of stuff has been improved in PostgreSQL 11 to make life even easier for people who want to use this important feature. 

To show you how partitioning works, I have compiled a simple example featuring range partitioning, as follows:

CREATE TABLE data (
   payload  integer
 )  PARTITION BY RANGE (payload);

CREATE TABLE negatives PARTITION
  OF data FOR VALUES FROM (MINVALUE) TO (0);
    
CREATE TABLE positives PARTITION
  OF data FOR VALUES FROM (0) TO (MAXVALUE);  

In this example, one partition will hold all negative values while the other one will take care of positive values. While creating the parent table, you can simply specify which way you want to partition data.

Once the parent table has been created, it is already time to create the partitions. To do that, the PARTITION OF clause has to be added. At this point, there are still some limitations (as of PostgreSQL 10). The most important one is that a tuple (a row) cannot move from one partition to the other, for example:

UPDATE data SET payload = -10 WHERE id = 5  

Fortunately, this restriction has been lifted and PostgreSQL 11 is able to move a row from one partition to the other. However, keep in mind that moving data between partitions might not be the best of ideas in general. 

The next important aspect is related to indexing: in PostgreSQL 10, every table (every partition) had to be indexed separately. This is not true in PostgreSQL 11 anymore. Let's try this out and see what happens:

test=# CREATE INDEX idx_payload ON data (payload);
CREATE INDEX
test=# d positives
 Table "public.positives"
 Column | Type | Collation | Nullable | Default 
---------+---------+-----------+----------+---------
 payload | integer | | | 
Partition of: data FOR VALUES FROM (0) TO (MAXVALUE)
Indexes:
 "positives_payload_idx" btree (payload)

What you can see here is that the index has also been added to the child table automatically, which is a really important feature of PostgreSQL 11 and which has widely been appreciated by users moving their applications to PostgreSQL 11 already.

Another important feature is the ability to create a default partition. To show you how that works, we can drop one of our two partitions:

test=# DROP TABLE negatives;
DROP TABLE

Then, a default partition of the data table can be created easily:

test=# CREATE TABLE p_def PARTITION OF data DEFAULT;
CREATE TABLE

All data that does not fit anywhere will end up in this default partition, which ensures that creating the right partition can never be forgotten. Experience has shown that the existence of a default partition makes applications a lot more reliable as time goes by.

The following listing shows how to insert data and where the data will finally end up:

test=# INSERT INTO data VALUES (1), (-1);
INSERT 0 2
test=# SELECT * FROM data;
 payload 
---------
 1
 -1
(2 rows)

test=# SELECT * FROM positives;
 payload 
---------
 1
(1 row)

test=# SELECT * FROM p_def;
 payload 
---------
 -1
(1 row)

As you can see, querying the parent tables will return all data. The various partitions will hold the data as defined by the partitioning rules.