Thread: Can PostgreSQL be a mail backend?
Hi people, I'm wondering if postgres could be capable of acting as a mail storage. This would imply storing messages, from 1KB to > 100MB in size. The expected volume is 4000 messages a day, and the database may store up to, lets say, 500000 messages. Could postgres cope with this? (How is the support for big data fields now BTW?) Thanks, Morten
> I'm wondering if postgres could be capable of acting as a > mail storage. This would imply storing messages, from > 1KB to > 100MB in size. The expected volume is 4000 > messages a day, and the database may store up to, > lets say, 500000 messages. > > Could postgres cope with this? (How is the support for > big data fields now BTW?) It works quite well (designing a web-based system on it right now), but because of a DDB::Pg limit, I can only get 8k into a 'text' field. So if your app is web-based, you might want to not use perl... John -- # John Madden weez@freelists.org ICQ: 2EB9EA # FreeLists, Free mailing lists for all: http://www.freelists.org # UNIX Systems Engineer, Ivy Tech State College: http://www.ivy.tec.in.us # Linux, Apache, Perl and C: All the best things in life are free!
On Tue, Feb 27, 2001 at 03:49:25PM +0100, Morten W. Petersen wrote: > Hi people, > > I'm wondering if postgres could be capable of acting as a > mail storage. This would imply storing messages, from > 1KB to > 100MB in size. The expected volume is 4000 > messages a day, and the database may store up to, > lets say, 500000 messages. > > Could postgres cope with this? (How is the support for > big data fields now BTW?) I don't see why it shouldn't work, since 7.1 supports an unlimited row width. The question is: Why would you want to do that? A filesystem is always faster than a database, which is usually a very important consideration with a mailserver (or were you thinking about archiving messages?). Regards, Frank
We're looking at porting GNU Mailman to use Postgres as a backend, breaking off the mail handling functionality into free-standing Python scripts, and allowing for a separate presentation layer as well... looks promising so far. We'll be doing the development on our greatbridge.org website - I know Chris (cc'ed) would love to have some help. Some people in the Mailman community have expressed interest as well... Morten W. Petersen wrote: > Hi people, > > I'm wondering if postgres could be capable of acting as a > mail storage. This would imply storing messages, from > 1KB to > 100MB in size. The expected volume is 4000 > messages a day, and the database may store up to, > lets say, 500000 messages. > > Could postgres cope with this? (How is the support for > big data fields now BTW?) > > Thanks, > > Morten > > > -- ---------------------------------------------------- Ned Lilly e: ned@greatbridge.com Vice President w: www.greatbridge.com Evangelism / Hacker Relations v: 757.233.5523 Great Bridge, LLC f: 757.233.5555
"Morten W. Petersen" <morten@esol.no> writes:
> I'm wondering if postgres could be capable of acting as a
> mail storage. This would imply storing messages, from
> 1KB to > 100MB in size. The expected volume is 4000
> messages a day, and the database may store up to,
> lets say, 500000 messages.
>
> Could postgres cope with this? (How is the support for
> big data fields now BTW?)
I've been interested in the same thing. I don't think your message volume
should be a problem from my previous experience with pgsql. The issues
would probably be around the message size, 7.1 has TOAST which allows
arbitrary sized tuples but I don't know how the performance would be for
something like a mailstorage. Do anybody else have any comments on how the
upcoming 7.1 would perform in this area ?
[BTW. I would like to use it has a storage for James(An apache mail server)
and possibly different mail archive solutions.]
Regards,
Gunnar
On Tue, 27 Feb 2001, Morten W. Petersen wrote: > I'm wondering if postgres could be capable of acting as a > mail storage. This would imply storing messages, from > 1KB to > 100MB in size. The expected volume is 4000 > messages a day, and the database may store up to, > lets say, 500000 messages. I've thought about this before, now that 7.1 (still beta) breaks the 8K max row length limit from 7.0 and before. I get the idea that the occasional 100MB message shouldn't be a big problem with TOAST, and heck postgres can even compress the data (though at some cost, especially for >1MB messages). Compression can be disabled on a per column basis though, I believe. Since the ability to do this kind of thing is new in 7.1 beta (which has not been officially released yet), I'd be surprised if anyone has tried to implement a mail store in postgres. It's an intriguing idea, though, and as long as you match the hardware to your expected load, it should be quite feasable. 4000 messages a day is an average of about 3 per minute -- even if they reach one per second (20 times more) at peak times, it seems reasonable to expect postgres to be able to handle this pretty well. The next question is, can we use it for a Usenet spool? -- Tod McQuillin
Frank Joerdens <frank@joerdens.de> writes:
> I don't see why it shouldn't work, since 7.1 supports an unlimited row
> width. The question is: Why would you want to do that? A filesystem is
> always faster than a database, which is usually a very important
> consideration with a mailserver (or were you thinking about archiving
> messages?).
I'm interested in the archiving aspect, e.g. having pgsql as the storage
for an imap server.
regards,
Gunnar
> It works quite well (designing a web-based system on it right now), but > because of a DDB::Pg limit, I can only get 8k into a 'text' field. So if > your app is web-based, you might want to not use perl... Umm... I'm not sure what you're talking about here. I think you are referring to the 8KB row length limit in pre-7.1 PostgreSQL. AFAIK, Perl imposes no such limitation. In fact, I am using a database here based on v7.1b4 that quite happily stores records with text fields that exceed 50KB, and I am using a Perl program on the front end to read and write those records. So, what 8KB limit are you talking about? If there is one that I'm not aware of, I'd sure like to find out about it... Regards. Gordan