PostgreSQL 分區(qū)表的實現(xiàn)示例

更新時間：2026年01月13日 11:29:42 作者：吳聲子夜歌

本文主要介紹了PostgreSQL中的分區(qū)表實現(xiàn),包括傳統(tǒng)分區(qū)表和內(nèi)置分區(qū)表兩種方式,文中通過示例代碼介紹的非常詳細(xì),對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值,需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧

一、分區(qū)表的意義

分區(qū)表主要有以下優(yōu)勢：

當(dāng)查詢或更新一個分區(qū)上的大部分?jǐn)?shù)據(jù)時，對分區(qū)進(jìn)行索引掃描代價很大，然而，在分區(qū)上使用順序掃描能提升性能。
當(dāng)需要刪除一個分區(qū)數(shù)據(jù)時，通過 DROP TABLE 刪除一個分區(qū)，遠(yuǎn)比 DELETE 刪除數(shù)據(jù)高效，特別適用于日志數(shù)據(jù)場景。
由于一個表只能存儲在一個表空間上，使用分區(qū)表后，可以將分區(qū)放到不同的表空間上，例如可以將系統(tǒng)很少訪問的分區(qū)放到廉價的存儲設(shè)備上，也可以將系統(tǒng)常訪問的分區(qū)存儲在高速存儲上。

二、傳統(tǒng)分區(qū)表

傳統(tǒng)分區(qū)表是通過繼承和觸發(fā)器方式實現(xiàn)的，其實現(xiàn)過程步驟多，非常復(fù)雜，需要定義父表、定義子表、定義子表約束、創(chuàng)建子表索引、創(chuàng)建分區(qū)插入、刪除、修改函數(shù)和觸發(fā)器等，可以說是在普通基礎(chǔ)上手動實現(xiàn)的分區(qū)表。在介紹傳統(tǒng)分區(qū)表之前先介紹繼承，繼承是傳統(tǒng)分區(qū)表的重要組成部分。

2.1、繼承表

PostgreSQL提供繼承表，簡單地說是首先定義一張父表，之后可以創(chuàng)建子表繼承父表，下面通過一個簡單的例子來理解。

創(chuàng)建一張日志模型表tbl_log：

create table tbl_log(
	id int4,
	create_date date,
	log_type text
);

create table tbl_log_sql(
	sql text
) inherits(tbl_log);

通過inherits(tbl_log)表示表tbl_log_sql繼承表tbl_log，子表可以定義額外的字段，以上定義了sql為額外字段，其它字段則繼承父表tbl_log，查看tbl_log_sql表結(jié)構(gòu)如下：

父表和子表都可以插入數(shù)據(jù)，接著分別在父表和子表中插入一條數(shù)據(jù)，如下所示：

insert into tbl_log
values(1, '2017-08-26', null);

insert into tbl_log_sql
values(2, '2017-08-27', null, 'select 2');

這時如果查詢父表tbl_log會顯示兩表的記錄，如下所示：

select * from tbl_log;

盡管查詢父表會將子表的記錄數(shù)也列出，但子表自定義的字段沒有顯示，如果想確定數(shù)據(jù)來源于哪張表，可通過以下SQL查看表的OID，如下所示：

select tableoid, *
from tbl_log;

tableoid是表的隱藏字段，表示表的OID，可以通過pg_class系統(tǒng)關(guān)聯(lián)找到表名，如下所示：

select p.relname, c.*
from tbl_log c, pg_class p
where c.tableoid = p.oid;

如果只想查詢父表的數(shù)據(jù)，需在父表名稱前加上關(guān)鍵字ONLY，如下所示：

select * 
from only tbl_log;

因此，對于UPDATE、DELETE、SELECT操作，如果父表名稱前面沒有加上ONLY，則會對父表和所有子表進(jìn)行DML操作，如下所示：

delete from tbl_log;

select count(*) 
from tbl_log;

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

傳統(tǒng)分區(qū)表創(chuàng)建過程主要包括以下幾個步驟：

