postgresql 怎么自動創(chuàng)建分區(qū)

在數(shù)據(jù)庫日漸龐大的今天，為了方便對數(shù)據(jù)庫數(shù)據(jù)的管理，比如按時間，按地區(qū)去統(tǒng)計一些數(shù)據(jù)時，基數(shù)過于龐大，多有不便。很多商業(yè)數(shù)據(jù)庫都提供分區(qū)的概念，按不同的維度去存放數(shù)據(jù)，便于后期的管理，PostgreSQL也不例外。

專業(yè)從事成都做網(wǎng)站、成都網(wǎng)站建設(shè),高端網(wǎng)站制作設(shè)計,小程序開發(fā),網(wǎng)站推廣的成都做網(wǎng)站的公司。優(yōu)秀技術(shù)團(tuán)隊竭力真誠服務(wù)，采用H5頁面制作+CSS3前端渲染技術(shù)，成都響應(yīng)式網(wǎng)站建設(shè)公司，讓網(wǎng)站在手機(jī)、平板、PC、微信下都能呈現(xiàn)。建站過程建立專項小組，與您實時在線互動，隨時提供解決方案，暢聊想法和感受。

PostgresSQL分區(qū)的意思是把邏輯上的一個大表分割成物理上的幾塊兒。分區(qū)不僅能帶來訪問速度的提升，關(guān)鍵的是，它能帶來管理和維護(hù)上的方便。

分區(qū)的具體好處是：

某些類型的查詢性能可以得到極大提升。

更新的性能也可以得到提升，因為表的每塊的索引要比在整個數(shù)據(jù)集上的索引要小。如果索引不能全部放在內(nèi)存里，那么在索引上的讀和寫都會產(chǎn)生更多的磁盤訪問。

批量刪除可以用簡單的刪除某個分區(qū)來實現(xiàn)。

可以將很少用的數(shù)據(jù)移動到便宜的、轉(zhuǎn)速慢的存儲介質(zhì)上。

在PG里表分區(qū)是通過表繼承來實現(xiàn)的，一般都是建立一個主表，里面是空，然后每個分區(qū)都去繼承它。無論何時，都應(yīng)保證主表里面是空的。

小表分區(qū)不實際，表在多大情況下才考慮分區(qū)呢？PostgresSQL官方給出的建議是：當(dāng)表本身大小超過了機(jī)器物理內(nèi)存的實際大小時（the size of the table should exceed the physical memory of the database server），可以考慮分區(qū)。

PG目前（9.2.2）僅支持范圍分區(qū)和列表分區(qū)，尚未支持散列分區(qū)。

二、環(huán)境

系統(tǒng)環(huán)境：CentOS release 6.3 (Final)

PostgreSQL版本：PostgreSQL 9.2.2 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.6 20120305 (Red Hat 4.4.6-4), 64-bit

三、實現(xiàn)分區(qū)

3.1 創(chuàng)建主表

david=# create table tbl_partition (

david(# id integer,

david(# name varchar(20),

david(# gender boolean,

david(# join_date date,

david(# dept char(4));

CREATE TABLE

david=#

3.2 創(chuàng)建分區(qū)表

david=# create table tbl_partition_201211 (

check ( join_date = DATE '2012-11-01' AND join_date DATE '2012-12-01' )

) INHERITS (tbl_partition);

CREATE TABLE

david=# create table tbl_partition_201212 (

check ( join_date = DATE '2012-12-01' AND join_date DATE '2013-01-01' )

) INHERITS (tbl_partition);

CREATE TABLE

david=# create table tbl_partition_201301 (

check ( join_date = DATE '2013-01-01' AND join_date DATE '2013-02-01' )

) INHERITS (tbl_partition);

CREATE TABLE

david=# create table tbl_partition_201302 (

check ( join_date = DATE '2013-02-01' AND join_date DATE '2013-03-01' )

) INHERITS (tbl_partition);

CREATE TABLE

david=# create table tbl_partition_201303 (

check ( join_date = DATE '2013-03-01' AND join_date DATE '2013-04-01' )

) INHERITS (tbl_partition);

CREATE TABLE

david=# create table tbl_partition_201304 (

