This is my 10th day of the August Challenge. PostgreSQL uses fixed page sizes and does not allow tuples to span multiple pages. In order to store big data, PG introduced a TOAST technology -The paric-Attribute Storage Technique. This technique compresses or decomposes large data into multiple physical rows at the bottom level, and the processing is user-insensitive. The database uses different storage types for each data type by default. You can also change the storage type of the column if you are not satisfied with it. The statement that changes the storage type of the column is:

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

There may be some minor problems with the modifications. So let’s do that.

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
Copy the code

For tables with out-of-row storage, you can confirm toast information with the following statement.

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 24885Copy the code

Insert a piece of data and then check

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 24882Copy the code

Insert bar more than 8K data is looking up

[postgres13@rhel711g 13577]$ls-lrth {24882,24885} -rw------- 1 postgres13 postgres13 8.0k Aug 10 16:1824882 -rw------- 1 postgres13 postgres13 8.0K Aug 10 16:20 24885Copy the code

The first one we want to change to inline storage uses the following command:

postgres=# alter table toast_1 alter name set storage PLAIN ;
ALTER TABLE
Copy the code

After checking the TOAST information again, it is found that the out-of-line storage still exists.

postgres=# select reltoastrelid,oid,relname from pg_class where relname='toast_1';
 reltoastrelid |  oid  | relname 
---------------+-------+---------
         24885 | 24882 | toast_1
(1 row)
Copy the code

The reason is that after the ALTER TABLE storage mode is changed, only new data is affected. Existing data is still stored in the previous storage mode. In this case, if the storage mode is valid relative to all data, vacuum full needs to be executed.

Then came the second pit.

postgres=# vacuum FULL toast_1;
ERROR:  row is too big: size 30696, maximum size 8160
Copy the code

The reason is that the tuple exceeds the size of a page and cannot be stored and can only be stored outside the line. Therefore, extra-large data can only be stored outside the line. If normal, a vacuum full effect is executed.

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)
Copy the code

Since this Relation has no out-of-row columns, the TOAST table is recycled and the reltoastrelid column is 0.

Therefore, after the storage mode is changed, you must run vacuum FULL to apply the new storage mode to historical data