Significant size differences between size calculations for table(s) in PostgreSQL

Question

We are in the process of offering PostgreSQL as an alternative to Oracle, which is currently our only database. For making accurate predictions as to the required mount point sizes for the converted data we would need to find out how much space is used for the data from Oracle when converted to PostgreSQL.

We have this table on which is one of our use cases. The Oracle structure:

CREATE TABLE SAMPLETABLE
(
  REFDATE   DATE,
  ID        NUMBER(10),
  ARCVALUE  NUMBER,
  VALSTAT   NUMBER(2),
  IO        NUMBER(1),
  SOURCE    NUMBER(2),
  MODDATE   DATE                                DEFAULT sysdate
);

And the PostgreSQL equivalent:

CREATE TABLE sampletable (
    refdate timestamptz NOT NULL,
    id numeric(10) NOT NULL,
    arcvalue numeric NULL,
    valstat numeric(10) NULL,
    source numeric(2) NULL,
    moddate timestamptz NULL DEFAULT clock_timestamp(),
    io numeric(1) NULL,
    CONSTRAINT sampletable_pk PRIMARY KEY (refdate, id)
);

On our Oracle side, for a specific project data, the average row length of the 45 million rows is 45 bytes. We've calculated that by this query:

select sum(bytes) from  DBA_EXTENTS where segment_name = 'sampletable';

divided by the number of rows (45 million).

Looking into PostgreSQL, we find out the size this way:

SELECT l.metric, l.nr AS bytes
     , CASE WHEN is_size THEN pg_size_pretty(nr) END AS bytes_pretty
     , CASE WHEN is_size THEN nr / NULLIF(x.ct, 0) END AS bytes_per_row
FROM  (
   SELECT min(tableoid)        AS tbl
        , count(*)             AS ct
        , sum(length(t::text)) AS txt_len  -- length in characters
   FROM   sampletable t                     -- provide table name *once*
   ) x
CROSS  JOIN LATERAL (
   VALUES
     (true , 'core_relation_size'               , pg_relation_size(tbl))
   , (true , 'visibility_map'                   , pg_relation_size(tbl, 'vm'))
   , (true , 'free_space_map'                   , pg_relation_size(tbl, 'fsm'))
   , (true , 'table_size_incl_toast'            , pg_table_size(tbl))
   , (true , 'indexes_size'                     , pg_indexes_size(tbl))
   , (true , 'total_size_incl_toast_and_indexes', pg_total_relation_size(tbl))
   , (true , 'live_rows_in_text_representation' , txt_len)
   , (false, '------------------------------'   , NULL)
   , (false, 'row_count'                        , ct)
   , (false, 'live_tuples'                      , pg_stat_get_live_tuples(tbl))
   , (false, 'dead_tuples'                      , pg_stat_get_dead_tuples(tbl))
   ) l(is_size, metric, nr);

From this, using the easy math core_relation_size/row_count, it displays a size of 110 bytes for a row in PostgreSQL. That is a brutal growth, and we tried to find out how the data can be that much larger after conversion.

Taking the byte sizes of a normal row, it seems the row should be much smaller:

select
refdate, id,
    pg_column_size(refdate) s_refdate,
    pg_column_size(id) s_id,
    pg_column_size(arcvalue) s_arcvalue,
    pg_column_size(valstat) s_valstat,
    pg_column_size(source) s_source,
    pg_column_size(moddate) s_moddate,
    pg_column_size(io) s_io,
    pg_column_size(refdate) + pg_column_size(tid) + pg_column_size(arcvalue) + pg_column_size(valstat) + pg_column_size(source) + pg_column_size(moddate) + coalesce (pg_column_size(io), 0) rowsize
from sampletable
order by rowsize desc;

This gives 48 bytes as the longest row we have, in bytes. All we have now is ideas on how the significant difference could be explained:

• Is there significant padding going on?
• Is the numeric field without precision somehow using much, much more bytes than necessary?
• Is the Oracle size calculation wrong?

Does anyone have an idea on how to figure out the differences?

Answer 1

By the grace of the fantastic post How much disk space you can save by using INT4/INT instead of INT8/BIGINT? by depesz we understand now every single byte, where it went and how we can optimize it. We have both analyzed the overhead for NUMERIC type columns, and for unfortunate orders for fields that require padding by PostgreSQL. At last the additional fields tableoid, cmax, xmax, cmin, xmin, ctid also need space, and everything together explains the size differences we saw.

Thank you for your help.