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

瀚高PG实验室（Highgo PG Lab）- 徐云鹤

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)
[[email protected] 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
[[email protected] 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的数据在查

[[email protected] 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
           

坑来了

第一个

我们想改成行内存储

使用如下命令：

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才能对历史数据应用新设置的存储方式~~