Experts... What is your experience with the difference in

Question

Experts... What is your experience with the difference in

DISK required for Oracle vs. PG?
Right now we are showing 220% (more than double) space requirement of Oracle (edited).
We used a few IOTs for our largest tables in Oracle. And this is clearly a problem, because we need the index and table in PG...

I did not expect that big of a difference, curious if others have seen similar situations?

#programming #russian

0

02.12.2023

5 ответов

25 просмотров

Kirk Автор вопроса

Yaroslav Schekin
Well... not sure why didn't you expect that? For ...

Стикер

0

11 месяцев назад

Kirk Автор вопроса

Yaroslav Schekin
Well... not sure why didn't you expect that? For ...

Стикер

0

11 месяцев назад

Yaroslav Schekin

Well, PostgreSQL per-row overhead might be highest in the industry (IIRC, it's 3 for Oracle; 18-21 for MS SQL (in MVCC mode); 18 (or less?) for MySQL/InnoDB... and so on), and if the tables are narrow, it really matters. ;(

0

11 месяцев назад

Kirk Автор вопроса

Yaroslav Schekin
Well, PostgreSQL per-row overhead might be highest...

Thanks again. yes, some of this is relatively narrow stuff as well... But overall it is a mix. Unfortunately the biggest tables suffer the most. Luckily disk space is cheap 😊

0

11 месяцев назад

Yaroslav Schekin · Accepted Answer

Well... not sure why didn't you expect that? For a usual m:m table (like m2m(table1_id, table2_id, PK(table1_id, table2_id)) ), PostgreSQL needs to store the table and the (secondary) index (i.e. all the data is stored twice, note the index also stores pointers to rows in the heap), while an IOT would store it only once. It's 100% right there. Then, PostgreSQL per-row overhead (see https://www.postgresql.org/docs/current/storage-page-layout.html#STORAGE-TUPLE-LAYOUT ) is [very] high (23-24 bytes), while in Oracle it's what... 3 bytes, right? If so, that's yet another ~20%. So, what you see seems to be about right...

169 похожих чатов

Experts... What is your experience with the difference in

5 ответов

Похожие вопросы