Anyone ever consider a filesystem served by MySQL for mail folders?

[Doug Lee]

On Sat, Apr 09, 2005 at 05:33:22PM -0400, Chuck Swiger wrote:
Doug Lee wrote:
>Is it practical to implement a mountable filesystem for mail archiving
>whose contents are served by a MySQL (or other SQL) database?
>Creating this is surely way beyond my level of expertise in FreeBSD,
>and maybe even the full design is, but I imagine this much:
>
>The actual supporting database would include category strings for each
>message (many-to-many).  File names in the filesystem would be
>category strings, so saving an email would file it in that category
>(to save in several categories, resave to the corresponding names;
>only one actual copy of the message would be saved).
[ ... ]

Using a database backend for mail storage and to provide fancy searching 
and the like is the architecture used to build Microsoft Exchange and Lotus 
Notes.

The advantage is that users gets fancy searching.

The disadvantage is that you need to provide around 4 times as much disk 
space for a DB-based mailstore as you would for a normal mbox/maildir style 
representation, you need to provide a lot more server horsepower, you need 
to continuously maintain and purge old mail from the database, and you end 
up with your mail buried in database tables, so heaven help you if the 
database becomes inconsistent and you need to recover.

Horsepower yes, trouble if things become inconsistent yes, purging
requirements not really (you are entitled to just as much hoarding
under either system <g>) ...

But as for increased storage requirements, I've always wondered how
much could be saved by an intelligent method of behind-the-scenes
handling of quoting among messages in a thread.  Goodness knows half
the mail on a lot of lists, and even in a lot of personal mail
streams, is simply copies of some or all of other messages, perhaps
shifted over by quote signs like `>' etc.  Seems to me a system could
be devised to store directions for rebuilding a message instead of the
message itself with all quoting intact.  Dangerous, in need of a LOT
of testing before production use, likely not to catch all possible
cases because of, for example, people who like unique quoting prefixes
(:P) etc. etc... but I think still feasible.  I don't know how much
could be gained, but I wouldn't be surprised if it could reverse the
increased storage requirements you mention.


-- 
Doug Lee           dgl at dlee.org        http://www.dlee.org
BART Group         doug at bartsite.com   http://www.bartsite.com
"It's not easy to be crafty and winsome at the same time, and few accomplish
it after the age of six." --John W. Gardner and Francesca Gardner Reese