MySQL and PostgreSQL as NoSQL alternatives

PostgreSQLPostgreSQL

This is the next post in my NoSQL evaluation series.  I was tasked with finding alternative data persistence mechanisms for a customer because of the perceived cost associated with SQL Server.  The customer eventually decided to stick with SQL Server, for now, but initially they were very intrigued with porting part of the application to a NoSQL solution.  The biggest problem I had with this is that there was no in-house NoSQL expertise.  My fear is our developers would make a lot of rookie mistakes that would cost us money and time.  

 

I agreed with the initial premise that SQL Server was too expensive but steered the conversation toward cheaper RDBMS alternatives.  At least our people would have less of a learning curve.  Naturally MySQL and PostgresSQL were at the top of the list.  Furthermore, both products are open source and therefore we could extend either to make it a hybrid, customized PartialSQL solution.  

 

It wasn't terribly difficult for me, with limited experience with either product, to put together a proof-of-concept for a small portion of our application.  Both products are relational (or at least "set-based").  Both can use Navicat as a development/DBA GUI.  Navicat is my preferred GUI tool when I'm a bit stumped on esoteric SQL extensions in either of these products.  

 

Both support triggers.  

 

Both support variants of stored procedures.  In fact, I'd argue that MySQL support stored procedures closer to the SQL:2003 syntax than even SQL Server does.  If you need to migrated stored procedures then MySQL will be easier.  If you are familiar with Oracle then Postres will be a little easier.  It's PL/pgSQL is very close to PL/SQL and follows most of the same conventions.  For instance, in PL/SQL you can't return a result set directly from a stored procedure.  Instead you build a cursor over a result set and then pull from that.  Postres calls its stored procedures "stored functions" but they are functionally equivalent.  

 

I'm fascinated by history and the history of PostreSQL (or, just postgres) is kinda fascinating.  When System R, the IBM project that was the first RDBMS to use SQL, was being developed there were a bunch of research papers that went along with it.  These needed to be searched and cataloged.  Instead of using System R to do this a bunch of researchers created Ingres (INteractive Graphics REtrieval System).  This was open-sourced and gained quite a bit of traction.  Postgres is "post-ingres", or the next evolution of Ingres.  Great story to tell at IT cocktail parties.  

 

Either PostreSQL or MySQL make a great SQL Server alternative if you are price-conscious.  I find either to be very quick to setup and learn.  

relational/set-based

TRIGGers, stored procs (multiple languages)

ACID compliance

SQL and PL/pgSQL (also Ruby, Java, PHP, and more)

Port 5432 (default)

"design-first datastore" (create the schema, then enter the data)

ANSI compliance

GUI Tool:  Navicat