mysql的冷热分离_postgresql 数据库中间件 pgoneproxy 实现冷热数据分离查询（二）...

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