Postgres has transactional DDL. Last time I looked MySQL didn't - that
was enough for me.
Simon Harris once told me that when he was maintaining the
RedhillOnRails database plugins 90% of the reported defects (he
actually said "all", but there might have been some exaggeration) were
related to MySQL idiosyncracies. Of course that doesn't affect everyone.
On 26/01/2010, at 10:32 AM, Lachie wrote:
The major reason I chose postgres for our apps was consistency
Firstly, consistency of use:
Pop quiz: In which version of mysql did subselects start to work? Yes,
you can usually avoid subselect, but they're certainly not triggers or
stored procedures.
Pop quiz: What are the rules for using indexes in mysql?
And this, from the mysql <= 4.1 manual
(http://dev.mysql.com/doc/refman/4.1/en/join.html), sorry for the big
paste:
"Note that several changes in join processing were made in MySQL
5.0.12 to make MySQL more compliant with standard SQL. These changes
include the ability to handle nested joins (including outer joins)
according to the standard. If a nested join returns results that are
not what you expect, please consider upgrading to MySQL 5.0. Further
details about the changes in join processing can be found at JOIN
Syntax.
You should generally not have any conditions in the ON part that are
used to restrict which rows you want in the result set, but rather
specify these conditions in the WHERE clause. There are exceptions to
this rule.
Note that INNER JOIN syntax allows a join_condition only from MySQL
3.23.17 on. The same is true for JOIN and CROSS JOIN only as of MySQL
4.0.11."
The mysql team made big *interface* (ie SQL) design mistakes in the
beginning, which they're rectifying over time. In the beginning it was
a feisty reaction to the stuffy, dogmatic SQL adherents. Now they seem
to be walking that attitude back, somewhat.
Note from the version number that these interface changes were made
*between patch level versions*! (Incidentally, this is the main reason
that I think of mysql as a toy database. I'm not opposed to toys in
production as such, but I'd rather their fun came from areas other
than the interface against which I program)
I couldn't say definitively that this hasn't ever happened in
postgres, but to me it seems that they doggedly adhered to SQL
standards (perhaps, say, at the expense of performance) which then
stayed rather static. The internals behind a stable interface can
always be optimised.
The second big reason is consistency of ownership. Mysql has recently
been owned by Mysql ab, then its main storage engine (innodb) was
bought by Oracle. Then sun bought mysql, now sun is owned by Oracle.
I mean, good on 'em for creating a disruptive and desirable product,
but it doesn't inspire me to base my own business on their shifting
sands.
Realistically, we're moving slowly but surely *away* from sql
databases to JSONny key value stores. If you're talking about a
database that's a better YAGNI fit for web apps then couch, mongo,
redis and their ilk are it.
But if I'm going to use a relational database, I want it to hop out of
the way as much as possible. This is not something that mysql readily
does.
:lachie
On Mon, Jan 25, 2010 at 11:44 PM, Jason Stirk <[email protected]>
wrote:
2010/1/25 Xavier Shay <[email protected]>
On 25/01/10 12:35 PM, Jason Stirk wrote:
... Deployment database?
MySQL, recently played a bit with Postgres (although I'm still
unimpressed)
This is contrary to most opinion I've heard, so I'm interested in
hearing
more.
What follows is just (ranty) my take on MySQL/Postgres. I'm more
than happy
to have the record set straight by a Postgres guru...
I'm strongly of the opinion that, unless you're admining the
installation,
things like Rails and ActiveRecord are going to hide just about all
the
differences between the two anyway. That's, you know, the idea of
them...
I guess part of my preference for MySQL comes from my familiarity
with it:
I've admined and used installations of it for about 8 years, as
opposed to
about 6 months on Postgres. However, permit me to throw caution to
the wind
and rant anyway!
Generally, my thoughts are that MySQL is postfix to Postgres's
sendmail.
Sure, Postgres has all these extra features, but my experience of
it is
crazy arcane syntax, and a mishmash of CLI and in-client commands.
For example, what's with these crazy "\d" commands just to do
simple things
like showing a list of databases or table? "SHOW DATABASES" or
"SHOW TABLES"
might be longer to type, but they're trivial to remember. (I'm
prepared to
have my ass handed to me here, explaining a simple, in client way
of doing
this in Postgres... Please, hand away...)
I was also very unimpressed with the way postgres created and
managed DBs
and users (createdb, and friends). The idea of CLI tools (like
"createdb")
to manipulate the state within a daemon process does give me the
creeps more
than a little...
Many folks give the excuse that MySQL is a "toy" database, as the
older
versions lacked stored procedures, triggers and the like. Whilst
that's been
changed recently, realistically, I'm prepared to call bullshit when
this
justification comes up in the context of web dev: YAGNI anyway...
In fact, I'd be very interested to talk with any Ruby web developer
who's
ever needed stored procedures, triggers, or anything like that. I'm
genuinely interested to know what situation could have called for
them in
the web world, and how they actually benefited your project.
That's not to say I think they're unnecessary on the whole - there
are a
metric shitload of uses where they're essential to preserve DB
integrity, or
speed things up when load dictates, but in the web world, I'd be very
surprised if you're grabbing these tools early on in the game.
(Unless
you're writing a web console for a nuke reactor, or rebuilding the
ASX on
Rails...)
To be honest, these advanced features are probably going to be a
pain to
work with if you're using a ORM layer anyway, whether it's
ActiveRecord or
something else.
(Not to mention that the idea of code in the DB layer scares the
crap out of
me...)
(Offtopic -1: Hell, do things like foreign key constraints even work
reliably in ActiveRecord yet?!?)
Configuration wise, MySQL is a snap to work with, and it's all
pretty damn
logical how it all hangs together. If you want a low memory
deployment for
dev, or a dedicated DB server, MySQL can handle it with just a few
tweaks.
Sure, there's a bit of thought work involved when looking at the
pros and
cons of each engine for the tables, but even that's pretty simple,
and only
going to happen when you define your schema. Plus, that is less of a
question now that awesome tools like Sphinx reduce the need to do
in DB
fulltext search (so why the hell would you use MyISAM? Unless you
really,
really dig table-level locking!)
Finally, when you _do_ need to outgrow a single DB installation,
you still
have the ability to look at replication or the like. I can't claim
to have
set it up myself, but I've read up on it, and it really doesn't
look _that_
hard. I believe that multi-master replication is impossible/very
hard out of
the box with MySQL, but if you've got enough load to need to deal
with that
kind of issue, good luck to you!
If you're interested in more advanced topics about MySQL, check out
O'reilly's "High Performance MySQL" by Jeremy Zawodny (who moved
all of
Yahoo! Finance over to MySQL from BDB in 2000/2001). It's probably
a tad
dated now (6 years old) but is well worth the read for some
interesting
ideas.
Thus concludes my rant for the evening.
J
--
You received this message because you are subscribed to the Google
Groups
"Ruby or Rails Oceania" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/rails-oceania?hl=en.
--
You received this message because you are subscribed to the Google
Groups "Ruby or Rails Oceania" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to [email protected]
.
For more options, visit this group at http://groups.google.com/group/rails-oceania?hl=en
.
-----
Steve Hayes
Cogent Consulting
http://www.cogentconsulting.com.au
http://iridescenturchin.blogspot.com/
twitter: steve_hayes
GTalk: steve.e.hayes
Yahoo: steve_e_hayes
Mobile: 0403 902 431
SkypeIn: +61 3 9005 6695
Measure your Ruby code quality: http://www.codeyak.com
Have more things to do than you have time to do them? Use http://www.runwayapp.com
.
Runway - where your life takes off.
--
You received this message because you are subscribed to the Google Groups "Ruby or
Rails Oceania" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/rails-oceania?hl=en.