創(chuàng)建父表，如果父表上定義了約束，子表會繼承，因此除非是全局約束，否則不應(yīng)該在父表上定義約束，另外，父表不應(yīng)該寫入數(shù)據(jù)。
通過inherits方式創(chuàng)建繼承表，也稱之為子表或分區(qū)，子表的字段定義應(yīng)該和父表保持一致。
給所有子表創(chuàng)建約束，只有滿足約束條件的數(shù)據(jù)才能寫入對應(yīng)分區(qū)，注意分區(qū)約束值范圍不要有重疊。
給所有子表創(chuàng)建索引，由于繼承操作不會繼承父表上的索引，因此索引需要手工創(chuàng)建。
在父表上定義insert、delete、update觸發(fā)器，將SQL分發(fā)到對應(yīng)分區(qū)，這步可選，因為應(yīng)用可以根據(jù)分區(qū)規(guī)則定位到對應(yīng)分區(qū)進(jìn)行DML操作。
啟用constraint_exclusion參數(shù)，如果這個參數(shù)設(shè)置成off，則父表上的SQL性能會降低，后面會通過示例解釋這個參數(shù)。

以上六個步驟是創(chuàng)建傳統(tǒng)分區(qū)表的主要步驟，接下來通過一個示例演示創(chuàng)建一張范圍分區(qū)表，并且定義年月子表存儲月數(shù)據(jù)。

首先創(chuàng)建父表：

create table log_ins(
	id serial,
	user_id int4,
	create_time timestamp(0) without time zone
);

創(chuàng)建13張子表：

create table log_ins_history(
	check(create_time < '2017-01-01')
) inherits(log_ins);

create table log_ins_history_201701(
	check(create_time >= '2017-01-01' and create_time < '2017-02-01')
) inherits(log_ins);

create table log_ins_history_201702(
	check(create_time >= '2017-02-01' and create_time < '2017-03-01')
) inherits(log_ins);

create table log_ins_history_201703(
	check(create_time >= '2017-03-01' and create_time < '2017-04-01')
) inherits(log_ins);

create table log_ins_history_201704(
	check(create_time >= '2017-04-01' and create_time < '2017-05-01')
) inherits(log_ins);

create table log_ins_history_201705(
	check(create_time >= '2017-05-01' and create_time < '2017-06-01')
) inherits(log_ins);

create table log_ins_history_201706(
	check(create_time >= '2017-06-01' and create_time < '2017-07-01')
) inherits(log_ins);

create table log_ins_history_201707(
	check(create_time >= '2017-07-01' and create_time < '2017-08-01')
) inherits(log_ins);

create table log_ins_history_201708(
	check(create_time >= '2017-08-01' and create_time < '2017-09-01')
) inherits(log_ins);

create table log_ins_history_201709(
	check(create_time >= '2017-09-01' and create_time < '2017-10-01')
) inherits(log_ins);

create table log_ins_history_201710(
	check(create_time >= '2017-10-01' and create_time < '2017-11-01')
) inherits(log_ins);

create table log_ins_history_201711(
	check(create_time >= '2017-11-01' and create_time < '2017-12-01')
) inherits(log_ins);

create table log_ins_history_201712(
	check(create_time >= '2017-12-01' and create_time < '2018-01-01')
) inherits(log_ins);

給子表創(chuàng)建索引：

create index idx_his_ctime on log_ins_history 
using btree (create_time);

create index idx_log_ins_201701_ctime on log_ins_history_201701 
using btree (create_time);

create index idx_log_ins_201702_ctime on log_ins_history_201702
using btree (create_time);

create index idx_log_ins_201703_ctime on log_ins_history_201703
using btree (create_time);

create index idx_log_ins_201704_ctime on log_ins_history_201704
using btree (create_time);

create index idx_log_ins_201705_ctime on log_ins_history_201705
using btree (create_time);

create index idx_log_ins_201706_ctime on log_ins_history_201706
using btree (create_time);

create index idx_log_ins_201707_ctime on log_ins_history_201707
using btree (create_time);

create index idx_log_ins_201708_ctime on log_ins_history_201708
using btree (create_time);

create index idx_log_ins_201709_ctime on log_ins_history_201709
using btree (create_time);

create index idx_log_ins_201710_ctime on log_ins_history_201710
using btree (create_time);

create index idx_log_ins_201711_ctime on log_ins_history_201711
using btree (create_time);

create index idx_log_ins_201712_ctime on log_ins_history_201712
using btree (create_time);

由于父表上不存儲數(shù)據(jù)，可以不用在父表上創(chuàng)建索引。

創(chuàng)建觸發(fā)器函數(shù)，設(shè)置數(shù)據(jù)插入父表時的路由規(guī)則，如下所示：

create or  replace function log_ins_insert_trigger()
	returns trigger
	language plpgsql
