4

# 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;
``````

## 基本方法的探討

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)
``````

## 擴展紀錄空間的探討

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

## 摻在一起吧!

``````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"}}

``````

``````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"} 已經被推出去了!
``````

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 結合運用,可以擴展使用彈性.進而減少對table的查詢,提高整體的效能.

### 1 則留言

0

iT邦大神 1 級 ‧ 2021-04-06 09:26:27