[Dovecot] SQL mail storage - yes yes yes!

[Richard P. Welty]

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.

depending on the code's structure, you can wrap bundles of 100 or so
inserts in transactions, which will speed things up rather noticeably.
also watch for connection handling, opening a connection is really
expensive so pooling of some sort is good (maybe look at SQL Relay,
it's database agnostic to a fair degree and could form part of a
bridge to database independence.)

this is also likely something that would benefit from smart disk
layout, e.g. putting transaction logs (/var/lib/pgsql/data/pg_xlog)
on a separate spindle (or raid 1 mirror), maybe separating message
store from other infrastructure using table spaces, etc. etc.
in other words, there are a lot of DBA type issues in getting it to
perform well.

so for installs, you'll want a guide for DBAs and some flexibility
to manage the database setup.

richard
    (not a real PostgreSQL DBA but i play one at the day job)