as $function$
begin
	if (NEW.create_time < '2017-01-01') then
		insert into log_ins_history VALUES(NEW.*);
	elsif (NEW.create_time >= '2017-01-01' and NEW.create_time < '2017-02-01') then
		insert into log_ins_history_201701 VALUES(NEW.*);
	elsif (NEW.create_time >= '2017-02-01' and NEW.create_time < '2017-03-01') then
		insert into log_ins_history_201702 VALUES(NEW.*);
	elsif (NEW.create_time >= '2017-03-01' and NEW.create_time < '2017-04-01') then
		insert into log_ins_history_201703 VALUES(NEW.*);
	elsif (NEW.create_time >= '2017-04-01' and NEW.create_time < '2017-05-01') then
		insert into log_ins_history_201704 VALUES(NEW.*);
	elsif (NEW.create_time >= '2017-05-01' and NEW.create_time < '2017-06-01') then
		insert into log_ins_history_201705 VALUES(NEW.*);
	elsif (NEW.create_time >= '2017-06-01' and NEW.create_time < '2017-07-01') then
		insert into log_ins_history_201706 VALUES(NEW.*);
	elsif (NEW.create_time >= '2017-07-01' and NEW.create_time < '2017-08-01') then
		insert into log_ins_history_201707 VALUES(NEW.*);
	elsif (NEW.create_time >= '2017-08-01' and NEW.create_time < '2017-09-01') then
		insert into log_ins_history_201708 VALUES(NEW.*);
	elsif (NEW.create_time >= '2017-09-01' and NEW.create_time < '2017-10-01') then
		insert into log_ins_history_201709 VALUES(NEW.*);
	elsif (NEW.create_time >= '2017-10-01' and NEW.create_time < '2017-11-01') then
		insert into log_ins_history_201710 VALUES(NEW.*);
	elsif (NEW.create_time >= '2017-11-01' and NEW.create_time < '2017-12-01') then
		insert into log_ins_history_201711 VALUES(NEW.*);
	elsif (NEW.create_time >= '2017-12-01' and NEW.create_time < '2018-01-01') then
		insert into log_ins_history_201712 VALUES(NEW.*);
	else 
		raise exception 'create_time out of range. Fix the log_ins_insert trigger() function!';
	end if;
	return null;
end;
$function$;

函數(shù)中的NEW.*是指要插入的數(shù)據(jù)行，在父表上定義插入觸發(fā)器：

create trigger insert_log_ins_trigger
before insert
on log_ins
for each row
execute procedure log_ins_insert_trigger();

觸發(fā)器創(chuàng)建完成后，向父表log_ins插入數(shù)據(jù)時，會執(zhí)行觸發(fā)器并觸發(fā)函數(shù)log_ins_insert_trigger()將表數(shù)據(jù)插入到相應(yīng)的分區(qū)中。DELETE、UPDATE觸發(fā)器和函數(shù)創(chuàng)建過程和INSERT方式類似，傳統(tǒng)分區(qū)表的創(chuàng)建步驟已全部完成。

2.3、使用分區(qū)表

向父表log_ins插入測試數(shù)據(jù)，并驗證數(shù)據(jù)是否插入對應(yīng)分區(qū)：

insert into log_ins(user_id, create_time)
select round(10000000*random()), generate_series('2016-12-01'::date, '2017-12-01'::date, '1 minute');

這里通過隨機(jī)生成數(shù)據(jù)插入，數(shù)據(jù)如下所示：

select * 
from log_ins limit 5;

查看父表數(shù)據(jù)，發(fā)現(xiàn)父表沒有數(shù)據(jù)；

select count(*) from only log_ins;

select count(*) from log_ins;

查看子表數(shù)據(jù)：

select min(create_time), max(create_time)
from log_ins_history_201701;

這說明子表里可查到數(shù)據(jù)，查看子表大?。?/p>

由此可見數(shù)據(jù)都已經(jīng)插入到子表里。

2.4、查詢父表還是子表

假如我們檢索2017-01-01這一天的數(shù)據(jù)，我們可以查詢父表，也可以直接查詢子表，兩者性能上是否有差異？

查詢父表的執(zhí)行計劃如下：

explain analyze select *
from log_ins 
where create_time > '2017-01-01' and create_time < '2017-01-02';

從以上執(zhí)行計劃看出在分區(qū)log_ins_history_201701上進(jìn)行了索引掃描，接著查看直接查詢子表log_ins_history_201701的執(zhí)行計劃：

