0

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?

5
  • Why in Oracle VALSTAT is defined as NUMBER(2) but in PostgreSQL you've chosen NUMERIC(10)?
    – J.D.
    Nov 30, 2022 at 13:52
  • numeric(10) should be bigint and numeric(2) numeric(1) should be int or maybe even smallint
    – user1822
    Nov 30, 2022 at 14:00
  • Good question, we didn't recognize that. The conversion of the structure has not been done by lets say ora2pg, but by Liquibase. Let me check why the format is different.
    – 0xCAFEBABE
    Nov 30, 2022 at 14:00
  • Postgres does have a higher overhead per row though
    – user1822
    Nov 30, 2022 at 14:04
  • You can get the real size of a row using select pg_column_size(sampletable) from sampletable. That will include the overhead of padding and the row overhead if I'm not mistaken.
    – user1822
    Nov 30, 2022 at 14:21

1 Answer 1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.