3

I'm aware there are a few threads out there addressing this issue, but I'm wondering if anything has changed since those have been published.

I'm looking to build a GIS webapp, and people are all saying that PostgreSQL is the way to go because it supports various things that have to do with mapping better, whereas MySQL's spatial extensions aren't too great.

So PostgreSQL seems like the way to go, but everywhere I go I'm reading that PostgreSQL is terribly slow compared to MySQL, is this still true?

If I want to use GeoDjango with MySQL, will I be able to do most everything?

I'm really stuck between the two, simply because people keep saying PostgreSQL is really slow, but MySQL isn't really great for dealing with GIS stuff.

What's your take SO?

aroooo
  • 4,726
  • 8
  • 47
  • 81
  • 3
    PostgreSQL might have been slow back in the 90s (versions 7.x) but that is definitely not true any longer for any recent version of Postgres (>= 8.3) –  Nov 30 '11 at 12:37

4 Answers4

8

No, postgresql is not slower. This myth is due to people running single threaded sequential benchmarks on myisam vs postgresql. Benchmarks that attempt to model actual usage conditions with many concurrent queries put postgresql on par with or ahead of mysql in performance, especially as you scale up in CPUs/cores.

http://www.randombugs.com/linux/mysql-postgresql-benchmarks.html

http://tweakers.net/reviews/657/5

Some Guy
  • 239
  • 1
  • 6
  • 4
    There are so many BS benchmarks on the web it's just sickening. For example, the randombugs.com benchmark you linked is not using InnoDB, even though it claims to. Look closely: skip-innodb is used in the configuration files. It's using MyISAM, then, because MySQL stupidly falls back to that as a default when InnoDB isn't available. So *of course* it's going to suffer on OLTP benchmarks, because MyISAM has zero read/write concurrency! And I might add that those benchmarks are *ancient*. 2006? Come on. The whole world has changed since 2006. –  Dec 02 '11 at 19:29
  • 1
    Yes, that is the whole point. The myth started because of BS benchmarks. Yes, postgresql has been faster than mysql for years, even in 2006. All that has changed since then is postgresql has gotten even faster, and mysql has added some more of the functionality they were missing. – Some Guy Dec 03 '11 at 00:27
  • 4
    You didn't link to any non-BS benchmarks to prove your claim that PostgreSQL is faster. It's a frequent claim. Proof by repeated assertion doesn't work for me. –  Feb 14 '12 at 16:55
  • Actually, they were intentionally calling skip-innodb on that benchmark, they were using the innoDB-plugin which has better performance and was integrated into the core in later versions. – phazei Dec 17 '12 at 20:56
5

In my opinion, it's silly to compare MySQL and PostgreSQL in terms of speed if there are variables unknown, such as - what's your budget, what's your target system output and what's your load rate?

Both RDBMSs are great, and they can be scaled. The difference is that MySQL has pluggable engine architecture, allowing it to plug in various engines. Natively, MySQL supports 9 engines if I'm not mistaken but it has a plethora of commercial engines to choose from, along with 2 popular forks (Percona's and MariaDB) that introduce various enhancements, especially for InnoDB storage engine.

Real question is, what does it mean that something is "bad" at GIS "stuff"? What does bad mean? Can't calculate something? Can't store something? I just don't get what you consider bad really.

I doubt you can go wrong by choosing either of the two databases, just beware of false benchmarks claiming one product is faster than another. Set your goal in terms of performance, install both products on your test machine and run them. If both satisfy your performance needs, use the one you feel more comfortable developing with.

N.B.
  • 13,688
  • 3
  • 45
  • 55
  • 1
    "Bad at GIS stuff" means "does not support geospatial processing" or "has no spatial extensions". For instance geospatial queries like "select all points within this polygon". The OP is correct that until recently MySQL did not support this sort of query properly, so PostGreSQL/PostGIS was a leader among open source geospatial databases. The docs *say* that MySQL 5.6.1 and later now have exact geometry algorithms, so it's possible that MySQL is now a contender for geospatial work. – MarkJ Nov 30 '11 at 12:59
5

Check this topic: GIS: PostGIS/PostgreSQL vs. MySql vs. SQL Server?

PostGIS is much more mature and complete, and competes with Oracle and SQL Server, not MySQL. Sorry.

Community
  • 1
  • 1
Frank Heikens
  • 117,544
  • 24
  • 142
  • 135
  • I agree, if you really need sophisticated GIS support, MySQL simply doesn't have it. You can use something proprietary, you can use Sphinx Search for some simple things, or you can use PostGIS. Those are the practical options. –  Dec 02 '11 at 19:31
3

When it comes to GIS capabilities, have a look at this GIS SE question:

Would PostGIS offer an advantage over MySQL for a produce farm application?

I think that from all that I read here and on GIS SE site, PostgreSQL with PostGIS is a clear winner when it comes to handling spatial data.

Community
  • 1
  • 1
radek
  • 7,240
  • 8
  • 58
  • 83