postgresql 中间件pgoneproxy支持范围(range）分库分表 http://my.oschina.net/u/918218/blog/715768
今天来给大家讲解下postgresql数据库的商业中间件pgoneproxy支持范围分表的问题。这种分表的方式在很多场景中会被使用到。比如在电商，电信等行业，需要把每个月的数据存放到不同的月份，就可以使用range这个功能。
先看看针对一级分表的情况下的配置情况：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
[ { "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结尾的表中。下面讲解的例子没有以这种场景来讲解，举了用户注册信息的例子，同样按照用户编号和年份分割。配置如下所示：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
[ { "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 表，则可以得到如下所示的表：
|
1 2 3 4 5 6 7 8 |
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表的情况：
|
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 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 |
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) |
更多请访问平民软件的官方网站。
