
MySQL vs PostgreSQL - llambda
http://www.wikivs.com/wiki/MySQL_vs_PostgreSQL#bodyContent
======
zheng
Slightly OT, but I'm going to reiterate a comment from the older submission,
this is one of the best written comparisons of two similar technologies I've
ever read. Generally you either get one-sided pieces or supposed "fair fight"
pieces which slant the view towards whatever the author has chosen as the
better solution for them. Those can be helpful, but something divorced from
justifying a decision like this is amazing. So thanks to the writer(s)!

~~~
3amOpsGuy
I only skimmed the page, but i didn't find any references or stats to back up
any claims in the text?

~~~
nl
Really?

Just about every single claim provides a reference. For example, the sentence
_MySQL 5.1 natively supports 9 storage engines_ links directly to the MySQL
documentation where they are listed.

------
sciurus
If you're evaluating databases based on their replication capabilities, don't
stop with what MySQL has built-in. Take a look at
[http://www.percona.com/live/mysql-
conference-2012/sessions/h...](http://www.percona.com/live/mysql-
conference-2012/sessions/how-evaluate-which-mysql-high-availability-solution-
best-suits-you) The advice boils down to "You probably want to ignore the
built-in replication and use Galera".

You can get Galera integrated with an otherwise vanilla MySQL from
<http://codership.com/products/mysql_galera> or integrated with Percona's
XtraDB fron <http://www.percona.com/software/percona-xtradb-cluster/>

~~~
briandear
Why bother? PostgreSQL is better.

~~~
taligent
But yet every major website chooses MySQL over PostgreSQL.

Why is that ?

~~~
spudlyo
Because up until recently PostgreSQL replication didn't really exist, and you
had to choose between 10 different trigger-be-using unscalable bag-on-the-side
technologies to fill that void.

~~~
jeltz
Still I know of at least one quite large website (one of the largest in
Sweden) that use Slony despite the performance hit from trigger based
replication.

------
fleitz
Call me when MySQL has a working insert statement.

One that doesn't randomly truncate your data, or allow insertion of nulls into
not null columns.

~~~
ceejayoz
You mean like:

    
    
      SET GLOBAL sql_mode='TRADITIONAL';
    
    ?

~~~
twerquie
That's like selling aftermarket airbags for your car.

~~~
ceejayoz
It's no more aftermarket than InnoDB support is.

------
sbov
Depending upon its definition, you can add a column to a PostgreSQL table
without locking it.

Maybe they changed it in later versions of MySQL, but adding a column to a
table become so lengthy for some of our projects that we switched for that
reason alone.

~~~
boyter
Its one of the big reasons I am looking at switching. I have been dumping the
table and reloading it in using a new name and the renaming the tables to get
around this issue but its hardly ideal.

~~~
AaronBBrown
pt-online-schema-change should resolve that issue for you:
[http://www.percona.com/doc/percona-toolkit/2.1/pt-online-
sch...](http://www.percona.com/doc/percona-toolkit/2.1/pt-online-schema-
change.html)

~~~
boyter
Seems the risks are such my approch works just as well,

"pt-online-schema-change modifies data and structures. You should be careful
with it, and test it before using it in production. You should also ensure
that you have recoverable backups before using this tool."

With that being the case what im doing is fine I guess.

~~~
AaronBBrown
Because there is a disclaimer, you are disregarding a thoroughly tested
product that solves your exact problem? One which is released by one of the
premier MySQL consultancies with the author of "the" MySQL book as the
designer?

~~~
boyter
I don't pay for support. So yes.

~~~
AaronBBrown
You could make the same exact disclaimer about SQL statement and it would be
true:

"INSERT modifies data and structures. You should be careful with it, and test
it before using it in production. You should also ensure that you have
recoverable backups before using this tool."

"ALTER modifies data and structures. You should be careful with it, and test
it before using it in production. You should also ensure that you have
recoverable backups before using this tool."

It's just good advice to test stuff before you use it in production.
Discarding an awesome product simply because the authors (responsibly) mention
that you should probably test it out seems overly paranoid and would severely
limit your choices. Percona Toolkit is the most well known and reliable set of
tools out there in the MySQL ecosystem.

------
rburhum
A huge advantage of PostgreSQL over MySQL is PostGIS (the geospatial extension
of PostGIS). I can't believe they don't even mention this.

------
jeffdavis
I think it's a bad idea to evaluate by checklists of features you think you
need. It's kind of like choosing where to live based on a checklist.

I started with MySQL, then used MySQL and PostgreSQL for a while. Then, when I
started to do more "real" projects, I just got so frustrated with MySQL in so
many ways at once. It wasn't that MySQL _couldn't_ do it, it's that it was
frustrating at every turn.

In my case, what caused me to drop MySQL almost entirely (around 2003 or 2004)
was doing a few simple reports involving dates. Then I started using postgres
and it was refreshingly consistent and flexible without so many caveats. And
now I develop for postgresql, and the code is similarly consistent and
flexible (and just all-around nice).

