PostgreSQL 如何修改文本類型字段的存儲方式

Posted on by

PostgreSQL存儲方式分為以下4種:

PLAIN避免壓縮或者線外存儲；而且它禁用變長類型的單字節頭部。這是不可TOAST數據類型列的唯一可能的策略。隻是對那些不能TOAST的數據類型才有可能。

EXTENDED允許壓縮和線外存儲。這是大多數可TOAST數據類型的默認策略。 首先將嘗試進行壓縮，如果行仍然太大，那麼則進行線外存儲。

EXTERNAL允許線外存儲，但是不許壓縮。使用EXTERNAL將令那些在寬text和 bytea列上的子串操作更快（代價是增加瞭存儲空間）， 因此這些操作被優化為隻抓取未壓縮線外數據中需要的部分。

MAIN允許壓縮，但不允許線外存儲（實際上，在這樣的列上仍然會進行線外存儲，但隻是作為沒有辦法把行變得足以放入一頁的情況下的最後手段）。

文本的默認存儲方式為EXTENDED,但是在文本字段非常小的情況下,為提升性能我們可以將文本存儲方式改為PLAIN.

創建表:

drop table if exists test;
create table test(
 f1 char(1),
 f2 varchar(16),
 f3 text 
 );

查看表定義:

\dS+ test;

Column | Type | Collation | Nullable | Default | Storage | Stats target | Description 
--------+-----------------------+-----------+----------+---------+----------+--------------+-------------
f1 | character(1) | | | | extended | | 
f2 | character varying(16) | | | | extended | | 
f3 | text | | | | extended | |

修改f1和f2的存儲方式:

列f1,f2因為非常小,采用EXTENDED存儲方式大量數據時性能不如PLAIN好,因此在創建表完成後可以使用以下語法修改列f1和f2的存儲方式為PLAIN.

alter table test 
 alter column f1 set storage plain,
 alter column f2 set storage plain;

重新查看定義:

\dS+ test;

Column |  Type  | Collation | Nullable | Default | Storage | Stats target | Description 
--------+-----------------------+-----------+----------+---------+----------+--------------+-------------
f1 | character(1)  |  |  |  | plain |  | 
f2 | character varying(16) |  |  |  | plain |  | 
f3 | text   |  |  |  | extended |  |

對於已經有數據的表修改存儲方式後的操作

首先修改字段的存儲方式,然後運行

vacuum full verbose test;

vacuum full 選項可以回收空間，但有排他鎖,它實際上創建該表的一個新拷貝，並且在操作完成之前都不會釋放舊的拷貝。

因為會長時間鎖表,請慎重使用vacuum full

補充：修改PostgreSQL的字段數據類型報錯

修改字段類型

1.使用可視化工具修改列數據類型 ：

註意：字段類型轉varchar 或者 從 varchar 改成其它數據類型是不會出問題的。

但是直接從 money類型轉到int類型（數字）就會報錯。這時需要先轉換成varchar 然後再轉換成指定的類型。

2. 使用SQL語句修改

註意：使用sql 方式修改字段類型，也需要使用 varchar 過度，例如將 money 類型的字段轉成 int4

類型，那麼將執行

alter table test alter column filed type varchar using filed:: varchar
alter table test alter column filed type int4 using filed:: int4

alter table test alter column filed type varchar using filed:: varchar
-- test 表名
-- filed 字段名

以上為個人經驗，希望能給大傢一個參考，也希望大傢多多支持WalkonNet。如有錯誤或未考慮完全的地方，望不吝賜教。

推薦閱讀：