Home PC Games Linux Windows Database Network Programming Server Mobile  
           
  Home \ Database \ TOAST function in PostgreSQL     - Installation under Linux Mint system guidelines for Gtk (Linux)

- Linux kernel boot to retain large memory method summary (Linux)

- Installation and deployment of MariaDB under CentOS (Database)

- Summary Linux bond of multi-interface load balancing (Linux)

- Binary search -Java achieve (Programming)

- Deploy the project to the server from GitHub (Server)

- Linux SSH remote connection service slow Solutions (Linux)

- To learn from scratch OpenWrt perfect tutorial (Linux)

- RedHat Linux source code package installation process (Linux)

- Why you should choose Python Programming (Programming)

- Ubuntu 12.04 64bit Install Source Insight 3.5 and create desktop icons (Linux)

- Linux, rename the file or folder (mv command and rename command) (Linux)

- Distributed transaction management Spring declarative transactions (Programming)

- Red Hat Enterprise Linux 6.4 Configuring VNC Remote Desktop login access (Linux)

- New experience Budgie (Budgerigar) desktop environment (Linux)

- CentOS 6.5 makes the LAN http source (Linux)

- Oracle 12c PDB Analysis (Database)

- Java implementation chain store binary tree (Programming)

- Analysis RabbitMQ cluster (Server)

- Reason C ++ program running under Linux a segmentation fault core dumped in (Programming)

 
         
  TOAST function in PostgreSQL
     
  Add Date : 2018-11-21      
         
         
         
  PostgreSQL contents TOAST is stored in another object of, and the original table is not the same OID. More like in Oracle LOB type storage (default out-of-line storage is stored in lobsegment Lane, Inline Storage storage space in the table).

 If the table has some of the fields using TOAST storage, then by conventional pg_relation_size ( 'table name') can not find the field TOAST space occupied. If you want to query TOAST space occupied by the field, you can check out the TOAST field corresponding OID, then check out the TOAST field space occupied by pg_relation_size (OID) manner.

- The following experiments pg_relation_size ( 'table name') can not find the space occupied by the field TOAST

testdb => create table t (id int, remark text);
 CREATE TABLE
 testdb => insert into t (id) select random () * 100 from generate_series (1,500);
 INSERT 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)
 TOAST field space occupied now 0

 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 (1,1000);
 INSERT 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)
 TOAST field occupied space has become 4000kB

 testdb => select pg_size_pretty (pg_total_relation_size ( 't'));
  pg_size_pretty
 ----------------
  4184 kB
 (1 row)
The result is isolated using pg_total_relation_size include TOAST field occupied space.

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 adding indexes, OID for the 24679 and 24682 are the same size, and the size of pg_total_relation_size increased, so the size is included in the index pg_total_relation_size occupied space.
     
         
         
         
  More:      
 
- RM Environment Database RMAN Backup Strategy Formulation (Database)
- Squid proxy server configuration under Linux (Server)
- Linux operating system log system (Linux)
- Incremental garbage collection mechanism for Ruby 2.2 (Programming)
- C ++ sequence containers basics summary (Programming)
- Spring multi data source configuration (Programming)
- Multipath configuration under Linux (Linux)
- MySQL my.cnf sql_mode schema modifications (Database)
- IOwait Linux system monitoring diagnostic tools (Linux)
- Vagrant build LNMP environment (Server)
- netfilter- in kernel mode network packet operation (Linux)
- Linux development environment to build and use the directory structure and file --Linux (Linux)
- Text editing and viewing text Linux command (Linux)
- Matters Oracle 11.2 single instance when connecting ASM need to pay attention and deal with the problem (Database)
- Download Manager uGet 2.0 installed in Debian, Ubuntu, Linux Mint and Fedora (Linux)
- systemd run levels and service management command Introduction (Linux)
- ORA-00824: can not set sga_target due to existing problem-solving (Database)
- Eclipse installs support for Java 8 (Linux)
- Solaris 11 forget the root password (Linux)
- Java code JIT compiler-friendly Mody (Programming)
     
           
     
  CopyRight 2002-2020 newfreesoft.com, All Rights Reserved.