explain analyze select *
from log_ins_history_201701
where create_time > '2017-01-01' and create_time < '2017-01-02';

從以上執(zhí)行計劃看出，直接查詢子表更快，如果并發(fā)量上去的話，這個差異將更明顯，因此實際生產(chǎn)過程中，對于傳統(tǒng)分區(qū)表分區(qū)方式，不建議應(yīng)用訪問父表，而是直接訪問子表，也許有人會問，應(yīng)用如何定位到訪問哪張子表呢？可以根據(jù)預(yù)先的分區(qū)約束定義，上面的例子是根據(jù)時間范圍分區(qū)，name應(yīng)用可以根據(jù)時間來判斷查詢哪張子表，當(dāng)然，以上是根據(jù)分表表分區(qū)鍵查詢的場景，如果根據(jù)非分區(qū)鍵查詢則會掃描分區(qū)表的所有分區(qū)。

2.5、constraint_exclusion參數(shù)

constraint_exclusion參數(shù)用來控制優(yōu)化器是否根據(jù)表上的約束來優(yōu)化查詢，參數(shù)為以下值：

on：所有表都通過約束優(yōu)化查詢
off：所有表都不通過約束優(yōu)化查詢
partition：只對繼承表和UNION ALL子查詢通過檢索約束來優(yōu)化查詢

簡單地說，如果設(shè)置成on或partition，查詢父表時優(yōu)化器會根據(jù)子表上的約束判斷檢索哪些子表，而不要掃描所有子表，從而提升查詢性能。

2.6、添加分區(qū)

添加分區(qū)屬于分區(qū)表維護(hù)的常規(guī)操作之一，比如歷史表范圍分區(qū)到期之前需要擴(kuò)分區(qū)，log ins表為日志表，每個分區(qū)存儲當(dāng)月數(shù)據(jù)，假如分區(qū)快到期了，可通過以下SQL擴(kuò)分區(qū)，首先創(chuàng)建子表，如下所示：

create table log_ins_history_201801(
	check(create_time >= '2018-01-01' and create_time < '2018-02-01')
) inherits(log_ins);

之后創(chuàng)建相關(guān)索引：

create index idx_log_ins_201801_ctime 
on log_ins_history_201801 using btree(create_time);

