以Postgresql为主,再聊聊资料库 PostgreSQL last N in-table cache 探讨

PostgreSQL last N in-table cache 探讨

前些天对悠游卡储值时,加值机有提供一个最近六次的纪录查询功能.
类似这种功能,在电商查询最近消费纪录等等,都带给消费者便利.
进而想以这个功能来做以下的探讨.

建立测试资料

create table mrt_cstmr (
  cardid text not null
);

insert into mrt_cstmr values
('A123'),('A456'),('A789');


create table mrt_tap_dtl (
  cardid text not null
, ts timestamp not null
, stname text not null
, primary key (cardid, ts)
);

insert into mrt_tap_dtl
select c.cardid
     , '2021-01-01 06:00:00'::timestamp 
     + interval '1 day' * n 
     + interval '1 minute' * ceil(random() * 1080)::int
     , (array['新店','七张', '景美', '万隆'
        , '公馆', '古亭', '西门', '北门'
        , '中山', '松山', '象山', '大安'
        ]::text[])[ceil(random() * 12)]
  from mrt_cstmr c
     , generate_series(1, 8) as g(n);

使用 Window function 来取出最後 6 笔

-- 取最後六笔
select *
  from (select *
             , rank() over(partition by cardid order by ts desc) as rnk
          from mrt_tap_dtl) a
 where rnk <= 6;

-- 查询计画可用以下语法
explain (analyze, verbose, costs, buffers, timing, summary)
select *
  from (select *
             , rank() over(partition by cardid order by ts desc) as rnk
          from mrt_tap_dtl) a
 where rnk <= 6;

--上面是很标准的步骤,单一使用查询时语法如下

explain (analyze, verbose, costs, buffers, timing, summary)
select *
  from (select *
             , rank() over(partition by cardid order by ts desc) as rnk
          from mrt_tap_dtl
         where cardid = 'A123') a
 where rnk <= 6;

基本方法的探讨

需要进行排序(sort).这需要消耗资源,增加执行的时间,随着资料量增大, 消耗的资源会相对增加.
为了控制资源消耗,保持适当的反应速度,常见的方式可将历史资料移到另外的table 或是 database,线上的table 维持一季度或是数个月的资料.
但是消费者上次消费或操作可能是去年或更早,若能够将最近N笔的纪录,适当保留,类似 cache 的概念,可以提供更好的服务.甚至可以减少对纪录表(如上面例子中 mrt_tap_dtl)的查询, 纪录表只做insert , 进而减少 lock 等资源消耗.

为了要做此类似 cache 的功能,我们需要建立另一个 table, 将每个卡号的最後消费或操作纪录保存.先做最後一笔,後面再来进化.
这时候有两种常用的方式可以选择,一种是 insert 到记录表时,也对 cache 表做相关操作.
因为 cache 表只保留最新的,所以当cardid 是第一次进入 cache表时,要做 insert,後续操作为 update.
这样的方式是直觉,但是每个对纪录表做 insert 的程序,都需要确保进行两段操作,若有程序遗漏对 cache 表操作,就容易产生不一致.
另一种方式是使用trigger.由资料库的机制来确保,避免遗漏.

至於insert 与 update 结合的语法,在不同资料库,语法略有差异.

MySQL 部分可以参考
https://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html

PostgreSQL
https://docs.postgresql.tw/reference/sql-commands/insert

里面的 ON CONFLICT Clause

https://docs.postgresql.tw/reference/sql-commands/insert#on-conflict-clause

使用 trigger 来纪录最後一次操作

--先建立 cache 用途的 table

create table mrt_tap_cach (
  cardid text not null primary key
, last_stname text not null
, last_ts timestamp not null
);

--接着来写 trigger function

create or replace function mrt_tap_dtl_insert()
returns trigger
language plpgsql
as $$
begin
  insert into mrt_tap_cach(cardid, last_stname, last_ts)
  values (new.cardid, new.stname, new.ts)
  on conflict (cardid) do update
  set last_stname = new.stname
    , last_ts = new.ts
  ;
  return new;
end;
$$;


--接着建立 trigger

create trigger mrt_tap_dtl_insert_trig
after insert on mrt_tap_dtl
for each row
execute procedure mrt_tap_dtl_insert();

接着来做测试

insert into mrt_tap_dtl
select c.cardid
     , '2021-02-01 06:00:00'::timestamp 
     + interval '1 day' * n 
     + interval '1 minute' * ceil(random() * 1080)::int
     , (array['新店','七张', '景美', '万隆'
        , '公馆', '古亭', '西门', '北门'
        , '中山', '松山', '象山', '大安'
        ]::text[])[ceil(random() * 12)]
  from mrt_cstmr c
     , generate_series(1, 3) as g(n); 

--这样会输入 9 笔.(3个卡号,每个 3 笔).

