[Dovecot] SQL mail storage

[Thierry de Montaudry]

On Sat, 31 Mar 2007 17:34:23 +0300, Timo Sirainen wrote:

>I wasted some time yesterday and today implementing a SQL storage
>plugin. It seems to be working, but:

> - Saving new messages is done in a regular INSERT statement, which is
>bad. PostgreSQL has at least this COPY TO command which could be used
>instead.
> - It breaks in stress testing
> - It's not that well optimized. Especially it could support caching
>some commonly requested fields (same as dovecot.index.cache file).
> - Currently works only with PostgreSQL.

>Nothing is committed to CVS yet. I'm not sure if I should even do that.
>I'm not sure if those lib-sql API changes were that great. Maybe there's
>a better way..

>If you're interested in trying it, you need the latest CVS HEAD sources
>and these patches:

>http://dovecot.org/patches/sql-storage-changes.diff
>http://dovecot.org/patches/sql-storage-plugin.diff

>The SQL schema is in src/plugins/mail-sql/schema.sql file. It creates a
>"tss" named user by default, so you might want to modify that. :)

>Then set:

>mail_location =3D sql:pgsql:host=3Dlocalhost dbname=3Dmails user=3Dmailuser
>password=3Dfoo
>mail_plugins =3D mail_sql

Hi Timo,

After thoughs about SQL: no time wasted, this is a very interesting project.
But instead of storing the mail in the SQL database, which would generate a huuuge database, wouldn't it be more 
optimised to store only the indexes (and some of the header fields) in SQL and keep the mails on file system? With maybe 
only the smallest mails in SQL as well...
This would sort out a lot of locking problems with the indexes, and give very easy sharing/backup/replication/migration 
facilities. And it might look a bit like the GMail solution, wich proved to work.

As I am looking for that type of solution, I'll be able to put some time to help on developpement.

Regards,

	Thierry