然后刷新觸發(fā)器函數(shù)log_ins_insert_trigger(0,添加相應(yīng)代碼，將符合路由規(guī)則的數(shù)據(jù)插入新分區(qū)，詳見之前定義的這個函數(shù)，這步完成后，添加分區(qū)操作完成，可通過d+log_ins命令查看log_ins的所有分區(qū)。

這種方法比較直接，創(chuàng)建分區(qū)時就將分區(qū)繼承到父表，如果中間步驟有錯可能對生產(chǎn)系統(tǒng)帶來影響，比較推薦的做法是將以上操作分解成以下幾個步驟，降低對生產(chǎn)系統(tǒng)的影響，如下所示：

-- 創(chuàng)建分區(qū)
create table log_ins_history_201802(
	like log_ins including all
);

-- 添加約束
alter table log_ins_history_201802 
add constraint log_ins_history_201802_create_time_check
check (create_time >= '2018-02-01' and create_time < '2018-03-01');

-- 刷新觸發(fā)器函數(shù)log_ins_insert_trigger()

-- 所有步驟完成后，將新分區(qū)log_ins_201802繼承到父表log_ins
alter table log_ins_hisroty_201802 inherit log_ins;

以上方法是將新分區(qū)所有操作完成后，再將分區(qū)繼承到父表，降低了生產(chǎn)系統(tǒng)添加分區(qū)操作的風(fēng)險，當(dāng)然，在生產(chǎn)系統(tǒng)添加分區(qū)前建議在測試環(huán)境事先演練一把。

2.7、刪除分區(qū)

分區(qū)表的一個重要優(yōu)勢是對于大表的管理上十分方便，例如需要刪除歷史數(shù)據(jù)時可以直接刪除一個分區(qū)，這比DELETE方式效率高了多個數(shù)量級，傳統(tǒng)分區(qū)表刪除分區(qū)通常有兩種方法，第一種方法是直接刪除分區(qū)，如下所示：

drop table log_ins_201802;

就像刪除普通表一樣刪除分區(qū)即可，當(dāng)然刪除分區(qū)前需再三確認(rèn)是否需要備份數(shù)據(jù)；另一種比較推薦的刪除分區(qū)方法是先將分區(qū)的繼承關(guān)系去掉，如下所示：

alter table log_ins_history_201802 no inherit log_ins;

執(zhí)行以上命令后，log ins201802分區(qū)不再屬于分區(qū)表log_ins的分區(qū)，但log_ins_history_201802表依然保留可供查詢，這種方式相比方法一提供了一個緩沖時間，屬于比較穩(wěn)妥的刪除分區(qū)方法，因為在拿掉子表繼承關(guān)系后，只要沒刪除這個表，還可以使子表重新繼承父表。

2.8、分區(qū)表相關(guān)查詢

分區(qū)表創(chuàng)建完成后，如何查看分區(qū)表定義、分區(qū)表分區(qū)信息呢？比較常用的方法是通過\d元命令，如下所示：

以上信息顯示了表 log_ ins有 14 個分區(qū)，并且創(chuàng) 建了觸發(fā)器，觸發(fā)器函數(shù)為 log_ins_insert_ trigger （），如果想列出分區(qū)名稱可通過\d+ log_ins 元命令列出。

另一種列出分區(qū)表分區(qū)信息方法是通過SQL命令：

select nmsp_parent.nspname as parent_schema,
	   parent.relname as parent,
	   nmsp_child.nspname as child_schema,
	   child.relname as child_schema
from pg_inherits join pg_class parent
on pg_inherits.inhparent = parent.oid join pg_class child
on pg_inherits.inhrelid = child.oid join pg_namespace nmsp_parent
on nmsp_parent.oid = parent.relnamespace join pg_namespace nmsp_child
on nmsp_child.oid = child.relnamespace
where parent.relname = 'log_ins';

pg_inherits系統(tǒng)表記錄了子表和父表之間的繼承關(guān)系，通過以上查詢列出指定分區(qū)表的分區(qū)。如果想查看一個庫中有哪些分區(qū)表，并顯示這些分區(qū)表的分區(qū)數(shù)量，可通過以下SQL查詢：

select nspname, relname, count(*) as partition_num
from pg_class c, pg_namespace n, pg_inherits i
where c.oid = i.inhparent 
and c.relnamespace = n.oid
and c.relhassubclass
and c.relkind in ('r', 'p')
group by 1,2 
order by partition_num desc;

以上結(jié)果顯示當(dāng)前庫中有兩個分區(qū)表，log_ins分區(qū)表有14個分區(qū)，tbl_log分區(qū)表只有一個分區(qū)。

2.9、傳統(tǒng)分區(qū)表注意事項

傳統(tǒng)分區(qū)表的使用有以下注意事項：

當(dāng)往父表上插入數(shù)據(jù)時，需事先在父表上創(chuàng)建路由函數(shù)和觸發(fā)器，數(shù)據(jù)才會根據(jù)分區(qū)鍵路由規(guī)則插入到對應(yīng)分區(qū)中，目前僅支持范圍分區(qū)和列表分區(qū)。
分區(qū)表上的索引、約束需要使用單獨的命令創(chuàng)建，目前沒有辦法一次性自動在所有分區(qū)上創(chuàng)建索引、約束。
父表和子表允許單獨定義主鍵，因此父表和子表可能存在重復(fù)的主鍵記錄，目前不支持在分區(qū)表上定義全局主鍵。
UPDATE時不建議更新分區(qū)鍵數(shù)據(jù)，特別是會使數(shù)據(jù)從一個分區(qū)移動到另一分區(qū)的場景，可通過更新觸發(fā)器實現(xiàn)，但會帶來管理上的成本。
性能方面：根據(jù)本節(jié)的測試數(shù)據(jù)和測試場景，傳統(tǒng)分區(qū)表根據(jù)非分區(qū)鍵查詢相比普通表性能差距較大，因為這種場景下分區(qū)表會掃描所有分區(qū)；根據(jù)分區(qū)鍵查詢相比普通表性能有小幅降低，而查詢分區(qū)表子表性能相比普通表略有提升；

三、內(nèi)置分區(qū)表

PostgreSQL10一個重量級新特性是支持內(nèi)置分區(qū)表，用戶不需要預(yù)先在父表上定義INSERT、DELETE、UPDATE觸發(fā)器，對父表的DML操作會自動路由到相應(yīng)分區(qū)，相比傳統(tǒng)分區(qū)表大幅度降低了維護(hù)成本，目前僅支持范圍分區(qū)和列表分區(qū)。

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

創(chuàng)建分區(qū)表的主要語法包含兩部分：創(chuàng)建主表和創(chuàng)建分區(qū)。
創(chuàng)建主表語法如下：

create table table_name (...)
[ partition by {range | list}  ({column_name | expression})]

創(chuàng)建主表時須指定分區(qū)方式，可選的分區(qū)方式為RANGE范圍分區(qū)或LIST列表分區(qū)，并指定字段或表達(dá)式作為分區(qū)鍵。

創(chuàng)建分區(qū)的語法如下：

create table table_name(

) partition of parent_table for values partition_bound_spec

創(chuàng)建分區(qū)時必須指定是哪張表的分區(qū)，同時指定分區(qū)策略partition bound spec,如果是范圍分區(qū)，partition bound spec須指定每個分區(qū)分區(qū)鍵的取值范圍，如果是列表分區(qū)
partition_bound_spec,需指定每個分區(qū)的分區(qū)鍵值。

PostgreSQL10創(chuàng)建內(nèi)置分區(qū)表主要分為以下幾個步驟：

創(chuàng)建父表，指定分區(qū)鍵和分區(qū)策略。
創(chuàng)建分區(qū)，創(chuàng)建分區(qū)時須指定分區(qū)表的父表和分區(qū)鍵的取值范圍，注意分區(qū)鍵的范圍不要有重疊，否則會報錯。
在分區(qū)上創(chuàng)建相應(yīng)索引，通常情況下分區(qū)鍵上的索引是必須的，非分區(qū)鍵的索引可根據(jù)實際應(yīng)用場景選擇是否創(chuàng)建。

接下來通過創(chuàng)建范圍分區(qū)的示例來演示內(nèi)置分區(qū)表的創(chuàng)建過程，首先創(chuàng)建一張范圍分區(qū)表，表名為log_par,如下所示：

create table log_par (
	id serial,
	user_id int4,
	create_time timestamp(0) without time zone
) partition by range(create_time);

表log par指定了分區(qū)策略為范圍分區(qū)，分區(qū)鍵為create time字段。創(chuàng)建分區(qū)，并設(shè)置分區(qū)的分區(qū)鍵取值范圍，如下所示：

create table log_par_his partition of log_par 
for values from ('2016-01-01') to ('2017-01-01');

create table log_par_201701 partition of log_par 
for values from ('2017-01-01') to ('2017-02-01');

create table log_par_201702 partition of log_par 
for values from ('2017-02-01') to ('2017-03-01');

create table log_par_201703 partition of log_par 
for values from ('2017-03-01') to ('2017-04-01');

create table log_par_201704 partition of log_par 
for values from ('2017-04-01') to ('2017-05-01');

create table log_par_201705 partition of log_par 
for values from ('2017-05-01') to ('2017-06-01');

create table log_par_201706 partition of log_par 
for values from ('2017-06-01') to ('2017-07-01');

create table log_par_201707 partition of log_par 
for values from ('2017-07-01') to ('2017-08-01');

create table log_par_201708 partition of log_par 
for values from ('2017-08-01') to ('2017-09-01');

create table log_par_201709 partition of log_par 
for values from ('2017-09-01') to ('2017-10-01');

create table log_par_201710 partition of log_par 
for values from ('2017-10-01') to ('2017-11-01');

create table log_par_201711 partition of log_par 
for values from ('2017-11-01') to ('2017-12-01');

create table log_par_201712 partition of log_par 
for values from ('2017-12-01') to ('2018-01-01');

注意分區(qū)的分區(qū)鍵范圍不要有重疊，定義分區(qū)鍵范圍實質(zhì)上給分區(qū)創(chuàng)建了約束。

給所有分區(qū)的分區(qū)鍵創(chuàng)建索引，如下所示：

create index idx_log_par_his_ctime
on log_par_his using btree(create_time);

create index idx_log_par_201701_ctime
on log_par_201701 using btree(create_time);

create index idx_log_par_201702_ctime
on log_par_201702 using btree(create_time);

create index idx_log_par_201703_ctime
on log_par_201703 using btree(create_time);

create index idx_log_par_201704_ctime
on log_par_201704 using btree(create_time);

create index idx_log_par_201705_ctime
on log_par_201705 using btree(create_time);

create index idx_log_par_201706_ctime
on log_par_201706 using btree(create_time);

create index idx_log_par_201707_ctime
on log_par_201707 using btree(create_time);

create index idx_log_par_201708_ctime
on log_par_201708 using btree(create_time);

create index idx_log_par_201709_ctime
on log_par_201709 using btree(create_time);

create index idx_log_par_201710_ctime
on log_par_201710 using btree(create_time);

create index idx_log_par_201711_ctime
on log_par_201711 using btree(create_time);

create index idx_log_par_201712_ctime
on log_par_201712 using btree(create_time);

以上三步完成了內(nèi)置分區(qū)表的創(chuàng)建。

3.2、使用分區(qū)表

向分區(qū)表插入數(shù)據(jù)：

insert into log_par(user_id, create_time)
select round(100000 * random()), 
	   generate_series('2016-12-01'::date, '2017-12-01'::"date", '1 minute');

查看表數(shù)據(jù)：

select count(*)
from log_par;

select count(*)
from only log_par;

從以上結(jié)果可以看出，父表log par沒有存儲任何數(shù)據(jù)，數(shù)據(jù)存儲在分區(qū)中，通過分區(qū)大小也可以證明這一點，如下所示：

3.3、內(nèi)置分區(qū)表原理

內(nèi)置分區(qū)表原理實際上和傳統(tǒng)分區(qū)表一樣，也是使用繼承方式，分區(qū)可稱為子表，通過以下查詢很明顯看出表log par和其分區(qū)是繼承關(guān)系：

select nmsp_parent.nspname as parent_schema,
	   parent.relname as parent,
	   nmsp_child.nspname as child_schema,
	   child.relname as child_schema
from pg_inherits join pg_class parent
on pg_inherits.inhparent = parent.oid join pg_class child
on pg_inherits.inhrelid = child.oid join pg_namespace nmsp_parent
on nmsp_parent.oid = parent.relnamespace join pg_namespace nmsp_child
on nmsp_child.oid = child.relnamespace
where parent.relname = 'log_par';

3.4、添加分區(qū)

添加分區(qū)的操作比較簡單，例如給 log_par 增加一個分區(qū)，如下所示：

create table log_par_201801 partition of log_par
for values from ('2018-01-01') to ('2018-02-01');

之后給分區(qū)創(chuàng)建索引，如下所示：

create index idx_log_par_201801_ctime 
on log_par_201801 using btree(create_time);

3.5、刪除分區(qū)

刪除分區(qū)有兩種方法，第一種方法通過 DROP 分區(qū)的方式來刪除，如下所示：

drop table log_par_201801;

D ROP 方式直接將分區(qū) 和分區(qū)數(shù)據(jù)刪除，刪除前需確認(rèn)分區(qū)數(shù)據(jù)是否需要備份，避免數(shù)據(jù)丟失；另一種推薦的方法是解綁分區(qū)，如下所示：

alter table log_par detach partition log_par_201801;

綁分區(qū)只是將分區(qū) 和父表間的關(guān)系斷開，分區(qū)和分區(qū)數(shù)據(jù)依然保留，這種方式比較穩(wěn)妥，如果后續(xù)需要恢復(fù)這個分區(qū)，通過連接分區(qū)方式恢復(fù)分區(qū)即可，如下所示：

alter table log_par attach partition log_par_201801 for values from ('2018-01-01') to ('2018-02-01');

連接分區(qū)時需要指定分區(qū)上的約束。

3.6、更新分區(qū)數(shù)據(jù)

內(nèi)置分區(qū) 表 UPDAT E 操作目前不支持新記錄跨分區(qū)的情況，也就是說只允許分區(qū) 內(nèi)的更新，例如以下 SQL 會報錯：

update log_par set create_time = '2017-02-02 01:01:01' 
where user_id = 16965492;

以上 user_id 等于 16965492 的記錄位于 log_par_201701 分區(qū)，將這條記錄的 create_time 更新為 ’ 2017-02-02 01 : 01:01 ’由于違反了當(dāng)前分區(qū)的約束將報錯，如果更新的數(shù)據(jù)不違反當(dāng)前分區(qū)的約束則可正常更新數(shù)據(jù)，如下所示：

update log_par set create_time = '2017-01-01 01:01:01' 
where user_id = 16965492;

目前內(nèi)置分區(qū)表的這一限制對于日志表影響不大，對于業(yè)務(wù)表有一定影響，使用時需注意。

3.7、內(nèi)置分區(qū)表注意事項

當(dāng)往父表上插入數(shù)據(jù)時，數(shù)據(jù)會自動根據(jù)分區(qū)鍵路由規(guī)則插入到分區(qū)中，目前僅支持范圍分區(qū)和列表分區(qū)。
分區(qū)表上的索引、約束需使用單獨的命令創(chuàng)建，目前沒有辦法一次性自動在所有分區(qū)上創(chuàng)建索引、約束。
內(nèi)置分區(qū)表不支持定義（全局)主鍵，在分區(qū)表的分區(qū)上創(chuàng)建主鍵是可以的。
內(nèi)置分區(qū)表的內(nèi)部實現(xiàn)使用了繼承。
如果UPDATE語句的新記錄違反當(dāng)前分區(qū)鍵的約束則會報錯，UPDAET語句的新記錄目前不支持跨分區(qū)的情況。
性能方面：根據(jù)本節(jié)的測試場景，內(nèi)置分區(qū)表根據(jù)非分區(qū)鍵查詢相比普通表性能差距較大，因為這種場景分區(qū)表的執(zhí)行計劃會掃描所有分區(qū)；根據(jù)分區(qū)鍵查詢相比普通表性能有小幅降低，而查詢分區(qū)表子表性能相比普通表略有提升。

