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?
VALSTAT
is defined asNUMBER(2)
but in PostgreSQL you've chosenNUMERIC(10)
?numeric(10)
should bebigint
andnumeric(2) numeric(1)
should beint
or maybe evensmallint
select pg_column_size(sampletable) from sampletable
. That will include the overhead of padding and the row overhead if I'm not mistaken.