Re: disabling OIDs?

The spam filtering package I use (dspam) had a section in their
release notes recently which stated that disabling OIDs greatly
increased speeds, and so they suggested that people do that on their
tables.

When creating new tables, you can disable OIDs with,
CREATE TABLE foo (...) WITHOUT OIDS;
And you can disable OIDs on existing tables by executing for each table,
ALTER TABLE foo SET WITHOUT OIDS;
and then running a vacuumdb (either with pg_vacuumdb or VACUUM ANALYSE;)

Does anyone know of any risks or potential downsides to doing this?

Thanks!

--
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
L. Friedman netllama(at)gmail(dot)com
LlamaLand http://netllama.linux-sxs.org

Lonni J Friedman <netllama(at)gmail(dot)com> writes:
> The spam filtering package I use (dspam) had a section in their
> release notes recently which stated that disabling OIDs greatly
> increased speeds, and so they suggested that people do that on their
> tables.

"greatly increased"? I doubt it.

Last I heard, dspam was not noted for any large amount of cluefulness
WRT postgres. It was only recently that we managed to talk them out of
their most egregious bits of mysql-centricity. Going to them for
postgres tuning tips is about like coming to me for mysql tuning ...

regards, tom lane

On Sun, 12 Dec 2004 22:16:27 -0500, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Lonni J Friedman <netllama(at)gmail(dot)com> writes:
> > The spam filtering package I use (dspam) had a section in their
> > release notes recently which stated that disabling OIDs greatly
> > increased speeds, and so they suggested that people do that on their
> > tables.
>
> "greatly increased"? I doubt it.
>
> Last I heard, dspam was not noted for any large amount of cluefulness
> WRT postgres. It was only recently that we managed to talk them out of
> their most egregious bits of mysql-centricity. Going to them for
> postgres tuning tips is about like coming to me for mysql tuning ...
>

OK, thanks. So is there any real benefit in doing this in a generic
(non-dspam) sense, or is it just a hack that wouldn't be noticable?
Any risks or potential problems down the line?

--
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
L. Friedman netllama(at)gmail(dot)com
LlamaLand http://netllama.linux-sxs.org

On Sun, 2004-12-12 at 20:25 -0800, Lonni J Friedman wrote:
> OK, thanks. So is there any real benefit in doing this in a generic
> (non-dspam) sense, or is it just a hack that wouldn't be noticable?
> Any risks or potential problems down the line?

It saves 4 bytes per row; depending on alignment and padding
considerations, that may or may not equate to disk space savings. Other
than the inability to use OIDs on the table, there is no real risks to
doing this -- I'm planning to advocate making WITHOUT OIDS the default
in PostgreSQL 8.1+. You can get this behavior in 8.0 by setting the
default_with_oids config variable to "false".

-Neil

Lonni J Friedman <netllama(at)gmail(dot)com> writes:

> OK, thanks. So is there any real benefit in doing this in a generic
> (non-dspam) sense, or is it just a hack that wouldn't be noticable?
> Any risks or potential problems down the line?

OIDs increase the storage requirements so they do slow postgres somewhat.
About (exactly?) the same impact as adding another integer column. That will
have a bigger impact on a narrow table than wider tables.

--
greg

On Sun, 2004-12-12 at 20:25 -0800, Lonni J Friedman wrote:
> OK, thanks. So is there any real benefit in doing this in a generic
> (non-dspam) sense, or is it just a hack that wouldn't be noticable?
> Any risks or potential problems down the line?
>
>

I'd just like to add that some 3rd party applications/interfaces make
use of OIDs, as a convenient id to use if there is no primary key (or if
the 3rd party software doesn't take the time to find the primary key).

One might argue that those 3rd party applications/interfaces are broken,
but you still might want to keep OIDs around in case you have a use for
one of those pieces of software.

Regards,
Jeff

On Sat, Jan 01, 2005 at 06:35:30PM -0800, Jeff Davis wrote:
> On Sun, 2004-12-12 at 20:25 -0800, Lonni J Friedman wrote:
> > OK, thanks. So is there any real benefit in doing this in a generic
> > (non-dspam) sense, or is it just a hack that wouldn't be noticable?
> > Any risks or potential problems down the line?
> >
> I'd just like to add that some 3rd party applications/interfaces make
> use of OIDs, as a convenient id to use if there is no primary key (or if
> the 3rd party software doesn't take the time to find the primary key).
>
> One might argue that those 3rd party applications/interfaces are broken,
> but you still might want to keep OIDs around in case you have a use for
> one of those pieces of software.

Yep, especially since an OID is not a unique value and so can't
possibly be a primary key and generally isn't indexed either. Even
Access asks you to identify the primary key...
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

On Sunday 02 January 2005 08:24, Martijn van Oosterhout wrote:
> On Sat, Jan 01, 2005 at 06:35:30PM -0800, Jeff Davis wrote:
> > On Sun, 2004-12-12 at 20:25 -0800, Lonni J Friedman wrote:
> > > OK, thanks. So is there any real benefit in doing this in a generic
> > > (non-dspam) sense, or is it just a hack that wouldn't be noticable?
> > > Any risks or potential problems down the line?
> >
> > I'd just like to add that some 3rd party applications/interfaces make
> > use of OIDs, as a convenient id to use if there is no primary key (or if
> > the 3rd party software doesn't take the time to find the primary key).
> >
> > One might argue that those 3rd party applications/interfaces are broken,
> > but you still might want to keep OIDs around in case you have a use for
> > one of those pieces of software.
>
> Yep, especially since an OID is not a unique value and so can't
> possibly be a primary key and generally isn't indexed either. Even
> Access asks you to identify the primary key...

Of course some 3rd party apps are nice and they look for a primary key first,
then a unique index, then look for an oid. Furthermore the really clueful
ones will check # of affected rows = 1 when modifying by oid, so its pretty
safe.

--
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL