postgresql 12 数据库分区表之 hash

os: centos 7.4
db: postgresql 12.2

postgresql 12 的分区表已经比较完善。

版本

# cat /etc/centos-release
CentOS Linux release 7.4.1708 (Core) 
# 
# su - postgres
Last login: Thu Mar 19 14:47:45 CST 2020 on pts/0
$ 
$ psql
psql (12.2)
Type "help" for help.

postgres=# select version();
                                                 version                                                 
---------------------------------------------------------------------------------------------------------
 PostgreSQL 12.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-39), 64-bit
(1 row)

postgres=# show enable_partition_pruning;
 enable_partition_pruning 
--------------------------
 on
(1 row)

postgres=# select name,setting from pg_settings where name like '%partition%';
               name                | setting 
-----------------------------------+---------
 enable_partition_pruning          | on
 enable_partitionwise_aggregate    | off
 enable_partitionwise_join         | off
(3 rows) 

single column hash

single column in the partition key

postgres=# CREATE TABLE orders (
    order_id     bigint not null,
    cust_id      bigint not null,
    status       text
) PARTITION BY HASH (order_id);

CREATE TABLE orders_1 PARTITION OF orders FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE orders_2 PARTITION OF orders FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE orders_3 PARTITION OF orders FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE orders_4 PARTITION OF orders FOR VALUES WITH (MODULUS 4, REMAINDER 3);

postgres=# \d+
                                       List of relations
 Schema |             Name              |       Type        |  Owner   |    Size    | Description 
--------+-------------------------------+-------------------+----------+------------+-------------
 public | orders                        | partitioned table | postgres | 0 bytes    | 
 public | orders_1                      | table             | postgres | 8192 bytes | 
 public | orders_2                      | table             | postgres | 8192 bytes | 
 public | orders_3                      | table             | postgres | 8192 bytes | 
 public | orders_4                      | table             | postgres | 8192 bytes | 
(5 rows)

postgres=# select * from pg_inherits;
 inhrelid | inhparent | inhseqno 
----------+-----------+----------
    16664 |     16661 |        1
    16671 |     16661 |        1
    16677 |     16661 |        1
    16683 |     16661 |        1
(4 rows)

postgres=# insert into orders
select id,
       id,
	   md5(id::text)
  from generate_series(1,100000) as id;

postgres=# \d+
                                        List of relations
 Schema |             Name              |       Type        |  Owner   |    Size    | Description 
--------+-------------------------------+-------------------+----------+------------+-------------
 public | orders                        | partitioned table | postgres | 0 bytes    | 
 public | orders_1                      | table             | postgres | 2112 kB    | 
 public | orders_2                      | table             | postgres | 2096 kB    | 
 public | orders_3                      | table             | postgres | 2096 kB    | 
 public | orders_4                      | table             | postgres | 2088 kB    | 
(5 rows)

postgres=# explain select * from orders where order_id=9;
                        QUERY PLAN                         
-----------------------------------------------------------
 Seq Scan on orders_2  (cost=0.00..570.23 rows=1 width=49)
   Filter: (order_id = 9)
(2 rows)

postgres=# explain select * from orders where order_id in (9,10);
                           QUERY PLAN                            
-----------------------------------------------------------------
 Append  (cost=0.00..1138.81 rows=4 width=49)
   ->  Seq Scan on orders_2  (cost=0.00..570.23 rows=2 width=49)
         Filter: (order_id = ANY ('{9,10}'::bigint[]))
   ->  Seq Scan on orders_4  (cost=0.00..568.56 rows=2 width=49)
         Filter: (order_id = ANY ('{9,10}'::bigint[]))
(5 rows)

postgres=# explain select * from orders where order_id between 199 and 202;
                           QUERY PLAN                            