check ( join_date = DATE '2013-04-01' AND join_date DATE '2013-05-01' )

) INHERITS (tbl_partition);

CREATE TABLE

david=# create table tbl_partition_201305 (

check ( join_date = DATE '2013-05-01' AND join_date DATE '2013-06-01' )

) INHERITS (tbl_partition);

CREATE TABLE

david=#

3.3 分區(qū)鍵上建索引

david=# create index tbl_partition_201211_joindate on tbl_partition_201211 (join_date);

CREATE INDEX

david=# create index tbl_partition_201212_joindate on tbl_partition_201212 (join_date);

CREATE INDEX

david=# create index tbl_partition_201301_joindate on tbl_partition_201301 (join_date);

CREATE INDEX

david=# create index tbl_partition_201302_joindate on tbl_partition_201302 (join_date);

CREATE INDEX

david=# create index tbl_partition_201303_joindate on tbl_partition_201303 (join_date);

CREATE INDEX

david=# create index tbl_partition_201304_joindate on tbl_partition_201304 (join_date);

CREATE INDEX

david=# create index tbl_partition_201305_joindate on tbl_partition_201305 (join_date);

CREATE INDEX

david=#

對于開發(fā)人員來說，希望數(shù)據(jù)庫是透明的，只管 insert into tbl_partition。對于數(shù)據(jù)插向哪個分區(qū)，則希望由DB決定。這點，ORACLE實現(xiàn)了，但是PG不行，需要前期人工處理下。

3.4 創(chuàng)建觸發(fā)器函數(shù)

david=# CREATE OR REPLACE FUNCTION tbl_partition_insert_trigger()

RETURNS TRIGGER AS $$

BEGIN

IF ( NEW.join_date = DATE '2012-11-01' AND

NEW.join_date DATE '2012-12-01' ) THEN

INSERT INTO tbl_partition_201211 VALUES (NEW.*);

ELSIF ( NEW.join_date = DATE '2012-12-01' AND

NEW.join_date DATE '2013-01-01' ) THEN

INSERT INTO tbl_partition_201212 VALUES (NEW.*);

ELSIF ( NEW.join_date = DATE '2013-01-01' AND

NEW.join_date DATE '2013-02-01' ) THEN

INSERT INTO tbl_partition_201301 VALUES (NEW.*);

ELSIF ( NEW.join_date = DATE '2013-02-01' AND

NEW.join_date DATE '2013-03-01' ) THEN

INSERT INTO tbl_partition_201302 VALUES (NEW.*);

ELSIF ( NEW.join_date = DATE '2013-03-01' AND

NEW.join_date DATE '2013-04-01' ) THEN

INSERT INTO tbl_partition_201303 VALUES (NEW.*);

ELSIF ( NEW.join_date = DATE '2013-04-01' AND

NEW.join_date DATE '2013-05-01' ) THEN

INSERT INTO tbl_partition_201304 VALUES (NEW.*);

ELSIF ( NEW.join_date = DATE '2013-05-01' AND

NEW.join_date DATE '2013-06-01' ) THEN

INSERT INTO tbl_partition_201305 VALUES (NEW.*);

ELSE

RAISE EXCEPTION 'Date out of range. Fix the tbl_partition_insert_trigger() function!';

END IF;

RETURN NULL;

END;

$$

LANGUAGE plpgsql;

CREATE FUNCTION

david=#

說明：如果不想丟失數(shù)據(jù)，上面的ELSE 條件可以改成 INSERT INTO tbl_partition_error_join_date VALUES (NEW.*); 同時需要創(chuàng)建一張結(jié)構(gòu)和tbl_partition 一樣的表tbl_partition_error_join_date，這樣，錯誤的join_date 數(shù)據(jù)就可以插入到這張表中而不是報錯了。

附上出處鏈接：

實體關(guān)系轉(zhuǎn)換成表的數(shù)量

用實體關(guān)系圖進(jìn)行數(shù)據(jù)庫建模

一、概述

