테이블의 물리적 크기를 측정하는 방법

[링크 : https://www.a2hosting.com/.../postgresql/determining-the-size-of-postgresql-databases-and-tables]

 

table 별 table / index / toast(이게 먼지 모르겠음) 용량 byte와 MB 단위로 나오는 쿼리문

SELECT *, pg_size_pretty(total_bytes) AS total
    , pg_size_pretty(index_bytes) AS INDEX
    , pg_size_pretty(toast_bytes) AS toast
    , pg_size_pretty(table_bytes) AS TABLE
  FROM (
  SELECT *, total_bytes-index_bytes-COALESCE(toast_bytes,0) AS table_bytes FROM (
      SELECT c.oid,nspname AS table_schema, relname AS TABLE_NAME
              , c.reltuples AS row_estimate
              , pg_total_relation_size(c.oid) AS total_bytes
              , pg_indexes_size(c.oid) AS index_bytes
              , pg_total_relation_size(reltoastrelid) AS toast_bytes
          FROM pg_class c
          LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
          WHERE relkind = 'r'
  ) a
) a;

[링크 : https://wiki.postgresql.org/wiki/Disk_Usage]

 

+

LZ 압축 기술로 압축하여 저장하는 공간인가?

 

TOAST(대형 속성 저장 기술:The Oversized-Attribute Storage Technique)

[링크 : http:// http://www.postgresql.org/docs/9.4/static/storage-toast.html]

[링크 : https://data-rider.blogspot.com/2015/07/postgresql-toast.html]

Posted by 구차니