PostgreSQL 建立高階分頁 API 技巧

簡單濃縮一下一些工作上建 API 經驗，以下用一個簡單的案例說明。

一個仿 Facebook 的動態文章系統假設有 3 個 Table，如下所示：

Schema

Post (文章)

pid (流水號)

message (訊息)

since (發布時間)

Comment (留言)

cid (流水號)

message (訊息)

since (發布時間)

Reply (回覆關聯)

pid (Post流水號)

cid (Comment流水號)

先不考慮 Comment 底下還可以 Reply 的狀況，假設只有一層

Post List

呈現所有文章列表應該沒有疑問，直接上語法

select * from post order by since desc offset 0 limit 10;

其中，可以透過offset與limit作為 API 的參數，除了能避免一次吐出太多 Post 外，也能做到分頁的效果。

基本範例 - 使用頁數分頁：

假設每分頁呈現 25 筆 Post

第 1 頁 SQL (offset=(1-1)*25)
select * from post order by since desc offset 0 limit 25;
第 2 頁 SQL (offset=(2-1)*25)
select * from post order by since desc offset 25 limit 25;
第 3 頁 SQL (offset=(3-1)*25)
select * from post order by since desc offset 50 limit 25;

以此類推，只要照著offset = (頁數-1)*(每頁筆數)這個規則，就可以做出能夠分頁的 API 了。

如果需要知道共有幾頁，只需要將資料總數/每分頁筆數 + 1即可：

select count(*)/25+1 from post;

每分頁的筆數，可以由前端傳送，或由後端決定。若由前端傳送則要注意檢查最大筆數，以免遭到惡意攻擊。

進階範例 - 先驗分頁：

上面的作法，已經有了基本的樣子。但是如果遇到以下這種變形的 case 怎麼辦呢?

每頁 25筆 Post，給我 Post (pid=251) 那一頁的 Post，並且要可以上翻頁或下翻頁

這個案例實際的情況會用在哪裡? 常見的就是 web RESTful 的設計的需求， 這種請求通常只會給一個 Post(pid) 然後畫面必須呈現該 Post 與其上下相鄰的 Posts。該怎麼做 ?


首先，必須先預測所有 Post 所在頁數，做成 View 表，但前提是每分頁的筆數需要故定。

create or replace view v_post as
select *,row_number() over(order by since desc) as p
from post order by since desc;

其中row_number()是一個 PostgreSQL 的 Window function，顧名思義就是取得 output 的所有資料中，每個 row 自己是第幾筆資料，後面一定要接over(order by ...)來告訴它該依照什麼排序來編號。

由於row_number會從 1 開始編號，因此透過(row_number-1)/每頁數量+1的計算，就可以知道該筆資料所在的頁數了。將 View 改成這樣：

create or replace view v_post as
select *,(row_number() over(order by since desc)-1)/25+1 as p
from post order by since desc;

有了這個頁數後，透過一個 subquery 就可以得到需要的效果了

select * from v_post where p = ( select p from v_post where pid=251 )

前端人員此時就可以透過目前的頁數p來進行上翻頁或下翻頁，此時也只需要這個 View 就可以取出翻頁後的資料。

Comment List

承上的先驗分頁技巧，在"取得某個 Post 的 Comments "情況下無法預先產生 View 來計算頁數，因為pid是從前端動態傳入的。

select r.pid,c.* from reply as r,comment as c
where r.cid=c.cid where r.pid=??


範例 - function 取代 View：

但是藉由 PostgreSQL 靈活的 function 功能照樣達到這個需求。

create or replace function fn_comment(_pid int)
returns table(pid int,cid int,message varchar,since timestamp,p bigint) as $$
begin

  return query
    select r.pid,c.cid,c.message,c.since,row_number() over(order by c.since desc) as p
    from replay r,comment c
    where r.cid=c.cid and r.pid=_pid;

end;
$$ language plpgsql;


上面這個 function 就可以動態產生類似於 View 的效果，但唯一不同的是它可以等到Post(pid)傳入後，再去抓取其Comment資料。

再透過上個段落的取得頁數方法，將 function 改成這樣：

create or replace function fn_comment(_pid int_)
returns table(pid int,cid int,message varchar,since timestamp,p bigint) as $$
begin

  return query
    select r.pid,c.cid,c.message,c.since,(row_number() over(order by c.since desc)-1)/25+1 as p
    from replay r,comment c
    where r.cid=c.cid and r.pid=_pid
    order by since desc;

end;
$$ language plpgsql;


如此一來，透過呼叫fn_comment就可以取得某個 Post 下的某一分頁所有 Comments，就跟一般的 View 用法完全一樣呢！

select * from fn_comment(251) where p=2



範例 - 透過 Comment(cid) 取得該頁資料：

透過剛才的fn_comment就可以藉由Comment(cid)取得該頁的資料，且也能夠提供前端人員進一步取得前後翻頁的資料。

select * from fn_comment(251) where p=( select p from fn_comment(251) where cid=999 );



結論

上面列舉的案例，僅用3張Table與簡單的關聯來說明，目的是希望聚焦在 API 批次取資料這件事情上面，但並非唯一的作法。

透過 View 表建立預測頁數的作法其實每次的 request 都在消耗電腦的運算，較好的做法應該是在 Reply Table 就預先建立頁數的資料或其他 cache 的策略，不僅加速 response 效能外，也能減少 server 的負擔。


以上就是對資料進行分頁技巧的一些經驗分享，
提供給需要跟前端合作的後端 Developers。