News for hackers. Stuff that matters.
Re:Urban Airship: Postgres to NoSQL and back by cmer on Monday September 19, 2011 @08:39PM (#3015782)
A little bit unrelated, but as of today, what would be the advantages of choosing pgsql over mysql for someone who already has decent knowledge of mysql? Is it still a matter of preference (and being able to deal with the mysql license) or did pgsql progress so much over the last 3-4 years that it became superior? What are the pros over mysql?

I'd consider switching to pgsql for a future project but throwing my mysql expertise out the window for nothing seems like a bit waste of time.

Re:Urban Airship: Postgres to NoSQL and back by samstokes on Monday September 19, 2011 @09:30PM (#3015893)
A few from my personal experience:

* microsecond granularity on timestamps, rather than second

* transactional DDL (if a migration fails halfway through, you don't end up in a broken or inconsistent state)

* really superb documentation - clear and detailed explanations of hard stuff like locking semantics

* No silent coercion/truncation between data types

* Functional indices (as far as I can tell from http://bugs.mysql.com/bug.php?id=4990, MySQL doesn't have these)

* http://postgres.heroku.com (yeah MySQL has RDS but I've not heard great things)

Re:Urban Airship: Postgres to NoSQL and back by jeffdavis on Tuesday September 20, 2011 @12:29PM (#3018230)
"Is it still a matter of preference"

The systems are quite different; I don't think it was ever really about preference -- it's more about the approach you take to development and operations.

It's hard to point to any one thing that you might find useful in postgres without knowing more about the kind of projects you work on. But if you start to do things the postgresql way, then I think you'll find some things that make development much easier and operations much smoother.

"Better" is a loaded word; but if developing a new application, I think it's a good idea to default to postgresql, and only use mysql if you have a specific reason (and there are a few good ones). I've been a postgresql community member for a long time and use postgresql much more than mysql, so take that opinion with a grain of salt.

Re:Urban Airship: Postgres to NoSQL and back by zzzeek on Tuesday September 20, 2011 @09:20AM (#3017393)
by far the largest - joins and subqueries actually optimize correctly on Postgresql. In mysql, if you say (pseduosql) "select a_few_rows_from (select many_many_rows from some_table) where <some criterion>", mysql scans "many rows" no matter what. Postgresql can figure out the big picture.
Re:Urban Airship: Postgres to NoSQL and back by alexg0 on Monday September 19, 2011 @08:51PM (#3015813)
This has a pretty decent, if dated list: http://sql-info.de/mysql/gotchas.html

Few of the items on that list bit me personally.

Re:Urban Airship: Postgres to NoSQL and back by cmer on Monday September 19, 2011 @09:20PM (#3015871)
I'm more interested in knowing what PgSQL does better than MySQL today, in 2011. MySQL definitely has a few caveats, my question is mostly, is PgSQL now significantly better than MySQL or they're both more or less the same, with different pros and cons.
Re:Urban Airship: Postgres to NoSQL and back by leandrod on Monday September 19, 2011 @11:50PM (#3016196)
No, PostgreSQL is way better.

It has a mostly compliant SQL dialect.

It is more compatible with other DBMSs, specially DB2 but also Oracle.

It implements a host of procedural programming languages and extensions, including types and operators.

It is way faster, and scales way more.

It is totally free (no paying Oracle to do InnoDB hot backups).

It is way more consistent.

It does geographic data second to none.

Its development is totally open and way faster. Reading the PostgreSQL TODO wiki page is a joy.

And so on and so on.

Re:Urban Airship: Postgres to NoSQL and back by jacques_chester on Tuesday September 20, 2011 @12:39AM (#3016302)
For me personally:

1. MySQL's "feature set" is usually described as a union of storage engine features; whereas in fact you only ever get some of them at a time. I find that extremely annoying.

2. PostgreSQL has a decent, smart query planner. In cases where I have multiply-layered views, I've seen MySQL throw up its hands and manually churn through each view in turn, while PostgreSQL did the smart thing and combined all the views into a single execution plan.

3. It's much more featuresome for in-database programming. The web world tends to look on RDBMSes as flat files with a funny accent, so this doesn't matter for a lot of programmers. But sometimes you absolutely must either a) protect the data or b) place computation as close to the data as possible. Featuresome databases like PostgreSQL, Oracle, DB2 etc let you do this. MySQL not as well.

Re:Urban Airship: Postgres to NoSQL and back by jeffdavis on Tuesday September 20, 2011 @01:32PM (#3018596)
"I'm more interested in knowing what PgSQL does better than MySQL today, in 2011."

The most striking difference[1] is probably the cost-based optimizer and the availability of a lot more execution plans. Most often cited is that MySQL has no hash join, but there are lots of plans that make a huge difference in execution time, and (for the most part), mysql just doesn't have those execution plans available.

And these are algorithmic differences. Typically, you think of optimization as something that does your same algorithm, just a little faster. In a SQL DBMS, optimization means choosing a better algorithm.

And it's hard for MySQL to add new algorithms because it doesn't have a cost-based optimizer, so it would have no idea which ones to choose.

[1] There are lots of other differences, but sometimes differences aren't easily represented as convincing "check-mark" features. So I really think you are asking the wrong question. The right question is: if I were making non-trivial application XYZ in postgres, how would it be done (from both dev and operational standpoint)? And is it easier to develop and smoother to operate if I do it that way?

Re:Urban Airship: Postgres to NoSQL and back by pvh on Tuesday September 20, 2011 @12:40PM (#3018307)
Looking over the list here, I'd call out a few specific things that are important to me:

* HStore, the key-value store column type * PGXN, still new, but going to change the world * Replication that Actually Works For Real No Kidding. * Window functions, which are a total brainf*ck but once you internalize them, indispensible.

Re:Urban Airship: Postgres to NoSQL and back by vegai on Monday September 19, 2011 @11:24PM (#3016145)
The enemy of better is good enough ...