postgresql中间件pgoneproxy支持冷热数据分离查询 http://my.oschina.net/u/918218/blog/713982
在某些应用场景中，随着时间的流逝，历史数据很少被访问，主要是访问新产生的数据。这种情况下会把很少访问的数据存储到IO比较慢的存储设备上，而把长期查询的数据存放到IO比较快的存储设备上面。比如，像网上交易系统，可以把几个月前的历史数据存放到机械硬盘上面，而把当月的数据存放到固态硬盘上面。从而让成本最优的情况下，提升用户体验。
pgoneproxy目前已经支持这种冷热数据分离的情况，只要使用pgoneproxy的分库分表功能，同时把method设置为buffer即可。下面来详细介绍下冷热数据分离查询的功能：
1. 配置
当前假设把冷数据存放到bigtest_1的表中，把热数据放到bigtest_0的表中。其中假设id在0-100内的数据为热数据，而id在101-1000内的数据为冷数据，则proxy-part-tables项的配置为：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
[ { "table" : "bigtest", "pkey" : "id", "type" : "int", "method" : "buffer", "partitions": [ {"suffix":"_0", "group":"data1", "minval":"0", "maxval":"100"}, {"suffix":"_1", "group":"data1", "minval":"101","maxval":"1000"} ] } ] |
通过上面的配置可以看到通过minval和maxval来指定数据的范围。其中minval和maxval可以是数字和字符串。
如果需要以时间来进行分表操作，则可以修改pkey为时间的字段，同时设置type为time。在通过minval和maxval来指定时间的范围。
2. 查询
查询时只要带上pkey指定的字段即可。pgoneproxy会根据pkey字段的值按照不同的规则进行数据的查询。下面得到的数据情况：
|
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 |
pgbench=> select * from bigtest_0; id | name | age ----+--------+----- 10 | name10 | 10 11 | name11 | 11 12 | name12 | 12 13 | name13 | 13 14 | name14 | 14 15 | name15 | 15 16 | name16 | 16 (7 rows) pgbench=> select * from bigtest_1; id | name | age -----+---------+----- 110 | name110 | 110 111 | name111 | 111 112 | name112 | 112 113 | name113 | 113 114 | name114 | 114 115 | name115 | 115 116 | name116 | 116 (7 rows) pgbench=> select * from bigtest where id = 10; id | name | age ----+--------+----- 10 | name10 | 10 (1 row) pgbench=> select * from bigtest where id = 110; id | name | age -----+---------+----- 110 | name110 | 110 (1 row) |
也可以通过这个规则直接把数据插入到对应的表中。如果不指定id指点，则会把查询语句发送到两张表中进行查询。比如进行下面的查询操作：
|
1 2 3 4 |
pgbench=> select * from bigtest where name = 'name110'; id | name | age -----+---------+----- 110 | name110 | 110 |
这个查询就会同时查询bigtest_0和bigtest_1表中的数据，最后进行合并后返回到客户端。像上面那样指定id，就会到对应的表中查询，而不是查询两张表，从而可以提升速度。
通过上面的方法就可以解决冷热数据分离 查询的问题了。欢迎大家使用。
postgresql 数据库中间件 pgoneproxy 实现冷热数据分离查询（二） http://my.oschina.net/u/918218/blog/714395
在前一篇《postgresql中间件pgoneproxy支持冷热数据分离查询》中讲解了按照id来进行数据的分离，针对时间至少稍微的提了一下。本篇这专门针对时间来进行讲解和测试下。
在我的数据库中新建了一张表bigtest，其中字段情况如下所示：
|
1 2 3 4 5 6 7 |
Table "public.bigtest_0" Column | Type | Modifiers --------+-----------------------------+----------- id | integer | name | character varying(1024) | age | integer | tt | timestamp without time zone | |
现在按照tt字段来进行数据的分离插入和查询。下面是bigtest表的分表的配置情况：
|
1 2 3 4 5 6 7 8 9 10 11 |
{ "table" : "bigtest", "pkey" : "tt", "type" : "timestamp", "method" : "buffer", "partitions": [ {"suffix":"_0", "group":"data1", "minval":"2004-01-01 00:00:00", "maxval":"2015-01-01 00:00:00"}, {"suffix":"_1", "group":"data1", "minval":"2015-01-01 00:00:01","maxval":"2037-01-01 00:00:00"} ] } |
从上面配置可以看出，时间在2004-01-01 00:00:00~2015-01-01 00:00:00的数据存放到bigtest_0的表中，时间在2015-01-01 00:00:01 ~2037-01-01 00:00:00的数据存放到bigtest_1的表中。
在配置好pgoneproxy的proxy-part-tables选项后，启动中间件pgoneproxy。进行表的创建，插入数据，查询数据的操作，情况如下所示：
1. 创建数据库表
直接执行创表语句，pgoneproxy就会根据配置情况自动创建两张分表，情况如下所示：
|
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 |
pgbench=> \dt; List of relations Schema | Name | Type | Owner --------+------------------+-------+---------- public | pgbench_accounts | table | postgres public | pgbench_branches | table | postgres public | pgbench_history | table | postgres public | pgbench_tellers | table | postgres (4 rows) pgbench=> create table bigtest(id int, name varchar(1024), age int, tt timestamp); CREATE 0 pgbench=> \dt; List of relations Schema | Name | Type | Owner --------+------------------+-------+---------- public | bigtest_0 | table | db_user public | bigtest_1 | table | db_user public | pgbench_accounts | table | postgres public | pgbench_branches | table | postgres public | pgbench_history | table | postgres public | pgbench_tellers | table | postgres (6 rows) pgbench=> |
2. 插入数据
下面插入两条语句，看是否能够根据要求插入到不同的数据表中
|
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 |
pgbench=> select * from bigtest_0; id | name | age | tt ----+------+-----+---- (0 rows) pgbench=> select * from bigtest_1; id | name | age | tt ----+------+-----+---- (0 rows) pgbench=> insert into bigtest(id, name, age, tt) values (10, 'name10', 10, '2024-01-01 00:00:00'); INSERT 0 1 pgbench=> insert into bigtest(id, name, age, tt) values (10, 'name10', 10, '2014-01-01 00:00:00'); INSERT 0 1 pgbench=> select * from bigtest_0; id | name | age | tt ----+--------+-----+--------------------- 10 | name10 | 10 | 2014-01-01 00:00:00 (1 row) pgbench=> select * from bigtest_1; id | name | age | tt ----+--------+-----+--------------------- 10 | name10 | 10 | 2024-01-01 00:00:00 (1 row) pgbench=> |
3. 查询数据
根据各种条件进行数据查询，情况如下所示：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
pgbench=> select * from bigtest where tt < '2015-01-01 00:00:00'; id | name | age | tt ----+--------+-----+--------------------- 10 | name10 | 10 | 2014-01-01 00:00:00 (1 row) pgbench=> select * from bigtest where tt > '2015-01-01 00:00:00'; id | name | age | tt ----+--------+-----+--------------------- 10 | name10 | 10 | 2024-01-01 00:00:00 (1 row) pgbench=> select * from bigtest where tt < '2035-01-01 00:00:00'; id | name | age | tt ----+--------+-----+--------------------- 10 | name10 | 10 | 2014-01-01 00:00:00 10 | name10 | 10 | 2024-01-01 00:00:00 (2 rows) pgbench=> select * from bigtest where tt < '2035-01-01 00:00:00' and tt > '2016-01-01 00:00:00'; id | name | age | tt ----+--------+-----+--------------------- 10 | name10 | 10 | 2024-01-01 00:00:00 (1 row) |
则从上面的查询情况看，能够根据时间进行准确的查询。故pgoneproxy也能够根据时间进行冷热数据的分离存储和查询。
