Skip to main content
Background Image
  1. PostgreSQL Posts/

Monitoring Table Size in PostgreSQL

·908 words·5 mins· ·
Ruohang Feng
Author
Ruohang Feng
Pigsty Founder, @Vonng
Table of Contents

Table Space Layout
#

In the broad sense, a Table includes two parts: the main table and TOAST table:

  • Main table: stores the relation’s own data, i.e., the narrow sense relation, relkind='r'.
  • TOAST table: corresponds one-to-one with the main table, stores oversized fields, relkind='t'.

Each table consists of main body and indexes - two Relations (for main tables, index relations may not exist):

  • Main relation: stores tuples.
  • Index relation: stores index tuples.

Each relation may have four forks:

  • main: the relation’s main file, numbered 0

  • fsm: stores information about free space in the main fork, numbered 1

  • vm: stores information about visibility in the main fork, numbered 2

  • init: used for unlogged tables and indexes, a rare special fork, numbered 3

Each fork is stored as one or more files on disk: files larger than 1GB are split into multiple segments of maximum 1GB each.

In summary, a table is not as simple as it appears - it consists of several relations:

  • Main table’s main relation (single)
  • Main table’s indexes (multiple)
  • TOAST table’s main relation (single)
  • TOAST table’s index (single)

Each relation may actually contain 1-3 forks: main (always exists), fsm, vm.

Getting Table’s Associated Relations
#

Use the following query to list all fork oids:

select
  nsp.nspname,
  rel.relname,
  rel.relnamespace    as nspid,
  rel.oid             as relid,
  rel.reltoastrelid   as toastid,
  toastind.indexrelid as toastindexid,
  ind.indexes
from
  pg_namespace nsp
  join pg_class rel on nsp.oid = rel.relnamespace
  , LATERAL ( select array_agg(indexrelid) as indexes from pg_index where indrelid = rel.oid) ind
  , LATERAL ( select indexrelid from pg_index where indrelid = rel.reltoastrelid) toastind
where nspname not in ('pg_catalog', 'information_schema') and rel.relkind = 'r';
 nspname |  relname   |  nspid  |  relid  | toastid | toastindexid |      indexes
---------+------------+---------+---------+---------+--------------+--------------------
 public  | aoi        | 4310872 | 4320271 | 4320274 |      4320276 | {4325606,4325605}
 public  | poi        | 4310872 | 4332324 | 4332327 |      4332329 | {4368886}

Statistical Functions
#

PostgreSQL provides a series of functions to determine the space occupied by various parts.

FunctionStatistical Scope
pg_total_relation_size(oid)Entire relation, including table, indexes, TOAST, etc.
pg_indexes_size(oid)Space occupied by relation’s index portion
pg_table_size(oid)Space occupied by relation excluding indexes
pg_relation_size(oid)Get size of a relation’s main file part (main fork)
pg_relation_size(oid, 'main')Get relation’s main fork size
pg_relation_size(oid, 'fsm')Get relation’s fsm fork size
pg_relation_size(oid, 'vm')Get relation’s vm fork size
pg_relation_size(oid, 'init')Get relation’s init fork size

Although physically a table consists of so many files, logically we usually only care about the size of two things: table and indexes. Therefore, the main functions used here are pg_indexes_size and pg_table_size, whose sum equals pg_total_relation_size for regular tables.

The table size portion can typically be calculated as:

 pg_table_size(relid)
 	= pg_relation_size(relid, 'main') 
 	+ pg_relation_size(relid, 'fsm') 
 	+ pg_relation_size(relid, 'vm') 
 	+ pg_total_relation_size(reltoastrelid)
 	
 pg_indexes_size(relid)
 	= (select sum(pg_total_relation_size(indexrelid)) where indrelid = relid)

Note that TOAST tables also have their own indexes, but there is only one, so using pg_total_relation_size(reltoastrelid) can calculate the overall size of the TOAST table.

Example: Statistics for a Specific Table and Related Relations UDTF#

SELECT
  oid,
  relname,
  relnamespace::RegNamespace::Text               as nspname,
  relkind                                        as relkind,
  reltuples                                      as tuples,
  relpages                                       as pages,
  pg_total_relation_size(oid)                    as size
  FROM pg_class
WHERE oid = ANY(array(SELECT 16418 as id -- main
UNION ALL SELECT indexrelid FROM pg_index WHERE indrelid = 16418 -- index
UNION ALL SELECT reltoastrelid FROM pg_class WHERE oid = 16418)); -- toast

This can be wrapped as a UDTF: pg_table_size_detail, for convenient use:

