[Mailman-Developers] HyperKitty MongoDB vs PostgreSQL.

[Toshio Kuratomi]

On Fri, May 25, 2012 at 11:03:10AM -0400, Barry Warsaw wrote:
> On May 25, 2012, at 11:18 AM, Pierre-Yves Chibon wrote:
> 
> >Thus we will move forward with PostgreSQL as a back-end for HyperKitty.
> >The good news is that HyperKitty already works fine with PostgreSQL and
> >KittyStore (if you use the correct branch [4]).
> >However, we have to rebuild our test server, so we cannot show you how
> >the latest version works right now.
> 
> I haven't looked, but is it possible to use an intermediate layer like an ORM
> which would allow a user to use different databases under the hood?  For
> example, the core is written using Storm, so is pretty easy to use any
> database that Storm supports.  Currently, because of minor SQL differences,
> this means SQLite and PostgreSQL, but it should be easy for someone to
> contribute MySQL code.  An nosql database might be harder though because that
> doesn't fit naturally into Storm's view of the world.
> 
We're using SQLAlchemy i(a different ORM) to abstract the database layer.
That means the big three open source solutions (sqlite, postgres, and mysql)
and proprietary ones like oracle should all be supported.  However, we're
thinking about using fulltext search indexes which would mean tying it to
specific backends.  It may be possible to run in a degraded mode (searches
being slower... but for small to medium lists this is likely fine) so we
could keep the backend abstraction while running faster on certain
databases..

MongoDB is not a relational database so using an ORM seems like a bit of
a hack.  People have adapted SQLAlchemy for use with MongoDB but I'm not
sure what sacrifices they had to make to make that work.

Running comparisons using postgres was actually a choice dictated by our
experience with the Core.  In our use, using the sqlite backend quickly
resulted in database locks that we couldn't figure our way out of.  So that
effectively left us with postgres as the only db for the core.  If admins
have to run postgres for the Core then the thinking is that it wouldn't be
hard for them to also run it for the archiver. I've never had the sqlite
issue with SQLAlchemy,though,  so we could probably support
a non-fulltext-optimized search in sqlite without doing much of anything.

Note that pingou's current blog post is done without fulltext indexes on
a moderate sized list and we made the decision to focus on postgres from
those benchmark numbers.  Postgres is slower but not enough that we think
users will be upset at the speed.  A mongodb backend would be nice for those
who have even larger lists (or possibly for a large number of lists on the
same server -- since the contents of tables are cached in memory if there's
enough available, a system that hosted a large number of moderate sized
lists might also benefit from a faster backend.

-Toshio
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: application/pgp-signature
Size: 198 bytes
Desc: not available
URL: <http://mail.python.org/pipermail/mailman-developers/attachments/20120525/335ef69a/attachment.pgp>