By Mouhamadou Diaw

PostgreSQL 10 comes with partition support. But Hash Partitioning was not supported. PostgreSQL 11 will support Hash Partition.
In the documentation we can read
The table is partitioned by specifying a modulus and a remainder for each partition. Each partition will hold the rows for which the hash value of the partition key divided by the specified modulus will produce the specified remainder.
For this demonstration let’s create a table with a hash partition

1
2
drop table dept;
create table dept (id  int primary key) partition by hash(id) ;

Now let’s create for example 10 partitions

1
2
3
4
5
6
7
8
9
10
create table dept_1 partition of dept FOR VALUES WITH (MODULUS 10, REMAINDER 0);
create table dept_2 partition of dept FOR VALUES WITH (MODULUS 10, REMAINDER 1);
create table dept_3 partition of dept FOR VALUES WITH (MODULUS 10, REMAINDER 2);
create table dept_4 partition of dept FOR VALUES WITH (MODULUS 10, REMAINDER 3);
create table dept_5 partition of dept FOR VALUES WITH (MODULUS 10, REMAINDER 4);
create table dept_6 partition of dept FOR VALUES WITH (MODULUS 10, REMAINDER 5);
create table dept_7 partition of dept FOR VALUES WITH (MODULUS 10, REMAINDER 6);
create table dept_8 partition of dept FOR VALUES WITH (MODULUS 10, REMAINDER 7);
create table dept_9 partition of dept FOR VALUES WITH (MODULUS 10, REMAINDER 8);
create table dept_10 partition of dept FOR VALUES WITH (MODULUS 10, REMAINDER 9);

We can verify that partitions are created using the d+ command

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
(postgres@[local]:5432) [test] > d+ dept
                                   Table "public.dept"
 Column |  Type   | Collation | Nullable | Default | Storage | Stats target | De
scription
--------+---------+-----------+----------+---------+---------+--------------+---
----------
 id     | integer |           | not null |         | plain   |              |
Partition key: HASH (id)
Indexes:
    "dept_pkey" PRIMARY KEY, btree (id)
Partitions: dept_1 FOR VALUES WITH (modulus 10, remainder 0),
            dept_10 FOR VALUES WITH (modulus 10, remainder 9),
            dept_2 FOR VALUES WITH (modulus 10, remainder 1),
            dept_3 FOR VALUES WITH (modulus 10, remainder 2),
            dept_4 FOR VALUES WITH (modulus 10, remainder 3),
            dept_5 FOR VALUES WITH (modulus 10, remainder 4),
            dept_6 FOR VALUES WITH (modulus 10, remainder 5),
            dept_7 FOR VALUES WITH (modulus 10, remainder 6),
            dept_8 FOR VALUES WITH (modulus 10, remainder 7),
            dept_9 FOR VALUES WITH (modulus 10, remainder 8)
(postgres@[local]:5432) [test] >

Now let’s insert some rows in the table dept

1
2
3
(postgres@[local]:5432) [test] > insert into dept (select generate_series(0,200000));
INSERT 0 200001
(postgres@[local]:5432) [test] >

We can verify that rows are not in the base table

1
2
3
4
5
6
7
(postgres@[local]:5432) [test] > select count(*) from  only dept ;
 count
-------
     0
(1 row)
(postgres@[local]:5432) [test] >

But that row are in the partitions

1
2
3
4
5
(postgres@[local]:5432) [test] > select count(*) from  dept ;
 count
--------
 200001
(1 row)

What we can also observe it that rows are uniformly distributed among partitions. This distribution is automatically done by the hash algorithm.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
(postgres@[local]:5432) [test] > select count(*) from  only dept_1 ;
 count
-------
 19982
(1 row)
                                   
(postgres@[local]:5432) [test] > select count(*) from  only dept_2 ;
 count
-------
 20199
(1 row)
(postgres@[local]:5432) [test] > select count(*) from  only dept_3 ;
 count
-------
 19770
(1 row)
(postgres@[local]:5432) [test] > select count(*) from  only dept_5 ;
 count
-------
 20068
(1 row)
(postgres@[local]:5432) [test] >
Post Views: 2,036
Thumbnail [60x60]
by
Oracle Team