--先来看 mrt_tap_dtl,里面新的资料

select *
  from (select *
             , rank() over(partition by cardid order by ts desc) as rnk
          from mrt_tap_dtl) a
 where rnk <= 3;

 cardid |         ts          | stname | rnk
--------+---------------------+--------+-----
 A123   | 2021-02-04 12:59:00 | 古亭   |   1
 A123   | 2021-02-03 14:54:00 | 公馆   |   2
 A123   | 2021-02-02 08:34:00 | 七张   |   3
 A456   | 2021-02-04 20:37:00 | 七张   |   1
 A456   | 2021-02-03 14:50:00 | 中山   |   2
 A456   | 2021-02-02 23:21:00 | 象山   |   3
 A789   | 2021-02-04 10:15:00 | 公馆   |   1
 A789   | 2021-02-03 15:06:00 | 古亭   |   2
 A789   | 2021-02-02 20:21:00 | 北门   |   3
(9 rows)

--再来看 mrt_tap_cach 保留的最後操作资料.

select *
  from mrt_tap_cach;

 cardid | last_stname |       last_ts
--------+-------------+---------------------
 A123   | 古亭        | 2021-02-04 12:59:00
 A456   | 七张        | 2021-02-04 20:37:00
 A789   | 公馆        | 2021-02-04 10:15:00
(3 rows)

有效的纪录了!
至此并无太多新的技术,保留最後一笔的功能 trigger 也是属於广泛应用.

扩展纪录空间的探讨

接着来思考一下,怎样保留最後 N 笔,在此先以 6 笔为例.
目前在 mrt_tap_cach 中,除了卡号,我们建立了两个栏位,保留站名与时间戳.
若要保留6笔,是否再建立 10 个栏位,也就是 5 组, 但是怎样做推陈出新 ??
这类操作对 SQL 语法来说并不自然.
是否有什麽好的方式,提供一个资料结构,来做推陈出新.

首先我们来看 PostgreSQL 是有 array 的资料型态.
可以设法使用 array 来存放,再做推陈出新的操作.
另外我们也知道 PostgreSQL可以使用 PL/Python.
可以用 PL/Python 来写 function 与 trigger.
透过 array 可以将资料传给 PL/Python 的函数, 再利用 Python 处理 List
强大的功能.看来似乎有点眉目了.
另外查找 Python 的函数库, 在 collections 中有一个 deque

https://docs.python.org/zh-tw/3/library/collections.html#collections.deque

搭配使用,就可以很方便的做限制长度,而且有推陈出新的功能,无需重新造轮子.相关搭配技术似乎找齐了.

掺在一起吧!

首先我们将 mrt_tap_cach 增加一个 text[] ,也就是text型态的 array .

alter table mrt_tap_cach
add column last_n text[];

--接着来写 trigger function. 因为 mrt_tap_dtl_insert_trig 呼叫的
--mrt_tap_dtl_insert() 使用了 insert on conflict, 实际上会有
--insert 与 update 两种操作.

create or replace function mrt_tap_cach_insert_update()
returns trigger
language plpython3u
as $$
  from collections import deque
  d = deque(maxlen = 6)
  if TD['new']['last_n'] != None:
    for elem in TD['new']['last_n']:
      d.append(elem)
  d.appendleft([TD['new']['last_stname'], TD['new']['last_ts']])
  TD['new']['last_n'] = list(d)
  rv = 'MODIFY'
  return rv
$$;

--建立两个 trigger

create trigger mrt_tap_cach_insert_trig
before insert on mrt_tap_cach
for each row
execute procedure mrt_tap_cach_insert_update();

create trigger mrt_tap_cach_update_trig
before update on mrt_tap_cach
for each row
execute procedure mrt_tap_cach_insert_update();

测试!

--先将 mrt_tap_dtl 与 mrt_tap_cach 的资料 truncate 

truncate table mrt_tap_dtl;
truncate table mrt_tap_cach;

insert into mrt_tap_dtl
select c.cardid
     , '2021-03-01 06:00:00'::timestamp 
     + interval '1 day' * n 
     + interval '1 minute' * ceil(random() * 1080)::int
     , (array['新店','七张', '景美', '万隆'
        , '公馆', '古亭', '西门', '北门'
        , '中山', '松山', '象山', '大安'
        ]::text[])[ceil(random() * 12)]
  from mrt_cstmr c
     , generate_series(1, 3) as g(n); 
     
--来看一下 mrt_tap_cach 的内容

select *
  from mrt_tap_cach;

