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
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;
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?