PostgreSQL数据库修改行外存储（TOAST）可能遇到的坑

　　PostgreSQL使用固定的页面大小，并且不允许元组跨越多个页面。为了存储大数据，PG引入了TOAST技术-The Oversized-Attribute Storage Technique。这种技术在底层将大的数据压缩或分解成多个物理行，并且这些处理对用户都是无感的。

　　数据库会默认为各类数据类型应用不同的存储类型。

　　对列的存储类型不满意时也可以进行修改。

　　修改列的存储类型的语句是：

　　ALTER TABLE name ALTER [ COLUMN ] column_name SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN }

　　修改可能会遇到一些小问题。

　　下边演示一下。

　　postgres=# create table toast_1 (id int ,name text);

　　CREATE TABLE

　　postgres=# \d+ toast_1

　　Table "public.toast_1"

　　Column | Type | Collation | Nullable | Default | Storage | Stats target | Description

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

　　id | integer | | | | plain | |

　　name | text | | | | extended | |

　　Access method: heap

　　针对有行外存储的表，可以通过如下语句确认toast相关信息。

　　postgres=# select reltoastrelid,oid,relname from pg_class where relname='toast_1';

　　reltoastrelid | oid | relname

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

　　24885 | 24882 | toast_1

　　(1 row)

　　[postgres13@rhel711g 13577]$ ls -lrth {24882,24885}

　　-rw------- 1 postgres13 postgres13 0 Aug 10 16:11 24882

　　-rw------- 1 postgres13 postgres13 0 Aug 10 16:11 24885

　　插入一条数据后再查

　　postgres=# insert into toast_1 values (1,'1');

　　INSERT 0 1

　　[postgres13@rhel711g 13577]$ ls -lrth {24882,24885}

　　-rw------- 1 postgres13 postgres13 0 Aug 10 16:11 24885

　　-rw------- 1 postgres13 postgres13 8.0K Aug 10 16:16 24882

　　插入条超过8k的数据在查

　   [postgres13@rhel711g 13577]$ ls -lrth {24882,24885}

　　-rw------- 1 postgres13 postgres13 8.0K Aug 10 16:18 24882

　　-rw------- 1 postgres13 postgres13 8.0K Aug 10 16:20 24885

　　坑来了大连无痛人流医院 http://www.bhbyby.net/

　　第一个

　　我们想改成行内存储

　　使用如下命令：

　　postgres=# alter table toast_1 alter name set storage PLAIN ;

　　ALTER TABLE

　　再次查询toast信息，发现还是存在行外存储。

　　postgres=# select reltoastrelid,oid,relname from pg_class where relname='toast_1';

　　reltoastrelid | oid | relname

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

　　24885 | 24882 | toast_1

　　(1 row)

　　原因是通过alter table修改存储方式后，只对新数据有影响，现有数据还是按照之前存储方式存放。

　　这样如果相对所有数据生效存储方式的话，需要执行vacuum full。

　　这时，第二个坑来了。

　　postgres=# vacuum FULL toast_1;

　　ERROR: row is too big: size 30696, maximum size 8160

　　提示行过大，原因就是该元组超过了一个页面的大小，没法存放只能使用行外存储。

　　因此对于超大的数据，只能采用行外存储方式。

　　如果正常的话，执行完vacuum full的效果。

　　postgres=# vacuum FULL toast_1;

　　VACUUM

　　postgres=# select reltoastrelid,oid,relname from pg_class where relname='toast_1';

　　reltoastrelid | oid | relname

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

　　0 | 24882 | toast_1

　　(1 row)

　　因为该relation没有行外存储的列，所以toast表就被回收掉了，reltoastrelid列就成0了。

　　所以修改存储方式后，务必执行vacuum FULL才能对历史数据应用新设置的存储方式~~