On Tue, Jan 04, 2005 at 05:18:30PM -0500, Keith Worthington wrote:
> For lack of a contrary rason I created most tables with OIDs as I thought I
> read somewhere that they improved performance. Is there another reason for
> using them? Do they increase performance and if not then what good are they?
Performance will probably be better without OIDs. See the WITHOUT
OIDS section of the CREATE TABLE documentation:
In addition, excluding OIDs from a table reduces the space
required on disk to storage the table by 4 bytes per row, leading
to increased performance.
The "Object Identifier Types" section of the "Data Types" chapter
says that "OIDs are best used only for references to system tables."
The 8.0 Release Notes state that future versions of PostgreSQL will
change the default behavior to create tables without OIDs. In 8.0
you can make WITHOUT OIDS the default by setting the default_with_oids
configuration variable to "off".
> The documentation implies that if you really want a row identifier use a
> serial type. Isn't a row identifier the primary key? Sighhhh. So much to
> learn, so little time.
The primary key is whatever field(s) you specify with a PRIMARY KEY
constraint. If you want automatically-generated primary keys, then
using a sequence (typically via a SERIAL or BIGSERIAL type) makes
wraparound less of a problem than if you use OIDs.
> I do not use OIDs at this time. As I implied above I do not yet see what use
> they are. If I had to build my database over from scratch (which I am about
> to do.) I would probably not even use them so I am eager to learn how they
> might benefit me.
OIDs probably won't benefit you, so if you don't need them and if
you're not using any third-party software that needs them, then
consider creating your tables using WITHOUT OIDS. If you're using
PostgreSQL 8.0, then you could make that the default behavior by
setting default_with_oids to "off" in postgresql.conf.
> Well, now that my education has progressed some more I believe that I will try
> the following.
> 1) Use pgadmin to create NEWDB
> 2) Run the following: pg_dump IPADB --format=p --schema-only --verbose -U
> postgres > psql --dbname NEWDB --username postgres
You probably want to pipe (|) instead of redirect (>). If you don't
understand the difference then read your shell's documentation.
Consider storing the database schema in a file instead of extracting
it from one database and piping it directly into another. That file,
along with explanatory comments, becomes part of your application's
source code and documentation.
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/