Re: Help needed with PostgreSQL clustering/switching from MySQL

On Tue, Jun 21, 2011 at 05:07:10AM +0000, Vikram Vaswani wrote:
>
> Hello
>
> I'm new to PostgreSQL, coming at it from a MySQL background. I'm
> currently looking at switching one of our applications (which
> currently uses MySQL) over to PostgreSQL and had some questions.
>
> We're considering the switch because of issues we have faced when
> using MySQL in a clustered scenario and we're hoping that switching
> to PostgreSQL will help us resolve these issues. Our three biggest
> pain points with MySQL are:
>
> 1. MySQL's NDB engine (used for clustering) cannot index textual
> data stored in a BLOB field

PostgreSQL's full text capability is quite good.  There are better
specialty (non-relational) engines out there, and some proprietary
engines that do more (or at least different) things, so you'll need to
assess carefully what type of text searching you want to do, and what
you'll trade that capability for.

> 2. When configuring a MySQL cluster, there is a memory limit on the
> number of objects (tables and fields). We often have problems when
> importing new tables, wherein we need to increase the memory limit
> for the server's NDB engine before it allows us to import. This can
> be a problem for dynamically-generated tables, as we cannot
> accurately forecast the number of database objects in advance in
> these cases.

Dynamically generated tables are generally a problem at the design
level.  Neither PostgreSQL nor any other engine will solve that.

> 3. MySQL's NDB engine doesn't support or enforce foreign keys.

This, PostgreSQL does extremely well.

> So my first question is, I'd like to know if PostgreSQL has similar
> issues when running in a clustered scenario.

I'm not entirely sure what you mean by a "clustered scenario," but I'd
like to digress into the matter of multi-master replication.  It can
be fast, so long as it doesn't have to be correct, or it can be
correct, so long as your users are willing to wait, but it can't be
both fast and correct at once.

In 90%+ of cases, it's neither fast nor correct.

I guess what I'm saying here is that you should not design systems
that depend on unicorn steak, skyhooks, magic pixie dust, or fast,
accurate multi-master replication.

> Second, on reviewing the manual and some sites, it seems that there
> are a number of different OSS solutions for implementing failover
> and clustering with PostgreSQL, but no "official" version.

As of 9.0, there is built-in asynchronous replication, which can be
streamed (lower lag times) if you like.  You might also want to
consider some of the other solutions.

http://wiki.postgresql.org/wiki/Replication,_Clustering,_and_Connection_Pooling

> Is this understanding correct? If yes, which solution is best suited
> for running PostgreSQL in a private cloud, with clustering/failover
> support?

Please to understand that you need to set priorities for these things
and decide which you might sacrifice in order to get the others.

> Thank you,
>
> Vikram
> THIS EMAIL & ANY ATTACHED FILES ARE PRIVATE & CONFIDENTIAL If you are not the addressee, any disclosure,
reproduction,copying, distribution, or any other dissemination or use of this communication is strictly prohibited. If
youhave received this transmission in error please notify the sender immediately and then delete this email. Email
transmissioncannot be guaranteed to be secure or error free as information could be intercepted, corrupted, lost,
destroyed,arrive late or incomplete, or contain viruses. The sender therefore does not accept liability for any errors
oromissions in the contents of this message which arise as a result of email transmission. If verification is required
pleaserequest a hard copy version. 

In future, please to elide these "disclaimers."  The serve no legal or
practical purpose, but they do give people they annoy a convenient
excuse not to reply.

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate