一、引子
先来关注下面这个存储文章的表,假设每篇文章内容平均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=#
唯一索引,便于快速查找数据。