Matthew's Journal

Initially I wanted to keep all mail in a disk-based database, on the basis that this would give O(log N) time to look up practically anything. Then I decided this plan had two major flaws. Firstly, a single disk file growing without bound as your mail archive increases is unlikely to make your sysadmin's incremental backup script, or your sysadmin, a happy bunny. Secondly, should the database go unaccountably tits-up for whatever reason, so does your entire mail archive.

So I devised plan B. First, the actual mail is held in a series of mbox files, each with a fixed maximum size. Mail is thrown sequentially on to the end of the highest-numbered file until it fills up, at which point a new file is started. Secondly, a database is kept alongside the mboxes, containing all index-useful header information (subjects, from, to, cc, etc) and indexing the actual mail by (filename,offset,length) tuples. Thus, in normal usage, the program never has to actually parse an mbox from beginning to end. Thirdly, the same information stored in the database is also stored in a series of flat text files, one for each mbox containing the information about all the messages in that mbox. This deals with the incremental backups problem because each mbox (and each index file) eventually stops changing. The only single file which continues to grow and change is the database file, and since that can always be reconstructed from the textual index files alongside the mboxes there's no need to back it up, so if your sysadmin maintains an un-backed-up partition then it's safe to keep it on that. Meanwhile, the technological lock-in is also solved because all the mail is held in the world's most standard mail storage format and hence can easily be extracted into another MUA without needing the aid of the one which understands the database. (This would lose the folder organisation, admittedly, but some reasonably trivial Perl could reconstruct that from the textual indices.) Meanwhile, lookup of any given message is nicely O(log N), and if the database has sufficient indexing magic in it then all sorts of other common lookups ("the last 500 messages in $category", "all messages exchanged with $user") become efficient as well.

The only snag with this arrangement, which didn't start to bother me until half way through coding, is that it really doesn't make it easy to modify or delete a message from the mail store. Modifying a message is something I do quite often, because idiots have a habit of sending me bloody enormous attachments which I absolutely do not need, and if I kept them all then my mail archive would be even more unmanageable than it is now. So my current (also home-grown) MUA has a "delete attachment" command which I use fairly regularly, and which the above mail store design wouldn't have supported. As for deleting messages, well, I don't do it often, but the more spam I get the more I start to wonder whether I really need to keep it all or whether I ought to start using the delete button. Most of the problems with deletion and editing centre around atomicity: it's OK to add data to the end of an mbox and then update the database to say it's in there, because if there's a failure between those two steps then the data is still consistent. But if you shuffle everything up in an mbox file, you inevitably have a moment when the file offsets in the database don't match the ones in the mbox, and a failure at that point is catastrophic. I never figured out a good solution to this.