2017年2月5日 星期日

PostgreSQL 表格分割法

目標設定:
  • 利用 PostgreSQL 繼承功能,分割使用中的表格

快速操作流程
  1. 先轉成 postgres 使用者:
    #su - postgres
    
  2. 利用 psql 指令,連入資料庫:
    $psql
    postgres=# \c btrecord
    btrecord=#
    
  3. 複刻一個與原來表格相同的主表格:
    btrecord=# CREATE TABLE Trecords (like Orecords including all);
    
  4. 查看已複刻的表格:
    btrecord=#\d Trecords
    
  5. 建立一個繼承主表格的子表格:
    btrecord=# CREATE TABLE score_2017 (like Trecords including all) INHERITS (Trecords);
    
  6. 設定子表格的約束,只同意某一年份的資料寫入:
    btrecord=# ALTER TABLE score_2017 add constraint partition_check CHECK ("score_time" >= '2017-01-01'::date AND "score_time" < '2018-01-01'::date);
    
  7. 使用觸發程序來分類資料,寫入子表格內:
    btrecord=# CREATE OR REPLACE FUNCTION score_record_trigger()
    RETURNS TRIGGER AS $$
    BEGIN
    IF ( NEW.'score_time'>= '2017-01-01'::date AND NEW.'score_time' < '2018-01-01'::date) THEN
    INSERT INTO score_2017 VALUES (NEW.*);
    ELSE
    RAISE EXCEPTION 'Date out of range. Fix the score_record_trigger() function';
    END IF;
    RETURN NULL;
    END;
    $$
    LANGUAGE plpgsql;
    
    CREATE TRIGGER score_record_trigger
    BEFORE INSERT ON Trecords
    FOR EACH ROW EXECUTE PROCEDURE score_record_trigger();
    
  8. 將原來的資料表內容,寫入主表格:
    btrecord=#INSERT INTO Trecords (select * from Orecords); 
  9. 若要刪除子表格,可直接刪除,不影響主表格:
    btrecord=#DROP TABLE Trecords; 
  10. 若要刪除主表格,不可直接刪除!但追加 CASCADE 則可將所有表格刪除:
    btrecord=#DROP TABLE Trecords CASCADE; 
參考文獻:
  • https://www.depesz.com/2015/06/07/partitioning-what-why-how/