PostgreSQL 務實應用（三/5）分表複制

問題的提出

在項目中，有些表的記錄增長非常快，記錄數過大時會使得查詢變得困難，導緻整個資料庫處理性能下降。此時，我們會考慮按一定的規則進行分表存儲。

常用的分表方式是按時間周期，如每月一張，每天一張等。當每月或每天首條記錄到達時，根據表結建構立該周期為字尾的表進行存儲。

相關考慮

這其中主要考慮兩個問題：

（1）如何複制表

采用分表機制，通常會建立一個模闆表。所謂模闆表，是隻定義結構不存儲資料的，也可稱之為類表，而分表，通常會以增加字尾的方式命名，如 log_201901，分表實際存儲資料，可稱之為執行個體表。

表存在關聯、鍵、索引、限制等，這讓表的複制聽起來比較繁瑣，即便通過中繼資料得到這些資訊，還需要自己考慮如索引的命名沖突等問題。而 PostgreSQL 為我們提供了極其便捷的方式。

CREATE TABLE [IF NOT EXISTS] 執行個體表 (LIKE 模闆表 [INCLUDING ALL]);
           

其中 [ ] 内表示可選項，INCLUDING 除了 ALL 還要其它細分的選項，具體可參考幫助文檔。

（2）資料存儲的邏輯過程

首先得知道表存不存在，不存在則要建立，然後執行資料操作語句。

由于表名是動态的，在應用系統中可以先取得表名形成 SQL 語句再執行，在資料庫存儲過程中則可以使用 EXECUTE 執行動态SQL語句。

分表執行個體

下邊，本文以日志記錄表為例來完整地實踐分表處理過程。

功能描述：日志數量大，目前日志查詢頻繁，曆史日志需要全部儲存。要求每天一個分表，日志主鍵要求全局保持唯一性（即多個分表間不重複），日志到達自動根據目前的時間進行分表存儲。

首先建立日志模闆表，命名為 log_template，并為其建立相關索引，主鍵序列。

-- 建立模闆表，log_id 主鍵，log_at 日志時間， log_content 日志内容
CREATE TABLE log_template (log_id bigint PRIMARY KEY, log_at timestamp, log_content varchar(1000));
-- 對日志時間索引
CREATE INDEX idx_log_at on log_template (log_at);
-- 用于主鍵的序列（各分表使用同一序列）
CREATE SEQUENCE seq_log_id;
           

我們通過一個過程來完成日志的自動分表存儲。

CREATE OR REPLACE FUNCTION func_log(v_conent varchar) RETURNS bool LANGUAGE 'plpgsql'
AS $$
DECLARE
    lv_log_at timestamp := current_timestamp;
    lv_suffix_tname varchar; -- 帶字尾的分表名
    lv_dsql text;            -- 動态SQL
BEGIN
    -- 根據時間得到應使用的分表名稱
    lv_suffix_tname := 'log_' || to_char(lv_log_at, 'YYYYMMDD');
    
    -- 判斷是否存在，不存在時複制模闆建立分表
    lv_dsql := 'CREATE TABLE IF NOT EXISTS ' || lv_suffix_tname || ' (LIKE log_template INCLUDING ALL)';
    EXECUTE lv_dsql;  
    
    -- 将資料儲存至分表
    lv_dsql := 'INSERT INTO ' || lv_suffix_tname || '(log_id, log_at, log_content) VALUES($1, $2, $3)';
    EXECUTE lv_dsql USING nextval('seq_log_id'), lv_log_at, v_conent;
    
    RETURN true;
END $$;
           

執行以下語句來看看預期的結果。

SELECT func_log('hello, the first log!');
SELECT func_log('toady is a nice day!');
SELECT func_log('每天都有新的開始，不再擔心爆表!');
           

結束語

分表能夠避免單表記錄過于龐大，提高查詢性能。但同時，分表也會給部分查詢或資料處理帶有複雜性，是以是否分表應該根據業務需要來，同時應盡早規劃，後期更改相對繁瑣。

在 MySQL 中也有類似的 CREATE TABLE LIKE 文法，我想都是應運而生，簡單就是美。