PostgreSQL從小白到專(zhuān)家，是從入門(mén)逐漸能力提升的一個(gè)系列教程，內(nèi)容包括對(duì)PG基礎(chǔ)的認(rèn)知、包括安裝使用、包括角色權(quán)限、包括維護(hù)管理、、等內(nèi)容，希望對(duì)熱愛(ài)PG、學(xué)習(xí)PG的同學(xué)們有幫助，歡迎持續(xù)關(guān)注CUUG PG技術(shù)大講堂。

第24講：TOAST技術(shù)

內(nèi)容1 : Toast簡(jiǎn)介

內(nèi)容2 : Toast的存儲(chǔ)方式

內(nèi)容3 : Toast4種壓縮策略

內(nèi)容4 : Toast表的計(jì)算方式

內(nèi)容5 : Toast表的優(yōu)點(diǎn)與缺點(diǎn)

內(nèi)容6 : 與Oracle大對(duì)象存儲(chǔ)方式對(duì)比

Toast簡(jiǎn)介

· Toast技術(shù)產(chǎn)生背景

元組不允許跨頁(yè)面存儲(chǔ)

· TOST技術(shù)特點(diǎn)

Toast是超長(zhǎng)字段在PG的一個(gè)存儲(chǔ)方式

全稱(chēng)The OverSized Attribute Storage Technique(超尺寸字段存儲(chǔ)技術(shù))

它會(huì)將大字段值壓縮或者分散為多個(gè)物理行來(lái)存儲(chǔ)

對(duì)于用戶(hù)來(lái)說(shuō)不用關(guān)注這一技術(shù)實(shí)現(xiàn)，完全是透明的

Toast的存儲(chǔ)方式

· PG的部分類(lèi)型數(shù)據(jù)支持toast，因?yàn)橛行┳侄晤?lèi)型是不會(huì)產(chǎn)生大字段數(shù)據(jù)(比如date,time,boolean等)

· 支持Toast的數(shù)據(jù)類(lèi)型應(yīng)當(dāng)是可變長(zhǎng)度的(variable-length)

· 表中任何一個(gè)字段有Toast，這個(gè)表都會(huì)有這一個(gè)相關(guān)聯(lián)的Toast表，OID被存儲(chǔ)在pg_class.reltoastrelid里

· 超出的的數(shù)值將會(huì)被分割成chunks，并且最多toast_max_chunk_size 個(gè)byte(缺省是2KB)

· 當(dāng)存儲(chǔ)的列長(zhǎng)度超過(guò)toast_tuple_threshold值(通常是2KB)，就會(huì)觸發(fā)toast存儲(chǔ)

· toast將會(huì)壓縮或者移動(dòng)字段值直到超出部分比toast_tuple_targer值小(這個(gè)值通常也是2KB)。

建表時(shí)自動(dòng)創(chuàng)建Toast表

--創(chuàng)建表

create table toast_t(id int,vname varchar(48),remark text);

--其中remak數(shù)據(jù)類(lèi)型是text，列值長(zhǎng)度超過(guò)2KB則就會(huì)自動(dòng)產(chǎn)生toast表來(lái)存儲(chǔ)。

更改表的存儲(chǔ)方式為T(mén)oast

語(yǔ)法：

ALTER TABLE toast_t ALTER COLUMN vname

SET STORAGE {PLAIN | EXTENDED | MAIN | EXTERNAL};

示例：

create table toast_t1(dd character varying);

alter table toast_t1 alter column dd set storage main;

/d+ toast_1

Column | Type | Storage |

--------+-----+---------+-

dd | character varying | main |

Access method: heap

查看Toast表的名字

--查看tost表的oid

testdb=# select relname,relfilenode,reltoastrelid from pg_class where relname='toast_t1';

relname | relfilenode | reltoastrelid

----------+-------------+---------------

toast_t1 | 16385 | 16389

--根據(jù)tost表oid查看其名字

testdb=# select relname from pg_class where oid = '16389';

relname

----------------

pg_toast_16385

Toast4種策略

策略：PLAIN

說(shuō)明：避免壓縮和行外存儲(chǔ)。

只有那些不需要 TOAST 策略就能存放的數(shù)據(jù)類(lèi)型允許選擇(例如 int 類(lèi)型)，而對(duì)于 text 這類(lèi)要求存儲(chǔ)長(zhǎng)度超過(guò)頁(yè)大小的類(lèi)型，是不允許采用此策略的。

策略：MAIN

