Postgres Pro
Downloads
Home   >   mailing lists

Thread: OIDs / noOIDs tables?

OIDs / noOIDs tables?

From
Heni Lolov
Date:
Hi

As for version 7.2 postgres supports tables wothout OID. So I have 10 Gb
database and I would like to upgrade it to 7.2 to make the tables without oids
in order to  save disc space. I made a test. I created one table with oids and
another without oids and dumped the same data in both of them but there was no
difference in table sizes.
Btw the size is the same as the size of the original table in the previous
release i use (7.0.3).

Can anybody tell me why?
Thanks in advance!

regards, hal

__________________________________________________
Do You Yahoo!?
Try FREE Yahoo! Mail - the world's greatest free email!
http://mail.yahoo.com/

Re: OIDs / noOIDs tables?

From
Bruce Momjian
Date:
Heni Lolov wrote:
> Hi
>
> As for version 7.2 postgres supports tables wothout OID. So I have 10 Gb
> database and I would like to upgrade it to 7.2 to make the tables without oids
> in order to  save disc space. I made a test. I created one table with oids and
> another without oids and dumped the same data in both of them but there was no
> difference in table sizes.
> Btw the size is the same as the size of the original table in the previous
> release i use (7.0.3).

Yes, tables are the same size. The only difference is that the oid
counter doesn't increase for INESRTs.

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Re: OIDs / noOIDs tables?

From
Peter Eisentraut
Date:
Heni Lolov writes:

> As for version 7.2 postgres supports tables wothout OID. So I have 10 Gb
> database and I would like to upgrade it to 7.2 to make the tables without oids
> in order to  save disc space. I made a test. I created one table with oids and
> another without oids and dumped the same data in both of them but there was no
> difference in table sizes.

Did you actually change the CREATE TABLE commands to say WITHOUT OIDS?  By
default, OID columns are still created.

--
Peter Eisentraut   peter_e@gmx.net

Re: OIDs / noOIDs tables?

From
Mathieu Arnold
Date:
--On lundi 11 mars 2002 08:10 -0800 Heni Lolov <hal_bg@yahoo.com> wrote:

> As for version 7.2 postgres supports tables wothout OID. So I have 10 Gb
> database and I would like to upgrade it to 7.2 to make the tables without
> oids in order to  save disc space. I made a test. I created one table
> with oids and another without oids and dumped the same data in both of
> them but there was no difference in table sizes.
> Btw the size is the same as the size of the original table in the previous
> release i use (7.0.3).
>
> Can anybody tell me why?
> Thanks in advance!

well, I had a 7.1.3 database, and I switched over to 7.2 without oids,
here's the change :
7.1.3 :
4367448 /opt/pg/data/base/18721
7.2 :
3551340 /opt/pgsql/data/base/16567
and there's a bit more info in the 7.2 database now...
so I believe that without oids give you a bit smaller databases.

--
Mathieu Arnold

Re: OIDs / noOIDs tables?

From
Tom Lane
Date:
Mathieu Arnold <mat@mat.cc> writes:
> ... there's a bit more info in the 7.2 database now...
> so I believe that without oids give you a bit smaller databases.

OIDs vs. no OIDs makes absolutely *zero* difference in disk space.
The tuple header overhead is the same either way.

(Well, okay, it saves you one pg_attribute row per OID-less table.
Wow...)

The only reason for the no-OIDs feature is to postpone wraparound
of the OID counter.

            regards, tom lane

PS: probably your space savings from updating to 7.2 is a transient
result of having rebuilt the indexes.