TOAST function in PostgreSQL

TOAST function in PostgreSQL

The content of TOAST in PostgreSQL is stored in another object, which is different from the OID of the original table. Similar to the LOB type storage in Oracle (the out-of-line Storage is stored in lobsegment by default, and the Inline storage is stored in the table space ).

If some fields in the table are stored using TOAST, the space occupied by the TOAST field cannot be queried through the general pg_relation_size ('table name. To query the space occupied by the TOAST field, you can first query the OID corresponding to the TOAST field, and then query the space occupied by the TOAST field through pg_relation_size (OID.

-- The following experiment shows that pg_relation_size ('table name') cannot query the space occupied by the TOAST field.

Testdb => create table t (id int, remark text );
CREATE TABLE
Testdb => insert into t (id) select random () * 100 from generate_series (1,500 );
Inserts 0 500

Testdb => select oid, relname, reltoastrelid from pg_class where relname = 'T ';
Oid | relname | reltoastrelid
------- + --------- + ---------------
24679 | t | 24682
(1 row)

Testdb => select pg_size_pretty (pg_relation_size (24679 ));
Pg_size_pretty
----------------
24 kB
(1 row)

Testdb => select pg_size_pretty (pg_relation_size (24682 ));
Pg_size_pretty
----------------
0 bytes
(1 row)
The TOAST field currently occupies 0 space.

Testdb => select pg_size_pretty (pg_total_relation_size ('T '));
Pg_size_pretty
----------------
56 kB
(1 row)

Testdb => insert into t (remark) select repeat (md5 (random (): text), 10000) from generate_series );
Inserts 0 1000
Testdb => select pg_size_pretty (pg_relation_size (24679 ));
Pg_size_pretty
----------------
72 kB
(1 row)

Testdb => select pg_size_pretty (pg_relation_size (24682 ));
Pg_size_pretty
----------------
4000 kB
(1 row)
The space occupied by the TOAST field has changed to kb.

Testdb => select pg_size_pretty (pg_total_relation_size ('T '));
Pg_size_pretty
----------------
4184 kB
(1 row)
The results obtained using pg_total_relation_size include the space occupied by the TOAST field.

Testdb => create index idx_id_id on t (id );
CREATE INDEX

Testdb => select pg_size_pretty (pg_relation_size (24679 ));
Pg_size_pretty
----------------
72 kB
(1 row)

Testdb => select pg_size_pretty (pg_relation_size (24682 ));
Pg_size_pretty
----------------
4000 kB
(1 row)

Testdb => select pg_size_pretty (pg_total_relation_size ('T '));
Pg_size_pretty
----------------
4240 kB
(1 row)

After the index is added, the size of OID 24679 and 24682 remains unchanged, while the size of pg_total_relation_size increases. Therefore, the size of pg_total_relation_size includes the space occupied by the index.

------------------------------------ Lili split line ------------------------------------

Install the PostgreSQL 9.3.5 database in Ubuntu Server 14.04

Install PostgreSQL 6.3 on yum in CentOS 9.3

PostgreSQL cache details

Compiling PostgreSQL on Windows

Configuration and installation of LAPP (Linux + Apache + PostgreSQL + PHP) Environment in Ubuntu

Install and configure phppgAdmin on Ubuntu

Install PostgreSQL9.3 on CentOS

Configure a Streaming Replication cluster in PostgreSQL

How to install PostgreSQL 7/6 and phpPgAdmin in CentOS 5/6. 4

------------------------------------ Lili split line ------------------------------------

PostgreSQL details: click here
PostgreSQL: click here

This article permanently updates the link address: