[Dovecot] Using MySQL to store email?

[Simon Waters]

On Tuesday 06 Jun 2006 16:25, Marc Perkel wrote:
> Steffen Kaiser wrote:
> > On Tue, 6 Jun 2006, nodata wrote:
> >>> Seems to me that MySQL could somplify all the backend stuff
> >
> > There are different SQL-Servers out there, e.g. Postgres.
> >
> >> Putting everything in a database would provide one benefit:
> >> * Less storage space needed due to duplicated e-mail

Unlikely -- that would depend on the delivery agent (and MTA) handling things 
appropriately before it gets to dovecot. This is a difficult problem in SMTP, 
although somethings that combined MTA and POP type systems have solved it 
(kind of).

> > And limits. Why does Dovecot has so many options for locking? ;-)

Coping with a plethora of other products that already exist to do the email 
delivery. No sane person deploys email without at least maildir.

> > What 
> > are the files days old in the tmp/ folders?

All empty here.

> > Well, the problem with a file-based database (Dovecot's indexes etc.
> > are in fact a database) is that you must use the same locking and/or
> > terminate / suspend the service, otherwise there is the possibility
> > that the data and the indexes are out-of-sync.

Yes, but indexes are cheap to rebuild, but expensive to maintain, so you might 
find this cuts the wrong way.

I'm quite a fan of the idea of putting email in databases, I can see the 
upside. But those who think it will save any resource at all haven't spent 
enough time with big database systems. It will be a lot slower, except where 
you can utilise indexes to speed operations, which will be rarely if at all.

Just consider the number of blocking writes to commit an email to maildir 
(remember it uses a lot of rename), now consider the kind of indexes you want 
to maintain on the database that'll be updated when an email is delivered 
(and possibly when it is read, files etc).

I got into pondering mail in databases from the issues pertaining to 
consistency of reads of directories in Unix filesystems. Whilst it is easy to 
guarantee the consistency of a read from an ACID style database (unlike 
reading directories in a big maildir folder). Of course when I asked Hans 
Reiser he said it sounds like the kind of modular functionality that modern 
filesystems ought to provide and offered to write a filesystem plugin for 
ReiserFS that guarantees the consistency of directory reads for maildir use. 
Of course there is a performance (or resource) penalty in doing a consistent 
read of a directory.

Maybe more than one way to solve a problem, just need to make sure you know 
precisely which problems you are trying to solve.

 Simon, who'll continue moving systems to maildir, till something better 
arrives.