Using pgsql to archive e-mail

I am looking for a way that I can archieve a lot e-mail messages to a
data
base. I'm thinking of using pgsql.

Been there, done that. Works OK for me. Desreves a major rewrite, because
storing message bodies as BLOBs is useless and dangerous. If only there
were 25 hours in the day (sigh)
So yes, it's possible. And if you know nothing about DBs and SQL, get a
couple of books and start there. There are caveats in a general email
archive application, so you'll be better off beginning with something
simple.

--

contaminated fish and microchips
huge supertankers on Arabian trips
oily propaganda from the leaders' lips
all about the future
there's people over here, people over there
everybody's looking for a little more air
crossing all the borders just to take their share
planning for the future

Rainbow, Difficult to Cure

Well, with the next version having toast, what about parsing the email and
storing the body as TEXT?

Adam Lang
Systems Engineer
Rutgers Casualty Insurance Company
----- Original Message -----
From: "KuroiNeko" <evpopkov@carrier.kiev.ua>
To: <pgsql-general@postgresql.org>
Sent: Tuesday, October 24, 2000 5:31 PM
Subject: Re: [GENERAL] Using pgsql to archive e-mail

I am looking for a way that I can archieve a lot e-mail messages to

a

data
base. I'm thinking of using pgsql.

Been there, done that. Works OK for me. Desreves a major rewrite,

because

storing message bodies as BLOBs is useless and dangerous. If only

there

were 25 hours in the day (sigh)
So yes, it's possible. And if you know nothing about DBs and SQL, get

a

couple of books and start there. There are caveats in a general

email

archive application, so you'll be better off beginning with

something

Well, with the next version having toast, what about parsing the email
and
storing the body as TEXT?

Actually, email message should be parsed anyway, at least headers
separated from the body. In most cases it's not enough and one has to store
the headers parsed into separate tuples etc etc.
TOAST is a splendid endeavour, but there were huge archives before, when
we didn't have technology like that at hand. It takes a significant burden
off developer's shoulders, but it can't stop the preservation law. This
burden is placed on your server, both hardware and software parts.
In brief, the strategy here depends on usage pattern. We can't predict
maximal length of message body, so we have to decide beforehand, what parts
should be searchable, indexed and what can just be stored.
As an example, the dirty hack that I call my email archive (it was written
in 40 minutes, it even uses psql to talk to the server), stores message
bodies as BLOBs (it shouldn't, plain files are easier). Searching on
sender's address and subject is enough for me. What's enough for you is up
to you.

--

contaminated fish and microchips
huge supertankers on Arabian trips
oily propaganda from the leaders' lips
all about the future
there's people over here, people over there
everybody's looking for a little more air
crossing all the borders just to take their share
planning for the future

Rainbow, Difficult to Cure