到此這篇關(guān)于PostgreSQL 分區(qū)表的實現(xiàn)示例的文章就介紹到這了,更多相關(guān)PostgreSQL 分區(qū)表內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章:

最新国产好看的视频,伊人天堂AV在线,国产Aaaaaa视频,蜜臀视频在线观看一区,人妻av色图,密臀久久久精品影片,青青视频免费观看毛片,久草在线观看视,国产三级精品色情在线

PostgreSQL 分區(qū)表的實現(xiàn)示例

目錄

一、分區(qū)表的意義

二、傳統(tǒng)分區(qū)表

2.1、繼承表

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

2.3、使用分區(qū)表

2.4、查詢父表還是子表

2.5、constraint_exclusion參數(shù)

2.6、添加分區(qū)

2.7、刪除分區(qū)

2.8、分區(qū)表相關(guān)查詢

2.9、傳統(tǒng)分區(qū)表注意事項

三、內(nèi)置分區(qū)表

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

3.2、使用分區(qū)表

3.3、內(nèi)置分區(qū)表原理

3.4、添加分區(qū)

3.5、刪除分區(qū)

3.6、更新分區(qū)數(shù)據(jù)

3.7、內(nèi)置分區(qū)表注意事項

相關(guān)文章

最新評論

大家感興趣的內(nèi)容

