Обсуждение: Best way to store and retrieve large objects in database for email program

    Greg wrote:

> I am creating an email client that will contain email messages. Now a
> message may have multiple large attachments.
>
>
>
> 1.)    Would the best way to store these large objects be in an oid field?
>
> 2.)    I assume for performance reasons I should place these large objects
> in a separate table called attachments. And create an attachments table for
> each user?
>
> 3.)    Only when a user clicks on an attachment to open it, should I use
> server side SQL functions like lo_export and lo_open to read the data to a
> file stream for my application to deal with?
>
> Am I on the right track or can anybody recommend a better solution?

For an application quite similar to what you describe, I choosed
large objects for storing attachments and have been quite happy with
them so far.
The database structure is described here:
http://www.manitou-mail.org/articles/db-diagram.html

The performance with lo is very good, and the ability to stream contents
with repeated calls to lo_read/lo_write is quite handy to avoid
allocating giant buffers and encoding/decoding passes.
However, you need to make sure the language/library you
plan to use has proper support for the lo API.

If you were to use bytea instead, I'd recommend splitting your contents
into bytea segments with one segment per row, rather than one row
per attachment.

--
 Daniel
 PostgreSQL-powered mail user agent and storage: http://www.manitou-mail.org

Daniel's answer reflects our experience with large objects.

We recently switch our 50-or-so G of stored postscript documents from
large objects to bytea, but only because we're going to use Slony for
our "more-availability" setup and Slony doesn't replicate large
objects.