【PostgreSQL】分區表

來源:互聯網
上載者:User

標籤:exp   replace   var   code   and   報錯   bsp   width   str   

PostgreSQL中的分區表是通過表繼承來實現的(表繼承部落格http://www.cnblogs.com/NextAction/p/7366607.html)。建立分區表的步驟如下:

(1)建立“父表”,所有的分區表都從這張表繼承。“父表”中不存資料,也不要定義約束和索引。

(2)建立“子表”,所有“子表”都是從“父表”中繼承而來。這些“子表”就是所謂的分區,其實它們也是PostgreSQL表。

(3)給分區表建立約束。

(4)在分區表上建立索引。

(5)建立觸發器,把對“父表”的插入重新導向到分區表中。

(6)確保postgresql.conf中constraint_exclusion的配置參數是開啟狀態。開啟後,可以確保查詢智能的只查詢分區表,而不會對其他分區表進行查詢。

下面是建立分區表的例子:

--建立銷售明細表,作為“父表”create table sales_detail (product_id    int    not null,price         numeric(12,2),amount        int    not null,sale_date     date   not null,buyer         varchar(40),buyer_contact text);--根據銷售日期sale_date欄位,每個季度作為一個分區,建立分區表create table sales_detail_Y2017Q01(check (sale_date >= date ‘2017-01-01‘ and sale_date < date ‘2017-04-01‘) ) inherits (sales_detail);create table sales_detail_Y2017Q02(check (sale_date >= date ‘2017-04-01‘ and sale_date < date ‘2017-07-01‘) ) inherits (sales_detail);create table sales_detail_Y2017Q03(check (sale_date >= date ‘2017-07-01‘ and sale_date < date ‘2017-10-01‘) ) inherits (sales_detail);create table sales_detail_Y2017Q04(check (sale_date >= date ‘2017-10-01‘ and sale_date < date ‘2018-01-01‘) ) inherits (sales_detail);--在分區鍵sale_detail上建立索引create index sales_detail_Y2017Q01_sale_date on sales_detail_Y2017Q01 (sale_date);create index sales_detail_Y2017Q02_sale_date on sales_detail_Y2017Q02 (sale_date);create index sales_detail_Y2017Q03_sale_date on sales_detail_Y2017Q03 (sale_date);create index sales_detail_Y2017Q04_sale_date on sales_detail_Y2017Q04 (sale_date);--建立觸發器,當向sales_detail表中插入資料時,可以重新導向插入到分區表中create or replace function sales_detail_insert_trigger()returns trigger as $$begin    if (new.sale_date >= date ‘2017-01-01‘ and new.sale_date < date ‘2017-04-01‘) then    insert into sales_detail_Y2017Q01 values (new.*);    elsif (new.sale_date >= date ‘2017-04-01‘ and new.sale_date < date ‘2017-07-01‘) then    insert into sales_detail_Y2017Q02 values (new.*);    elsif (new.sale_date >= date ‘2017-07-01‘ and new.sale_date < date ‘2017-10-01‘) then    insert into sales_detail_Y2017Q03 values (new.*);    elsif (new.sale_date >= date ‘2017-10-01‘ and new.sale_date < date ‘2018-01-01‘) then    insert into sales_detail_Y2017Q04 values (new.*);    else     raise exception ‘Date out of range.Fix the sales_detail_insert_trigger () function!‘;  end if;  return null;end;$$language plpgsql;create trigger insert_sales_detail_trigger before insert on sales_detailfor each row execute procedure sales_detail_insert_trigger ();--設定constraint_exclusion參數為“partition”狀態。此參數預設為“partition”set constrait_exclusion ‘partition‘

測試分區表:

--向“父表”中插入一條資料test=# insert into sales_detail values (1,23.22,1,date‘2017-08-16‘,‘zhaosi‘,‘xiangyashan222hao‘);--資料已經插入到分區表中test=# select * from sales_detail_Y2017Q03; product_id | price | amount | sale_date  | buyer  |   buyer_contact   ------------+-------+--------+------------+--------+-------------------          1 | 23.22 |      1 | 2017-08-16 | zhaosi | xiangyashan222hao(1 row)--並且查詢“父表”也可以查到插入的資料test=# select * from sales_detail; product_id | price | amount | sale_date  | buyer  |   buyer_contact   ------------+-------+--------+------------+--------+-------------------          1 | 23.22 |      1 | 2017-08-16 | zhaosi | xiangyashan222hao(1 row)--通過查看執行計畫,可以看出當查詢資料時,資料庫會自動的去sales_detail_Y2017Q03分區表中尋找,而不會掃描所有的分區表。test=# explain select * from sales_detail where sale_date=date‘2017-08-16‘;                                             QUERY PLAN                                             ---------------------------------------------------------------------------------------------------- Append  (cost=0.00..9.50 rows=3 width=158)   ->  Seq Scan on sales_detail  (cost=0.00..0.00 rows=1 width=158)         Filter: (sale_date = ‘2017-08-16‘::date)   ->  Bitmap Heap Scan on sales_detail_y2017q03  (cost=4.16..9.50 rows=2 width=158)         Recheck Cond: (sale_date = ‘2017-08-16‘::date)         ->  Bitmap Index Scan on sales_detail_y2017q03_sale_date  (cost=0.00..4.16 rows=2 width=0)               Index Cond: (sale_date = ‘2017-08-16‘::date)(7 rows)

總結:

刪除分區表中的子表,不會使觸發器失效,只是當向被刪除表中插入資料時會報錯。

建立分區表過程中的觸發器,可以用“規則”來代替,但觸發器比“規則”更有優勢,再此不再贅述。

 

The End!

2017-08-17

【PostgreSQL】分區表

相關文章

聯繫我們

該頁面正文內容均來源於網絡整理,並不代表阿里雲官方的觀點,該頁面所提到的產品和服務也與阿里云無關,如果該頁面內容對您造成了困擾,歡迎寫郵件給我們,收到郵件我們將在5個工作日內處理。

如果您發現本社區中有涉嫌抄襲的內容,歡迎發送郵件至: info-contact@alibabacloud.com 進行舉報並提供相關證據,工作人員會在 5 個工作天內聯絡您,一經查實,本站將立刻刪除涉嫌侵權內容。

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.