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