-----------------------------------------------------------------
 Append  (cost=0.00..2533.04 rows=8 width=49)
   ->  Seq Scan on orders_1  (cost=0.00..636.89 rows=2 width=49)
         Filter: ((order_id >= 199) AND (order_id <= 202))
   ->  Seq Scan on orders_2  (cost=0.00..632.67 rows=2 width=49)
         Filter: ((order_id >= 199) AND (order_id <= 202))
   ->  Seq Scan on orders_3  (cost=0.00..632.57 rows=2 width=49)
         Filter: ((order_id >= 199) AND (order_id <= 202))
   ->  Seq Scan on orders_4  (cost=0.00..630.88 rows=2 width=49)
         Filter: ((order_id >= 199) AND (order_id <= 202))
(9 rows)

multiple columns hash

multiple columns in the partition key

postgres=# CREATE TABLE orders_his (
    order_id     bigint not null,
    cust_id      bigint not null,
    status       text
) PARTITION BY HASH (order_id,cust_id);

CREATE TABLE orders_his_1 PARTITION OF orders_his FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE orders_his_2 PARTITION OF orders_his FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE orders_his_3 PARTITION OF orders_his FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE orders_his_4 PARTITION OF orders_his FOR VALUES WITH (MODULUS 4, REMAINDER 3);

postgres=# \d+
                                       List of relations
 Schema |             Name              |       Type        |  Owner   |    Size    | Description 
--------+-------------------------------+-------------------+----------+------------+-------------
 public | orders_his                    | partitioned table | postgres | 0 bytes    | 
 public | orders_his_1                  | table             | postgres | 8192 bytes | 
 public | orders_his_2                  | table             | postgres | 8192 bytes | 
 public | orders_his_3                  | table             | postgres | 8192 bytes | 
 public | orders_his_4                  | table             | postgres | 8192 bytes | 
(5 rows)

postgres=# select * from pg_inherits;
 inhrelid | inhparent | inhseqno 
----------+-----------+----------
    16696 |     16693 |        1
    16702 |     16693 |        1
    16708 |     16693 |        1
    16714 |     16693 |        1
(4 rows)

postgres=# insert into orders_his
select id,
       id,
	   md5(id::text)
  from generate_series(1,200000) as id;

postgres=# \d+
                                        List of relations
 Schema |             Name              |       Type        |  Owner   |    Size    | Description 
--------+-------------------------------+-------------------+----------+------------+-------------
 public | orders_his                    | partitioned table | postgres | 0 bytes    | 
 public | orders_his_1                  | table             | postgres | 4168 kB    | 
 public | orders_his_2                  | table             | postgres | 4144 kB    | 
 public | orders_his_3                  | table             | postgres | 4160 kB    | 
 public | orders_his_4                  | table             | postgres | 4168 kB    | 
(5 rows)

postgres=# explain select * from orders_his where order_id=9;
                              QUERY PLAN                              
----------------------------------------------------------------------
 Append  (cost=0.00..4564.02 rows=4 width=49)
   ->  Seq Scan on orders_his_1  (cost=0.00..1142.81 rows=1 width=49)
         Filter: (order_id = 9)
   ->  Seq Scan on orders_his_2  (cost=0.00..1136.95 rows=1 width=49)
         Filter: (order_id = 9)
   ->  Seq Scan on orders_his_3  (cost=0.00..1140.99 rows=1 width=49)
         Filter: (order_id = 9)
   ->  Seq Scan on orders_his_4  (cost=0.00..1143.25 rows=1 width=49)
         Filter: (order_id = 9)
(9 rows)

postgres=# explain select * from orders_his where order_id=9 and cust_id=9;
                           QUERY PLAN                           
----------------------------------------------------------------
 Seq Scan on orders_his_2  (cost=0.00..1261.54 rows=1 width=49)
   Filter: ((order_id = 9) AND (cust_id = 9))
(2 rows)

参考：
https://www.postgresql.org/docs/12/sql-createtable.html
https://www.postgresql.org/docs/12/ddl-partitioning.html