[LRUG] Reasons for Postgres

[Sam Stokes]

Thanks for all the responses!

I can definitely appreciate the value of transactional DDL, having also 
been bitten by half-finished migrations in the past.  That's reason 
enough to check it out when I get the chance.

I've heard before that the PostgreSQL CLI is nicer than MySQL's.  Is its 
permission system any easier to administer?  I don't think I've ever 
successfully run a GRANT ALL PERMISSIONS TO fred at localhost AND HIS DOG 
without having to look up the syntax every time.

It seems like a lot of the "MySQL vs Postgres" material out there is 
fairly outdated - e.g. MySQL used to have no *data* transactions, no 
foreign key constraints, no nested SELECT etc.  Now it has most of 
those, which seems to make it more convincingly "good enough" if you 
already know it (don't 37Signals use it for everything?)... but it's 
always interesting to know whether one can do better than "good enough".

Speaking of 37Signals, I decided to look up that claim, and found this:
"migrating a database schema in MySQL on a huge table takes forever and 
a day ... I’m told that PostgreSQL is a lot more accommodating in this 
regard"
(from http://37signals.com/svn/posts/1509-mr-moore-gets-to-punt-on-sharding)

Anyone have experience to back that up?

-- 
Sam

Murray Steele wrote:
> 2009/11/16 Jocke Selin <jocke at selincite.com>:
>> 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?
>>>
>> 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 :) )
> 
> Transactional DDL is literally the only thing I miss from my MS SQL
> Server days.  Since I moved jobs and have been using MySQL practically
> every migration I've written that's involved massaging data as well as
> the schema has broken at least once and left me with a dev DB gunned
> down halfway along it's migratory path*. It's enormously painful to
> mess about with the down to try and get things back (and calling
> scorched earth on the DB and starting again from afresh means I'm not
> really convinced that my migration works as there was no data for it
> to massage).
> 
> There may be other things that PG does well, but "everything can be
> transactional" is a *huge* win.
> 
> Muz
> 
> * I admit that this says more about my (lack of) skills when writing
> migrations than it does failings of MySQL, but still...
> _______________________________________________
> Chat mailing list
> Chat at lists.lrug.org
> http://lists.lrug.org/listinfo.cgi/chat-lrug.org