20

I'm working on a website that could eventually have a very large database. The website will be an art community not until deviantART. I'll be running a lot of queries for analyzing how the site is used. I expect some of the queries to kill the database because I don't know how to optimize SQL queries (yet). I'm starting out with a small linux VPS and would move to physical servers in the future.

Which database would scale the best given this situation?

epochwolf
  • 639
  • 3
  • 11
  • 16

4 Answers4

16

Before you make a decision based on scalability, you might want to read through these two pages and decide how much you value your sanity:

John Siracusa
  • 738
  • 7
  • 12
  • 12
    I'm allowed to have sanity? – epochwolf May 01 '09 at 04:17
  • 1
    Excellent post. Don't forget, the larger your database gets, the less you will use it's relation features. If you look at the large social web sites you'll see their use of a relational database is limited to pure key / value storage. – Dave Cheney May 03 '09 at 04:57
  • 1
    Those links are VERY outdated. – Trey May 04 '09 at 04:06
  • 1
    At least in the PostgreSQL document, many items include "this is true for versions N.M and earlier of Product X" qualifiers. Where those are missing, a quick experiment with your installed version followed by an email to the document's author could remedy the situation... – John Siracusa May 04 '09 at 13:57
6

There are some pretty decent threads on this at stack overflow:
https://stackoverflow.com/questions/110927/do-you-recommend-postgresql-over-mysql
https://stackoverflow.com/questions/216601/postgressql-mysql-oracle-diferences
https://stackoverflow.com/search?q=postgres+mysql

That gotchas site is also great :)

Community
  • 1
Jeremy Huiskamp
  • 332
  • 3
  • 7
6

First let me prefix my answer with the fact that I am heavily biased towards PostgreSQL. I use both PostgreSQL and MySQL very heavily on fairly large site. I, from time to time, have problems with MySQL falling over performance wise. When this happens, I usually restart it and things go back to normal. It's also worth noting that we run PostgreSQL on everything from 8 to 32 core boxes and our MySQL cluster runs on a set of 8 core boxes. Monty, when he was at Sun, stated that MySQL does not scale on a single box beyond 4 cores.

Gavin M. Roy
  • 331
  • 3
  • 5
4

Really it depends on what kind of queries you will be running. Unless you are running very complex queries either will be fine for a very large web site. There are good examples of both databases running very large scale web sites

trent
  • 3,094
  • 18
  • 17
  • 1
    Very likely true. But besides performance there is also manageability and general ease of use to consider. My personal opinion is that postgres wins hands down in that department. I just can't figure out why everybody else seems to love mysql :p – Jeremy Huiskamp May 04 '09 at 04:40