最近更新的內(nèi)容

常用在線小工具

最新国产好看的视频,伊人天堂AV在线,国产Aaaaaa视频,蜜臀视频在线观看一区,人妻av色图,密臀久久久精品影片,青青视频免费观看毛片,久草在线观看视,国产三级精品色情在线

PostgreSQL 分區(qū)表的實現(xiàn)示例

目錄

一、分區(qū)表的意義

二、傳統(tǒng)分區(qū)表

2.1、繼承表

2.2、創(chuàng)建分區(qū)表

2.3、使用分區(qū)表

2.4、查詢父表還是子表

2.5、constraint_exclusion參數(shù)

2.6、添加分區(qū)

2.7、刪除分區(qū)

2.8、分區(qū)表相關(guān)查詢

2.9、傳統(tǒng)分區(qū)表注意事項

三、內(nèi)置分區(qū)表

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

3.2、使用分區(qū)表

3.3、內(nèi)置分區(qū)表原理

3.4、添加分區(qū)

3.5、刪除分區(qū)

3.6、更新分區(qū)數(shù)據(jù)

3.7、內(nèi)置分區(qū)表注意事項

相關(guān)文章

最新評論

大家感興趣的內(nèi)容

最近更新的內(nèi)容

常用在線小工具

一、分區(qū)表的意義

二、傳統(tǒng)分區(qū)表

2.1、繼承表

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

2.3、使用分區(qū)表

2.4、查詢父表還是子表

2.7、刪除分區(qū)

2.8、分區(qū)表相關(guān)查詢

2.9、傳統(tǒng)分區(qū)表注意事項

三、內(nèi)置分區(qū)表

3.2、使用分區(qū)表

3.3、內(nèi)置分區(qū)表原理

3.5、刪除分區(qū)

3.6、更新分區(qū)數(shù)據(jù)

3.7、內(nèi)置分區(qū)表注意事項