Re: Are there performance advantages in storing bulky field in separate table?

Ian Mayo <ianmayo@tesco.net> writes:
> I've got a fairly straightforward table that's similar to a blog table
> (entryId, date, title, author, etc).  There is, however, the
> requirement to allow a single, fairly bulky binary attachment to
> around 1% of the rows.

> There will be a few million rows, and I value efficient searches by
> date, title, and author.

> Would there be a performance advantage in storing the attachment in a
> separate table - linked by entryId foreign key?

No.  You'd basically be manually reinventing the TOAST mechanism;
or the large object mechanism, if you choose to store the blob
as a large object rather than a plain bytea field.  Either way,
it won't physically be in the same table as the main row data.

If you're curious, this goes into some of the gory details:
http://www.postgresql.org/docs/8.3/static/storage-toast.html

            regards, tom lane