MathJax

MathJax-2

MathJax-3

Google Code Prettify

置頂入手筆記

EnterproseDB Quickstart — 快速入門筆記

由於考慮採用 EnterpriseDB 或是直接用 PostgreSQL 的人，通常需要一些入手的資料。這邊紀錄便提供相關快速上手的簡單筆記 ～ 這篇筆記以 資料庫安裝完畢後的快速使用 為目標，基本紀錄登入使用的範例：

2017年6月14日 星期三

PostgreSQL 9.x 原生的 Partitioned Table 設置

第十版的 Postgres 將引進原生的 Partitioned Table 語法。所以要快點把舊的 Partitioned Table 設置筆記快快貼出來～～

當然，這些語法在 PGSQL 10 也還是能用的！此外，由於 PGSQL 10 雖然在 postgres_fdw 提供 Aggregation Pushdown，但針對 Partitioned Table 的外部小資料表（Foreign Partitions）還沒有 Aggregation Pushdown 的執行計畫，因此像這樣 DIY 的 Partitioned Table 在 PGSQL 10 還是有他的一點用途的～

最後提醒一下，EDB 的 Postgres 企業版也有針對 Oracle 相容的 Partitioned Table 語法；就目前（2017 年六月）的資料看來，企業版還是功能還是比較多一些。

以下操作範例是從參考資料借過來用的。
建立大資料表
postgres=# CREATE TABLE users(
 id          SERIAL PRIMARY KEY,
 username    TEXT NOT NULL UNIQUE,
 password    TEXT,
 created_on  TIMESTAMPTZ NOT NULL,
 last_logged_on TIMESTAMPTZ NOT NULL
);

建立小資料表的方式有兩種：
建立小資料表（1） 
postgres=# CREATE TABLE users_1 (
 LIKE users INCLUDING ALL,
 CONSTRAINT partition_check CHECK (id >= 0 AND id < 100000)
 ) INHERITS (users);
CREATE TABLE users_2 (
 LIKE users INCLUDING ALL,
 CONSTRAINT partition_check CHECK (id >= 100000 AND id < 200000)
 ) INHERITS (users);
CREATE TABLE users_3 (
 LIKE users INCLUDING ALL,
 CONSTRAINT partition_check CHECK (id >= 200000 AND id < 300000)
 ) INHERITS (users);
CREATE TABLE users_4 (
 LIKE users INCLUDING ALL,
 CONSTRAINT partition_check CHECK (id >= 300000 AND id < 400000)
 ) INHERITS (users);
CREATE TABLE users_5 (
 LIKE users INCLUDING ALL,
 CONSTRAINT partition_check CHECK (id >= 400000 AND id < 500000)
 ) INHERITS (users);
CREATE TABLE users_6 (
 LIKE users INCLUDING ALL,
 CONSTRAINT partition_check CHECK (id >= 500000 AND id < 600000)
 ) INHERITS (users);
CREATE TABLE users_7 (
 LIKE users INCLUDING ALL,
 CONSTRAINT partition_check CHECK (id >= 600000 AND id < 700000)
 ) INHERITS (users);
CREATE TABLE users_8 (
 LIKE users INCLUDING ALL,
 CONSTRAINT partition_check CHECK (id >= 700000 AND id < 800000)
 ) INHERITS (users);
CREATE TABLE users_9 (
 LIKE users INCLUDING ALL,
 CONSTRAINT partition_check CHECK (id >= 800000 AND id < 900000)
 ) INHERITS (users);
CREATE TABLE users_10 (
 LIKE users INCLUDING ALL,
 CONSTRAINT partition_check CHECK (id >= 900000 AND id < 1000000)
 ) INHERITS (users);

或建立小資料表另外方法（2） 
postgres=# CREATE TABLE users_1 (
 LIKE users INCLUDING ALL,
 CONSTRAINT partition_check CHECK (id % 10 = 0)
 ) INHERITS (users);
CREATE TABLE users_2 (
 LIKE users INCLUDING ALL,
 CONSTRAINT partition_check CHECK (id % 10 = 1)
 ) INHERITS (users);
CREATE TABLE users_3 (
 LIKE users INCLUDING ALL,
 CONSTRAINT partition_check CHECK (id % 10 = 2)
 ) INHERITS (users);
CREATE TABLE users_4 (
 LIKE users INCLUDING ALL,
 CONSTRAINT partition_check CHECK (id % 10 = 3)
 ) INHERITS (users);
CREATE TABLE users_5 (
 LIKE users INCLUDING ALL,
 CONSTRAINT partition_check CHECK (id % 10 = 4)
 ) INHERITS (users);
CREATE TABLE users_6 (
 LIKE users INCLUDING ALL,
 CONSTRAINT partition_check CHECK (id % 10 = 5)
 ) INHERITS (users);
CREATE TABLE users_7 (
 LIKE users INCLUDING ALL,
 CONSTRAINT partition_check CHECK (id % 10 = 6)
 ) INHERITS (users);
CREATE TABLE users_8 (
 LIKE users INCLUDING ALL,
 CONSTRAINT partition_check CHECK (id % 10 = 7)
 ) INHERITS (users);
CREATE TABLE users_9 (
 LIKE users INCLUDING ALL,
 CONSTRAINT partition_check CHECK (id % 10 = 8)
 ) INHERITS (users);
CREATE TABLE users_10 (
 LIKE users INCLUDING ALL,
 CONSTRAINT partition_check CHECK (id % 10 = 9)
 ) INHERITS (users);

建立 trigger 的函數 
CREATE FUNCTION partition_for_users() RETURNS TRIGGER AS $$
DECLARE
 v_parition_name TEXT;
BEGIN
 v_parition_name := format( 'users_%s', 1 + NEW.id % 10 );
 execute 'INSERT INTO ' || v_parition_name || ' VALUES ( ($1).* )' USING NEW;
 return NULL;
END;
$$ LANGUAGE plpgsql;

建立 trigger 物件 
postgres=# CREATE TRIGGER partition_users
 BEFORE INSERT ON users
 FOR EACH ROW
 EXECUTE PROCEDURE partition_for_users();

產生資料：這裡從參考資料借來一個隨機字串的產生函數來使用 
CREATE OR REPLACE FUNCTION random_string(length INTEGER) RETURNS TEXT AS
$$
SELECT string_agg (
 substr(
 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789'
 , ceil (random() * 62)::integer, 1)
 , '')
 FROM generate_series(1, length);
$$ LANGUAGE sql;

postgres=# INSERT INTO users (username, password, created_on, last_logged_on)
 SELECT
     random_string( (random() * 4 + 5)::INT4),
     random_string( 20 ),
     now() - '2 years'::INTERVAL * random(),
     now() - '2 years'::INTERVAL * random()
 FROM
     generate_series(1, 10000);

這樣便產生一萬個資料，並且依照分配規則，分到各個 pratition 中存放。

有相關套件可以對 partition 資料表進行管理，例如 pg_partman，有興趣可以自行尋找。

參考：

於
標籤：

沒有留言:

張貼留言

訂閱： 張貼留言 (Atom)