說(shuō)明：允許壓縮，但不許行外存儲(chǔ)。

不過(guò)實(shí)際上，為了保證過(guò)大數(shù)據(jù)的存儲(chǔ)，行外存儲(chǔ)在其它方式(例如壓縮)都無(wú)法滿足需求的情況下，作為最后手段還是會(huì)被啟動(dòng)。因此理解為盡量不使用行外存儲(chǔ)更貼切。

策略：EXTENDED

說(shuō)明：允許行外存儲(chǔ)和壓縮。

一般會(huì)先壓縮，如果還是太大，就會(huì)行外存儲(chǔ)

策略：EXTERNA

說(shuō)明：允許行外存儲(chǔ)，但不許壓縮。

類(lèi)似字符串這種會(huì)對(duì)數(shù)據(jù)的一部分進(jìn)行操作的字段，采用此策略可能獲得更高的性能，因?yàn)椴恍枰�讀取出整行數(shù)據(jù)再解壓。

Toast表額外的三個(gè)字段

字段名：chunk_id

屬性：標(biāo)識(shí)TOAST表的OID字段

字段名：chunk_seq

屬性：chunk的序列號(hào)，與chunk_id的組合唯一索引可以加速訪問(wèn)

字段名：chunk_data

屬性：存儲(chǔ)TOAST表的實(shí)際數(shù)據(jù)

--查看tost表oid

testdb=# select relname,oid,rreltoastrelid from pg_class where relname='toast_t1';

relname | relfilenode | reltoastrelid

----------+-------------+---------------

toast_t1 | 16385 | 16389

--查看tost表結(jié)構(gòu)，tost表屬于pg_tost模式

testdb=# \d+ pg_toast.pg_toast_16385

Column | Type | Storage

------------+---------+---------

chunk_id | oid | plain

chunk_seq | integer | plain

chunk_data | bytea | plain

Toast表的計(jì)算

計(jì)算一個(gè)表的大小時(shí)要注意統(tǒng)計(jì)Toast的大小，因?yàn)閷?duì)超長(zhǎng)字段存儲(chǔ)時(shí)，在基礎(chǔ)表上可能只存了20%，另外的數(shù)據(jù)都存到了Toast里面去了，計(jì)算大小時(shí)要結(jié)合起來(lái)看

索引也是一樣，對(duì)于表里有extended或者EXTERNA 類(lèi)型的會(huì)創(chuàng)建Toast表，兩者的關(guān)聯(lián)是通過(guò)pg_class里的OID去關(guān)聯(lián)的

· Toast表的計(jì)算案例(一)

testdb=# create table toast_t(id int,vname varchar(48),remark text);

CREATE TABLE

testdb=# select relname,oid from pg_class where relname = 'toast_t';

relname | oid

---------+-------

toast_t | 16392

testdb=# select relname,reltoastrelid from pg_class where relname = 'toast_t';

relname | reltoastrelid

---------+---------------

toast_t | 16395

testdb=# select relname from pg_class where oid = '16395';

relname

----------------

pg_toast_16392

· Toast表的計(jì)算案例(二)

--插入數(shù)據(jù)，此時(shí)remark列值長(zhǎng)度小于2KB，所以不會(huì)觸發(fā)tost存儲(chǔ)：

insert into toast_t

select generate_series(1,4),repeat('kenyon here'||'^_^',2),repeat('^_^ Kenyon is not God,Remark here!!',2000);

--查看表中列值大小：

testdb=# select pg_column_size(id),pg_column_size(vname),pg_column_size(remark) from toast_t limit 10;

pg_column_size | pg_column_size | pg_column_size

----------------+----------------+----------------

4 | 29 | 851

4 | 29 | 851

4 | 29 | 851

4 | 29 | 851

· Toast表的計(jì)算案例(三)

--查看基礎(chǔ)表和 Toast 的大小

testdb=# select pg_size_pretty(pg_relation_size('toast_t'));

pg_size_pretty

----------------

8192 bytes

--查看tost表尺寸

testdb=# select pg_size_pretty(pg_relation_size('16395'));

pg_size_pretty

----------------

0 bytes

此時(shí)remark列值長(zhǎng)度小于2KB，所以不會(huì)觸發(fā)tost存儲(chǔ)。

· Toast表的計(jì)算案例(四)

--remark列值超過(guò) 2kb 左右時(shí)觸發(fā)了tost存儲(chǔ)方式

insert into toast_t select generate_series(3,4),repeat('kenyon here'||'^_^',2),repeat('^_^ Kenyon is not God,Remark here!!',5500);

