MySQL vs PostgreSQL

http://www.wikivs.com/wiki/MySQL_vs_PostgreSQL#bodyContent

by llambda • 14 years ago

160 104 14 years ago

wikivs.com

ken• 14 years ago

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

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

lazyjones2• 14 years ago

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

kyrra• 14 years ago

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

Groxx• 14 years ago

That's from 1405 days ago. Duplicates can be submitted in a significantly shorter time-span, IIRC.

kemiller• 14 years ago

That was three years ago. The page has plenty of new information. Perhaps there should be a statute of limitations on the dupe check.

sophacles• 14 years ago

There is. It isn't an official policy, but dupe check only compares to values in some cache somewhere. Duplicate articles are FINE on HN, given that after a time period the ability to comment is removed from an article (unless of course, the suggestion is that there is nothing valuable to discuss about something n days after a conversation starts, ever).

jfb4• 14 years ago

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

pygy_3• 14 years ago

What is "bad enough" about Postgres?

jfb4• 14 years ago

It's SQL. Isn't that sufficient?

fleitz• 14 years ago

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.

ceejayoz3• 14 years ago

You mean like:

  SET GLOBAL sql_mode='TRADITIONAL';

?

twerquie• 14 years ago

That's like selling aftermarket airbags for your car.

einhverfr19• 14 years ago

But individual apps can change that on the session level, right?

So still no guarantees and you more or less have to audit every app connecting to your db to make sure it isn't tampering with the sql_mode if you value data integrity.

Roboprog• 14 years ago

I think ceejayoz took you a little too literally. MySQL continues to scare the hell out of me. It was bad enough before Oracle go a hold of it: the designers seemed completely unconcerned with data integrity. It has many patches to deal with individual issues, but when the creators just fundamentally don't care???

The GLP-not-LGPL license sounds like a booby trap gladly left in place by Oracle from before they got it. I can't see Oracle wanting to do anything other than make it light and fast (at expense of correctness), either, since if you want "data integrity", they have a solution for you.

swah• 14 years ago

But OTOH it powers gigantic websites a la Facebook. Can it really be that bad fundamentally?

spudlyo4• 14 years ago

I don't like Oracle either, but you're coming dangerously close to sounding like a conspiracy theorist. The GPL-not-LGPL issue with the client library hasn't been a problem for years now. The attentive have been linking their non-free mysql-be-using software with libdrizzle, which is a complete, clean-room implemented, Berkeley licensed MySQL client library. Before that they were using the public domain version that MySQL-AB released before Monty decided to get sneaky with the GPL.

I believe that MySQL has become better since Oracle got a hold of it, their stewardship I consider to be much better than Sun's. Since acquiring MySQL, Oracle has put out an extremely solid release, MySQL 5.5, and are making steady progress on 5.6. If you lived through the early releases of MySQL 5.1 you have an idea of what a botched MySQL release is like.

I don't believe you do anyone on HN a service by spreading your gut feeling FUD about Oracle and MySQL.

einhverfr19• 14 years ago

Yeah, I have also had multi-row inserts deadlock against themselves in MySQL.

However, you need to reword that slightly. MySQL does have such a working insert statement if you set strict mode. The problem is that apps can unset strict mode. Until that changes.....

So really you should word it as:

"One that can be guaranteed not to randomly truncate your data, or allow insertion of nulls into not null columns."

MySQL inserts in strict mode don't do these things. MySQL inserts cannot be guaranteed not to do these things however. Therefore this relegates MySQL, in my view, to a one-app-per-db environment because you cannot prove that your db constraints will be properly enforced and therefore have to independently verify this aspect in every app that connects.

sciurus2• 14 years ago

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... 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• 14 years ago

Why bother? PostgreSQL is better.

sciurus2• 14 years ago

PostgreSQL may not be better on all possible points of comparison.

PostgreSQL replcation alone isn't comparable to MySQL with Galera. I don't know enough about the various extensions to PostgreSQL to know which ones could give you

* Synchronous replication * Active-active multi-master topology * Read and write to any cluster node * Automatic membership control, failed nodes drop from the cluster * Automatic node joining * True parallel replication, on row level * Direct client connections

in a reliable and perfomant manner. Keep in mind I'm just atarting to play with Galera, I haven't used it in production yet, but it's making my planned architecure much simpler to manage than the traditional MySQL or PostgreSQL replication approaches.

taligent4• 14 years ago

But yet every major website chooses MySQL over PostgreSQL.

Why is that ?

einhverfr19• 14 years ago

The same goes with PostgreSQL. Replication isn't a one-size-fits-all need, and companies like Affilias will find Slony much easier to work with than the built in PostgreSQL replication because Slony allows for seemless, zero-downtime upgrades, partial replication, and a whole host of other features. On the other hand, Slony allows for partial replication.....

You also have Bucardo which can do master-master replication between two nodes, and a few other solutions out there.

zheng• 14 years ago

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• 14 years ago

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

nl• 14 years ago

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.

dredmorbius• 14 years ago

And the whole wiki (hit the homepage) is comparisons of things. Some are technologies, but it's more than just that.

Pretty slick.

AaronBBrown4• 14 years ago

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

sbov• 14 years ago

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.

boyter4• 14 years ago

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.

GrumpySimon• 14 years ago

What are you doing that you need to alter your schema so often?!

einhverfr19• 14 years ago

What's funny is I used to use MySQL for db prototyping because altering schemas on PostgreSQL was such a pain (prior to 7.3). Now it is much better.

AaronBBrown4• 14 years ago

pt-online-schema-change should resolve that issue for you: http://www.percona.com/doc/percona-toolkit/2.1/pt-online-sch...

therico• 14 years ago

It's a pity your team didn't come across pt-online-schema-change. It's been extremely useful for our large migrations.

magoon• 14 years ago

Xbox vs Playstation...

billsix• 14 years ago

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

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

rburhum• 14 years ago

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

fkdjs• 14 years ago

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• 14 years ago

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.

jeffdavis• 14 years ago

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.

MySQL vs PostgreSQL

Related Stories