# Postgresql vs mysql? **URL:** \ **Category:** Rails\ **Created:** [January 22, 2007, 3:31pm UTC](https://www.ruby-forum.com/t/postgresql-vs-mysql/82935 "2007-01-22T15:31:20Z")\ **Posts on this page:** 7\ **Page:** 1
**Author:** ![nick](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/n/f9ae1b/32.png) [@nick](https://www.ruby-forum.com/u/nick)\ **Post date:** [January 22, 2007, 3:31pm UTC](https://www.ruby-forum.com/t/postgresql-vs-mysql/82935/1 "2007-01-22T15:31:20Z")
hi, which is better between postgresql and mysql in a system with a lot of queries and where performance is very important ? is possible for both to have a database cluster? is there anyone who has one ? ---
**Author:** ![nick](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/n/f9ae1b/32.png) [@nick](https://www.ruby-forum.com/u/nick)\ **Post date:** [January 22, 2007, 3:37pm UTC](https://www.ruby-forum.com/t/postgresql-vs-mysql/82935/2 "2007-01-22T15:37:10Z")
nick wrote: > hi, which is better between postgresql and mysql in a system with a lot > of queries and where performance is very important ? is possible for > both to have a database cluster? is there anyone who has one ? Rails tries to stay fairly agnostic about this. I’m personally a _huge_ PostgreSQL fan. One major difference is the open source license. PostgreSQL has a BSD license, which is very similar to that of Rails, while MySQL has a dual-license scheme that has some limitations for how you sell your product. As far as features, PostgreSQL wins because of how far you can go with it… from built-in GIS (PostGIS) to creating stored procedures, triggers, etc… with Ruby… I could go on and on. PostgreSQL++ Cheers, Robby – Robby R. [http://www.robbyonrails.com/](http://www.robbyonrails.com/) > **[Planet Argon](https://www.planetargon.com/)** > > Planet Argon is a Ruby on Rails development agency specializing in maintenance and support for existing Rails apps. Based in Portland, OR, Planet Argon has been a member of the Rails community since 2004. ---
**Author:** ![nick](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/n/f9ae1b/32.png) [@nick](https://www.ruby-forum.com/u/nick)\ **Post date:** [January 22, 2007, 3:41pm UTC](https://www.ruby-forum.com/t/postgresql-vs-mysql/82935/3 "2007-01-22T15:41:12Z")
Robby R. wrote: > PostgreSQL++ You might do a few google searches on postgresql versus mysql. There have been several articles conducted on this. Exhibit A: [http://www.suite101.com/article.cfm/oracle/115560](http://www.suite101.com/article.cfm/oracle/115560) Good luck! Robby – Robby R. [http://www.robbyonrails.com/](http://www.robbyonrails.com/) > **[Planet Argon](https://www.planetargon.com/)** > > Planet Argon is a Ruby on Rails development agency specializing in maintenance and support for existing Rails apps. Based in Portland, OR, Planet Argon has been a member of the Rails community since 2004. ---
**Author:** ![nick](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/n/f9ae1b/32.png) [@nick](https://www.ruby-forum.com/u/nick)\ **Post date:** [January 22, 2007, 10:11pm UTC](https://www.ruby-forum.com/t/postgresql-vs-mysql/82935/4 "2007-01-22T22:11:45Z")
> MySQL Cluster is a very weird beast. It’s honestly not really MySQL, but seconded 🙂 > just uses the SQL parsing engine to connect into a radically different > back end. I read the MySQL Press book on MySQL cluster and was amused > when a “disadvantage” of MySQL Cluster was (paraphrased): You cannot > alter the schema while the database is online with statements such as > ALTER TABLE ADD COLUMN, and ALTER TABLE CREATE INDEX. Who in the world > has an application stable enough to accept that limitation? 🙂 It’s a bit worse than this though… you can’t add new tables either. Which gets annoying if you add a new feature to your site and that feature needs a new table… you have to put it into “single node” mode. We probably do this once a month… Now, one thing that the docs don’t (or didn’t) make very clear is that “online” and “offline” isn’t what you normally think of in this case. “single node” is really just that. All the nodes but one go offline, so you can still serve up content – as long as that single node can handle the load. There are other issues to consider when looking at mysql cluster though… at least with 5.0 it’s all ram based so if you have a huge dataset, keep that in mind. There is also a 32,000 “affected rows” limitation. You can’t update/delete/insert more than 32,000 rows at a time. I’ve found many times it’s actually much less (more like 10,000). Which isn’t a problem until you try to do a dump/restore and your dump uses extended inserts. The other big one is that there are no varchars in mysql cluster. Everything is padded out. And there’s a row length limit, but I don’t recall what it is. Also you can’t do “SELECT … FROM … WHERE a LIKE … OR b LIKE” (ie. OR’d LIKEs). And some other odd quirks. I’m just listing these out cause if you’re gonna decide now, better to know all the various gotchas before you get too far down that road 😕 ---
**Author:** ![nick](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/n/f9ae1b/32.png) [@nick](https://www.ruby-forum.com/u/nick)\ **Post date:** [January 22, 2007, 10:26pm UTC](https://www.ruby-forum.com/t/postgresql-vs-mysql/82935/5 "2007-01-22T22:26:06Z")
Philip H. wrote: > There are other issues to consider when looking at mysql cluster > though… > at least with 5.0 it’s all ram based so if you have a huge dataset, keep > that in mind. There is also a 32,000 “affected rows” limitation. You > can’t update/delete/insert more than 32,000 rows at a time. I’ve found > many times it’s actually much less (more like 10,000). Which isn’t a > problem until you try to do a dump/restore and your dump uses extended > inserts. The other big one is that there are no varchars in mysql > cluster. Everything is padded out. And there’s a row length limit, but > I > don’t recall what it is. Also you can’t do “SELECT … FROM … WHERE a > LIKE … OR b LIKE” (ie. OR’d LIKEs). And some other odd quirks. > > I’m just listing these out cause if you’re gonna decide now, better to > know all the various gotchas before you get too far down that road 😕 and about a postgresql cluster what do you think? ---
**Author:** ![nick](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/n/f9ae1b/32.png) [@nick](https://www.ruby-forum.com/u/nick)\ **Post date:** [January 22, 2007, 10:51pm UTC](https://www.ruby-forum.com/t/postgresql-vs-mysql/82935/6 "2007-01-22T22:51:53Z")
> > I > > don’t recall what it is. Also you can’t do “SELECT … FROM … WHERE a > > LIKE … OR b LIKE” (ie. OR’d LIKEs). And some other odd quirks. > > > > I’m just listing these out cause if you’re gonna decide now, better to > > know all the various gotchas before you get too far down that road 😕 > > and about a postgresql cluster what do you think? Nothing… I’ve never had the chance to use it. And it’s been a long time since I looked at slony which is the only free implementation I believe (I could be totally wrong). There’s at least one company that has a commercial version… Also, look into pgpool. It pretends it’s a postgresql database and can send selects to a pool of backend servers and send all inserts/updates/deletes to a single backend server. Seems like that would come in very handy with rails… but again, I haven’t used that either 🙂 ---
**Author:** ![nick](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/n/f9ae1b/32.png) [@nick](https://www.ruby-forum.com/u/nick)\ **Post date:** [January 22, 2007, 9:59pm UTC](https://www.ruby-forum.com/t/postgresql-vs-mysql/82935/7 "2007-01-22T21:59:43Z")
On Jan 22, 2007, at 6:31 AM, nick wrote: > hi, which is better between postgresql and mysql in a system with a > lot > of queries and where performance is very important ? is possible for > both to have a database cluster? is there anyone who has one? Hello Nick. I’m also a huge PostgreSQL fan, but have found that MySQL has a gigantic advantage in replication functionality -vs- PostgreSQL. The only built-in support for this is is log-shipping, which doesn’t allow the replica to be read from, which is very unfortunate. We make backups via replicas, for instance, so that primary database performance is not impacted while the backups are proceeding. MySQL Cluster is a very weird beast. It’s honestly not really MySQL, but just uses the SQL parsing engine to connect into a radically different back end. I read the MySQL Press book on MySQL cluster and was amused when a “disadvantage” of MySQL Cluster was (paraphrased): You cannot alter the schema while the database is online with statements such as ALTER TABLE ADD COLUMN, and ALTER TABLE CREATE INDEX. Who in the world has an application stable enough to accept that limitation? 🙂 In the end, I’d _highly_ recommend that you table performance for the time being. Write the application as quickly and efficiently as possible. Spend time on tests and careful factoring rather than performance concerns. You’ll have time and money when performance becomes an issue, and with tests and well factored code, you’ll have the foundation required to make whatever changes are required to achieve the performance you desire. – – Tom M., CTO – Engine Y., Ruby on Rails Hosting – Reliability, Ease of Use, Scalability – (866) 518-YARD (9273)