From: Gianni Mariani <dbmail@dbmail.org> > c) I go both ways on the contents of the message being in the database. > No question that it is faster to send a file to a socket (using > sendfile) than it is to read the data from the database. So, handling > the message contents differently (placing it in a file) could easily > improve performance, not to mention the issues with encoding of the > database causing all kinds of issues. At the minimum, recent versions > of Postgresql also do large objects so theoretically you could store > messages in a single record.
I think that last idea, the whole message in one record, is probably not a good idea on postgres because of the way it handles updates and free space. Every time you update a message (set seen flag, change status, etc... happens numerous times through the life of a message), it copies the row and marks the old as being free. This is both costly for large messages, and would require much higher fsm settings or more frequent vacuuming to clean up after (though I think that's supposed to be improved in future pgsql versions). The current schema handles those updates very well - only a small entry in the messages table needs to be updated each time. On using stored procedures for most everything and portability, it seems that making updates would be somewhat of a pain - as various things differ from one version to another, you would either have to maintain multiple sp sets for different pgsql versions, or you would have to require a minimum pgsql server version to use dbmail. We've not used pgsql a tremendous amount, but have had to rewrite some triggers/sp's when going from one version to another, I think in reguard to how null/empty values were handled. -- Jesse Norell jesse (at) kci.net