I have had a long string of positive experiences with postgres. It's hard to
wrap them up into a feature checklist.

If something held you back from using postgres in the past, it's a good idea
to watch the release notes to see if something new might solve that problem
(or better yet, discuss on the lists so maybe it will be solved faster). But I
tend to think that looking at long lists of features is a distraction.

------
fkdjs
I would say this site is too nice to mysql. For stored procedures, mysql had
those later whereas postgresql got those right early on. Mysql stored
procedures suck donkey balls compared to postgres. Postgres in general just
feels better designed, from the beginning.

re: replication, slony is horrible yet they focus on that. The slony author
says you can daisy chain things, but that's a setup nightmare. Also, slony's
n^2 communication gives you consistency guarantees, something I'm pretty sure
mysql can't do, but most people don't need that. I much prefer bucardo. It's
simple, easier to configure, fewer guarantees, but replicates much faster. I
just wouldn't run a bank on that. However, how many people design bank
software.

~~~
dlikhten
I read that entire site as:

PostgresSQL has feature X, Y, Z

MySQL had X, recently introduced Y, and does not always have Z.

It's always MySQL catching up to postgres. And lots of it's db engines are not
ACID. Its not the DB I thought it was.

------
ken
Looks like the fragment ID was added to escape duplication detection.

Previous discussion: <http://news.ycombinator.com/item?id=328257>

~~~
lazyjones
They could add a check for identically rendered topic titles (including site
name in parentheses) to avoid some of these.

~~~
kyrra
That won't work for pure javascript sites like Twitter.

~~~
lazyjones
I am referring to the title that is generated and displayed on HN for the link
(I don't know if it's done through scraping). Twitter has proper title tags on
status pages by the way (e.g.
<https://twitter.com/wikileaks/status/235818483588407296>)

~~~
bartonfink
That title is supplied by the submitting user according to some informal
recommendations. Every so often, there will be an argument over whether the
submitted title is appropriate for the content. HN doesn't scrape anything for
it.

------
billsix
Just pipe your data /dev/null to get that web-scale

<http://www.youtube.com/watch?v=b2F-DItXtZs>

------
AaronBBrown
Oh bloody hell, yet another PostgreSQL vs MySQL battle ensues.

------
magoon
Xbox vs Playstation...

------
jfb
Reading this makes me glad I don't ever have to interact with MySQL.
PostgreSQL is bad enough.

~~~
pygy_
What is "bad enough" about Postgres?

~~~
jfb
It's SQL. Isn't that sufficient?

~~~
justinsb
Mangling the well-known quotation: SQL is the worst syntax, except all the
others that have been tried.

What are you comparing it to?

~~~
beagle3
Kx system's kdb+/q query language.

It looks almost like SQL; the main philosophical differences:

a) it embraces order (that is, every query result has an implicit "running
index" field (called i, starting at 0).

This single handedly solves a lot of inconsistencies in practical SQL having
to do with order, which is abhorred by the relational data model, thus not a
first class concept, but is often required in practice, and thus
inconsistently bolted on.

b) columns can nest, and do not have to have the same type - which means that
aggregations like count, sum, max, min and distinct are not special in any
way.

c) there's a simple underlying programming language, so if you have an
intermediate select result that you need twice, you just give it a name by
prepending a "name: " to the select.

    
    
        temp: select from grades where age>10;
    
        b1: select from temp where eyecolor=`blue;
        b2: select from temp where eyecolor=`brown;
    

(compare to the mess that is correlated sub-queries, or alternatively,
horrible "create temporary table x as ..."

~~~
epo
SQL is fundamentally about sets, thinking of it in any other way provokes
unhappiness. Your differences are set-subverting add-ons which could be
accomplished by any higher level front end to SQL.

SQL is comparable to assembly language. Most people don't need it and wouldn't
know how to use it properly anyway. These are the sort of people who use PHP
and MySQL.

~~~
beagle3
> SQL is fundamentally about sets,

Nope. "Relational Algebra" / "Relational Calculus" / "The Relational Model" is
about sets.

SQL is about bags (orderless like sets, but each item might be repeated
multiple times). It's also about order ("ORDER BY" clause) in a horrible
inconsistent way.

> SQL is comparable to assembly language. Most people don't need it and
> wouldn't know how to use it properly anyway

No, SQL is not comparable to assembly in any meaningful way (you could replace
"assembly language" with "danish" in your statement and would be equally true)

While assembly language is more verbose, it is more fundamental than
everything else in the sense that eventually everything must be expressed in
assembly language (machine code, actually, which is equivalent to a proper
subset of assembly language) to be executed. Thus, going down to assembly
language might be more up-front work, but it is guaranteed that you can match
or improve on run-time results from any other language.

SQL is an inconsistent abstraction that makes some things simple, some things
hard, and some things essentially impossible -- and many of the things it does
do, it does in a way that's inherently inefficient. (And don't tell me about
the possible smart query optimizer - it doesn't really exist any more than
Intel's Itanium optimizer that makes code properly utilize the VLIW; or a
Unicorn).

edit: add the note about machine code.

