How is Postgres table size GREATER than Mysql Table Size?

Question

On comparing Mysql and Postgres table sizes we found that:

Postgre Table size (4758390 rows) (vanilla postgres): 1402MB

Data Length = 1063MB Index Length = 339MB

Mysql Table Size (4758390 rows) (with Inno DB): 1056MB

Data Length = 845MB Index Length = 211MB

The tables have the following schema:- The schema:-
MySQL
int(11)
varchar(15)
datetime
float
float
float
float
float
double
double
double
float
longtext
double
double
int(11)
double
float
int(11)
int(11)
float
int(11)
int(11)
int(11)
int(11)
varchar(50)
int(11)
int(11)
int(11)

Postgres
serial
varchar
timestamp
double precision
double precision
double precision
double precision
double precision
numeric
numeric
numeric
double precision
varchar
numeric
numeric
double precision
numeric
double precision
integer
integer
double precision
integer
integer
integer
integer
varchar
integer
integer
integer

The query used to calculate the sizes for the tables are:-

MySQL


SELECT table_name AS `Table`,data_length, index_length,
round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB`
FROM information_schema.TABLES
WHERE table_schema = "DB_NAME
      AND table_name = "TABLE_NAME";

Postgres

SELECT pg_size_pretty(pg_total_relation_size('TABLE_NAME'));

Edit:-
Indexes in MySQL: Size
(varchar(15),datetime) -> 133 MB
(datetime) -> 78 MB
Indexes in Postgres: Size
(varchar,timestamp) -> 339 MB
I am new to databases and wondering how is this possible.

This question lacks the details necessary to answer it. Compare the table and the indexes. Look how the data is stored in the pages, Determine how many rows are stored in each page in both systems. Add relevant details to the question, then maybe someone can answer. — Laurenz Albe
– Laurenz Albe, Commented Feb 21, 2020 at 8:13
You don't show any effort researching how the tables are implemented. — philipxy
– philipxy, Commented Feb 21, 2020 at 9:39

Jasen · Accepted Answer · 2020-02-21 10:26:40Z

4

You're using data types with different sizes:

Mysql floats are 4 bytes vs postgresql doubles which are 8 bytes

Mysql datetime looks like 4 bytes (I was unable to find clear documentation) whereas postgreql timestamp is 8 bytes.

Mysql integer(11) is 4 bytes, while for Postgresql numeric The actual storage requirement is two bytes for each group of four decimal digits, plus three to eight bytes overhead

edited Feb 21, 2020 at 10:26

answered Feb 21, 2020 at 10:20

Jasen

12.5k2 gold badges37 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Rick James Over a year ago

MySQL's DATETIME used to take 8 bytes; now it takes 5 bytes. And, as a rule of thumb, InnoDB's overhead is 2x to 3x.

Collectives™ on Stack Overflow

How is Postgres table size GREATER than Mysql Table Size?

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related