很可能你現(xiàn)在正在規(guī)劃一個數(shù)據(jù)庫驅(qū)動的網(wǎng)站；而且?guī)缀蹩梢钥隙ǖ氖?，你一定已?jīng)瀏覽過數(shù)據(jù)庫驅(qū)動的網(wǎng)站。過去，一些網(wǎng)站依賴CGI腳本和文本文件存儲實現(xiàn)數(shù)據(jù)持久化，但現(xiàn)在我們能夠訪問大量不同的關(guān)系型、對象-關(guān)系型、面向?qū)ο笮蛿?shù)據(jù)庫。

對于Web應(yīng)用來說，關(guān)系數(shù)據(jù)庫是一種強(qiáng)大的支持工具，這得感謝它們的高可用性、性能，而且相對來說，關(guān)系數(shù)據(jù)庫比較容易使用。要找出一個功能完善、源代碼開放、能夠在多種平臺上運(yùn)行的數(shù)據(jù)庫系統(tǒng)并不困難。你可以用Perl、Java、PHP以及其他服務(wù)器端腳本語言把關(guān)系數(shù)據(jù)庫和Web網(wǎng)站連結(jié)到一起。

隨著網(wǎng)站規(guī)模的發(fā)展，它對數(shù)據(jù)庫——通常是關(guān)系數(shù)據(jù)庫——的依賴程度也日益增加。大量頁面和服務(wù)需要向數(shù)據(jù)庫表寫入信息，或者從數(shù)據(jù)庫提取信息。對于大多數(shù)網(wǎng)站，數(shù)據(jù)庫表很快成為網(wǎng)站體系結(jié)構(gòu)中的關(guān)鍵部分，成為網(wǎng)站運(yùn)作的生命中樞。為了方便和輕松地管理大容量數(shù)據(jù)，用戶帳戶、新聞動態(tài)、內(nèi)容、統(tǒng)計數(shù)據(jù)都可以保存到關(guān)系數(shù)據(jù)庫管理系統(tǒng)（Relational Database Management System，RDBMS）。

用圖（Diagram）管理數(shù)據(jù)模型具有高效、方便的優(yōu)點。對于RDBMS，描述數(shù)據(jù)模型的圖通常稱為實體關(guān)系圖（Entity Relationship Diagram，ERD）。用ERD描述數(shù)據(jù)模型能夠幫助你預(yù)先精確定義數(shù)據(jù)需求，使你能夠?qū)σ院蟮母膭幼鞒鲇行У囊?guī)劃，能夠隨著網(wǎng)站的發(fā)展方便地改進(jìn)規(guī)劃。

本文將介紹ERD建模工具和概念。文章提供了一些圖的實例，但它們的目的不是提供精確的或者是全面的數(shù)據(jù)設(shè)計范例。它們的目的是以兩個建模工具為例，介紹數(shù)據(jù)建模符號。在不同的工具之間，圖的符號有著重大的差別，但它們的基本概念一樣。本文的圖例從PowerDesigner和Visio 2000 Professional的試用版得到，你可以從本文末尾找到這些工具和其他類似產(chǎn)品的鏈接。

二、是否使用建模工具？

許多規(guī)模較小的網(wǎng)站用ASCII形式的SQL（Structured Query Language）腳本文件進(jìn)行數(shù)據(jù)建模。當(dāng)開發(fā)小組人員較少，或者最理想的情況下僅由一個人構(gòu)成時，這種方法最有效。然而，數(shù)據(jù)模型將很快發(fā)展成為一個復(fù)雜的結(jié)構(gòu)——在這種情況下，CASE（Computer Aided Software Engineering，計算機(jī)輔助軟件設(shè)計）工具、有關(guān)所有數(shù)據(jù)信息的圖、集中式知識庫能夠極大地幫助你管理Web網(wǎng)站的數(shù)據(jù)層。

2.1 何時使用SQL？

即使當(dāng)你準(zhǔn)備用SQL直接管理數(shù)據(jù)模式（物理數(shù)據(jù)庫）時，圖也能有效地幫助你理解和改進(jìn)系統(tǒng)。然而，如果你的預(yù)算或者時間非常有限，采用復(fù)雜的新式建模工具可能得不償失。相反，在這種情況下，你應(yīng)該使用一個簡單的圖形工具把數(shù)據(jù)模式的基本情況記錄下來，然后逐步轉(zhuǎn)換到復(fù)雜的數(shù)據(jù)建模工具。

