On Tue, 2007-11-13 at 08:36 -0800, SHARMILA JOTHIRAJAH wrote:
> Hi
> I have a table with 29384048 records in oracle and postgresql. The
> table has 47 columns (16 numeric and 27 varchar and the rest
> timestamp). The tablesize in postgresql is twice as much than the
> tablesize in oracle (for the same number of rows and columns). There
> are no updates or deletes in this table. It is a test table that is
> used only for querying. The tables are vacuumed regularly
>
> Even a simple seqscan query takes twice as much time in postgres than
> in oracle.
> Does postgresql generally occupy more space than oracle tables?
> Thanks
> Sharmila
PostgreSQL generally does occupy slightly more space. However, your case
is extreme due to the number of columns in the table. In PostgreSQL 8.2
and before, it would store a full 4 byte length header for every
variable-width field (which is any text type).
8.3 (currently in beta) will substantially reduce this overhead, usually
just storing one byte of overhead for every variable-width field (saving
3 bytes), and also reducing the per-row overhead by either 4 or 8 bytes
(depending on platform).
My quick calculations show that you could save up to (47*3 + 8)*29384048
= 4378223152. So you might save up to 4GB with 8.3! It would be fairly
easy for you to check for yourself exactly how much by downloading the
beta.
Regards,
Jeff Davis