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 語法，我想都是應運而生，簡單就是美。