如果你正在設(shè)計的數(shù)據(jù)庫類型不常見（或者是非標(biāo)準(zhǔn)的），避免使用某些復(fù)雜CASE工具可能是明智的，因為這些工具的“反向工程”能力和某些自動功能可能無法在你的環(huán)境下發(fā)揮作用。這里所謂的自動功能，是指建模工具根據(jù)輸入模型的圖形和屬性信息，自動為目標(biāo)數(shù)據(jù)庫生成合適SQL命令的能力。反向工程是這樣一種能力，建模工具根據(jù)已經(jīng)部署的物理數(shù)據(jù)模式，從現(xiàn)有的表提取出實體和關(guān)系信息。

2.2 轉(zhuǎn)入建模工具

從簡單繪圖工具轉(zhuǎn)換到數(shù)據(jù)建模工具并不是一個很復(fù)雜的過程。大多數(shù)數(shù)據(jù)建模工具的工作方式就象是一個標(biāo)準(zhǔn)的繪圖工具，參見圖1a和圖1b，這是兩個數(shù)據(jù)建模工具的界面實例。你可以在這里創(chuàng)建和排列表，定義關(guān)系，以及指定其它信息（列的類型、長度，鍵等）。

圖1a：PowerDesigner的界面

圖1b：Visio的界面

轉(zhuǎn)向數(shù)據(jù)建模工具的主要挑戰(zhàn)在于：

學(xué)習(xí)使用建模符號。

在不丟失任何關(guān)鍵信息的前提下，用數(shù)據(jù)建模工具描述現(xiàn)有數(shù)據(jù)模型。

尋找一個對你的數(shù)據(jù)庫提供全面支持的工具，例如在生成SQL、從現(xiàn)有數(shù)據(jù)模式通過反向工程建立數(shù)據(jù)模型時。

一些入門級數(shù)據(jù)建模工具（參見本文后面的參考資源）只有少量的高級特性。這有好處，但也有弊端——它們很容易學(xué)習(xí)使用，但當(dāng)你積累了更多的經(jīng)驗時，它們可能不再滿足你日益增長的需要。然而，升級工具或更換工具一般不存在大的問題，特別是當(dāng)新的工具能夠?qū)ΜF(xiàn)有數(shù)據(jù)模式進(jìn)行精確、完整的反向工程時，升級或更換工具的過程尤其簡單。

三、ERD建模符號

本文使用Martin的Information Engineering符號。PowerDesigner采用的就是這種符號，Oracle的Designer產(chǎn)品所使用的符號也和它很相似。你可以在AIS Modeling Summary查看各種ERD符號的說明。基本的ERD繪圖規(guī)范很直觀易懂。你可以定義實體（表），描述各個實體之間的關(guān)系。在填寫表和關(guān)系的細(xì)節(jié)信息時，每一種工具的做法都有所不同；但就我所遇到的工具來看，基本概念在大多數(shù)軟件包之間是相通的。接下來的內(nèi)容將介紹你必須了解的主要圖形元素和設(shè)置方法。

3.1 表

所有構(gòu)造合理的數(shù)據(jù)建模工具都允許為表指定豐富的關(guān)聯(lián)信息。這些信息包括（但不局限于）：

表的描述、注解，以及實體（表）的標(biāo)題。

列，列的類型、長度、默認(rèn)值和強(qiáng)制條件。

主鍵，索引，唯一性約束。

要指定這些信息，一般你需要進(jìn)入表的屬性窗口，如圖2a和圖2b所示。

2a：PowerDesigner中表的屬性窗口

圖2b：Visio中表的屬性窗口

一旦輸入了新表的屬性信息，圖將被更新，顯示出你所提供的新的或更改后的表信息。下面的圖形顯示了一個表的實例，這個表的屬性信息見圖2a和圖2b。在圖2a和圖2b中，許多列被定義成了（m）andatory（強(qiáng)制的）、（p）rimary（主鍵）和（d）isplayed（被顯示的）列。下面的圖顯示了為該表輸入的部分屬性信息。

圖3a：PowerDesigner的表

