NOTE: This advice only applies to really old versions of PostgreSQL. User created tables stopped having OIDs added to them in the early 8.x versions.
Something most people don't know about the PostgreSQL RDBMS is that when you create tables each row is given a unique object identifier ( aka OID ) whether or not you define any primary keys for that table. This is useful if you need to be able to delete an individual row from the database that doesn't have any unique way of referring to it. You can retrieve the OID for any row by just adding it to the items you with to SELECT:
SELECT oid, id FROM table1;
If you try this on one of your existing tables you will see the various OIDs for your database rows. While these OIDs can be useful in certain circumstances, if you aren't aware of them then you probably aren't using them! Instructing PostgreSQL to not create OIDs for you can save some space on your filesystem and speed up your queries. However, don't expect to get a noticble amount of difference in either speed or disk space as this is just on extra integer per row. It really depends on how your schema is setup. To create a table without OIDs you simply need to do the following:
CREATE TABLE test (
.....
) WITHOUT OIDS;
If your tables were build with OIDs ( the default ) then you can remove them by issuing the following:
ALTER TABLE test SET WITHOUT OIDS;
However, this will only keep new rows and rows that are updated from having OIDs. It isn't optimal, but certainly a step in the right direction.
As I don't ever use OIDs in my queries I now explicitly define all of my tables without OIDs. Perhaps you should do the same?
