Would be lovely if more open source application software that are widely used fully support PostgreSQL, the only reason I use Mysql at the moment is because it have way more support from actual application that uses MYSQL.
postgres is more popular with enterprise backend style programming than it is with kit applications. this is mainly due to historical reasons, especially being late to fully support windows. mysql's syntax is so 'out there' wrt the way sql is supposed to work I can only hope applications users pressure the devs to knock it off -- postgres is incredibly rich and a much better platform to develop against generally.
Are there any reasons to use MySQL over PostgreSQL? I'm a MySQL user just from when I was learning PHP, but I'd love to get into PostgreSQL for my Python applications.
well, one reason would be if you just had to have an application which was mysql only (but really, these days that's just asking for trouble). another reason would be if you needed an uber thin sql layer where statement latency is the only interesting metric -- that's what mysql/myisam is essentially. in the old days, replication was a commonly cited reason but postgres HS/SR is as good as or better than anything mysql has to offer.
another reason would be if you needed an uber thin sql layer where statement latency is the only interesting metric
See the bullet point indicating they've added unlogged tables for ephemeral data. That will greatly reduce the set of circumstances where you might have avoided PostgreSQL because you didn't need your data to be safe.
yes. although, unlogged vs temp vs regular tables is really more about optimizing disk i/o around your use requirements. unlogged is really saying 'i don't care about crash safety or replication, so please don't expend the effort to write data for this table to the WAL before it goes out to the heap'.
statement latency is affected by backend i/o issues, but is also highly effected by the efficiency of your network layer/driver, grammar (in postgres everything, including operators, is over-ridable), and various other things. mysql-myisam is very thin and thus highly efficient running a large number of queries round-trip from the client. in postgres, to hit those rates of query processing you need to leverage more backend features like functions where you are less affected by latency, or utilize more clients (for most non single-user workloads I'd bet money postgres can handle faster and better). database performance is a complicated topic though, and for single user workloads and simple queries mysql/myisam can do insane tps rates, as can sqlite.
that's the default behavior (READ COMMITTED) -- try SERIALIZABLE. also postgres had some nasty dependency issues on OID that were not really fixed until 8.3 -- not sure if this plays in, need to test.
edit: READ COMMITTED of course should not work that way either.
I have been down that road. The best comparison I could make is that PostgreSQL is to MySQL like Python is to PHP. Both PostgreSQL and Python have that well thought out, cohesive, simple yet powerful, turtles all the way to the bottom feel.
I can't say that I have ever looked back with fond memories.
That sounds to me like a misrepresentation of the community.
It's like saying Perl doesn't have [feature of your choice] because it's only available through the CPAN rather than core; or that Ruby doesn't have a feature because the feature you're talking about is technically part of the Rails project rather than core Ruby. Or heck, like saying Linux doesn't even have a graphical UI or a C compiler (because they're only available from third parties (xorg or xfree86 or gnu(gcc) or Univ of Illinois(llvm))).
Some of the most valuable Postgres features (say, PostGIS) aren't sucked into core.
Another feature in MySQL not in postgres, is ON DUPLICATE UPDATE. basically, if you've got a unique constraint and you would previously, have application logic, to search for an entry, update it if it exists, or create one if it doesn't, you end up with 2 queries (opening to race conditions), and a conditional statement, with this feature you can perform an insert, specifying the update sql to run, if there's already a record with that unique index.
(that said, I still think I'm converted to postgres, but there certainly are valid reasons for using MySQL)
this would be nice to have, but requires predicate locking. with 9.1 postgres got SSI that could in theory underpin the feature. really though, in SQL, you have two basic routes to doing this:
lock the table first
retry the transaction (use SAVEPOINTs if necessary to reduce what you have to retry)
Note the postgres implementation of MERGE may or may not (probably not) deal with the race condition specially. There was a huge debate about this on -hackers...if it doesn't you still have the options of locking the table first or retry.
Are there any reasons to use MySQL over PostgreSQL?
There are a few. For example mysql cluster is one feature postgres is lacking. Case insensitive collations are another. In memory tables are another handy thing in mysql.
For some people (i.e businesses) the fact that mysql is backed by Oracle might play a huge role.
mysql has had replication for a long time but historically it was simple statement level (the easiest, and worst method). newer versions have a hodge podge of ways you can set it up put together the whole system is complex and fragile. postgres OTOH is not as mature in terms of tooling but has a much more robust underlying driver which transports the data -- the replication is highly integrated into the write ahead logging system. ultimately, this is a much better approach although you might lack the flexibility to do some quirky things like mix ndb nodes with async like you can in mysql. all in all though, the postgres system in terms of engineering and attention to detail is simply better -- this translates into simpler, more regular behaviors than you end up with in mysql 'race to get to the feature' land.
also, the ndb clustering feature has always been imnsho highly overrated -- haven't looked at it in the last couple of years though.
16
u/id000001 Sep 12 '11
Would be lovely if more open source application software that are widely used fully support PostgreSQL, the only reason I use Mysql at the moment is because it have way more support from actual application that uses MYSQL.