testdb=# select pg_size_pretty(pg_relation_size('16395'));

pg_size_pretty

----------------

8192 bytes

· Toast表的計(jì)算案例(五)

--查看各列的數(shù)據(jù)變化，說(shuō)明在列尺寸超過(guò)2k的時(shí)候就會(huì)把數(shù)據(jù)存放到toast表中：

testdb=# select pg_column_size(id),pg_column_size(vname),pg_column_size(remark) from toast_t;

pg_column_size | pg_column_size | pg_column_size

----------------+----------------+----------------

4 | 29 | 851

4 | 29 | 851

4 | 29 | 851

4 | 29 | 851

4 | 29 | 1651

4 | 29 | 1651

4 | 29 | 2247

4 | 29 | 2247

· Toast表的計(jì)算案例(六)

--繼續(xù)插入更多的數(shù)據(jù)：

insert into toast_t select generate_series(1,2),repeat('kenyon here'||'^_^',2),repeat('^_^ Kenyon is not God,Remark here!!',10000);

--查看toast表大小：

testdb=# select pg_size_pretty(pg_relation_size('16395'));

pg_size_pretty

----------------

16 kB

--繼續(xù)插入更多的數(shù)據(jù)，20000

可以看到后插入的數(shù)據(jù)隨著字段內(nèi)容的增多，toast 段一直在變大。基礎(chǔ)表的大小沒(méi)有變化。這個(gè)和 Oracle 存儲(chǔ)的大字段內(nèi)容比較像，Oracle 存儲(chǔ) Blob和clob 類(lèi)的數(shù)據(jù)時(shí)也是指定另外的 segment 來(lái)存儲(chǔ)，而不是在原表中存儲(chǔ)，當(dāng)然可以設(shè)置 enable storage in row 來(lái)指定表中存儲(chǔ)

Toast表的優(yōu)點(diǎn)

1.可以存儲(chǔ)超長(zhǎng)超大字段，避免之前不能直接存儲(chǔ)的限制

2.物理上與普通表是分離的，檢索查詢(xún)時(shí)不檢索到該字段會(huì)極大地加快速度

3.更新普通表時(shí)，該表的Toast數(shù)據(jù)沒(méi)有被更新時(shí)，不用去更新Toast表

Toast表的缺點(diǎn)

1.對(duì)大字段的索引創(chuàng)建是一個(gè)問(wèn)題，有可能會(huì)失敗，通常不建議在大字段上創(chuàng)建，全文檢索是一個(gè)解決方案

2.大字段的更新會(huì)有點(diǎn)慢，其它DB也存在相同問(wèn)題

Oracle大對(duì)象段存儲(chǔ)特點(diǎn)

11g版本中推出了針對(duì) LOB字段處理的新技術(shù)：SecureFiles

該技術(shù)在性能、可管理性、易用性等方面，具有如下具體特點(diǎn)和優(yōu)勢(shì)：

· 提供數(shù)據(jù)去重、壓縮和透明加密功能

· SecureFiles不僅可以有效降低LOB字段存儲(chǔ)空間消耗，提高了訪問(wèn)效率，而且提高了LOB字段的數(shù)據(jù)安全性。

以上述某系統(tǒng)為例，我們將其中一個(gè)100GB的LOB字段轉(zhuǎn)換為SecureFiles，并采用壓縮技術(shù)之后，最終只消耗30GB空間，大大壓縮了存儲(chǔ)空間。

·新的網(wǎng)絡(luò)協(xié)議

SecureFiles提供一種新的Client/Server方式的內(nèi)部讀寫(xiě)機(jī)制，有效提高了大量數(shù)據(jù)傳輸?shù)男�率�?/p>

· 簡(jiǎn)化物理屬性設(shè)計(jì)和管理

SecureFiles提供了大量自動(dòng)化的物理屬性機(jī)制，免去了大量物理屬性設(shè)計(jì)和管理工作。例如：CHUNK屬性為可變長(zhǎng)，最大能支持到64M;Oracle能自動(dòng)進(jìn)行碎片整理;

· SecureFiles還自動(dòng)進(jìn)行redo和undo的管理，避免大量不必要的redo和 undo信息的產(chǎn)生。

以上就是【PostgreSQL從小白到專(zhuān)家】第24講 - TOAST技術(shù)  的內(nèi)容，歡迎一起探討交流釘釘交流群：35，82，24，60，往期視頻及文檔內(nèi)容聯(lián)系CUUG