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