CREATE OR REPLACE FUNCTION pg_table_size_detail(relation RegClass)
  RETURNS TABLE(
    id      oid,
    pid     oid,
    relname name,
    nspname text,
    relkind "char",
    tuples  bigint,
    pages   integer,
    size    bigint
  )
AS $$
BEGIN
  RETURN QUERY
  SELECT
    rel.oid,
    relation::oid,
    rel.relname,
    rel.relnamespace :: RegNamespace :: Text as nspname,
    rel.relkind                              as relkind,
    rel.reltuples::bigint                    as tuples,
    rel.relpages                             as pages,
    pg_total_relation_size(oid)              as size
  FROM pg_class rel
  WHERE oid = ANY (array(
      SELECT relation as id -- main
      UNION ALL SELECT indexrelid FROM pg_index WHERE indrelid = relation -- index
      UNION ALL SELECT reltoastrelid FROM pg_class WHERE oid = relation)); -- toast
END;
$$
LANGUAGE PlPgSQL;

SELECT * FROM pg_table_size_detail(16418);

Sample return result:

geo=# select * from  pg_table_size_detail(4325625);
   id    |   pid   |        relname        | nspname  | relkind |  tuples  |  pages  |    size
---------+---------+-----------------------+----------+---------+----------+---------+-------------
 4325628 | 4325625 | pg_toast_4325625      | pg_toast | t       |   154336 |   23012 |   192077824
 4419940 | 4325625 | idx_poi_adcode_btree  | gaode    | i       | 62685464 |  172058 |  1409499136
 4419941 | 4325625 | idx_poi_cate_id_btree | gaode    | i       | 62685464 |  172318 |  1411629056
 4419942 | 4325625 | idx_poi_lat_btree     | gaode    | i       | 62685464 |  172058 |  1409499136
 4419943 | 4325625 | idx_poi_lon_btree     | gaode    | i       | 62685464 |  172058 |  1409499136
 4419944 | 4325625 | idx_poi_name_btree    | gaode    | i       | 62685464 |  335624 |  2749431808
 4325625 | 4325625 | gaode_poi             | gaode    | r       | 62685464 | 2441923 | 33714962432
 4420005 | 4325625 | idx_poi_position_gist | gaode    | i       | 62685464 |  453374 |  3714039808
 4420044 | 4325625 | poi_position_geohash6 | gaode    | i       | 62685464 |  172058 |  1409499136

Example: Relation Size Details Summary
#

select
  nsp.nspname,
  rel.relname,
  rel.relnamespace    as nspid,
  rel.oid             as relid,
  rel.reltoastrelid   as toastid,
  toastind.indexrelid as toastindexid,
  pg_total_relation_size(rel.oid)  as size,
  pg_relation_size(rel.oid) + pg_relation_size(rel.oid,'fsm') 
  + pg_relation_size(rel.oid,'vm') as relsize,
  pg_indexes_size(rel.oid)         as indexsize,
  pg_total_relation_size(reltoastrelid) as toastsize,
  ind.indexids,
  ind.indexnames,
  ind.indexsizes
from pg_namespace nsp
  join pg_class rel on nsp.oid = rel.relnamespace
  ,LATERAL ( select indexrelid from pg_index where indrelid = rel.reltoastrelid) toastind
  , LATERAL ( select  array_agg(indexrelid) as indexids,
                      array_agg(indexrelid::RegClass) as indexnames,
                      array_agg(pg_total_relation_size(indexrelid)) as indexsizes
              from pg_index where indrelid = rel.oid) ind
where nspname not in ('pg_catalog', 'information_schema') and rel.relkind = 'r';

Related

PostgreSQL Routine Maintenance
·130 words·1 min
Cars need oil changes, databases need maintenance. For PG, three important maintenance tasks: backup, repack, vacuum
Changing Engines Mid-Flight — PostgreSQL Zero-Downtime Data Migration
·681 words·4 mins
Data migration typically involves stopping services for updates. Zero-downtime data migration is a relatively advanced operation.
Using sysbench to Test PostgreSQL Performance
·301 words·2 mins
Although PostgreSQL provides pgbench, sometimes you need sysbench to outperform MySQL.
Batch Configure SSH Passwordless Login
·297 words·2 mins
Quick configuration for passwordless login to all machines
Wireshark Packet Capture Protocol Analysis
·982 words·5 mins
Wireshark is a very useful tool, especially suitable for analyzing network protocols. Here’s a simple introduction to using Wireshark for packet capture and PostgreSQL protocol analysis.
Common Linux Statistics CLI Tools
·2379 words·12 mins
top, free, vmstat, iostat: Quick reference for four commonly used CLI tools