PostgreSQL - TOAST ( The Oversized-Attribute Storage Technique )

一、引子

先来关注下面这个存储文章的表，假设每篇文章内容平均1M，存储1000篇文章就需要1G空间，1W篇文件就是10G空间。这时，需要统计已收集文章的篇数，运行统计语句select count(1) from article，虽然只有1W篇文章，但需要扫描10G的文件，运行效率可想而知。

那么有没有一种方法，能快速运行统计SQL，又能正确加载出文章的内容呢？有一种想法就是把content字段存储到另外的位置，只在原表中存储指向对应数据的指针。

在PG中，每个page默认大小是8KB（可以在编译源代码时指定），而且一行数据不允许夸页面存储。像遇到这样的情形，PG中就是使用行外存储的方式来处理的。这样既可以加快统计SQL的运行速度，有不影响文章内容正确的存取。

postgres=# create table article(id int primary key, title text, content text);
CREATE TABLE
postgres=# \d+ article
                                   Table "public.article"
 Column  |  Type   | Collation | Nullable | Default | Storage  | Stats target | Description
---------+---------+-----------+----------+---------+----------+--------------+-----------
 id      | integer |           | not null |         | plain    |              |
 title   | text    |           |          |         | extended |              |
 content | text    |           |          |         | extended |              |
Indexes:
    "article_pkey" PRIMARY KEY, btree (id)
postgres=#

二、TOAST存储方式

先来普及一个概念，在PG中，字段的存储方式分为四种，见上节表结构中Storage字段

1、PLAIN，这种方式存储的字段，既不允许压缩存储，由不允许行外存储，像int，boolean类型的数据。

2、EXTENDED，这种方式存储的字段，既可以压缩存储，又可以行外存储。这是大部分变长字段的存储方式。

3、EXTERNAL，这种方式存储的字段，可以做行外存储，但不可以压缩存储。这样存储去除压缩和解压消耗时间，提供操作效率，缺点是占用存储空间变大。

4、MAIN，这种方式存储的字段，可以压缩，但不可以行外存储。事实是，如果压缩后再一个page中还是无法存储，也需要行外存储来解决。

可以通过SQL改变一个字段的存储方式：

postgres=# alter table article alter COLUMN content set storage external ;
ALTER TABLE
postgres=# \d+ article
                                   Table "public.article"
 Column  |  Type   | Collation | Nullable | Default | Storage  | Stats target | Description
---------+---------+-----------+----------+---------+----------+--------------+----------
 id      | integer |           | not null |         | plain    |              |
 title   | text    |           |          |         | extended |              |
 content | text    |           |          |         | external |              |
Indexes:
    "article_pkey" PRIMARY KEY, btree (id)
​
postgres=#

在article表中，title和content字段都允许行外存储，那么行外数据存储到哪里呢？pg_class表中的reltoastrelid字段就是TOAST表的oid。找出TOAST表的schema和表名，查看表信息。

chunk_id和chunk_seq标记出唯一约束，chunk_data存储实际数据。

postgres=# select oid,relname,reltoastrelid from pg_class where relname = 'article';
  oid  | relname | reltoastrelid
-------+---------+---------------
 16468 | article |         16471
(1 row)
postgres=# select nspname,relname from pg_class a inner join pg_namespace b on a.relnamespace = b.oid where a.oid = 16471;
 nspname  |    relname
----------+----------------
 pg_toast | pg_toast_16468
(1 row)
postgres=# \d+ pg_toast.pg_toast_16468
TOAST table "pg_toast.pg_toast_16468"
   Column   |  Type   | Storage
------------+---------+---------
 chunk_id   | oid     | plain
 chunk_seq  | integer | plain
 chunk_data | bytea   | plain
​
postgres=#

下面我们来看一个例子，看行外数据是如何存储的。可以看出8KB的数据被成5块，每一块存储到一行，1996*4+208=8192。chunk_id和chunk_seq组成唯一约束。

postgres=# insert into article values(1,'a',repeat('a',8192));
INSERT 0 1
postgres=# select chunk_id,chunk_seq,length(chunk_data) from pg_toast.pg_toast_16468;
 chunk_id | chunk_seq | length
----------+-----------+--------
    16476 |         0 |   1996
    16476 |         1 |   1996
    16476 |         2 |   1996
    16476 |         3 |   1996
    16476 |         4 |    208
(5 rows)
​
postgres=#

在原来的表字段上，只需要存储一个18 bytes指针数据，包括TOAST表的oid、对于数据的chunk_id、数据压缩前的大小和压缩后的大小。

三、TOAST表索引

postgres=# select indexrelid,indrelid from pg_index where indrelid = 16471;
 indexrelid | indrelid
------------+----------
      16473 |    16471
(1 row)
​
postgres=#
postgres=# select pg_get_indexdef(16473);
​
                                             pg_get_indexdef
CREATE UNIQUE INDEX pg_toast_16468_index ON pg_toast.pg_toast_16468 USING btree (chunk_id, chunk_seq)
(1 row)
​
postgres=#

唯一索引，便于快速查找数据。

 

转载于:https://my.oschina.net/207miner/blog/2994869