PostgreSQL update 的探討

前言

大家都熟知幾個資料庫操作的語法,select,insert,delete與update.
我們來看這幾個語法,先由 update 來入手.

觀察一下 update 之後 table的使用空間

先建立一個 table, 指定不做 autovacuum.

create table t231124a (
  id int primary key
, val int
) with (autovacuum_enabled=false
, toast.autovacuum_enabled=false);

insert into t231124a values
(1, 1);

-- 一筆資料, 起跳 8K 
select pg_size_pretty(pg_table_size('t231124a'));
 pg_size_pretty 
----------------
 8192 bytes 

-- 建立 update x次 的 sp
create or replace procedure sp231124a(in x int)
language plpgsql
as $$
declare i int;
begin
  for i in 1..x loop
    update t231124a
       set val = i
     where id = 1;
  end loop;
  commit;
end $$;

--- update 1000 次
call sp231124a(1000);

commit;

select pg_size_pretty(pg_table_size('t231124a'));

 pg_size_pretty 
----------------
 64 kB
 
commit;

-- 變 64K了

-- 使用 vacuum 清理
vacuum full t231124a;

select pg_size_pretty(pg_table_size('t231124a'));

 pg_size_pretty 
----------------
 8192 bytes

-- 回到 8k

使用 pg_dirtyread 來觀察

我們可以使用 pg_dirtyread 來觀察,還沒做 vacuum 時的情況.

https://github.com/df7cb/pg_dirtyread

git clone https://github.com/df7cb/pg_dirtyread.git
 
make

sudo make install

安裝之後的操作

create extension pg_dirtyread;

create table t231222a (
  id int
, bar text
, ts timestamptz default clock_timestamp()
) with (autovacuum_enabled = false
, toast.autovacuum_enabled = false);

insert into t231222a(id, bar) values
(1, 'SONE-009');

update t231222a
   set bar = 'SSIS-911'
     , ts = clock_timestamp()
 where id = 1;

update t231222a
   set bar = 'SSIS-931'
     , ts = clock_timestamp()
 where id = 1;
 
delete
  from t231222a
 where id = 1;

insert into t231222a(id, bar) values
(1, 'SSIS-894');


-- 一定要 select 一次, 讓 pg_dirtyread 可以更新資訓
select *
  from t231222a;

 id |   bar    |              ts               
----+----------+-------------------------------
  1 | SSIS-894 | 2023-12-22 15:29:16.473379+08
(1 row)

--
select * 
  from pg_dirtyread('t231222a') 
       as t(ctid tid, xmin xid, xmax xid, dead boolean,
           id int, bar text, ts timestamptz);

 ctid  | xmin | xmax | dead | id |   bar    |              ts               
-------+------+------+------+----+----------+-------------------------------
 (0,1) | 2068 | 2070 | t    |  1 | SONE-009 | 2023-12-22 15:28:36.281726+08
 (0,2) | 2070 | 2072 | t    |  1 | SSIS-911 | 2023-12-22 15:28:47.288191+08
 (0,3) | 2072 | 2074 | t    |  1 | SSIS-931 | 2023-12-22 15:28:57.183544+08
 (0,4) | 2076 |    0 | f    |  1 | SSIS-894 | 2023-12-22 15:29:16.473379+08
(4 rows)

vacuum full t231222a;

select * 
  from pg_dirtyread('t231222a') 
       as t(ctid tid, xmin xid, xmax xid, dead boolean,
           id int, bar text, ts timestamptz);

 ctid  | xmin | xmax | dead | id |   bar    |              ts               
-------+------+------+------+----+----------+-------------------------------
 (0,1) | 2076 |    0 | f    |  1 | SSIS-894 | 2023-12-22 15:29:16.473379+08
(1 row)

說明

可以觀察到在做 vacumm 之前, 做了兩次 update, 做了一次 delete,最後做 delete.
第一次 update, 將 SONE-009, 變更為 SSIS-911, 並變更 timestamp.
第二次 update, 將 SSIS-911, 變更為 SSIS-931, 並變更timestamp.
可以看到,ctid xmin, max 的變化, dead 標誌為 true.
接著兩個動作, delete 將 SSIS-931 那筆標誌為 dead.
insert 輸入最新的 SSIS-894, 以及 timestamp.

我們可以把 delete 的動作,理解成做查詢(select), 其實語法是幾乎一樣,除了沒有 select 欄位,找到將後dead 標誌為 true.
insert 就是 insert 了.
update 就可以理解為 delete 與 insert 的組合.
這樣就能夠理解那段做1000次 update , table的使用空間是 64K.
因為其實就是做了1000次 delete, insert.

結語

透過理解指令的內部動作,對後續我們開發系統時,能夠更深入的思考規劃.