-[ RECORD 1 ]-----------------------------------------------------------------------------------------
cardid      | A123
last_stname | 北门
last_ts     | 2021-03-04 20:34:00
last_n      | {{北门,"2021-03-04 20:34:00"},{中山,"2021-03-03 20:47:00"},{七张,"2021-03-02 13:53:00"}}
-[ RECORD 2 ]-----------------------------------------------------------------------------------------
cardid      | A456
last_stname | 新店
last_ts     | 2021-03-04 19:20:00
last_n      | {{新店,"2021-03-04 19:20:00"},{万隆,"2021-03-03 13:23:00"},{公馆,"2021-03-02 11:16:00"}}
-[ RECORD 3 ]-----------------------------------------------------------------------------------------
cardid      | A789
last_stname | 北门
last_ts     | 2021-03-04 17:41:00
last_n      | {{北门,"2021-03-04 17:41:00"},{新店,"2021-03-03 06:30:00"},{万隆,"2021-03-02 23:31:00"}}

如同预期,再来每个 cardid 增加 4 笔,观察长度限制.

insert into mrt_tap_dtl
select c.cardid
     , '2021-03-04 06:00:00'::timestamp 
     + interval '1 day' * n 
     + interval '1 minute' * ceil(random() * 1080)::int
     , (array['新店','七张', '景美', '万隆'
        , '公馆', '古亭', '西门', '北门'
        , '中山', '松山', '象山', '大安'
        ]::text[])[ceil(random() * 12)]
  from mrt_cstmr c
     , generate_series(1, 4) as g(n); 
     
select *
  from mrt_tap_cach;
  
...
-[ RECORD 3 ]--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
cardid      | A789
last_stname | 北门
last_ts     | 2021-03-08 23:47:00
last_n      | {{北门,"2021-03-08 23:47:00"},{象山,"2021-03-07 15:31:00"},{新店,"2021-03-06 20:52:00"},{大安,"2021-03-05 15:17:00"},{北门,"2021-03-04 17:41:00"},{新店,"2021-03-03 06:30:00"}}

--保留到 {新店,"2021-03-03 06:30:00"} 而 {万隆,"2021-03-02 23:31:00"} 已经被推出去了!

至此已经达到保留最後 6 笔的操作纪录的功能.若想要增加保留的笔数,只要修改 mrt_tap_cach_insert_update()
trigger function 即可,也不需要重新建立trigger,也不用修改 table, 影响极小.

create or replace function mrt_tap_cach_insert_update()
returns trigger
language plpython3u
as $$
  from collections import deque
  d = deque(maxlen = 8)
  if TD['new']['last_n'] != None:
    for elem in TD['new']['last_n']:
      d.append(elem)
  d.appendleft([TD['new']['last_stname'], TD['new']['last_ts']])
  TD['new']['last_n'] = list(d)
  rv = 'MODIFY'
  return rv
$$;


insert into mrt_tap_dtl
select c.cardid
     , '2021-03-15 06:00:00'::timestamp 
     + interval '1 day' * n 
     + interval '1 minute' * ceil(random() * 1080)::int
     , (array['新店','七张', '景美', '万隆'
        , '公馆', '古亭', '西门', '北门'
        , '中山', '松山', '象山', '大安'
        ]::text[])[ceil(random() * 12)]
  from mrt_cstmr c
     , generate_series(1, 9) as g(n); 
     
select *
  from mrt_tap_cach;

...
-[ RECORD 3 ]-----------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------
cardid      | A789
last_stname | 象山
last_ts     | 2021-03-24 13:18:00
last_n      | {{象山,"2021-03-24 13:18:00"},{象山,"2021-03-23 16:40:00"},{西门,"2021-03-22 13:41:00"},{北门,"2021-03-21 21:08:00"},{古亭,"2021-03-20 07:44:00"},{公馆,"2021-03-19 13:11:00"},{景美,"2021-03-18 17:26:00"},{大安,"2021-03-17 09:10:00"}}

以上是综合运用了 trigger , plpython, array 等功能,来达到一个简单轻量的 last n in-table cache 功能.

结语

trigger 或是 plpython 结合运用,可以扩展使用弹性.进而减少对table的查询,提高整体的效能.

<<: 深度学习常用程序码

>>: {CMoney战斗营} 的第六周 # 游戏模组套用

以Postgresql为主,再聊聊资料库 PostgreSQL last N in-table cache 探讨

PostgreSQL last N in-table cache 探讨

建立测试资料

使用 Window function 来取出最後 6 笔

基本方法的探讨

使用 trigger 来纪录最後一次操作

扩展纪录空间的探讨

掺在一起吧!

结语

计算机概论 - 程序语言 Programming Languages

Multiple objects (下)

[Day19] SCSS 学习笔记

版控图

Day38 ( 游戏设计 ) 狙击气球 ( 两倍变焦 )

Day 8 MITRE ATT&CK for ICS

【Day 15】- 今天来实作一个 Kernel mode Thread

大脑如何精准学习 (1) 注意力

15.unity变数传递(下)场景间数据传递

【5】超参数 Batch size 与 Learning rate 的关系实验