[LRUG] Reasons for Postgres (was: Migrating SQLlite to MySQL)

[Jocke Selin]

First, I'm not very much for pros and cons, I'm not much for "religious wars" be it in the real world or online, regarding anything.

I am however, for informing about an "underdog". :)


On 16 Nov 2009, at 16:52, Sam Stokes wrote:
> Now that you've brought it up :)  Would you be able to list a few things that you like about Postgres, or even reasons to use Postgres over MySQL?
> 
> I'm not looking for a flamewar (and certainly not intending to defend MySQL!) - I'm genuinely curious about Postgres.  It's something I've never got around to looking into - MySQL has always been "good enough", since I'm already familiar with it - but I'd like to have a reason to try Postgres on a new project.


There's a few ways to see this.

One is that there's no reason for use of MySQL apart from that it's very popular. It's also perceived to be very fast (which is a flamewar on its own, so I'll drop that one).

Another one is that PostgreSQL is a real relational database, whilst IMO MySQL is simply a data-storage.

Yet another one is that if your data is your most valuable possession of your application then protecting the data from corruption should be your primary concern. In PostgreSQL you can do this with enforced foreign keys (which you can do to some extent in MySQL these days too), triggers and stored procedures. PostgreSQL also has got datatypes that should cover most things, if they don't cover your needs you can build your own (such as Geo data). On top of this I have not experience a table corruption nor any sort of data loss in a PostgreSQL database.

One more is that you can use transactions for anything in PostgreSQL, inlcuding DDL (Data Description Language; CREATE, DROP, ALTER, etc). - this allows you to create "patch scripts" that you can run on a more-or-less live server to migrate your changes over. If everything works in a SQL script you simply issue COMMIT and your changes are there. (and I just saw that Tom Stuart agrees on this one :) )

Then my own preference is that the PostgreSQL tools, including the command line interface, is just nicer to use.

It's easier to answer direct questions, so please feel free to ask. :)

/Jocke