圖3b：Visio的表

在圖3a中可以看到一些非標(biāo)準(zhǔn)的數(shù)據(jù)類型，如PHONENUMBER和PK。許多數(shù)據(jù)建模工具允許定義域或定制數(shù)據(jù)類型，它們可供一個以上的列使用。域不僅代表著數(shù)據(jù)類型——通常，它們還包含檢查約束、默認(rèn)值、值列表等信息。如果你想要更新一個域（例如定義一種新的電話號碼格式），所有該模型中引用該域的列都將自動更新。

3.2 關(guān)系

如果我們只定義數(shù)據(jù)模式中的表，數(shù)據(jù)建模工具就不那么重要了。各個表之間的關(guān)系、依賴情況往往很復(fù)雜，有一個管理和顯示這些關(guān)系的工具將帶來很大的幫助。對于一個給定的關(guān)系，必須收集的重要信息包括：

父表和子表。

兩個表之間的強(qiáng)制關(guān)系。例如，父表可能有一個子表，但子表必須有一個父表。

關(guān)系基數(shù)（Cardinality）。即，一個父表可以有零個或者多個子表，但一個子表有且只能有一個父表。

關(guān)于關(guān)系的注釋、意見和角色說明。

大多數(shù)建模工具通過在兩個或者更多表之間畫出連線的方式定義關(guān)系。默認(rèn)情況下，關(guān)系往往被定義成為一對多關(guān)系，而且它對于關(guān)系中的任何一方都是可選的。要修改關(guān)系，你必須打開關(guān)系的屬性窗口，更新實體關(guān)系的特征信息。圖4a和圖4b顯示了兩個不同的工具允許為關(guān)系定義的部分屬性：

圖4a：PowerDesigner的關(guān)系屬性設(shè)置界面

圖4b：Visio的關(guān)系屬性設(shè)置界面

該圖顯示了一個一對多關(guān)系——一個典型的父-子關(guān)聯(lián)關(guān)系。部門（Branch）和雇員（Emplyee）的關(guān)系是強(qiáng)制的。它意味著一個部門必須至少有一個雇員（1-N強(qiáng)制關(guān)系）；另一方面，它意味著一個雇員必須屬于且只能屬于一個部門（1-1強(qiáng)制關(guān)系）。圖5a和圖5b反映了修改后的關(guān)系。

圖5a：PowerDesigner中兩個表之間的關(guān)系

圖5b：Visio中兩個表之間的關(guān)系

這個圖顯示了如何把信息轉(zhuǎn)換成符號。強(qiáng)制的關(guān)系由一條實心垂直線（而不是橢圓）表示。某些工具用虛線表示可選的關(guān)系。關(guān)系中屬于“多”的這一邊用一個類似鳥爪的圖形表示，關(guān)系的基數(shù)在靠近它所描述的那一端顯示。

你可能已經(jīng)注意到，Employee表沒有定義外鍵列。這個圖仍舊處于“概念設(shè)計”階段——此后，從概念圖到物理數(shù)據(jù)模型之間的轉(zhuǎn)換是必不可少的。大多數(shù)工具區(qū)分概念和物理數(shù)據(jù)模型——概念數(shù)據(jù)模型描述信息的需求，但不關(guān)注細(xì)節(jié)問題，例如索引和強(qiáng)制性的引用完整性。

有些時候，你可能要定義自我引用的表。自我引用的表一般用來描述層次型關(guān)系。如下面的圖形所示，大多數(shù)數(shù)據(jù)建模工具能夠處理這類關(guān)系。注意在這個例子中，雇員可以有零個或者一個上級——它使你能夠處理一些特殊的情況，比如總統(tǒng)沒有直接的上級。

圖6a：PowerDesigner中自我引用的表

圖6b：Visio中自我引用的表

四、圖的規(guī)劃

