postgresql 数据库中间件 pgoneproxy 实现冷热数据分离查询（二）

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

    在前一篇《postgresql中间件pgoneproxy支持冷热数据分离查询》中讲解了按照id来进行数据的分离，针对时间至少稍微的提了一下。本篇这专门针对时间来进行讲解和测试下。

    在我的数据库中新建了一张表bigtest，其中字段情况如下所示：

            Table "public.bigtest_0"
 Column |            Type             | Modifiers 
--------+-----------------------------+-----------
 id     | integer                     | 
 name   | character varying(1024)     | 
 age    | integer                     | 
 tt     | timestamp without time zone | 

现在按照tt字段来进行数据的分离插入和查询。下面是bigtest表的分表的配置情况：

{
        "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就会根据配置情况自动创建两张分表，情况如下所示：

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. 插入数据

下面插入两条语句，看是否能够根据要求插入到不同的数据表中

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. 查询数据

根据各种条件进行数据查询，情况如下所示：

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也能够根据时间进行冷热数据的分离存储和查询。

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