Re: Performance with large mailboxes and nnsql.el

[Joseph Barillari <jbarilla@princeton.edu>]

[-- Attachment #1: Type: text/plain, Size: 3621 bytes --]

>>>>> "KG" == Kristoffer Gleditsch <toffer@ping.uio.no> writes:

    > [ Joseph Barillari ]
    >> Out of curiosity, is anyone working on nnsql.el? Or are there
    >> any fragments of a half-finished nnsql.el lying around with
    >> which I could begin work?

    > I did some work on such a backend some time ago, using Eric
    > Marsden's PostgreSQL interface library
    > (http://www.chez.com/emarsden/downloads/pg.el).  It almost gave
    > me a working summary buffer, but I never got around to doing the
    > database insertion stuff, so it has been standing still for some
    > time.  If anyone wants to take a look at it, it can be checked
    > out of anonymous CVS following the instructions at
    > http://www.ping.uio.no/anoncvs.shtml (just use 'nnsql' instead
    > of 'frotz'.)

I noticed in your code:

/* We store each line as a separate database entry.  I don't know if
   that is a good idea or not.  Probably not.  */

I'd agree. One cannot perform a regular expression search on the
message body quite so easily that way (Postgres has such a
feature). 

If splitting the bodies into individual lines is a bad idea,
presumably each body should be kept intact in a TEXT field.

What do you think of splitting headers? I assume splitting the
most-often-searched-upon headers (To:, Cc:, Subject:, etc.) into
separate fields would be a good idea -- it would aid searching, make
it easier to write other clients (web-based clients, for instance),
and it would let the underlying database build indexes. 

Most headers (except From:) can and often do consist of a
comma-delimited list of entries. Splitting these up further along the
comma-delimitations, and inserting them into a separate table is
probably a good idea, once again, as it can improve indexing
performance.

What to do with the message body is another matter.

If one stores the headers separately, should one also remove them from
the body text? Leaving them in the body would be a Bad Idea in the
relational-database frame of thinking -- data should never be
duplicated. But stripping them out means that the database will not be
storing exact copies of the messages. Reconstructing the headers in
the exact order in which they appeared may not be possible unless some
information is left in the database.

This would be a non-issue if the message headers were static. But
headers often need to be changed -- the drafts mailbox is a perfect
example. If the headers are left in the body, and Gnus needs to change
them, then the entire message has to be rewritten -- not just the
header in question.

The only way around that re-writing would be to remove the
stored-separately headers and replace them with some sort of tags that
Gnus will replace with the actual headers when the message is fed into
a buffer. This would speed writes, as the body would not need to be
touched, but make reads ugly and replete with string-replacements, to
say nothing of the additional difficulty in writing read-only clients
(like web-based readers.)

What do you think of this pseudo-model, using the
leave-headers-in-body option?

_Messages_           _Headers_  /* but only interesting headers */
 Body TEXT            Header TEXT                                
 Serial INTEGER--|    Contents TEXT                              
 From TEXT       |----Message INTEGER                            
 MsgID TEXT
 Rec'd TIMESTAMP   
 Date TIMESTAMP
 Mailbox TEXT /*all of the nnsql: mailboxes could be 
                   accommodated in a single table*/

--Joe







[-- Attachment #2: Type: application/pgp-signature, Size: 268 bytes --]