Postgres的TOAST技术(二)
postgres=# select relname,reltoastrelid from pg_class where relname = 't_kenyon';
relname | reltoastrelid
----------+---------------
t_kenyon | 0
(1 row)
postgres=# \d+ t_kenyon
Table "public.t_kenyon"
Column | Type | Modifiers | Storage | Stats target | Description
--------+---------+-----------+---------+--------------+-------------
id | integer | | plain | |
Has OIDs: no
上面的字段没有toast表，因为字段int是定长的。
Example b:
postgres=# select relname,reltoastrelid from pg_class where relname = 't_kenyon';
relname | reltoastrelid
----------+---------------
t_kenyon | 16411
(1 row)
postgres=# select relname from pg_class where oid = 16411;
relname
----------------
pg_toast_16408
(1 row)
2.TOAST表计算大小
postgres=# drop table t_kenyon;
DROP TABLE
postgres=# create table t_kenyon(id int,vname varchar(48),remark text);
CREATE TABLE
postgres=# \d+ t_kenyon
Table "public.t_kenyon"
Column | Type | Modifiers | Storage | Stats target | Description
--------+-----------------------+-----------+----------+--------------+-------------
id | integer | | plain | |
vname | character varying(48) | | extended | |
remark | text | | extended | |
Has OIDs: no
postgres=# select oid,relname,reltoastrelid from pg_class where relname = 't_kenyon';
oid | relname | reltoastrelid
--------+----------+---------------
121174 | t_kenyon | 121177
(1 row)
postgres=# insert into t_kenyon select generate_series(1,2000),repeat('kenyon here'||'^_^',2),repeat('^_^ Kenyon is not God',500);
INSERT 0 2000
postgres=# insert into t_kenyon select generate_series(1,2),repeat('kenyon here'||'^_^',2),repeat('^_^ Kenyon is not God,Remark here!!',2000);
INSERT 0 2
postgres=# select pg_column_size(id),pg_column_size(vname),pg_column_size(remark) from t_kenyon limit 2;
pg_column_size | pg_column_size | pg_column_size
----------------+----------------+----------------
4 | 29 | 851
4 | 29 | 851
(2 rows)
--查看基础表和Toast的大小
postgres=# select pg_relation_size(121174);
pg_relation_size
------------------
8192
(1 row)
postgres=# select pg_relation_size(121177);
pg_relation_size
------------------
0
(1 row)
--文本数据量增多，这时可以看到toast表字段大小在2kb左右时有大小了
postgres=# insert into t_kenyon select generate_series(3,4),repeat('kenyon here'||'^_^',2),repeat('^_^ Kenyon is not God,Remark here!!',4000);
INSERT 0 2
postgres=# select pg_relation_size(121174);
pg_relation_size
------------------
8192
(1 row)
postgres=# select pg_relation_size(121177);
pg_relation_size
------------------
0
(1 row)
postgres=# insert into t_kenyon select generate_series(5,6),repeat('kenyon here'||'^_^',2),repeat('^_^ Kenyon is not God,Remark here!!',5500);
INSERT 0 2
postgres=# select pg_column_size(id),pg_column_size(vname),pg_column_size(remark) from t_kenyon;
pg_column_size | pg_column_size | pg_column_size
----------------+----------------+----------------
4 |