postgresql 中间件pgoneproxy支持范围(range）分库分表

2019独角兽企业重金招聘Python工程师标准>>>

    今天来给大家讲解下postgresql数据库的商业中间件pgoneproxy支持范围分表的问题。这种分表的方式在很多场景中会被使用到。比如在电商，电信等行业，需要把每个月的数据存放到不同的月份，就可以使用range这个功能。

    先看看针对一级分表的情况下的配置情况：

[
	{
        "table":"trange",
        "pkey" : "id",
        "type" : "int",
        "method": "range",
        "partitions":
        [
           {"suffix":"_100", "value": "100", "group":"data1"}, 
           {"suffix":"_1000", "value":"1000", "group":"data1"}
        ]  

  }
]

上面配置的含义是：id<100的存放到trange_100这张表中，id >=100并且id < 1000的数据存放到trange_1000的表中。

如果需要根据时间来配置范围，则需要修改type为timestamp。再修改value为对应的时间即可。

在很多场景中需要二级分表，比如在电商行业中，需要把用户编号为0结尾的用户在7月份产生的订单放到以_0_201607结尾的表中。下面讲解的例子没有以这种场景来讲解，举了用户注册信息的例子，同样按照用户编号和年份分割。配置如下所示：

[
  {
        "table" :"bigrange",
        "pkey"  : "id",
        "type"  : "int",
        "method" : "crc32",
        "partitions" :
         [
            {"suffix":"_0", "group":"data1"},
            {"suffix":"_1", "group":"data1"}
         ],
        "subpkey":"tt",
        "subtype":"timestamp",
        "submethod":"range",
        "subpartitions":
        [
           {"suffix":"_2015", "value":"2015-01-01 00:00:00"},
           {"suffix":"_2016", "value":"2016-01-01 00:00:01"}
        ]
  }
]

通过上面的配置来创建bigrange 表，则可以得到如下所示的表：

pgbench=> \dt;
              List of relations
 Schema |       Name       | Type  |  Owner   
--------+------------------+-------+----------
 public | bigrange_0_2015  | table | db_user
 public | bigrange_0_2016  | table | db_user
 public | bigrange_1_2015  | table | db_user
 public | bigrange_1_2016  | table | db_user

    根据配置是需要把在2015-01-01 00:00:00以前的用户放到bigrange_0_2015或者bigrang_1_2015的两张表中。在2015-01-01 00:00:01~2016-01-01 00:00:01注册的用户存放到bigrange_0_2016, bigrange_1_2016的两张表中。

下面是简单的操作bigrange表的情况：

pgbench=> create table bigrange(id int, name varchar(32), age int, tt timestamp);
CREATE 0
pgbench=> insert into bigrange(id, name, age, tt) values(0, 'name0', 100, '2013-01-01 00:00:01');
INSERT 0 1
pgbench=> insert into bigrange(id, name, age, tt) values(1, 'name1', 100, '2014-01-01 00:00:01');
INSERT 0 1
pgbench=> insert into bigrange(id, name, age, tt) values(2, 'name2', 102, '2015-01-01 00:00:00');
INSERT 0 1
pgbench=> insert into bigrange(id, name, age, tt) values(3, 'name3', 103, '2016-01-01 00:00:00');
INSERT 0 1
pgbench=> insert into bigrange(id, name, age, tt) values(4, 'name4', 104, '2017-01-01 00:00:00');
ERROR:  Partitioned tables should choose at least one partition!
pgbench=> insert into bigrange(id, name, age, tt) values(4, 'name4', 104, '2015-01-01 03:00:00');
INSERT 0 1
pgbench=> select * from bigrang_0_2015;
ERROR:  relation "bigrang_0_2015" does not exist
LINE 1: select * from bigrang_0_2015;
                      ^
pgbench=> \dt;
              List of relations
 Schema |       Name       | Type  |  Owner   
--------+------------------+-------+----------
 public | bigrange_0_2015  | table | db_user
 public | bigrange_0_2016  | table | db_user
 public | bigrange_1_2015  | table | db_user
 public | bigrange_1_2016  | table | db_user
(4 rows)

pgbench=> select * from bigrange_0_2015;
 id | name  | age |         tt          
----+-------+-----+---------------------
  0 | name0 | 100 | 2013-01-01 00:00:01
(1 row)

pgbench=> select * from bigrange_1_2015;
 id | name  | age |         tt          
----+-------+-----+---------------------
  1 | name1 | 100 | 2014-01-01 00:00:01
(1 row)

pgbench=> select * from bigrange_0_2016;
 id | name  | age |         tt          
----+-------+-----+---------------------
  2 | name2 | 102 | 2015-01-01 00:00:00
  4 | name4 | 104 | 2015-01-01 03:00:00
(2 rows)

pgbench=> select * from bigrange_1_2016;
 id | name  | age |         tt          
----+-------+-----+---------------------
  3 | name3 | 103 | 2016-01-01 00:00:00
(1 row)

更多请访问平民软件的官方网站。

转载于:https://my.oschina.net/u/918218/blog/715768