定義表和關(guān)系只是挑戰(zhàn)的一部分，圖的清楚明白同樣很重要。雖然一些工具提供自動布局能力，我還沒有看到過一個完善的實現(xiàn)。相反，你的目標(biāo)應(yīng)該是遵從“孔雀東南飛”這一規(guī)則（這里的“孔雀”是關(guān)系中代表“多”這一方的符號，它是連接到表的三條分叉線，象個鳥爪）。換句話說，子表應(yīng)該位于父表的右方和下方。這種安排使得從邏輯上組織和理解數(shù)據(jù)模型更加方便。最重要、最高級別的表應(yīng)該出現(xiàn)在左上角，讓級別較低的表出現(xiàn)在頁面的右下角。為了清楚起見，減少圖中交叉線的數(shù)量也是很重要的。正如Eberhardt Rechtin在The Art of Systems Architecting中強(qiáng)調(diào)的，“一個好的設(shè)計往往看起來很舒服”。如果無論怎樣安排，你的數(shù)據(jù)模型看起來都很混亂，那么，它可能正在告訴你數(shù)據(jù)模型本身有一些值得注意的問題。

圖7a：完整的ER圖（PowerDesigner）

圖7b：完整的ER圖（Visio）

五、從圖到數(shù)據(jù)庫

依賴于你所選擇的用來建立數(shù)據(jù)模型的軟件包，建模工具可能會根據(jù)模型生成SQL命令或直接修改數(shù)據(jù)庫模式。這種功能帶來了極大的便利；和使用ASCII格式的SQL腳本相比，這種方式有著許多優(yōu)點。一些建模工具的功能適合于大量的數(shù)據(jù)庫類型，例如PostgreSQL、MySQL、Oracle、DB2，等等。對于簡單的數(shù)據(jù)庫修改，改動操作可以從建模工具通過ODBC直接完成。數(shù)據(jù)庫改動還允許以增量方式進(jìn)行（例如，ALTER命令或創(chuàng)建命令，以及對特定表的更新命令）。當(dāng)你第一次使用建模工具時，你可以查看建模工具生成的SQL，看看自己是否可以信任和認(rèn)可建模工具對數(shù)據(jù)模型的解釋。一段時間之后，你就會熟悉建模工具對各種關(guān)系和表細(xì)節(jié)的解釋。

【結(jié)束語】數(shù)據(jù)建模是一種很好的軟件工程實踐。它能夠幫助你在正式編寫程序代碼之前規(guī)劃數(shù)據(jù)需求。在維護(hù)和改進(jìn)系統(tǒng)的數(shù)據(jù)布局的過程中，數(shù)據(jù)建模同樣很有用。一些工具能夠讓這個過程變得非常簡單，能夠在你管理和設(shè)計數(shù)據(jù)庫系統(tǒng)的時候帶來極大的幫助。然而，根據(jù)你所需功能的不同，建模工具的價格也有著極大的差異。在不出現(xiàn)預(yù)算赤字的情況下，輕松掌握和運(yùn)用數(shù)據(jù)建模技術(shù)的最好方法是，從小型的工具開始，然后逐漸深入和提高。

六、參考和資源

■ 工具

Sybase PowerDesigner - 一個高端數(shù)據(jù)建模工具。你可以下載一個45天試用版。

ERWin - 一個高端數(shù)據(jù)建模工具?？上螺d試用版。

Rational Rose Enterprise - 一個高端UML工具，恰如其分的數(shù)據(jù)庫建模支持?？上螺d試用版。

Visio Professional - 一個價格低廉的繪圖工具，可用來生成數(shù)據(jù)模型、UML圖等。企業(yè)版還支持針對各種數(shù)據(jù)庫的雙向工程能力。你可以訂購60天試用版的CD。

Dezign - 一個價格極其低廉的ERD建模工具。你可以下載一個有限制的試用版本。

ERD Tool List - 一個關(guān)于各種數(shù)據(jù)庫和UML建模工具的鏈接和資源的清單。

■ 參考

Database Design for the Web

Designing Databases

ERD Notation Summary

On-Line Database Books

Diagramming Methods

Introduction to ER Modeling

分享標(biāo)題：包含postgresql基數(shù)的詞條
瀏覽路徑：http://jinyejixie.com/article38/dsdjdpp.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供做網(wǎng)站、面包屑導(dǎo)航、用戶體驗、標(biāo)簽優(yōu)化、電子商務(wù)、ChatGPT

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源： 創(chuàng)新互聯(lián)