4 ms·

101 Reasons Why PostgreSQL is a better fit for Rails than MySQL

24 points by qhoxie 18y ago

silentbicycle 18y ago
0b101, that is, five. groan
patrickg-zill 18y ago
I don't quite understand #5. Wouldn't you instead have one larger query with a sub-query contained along with it under Postgresql, like a correlated sub-query or the like?
damien 18y ago
Technology-wise, PostgreSQL is superior to MySQL. The main reservation most people seem to have about it is due to its lack of a good, easy to use replication solution. But I hear that they are actively working to resolve that now.
- Andys 18y ago
  PostgreSQL community has created several replication solutions that are less-hackish than MySQL's. Its more the lack of an officially blessed one that comes pre-installed. I'm using one right now that proxies your queries to multiple servers at once, effectively giving you genuine multi-master replication.
  - damien 18y ago
    Which one is that? pgpool?
ajross 18y ago
Is #1 actually correct? The plain interpretation would be that if you join two tables on one field, it can only read the index from one table at a time and needs to do a linear search through the second table for each row. Clearly that's ridiculous, and not MySQL's behavior at all. Clearly this is a more subtle problem. The rest of the text implies this is a scaling issue ("wait until your tables get large enough"), but I don't see why that's the case. Can someone enlighten me?
- ambition 18y ago
  I'm not sure, but perhaps he means one index per table per query.
comatose_kid 18y ago
I'm trying PostgreSQL for the first time, with Merb and Rack, to implement an API. The reasons I switched: 1) Built in full text search (tsearch2). No messing with Sphinx or Ferret. 2) Write ahead logging is pretty useful if you're running on EC2 and don't want your DB to go away when an instance goes down. 3) Basic GIS functionality available via module.
gtani 18y ago
#2: Sorry i consider this moot: - the docs for tsearch are dense, not terribly friendly, and acts_as_tsearch appears to be under documented and possibly not maintained/updated. So if tsearch has any deal winners over sphinx and solr (for me, key ones would be custom tokenizers/stemmers, UTF-8, column weights, indexing short words and acronyms, faceted search, indexing instance methods and has_many child records along with what's in parent record) they're not readily apparent. Granted i only tried to understand tsearch for ~20 minutes) and a_a_tsearch did well in benchmarks, see comments: (teh tsearch count for "god" in index by book does indicate a possibly serious problem) http://blog.evanweaver.com/articles/2008/03/17/rails-search-benchmarks/ http://blog.evanweaver.com/articles/2008/03/17/rails-search-... - The four leading search plugins: acts_as_[ferret, solr],ultrasphinx , thinkng sphinx work equally well with MySQL, and postgres, as far as i know. They're all pretty painless installs, if not terribly fast to build indexes, in the case of ferret and solr
- Maascamp 18y ago
  tsearch is not complicated for anyone who knows enough about a database to install tsearch. It has pretty straight forward documentation and I was able to get my install up and running with differently weighted columns (and stemming) in the ~20 minutes you took to briefly scan the literature.

24 points by qhoxie 18y ago

0b101, that is, five. groan

I don't quite understand #5. Wouldn't you instead have one larger query with a sub-query contained along with it under Postgresql, like a correlated sub-query or the like?

damien 18y ago

Technology-wise, PostgreSQL is superior to MySQL. The main reservation most people seem to have about it is due to its lack of a good, easy to use replication solution. But I hear that they are actively working to resolve that now.

Andys 18y ago
PostgreSQL community has created several replication solutions that are less-hackish than MySQL's. Its more the lack of an officially blessed one that comes pre-installed. I'm using one right now that proxies your queries to multiple servers at once, effectively giving you genuine multi-master replication.
- damien 18y ago
  Which one is that? pgpool?

ajross 18y ago

Is #1 actually correct? The plain interpretation would be that if you join two tables on one field, it can only read the index from one table at a time and needs to do a linear search through the second table for each row. Clearly that's ridiculous, and not MySQL's behavior at all. Clearly this is a more subtle problem. The rest of the text implies this is a scaling issue ("wait until your tables get large enough"), but I don't see why that's the case. Can someone enlighten me?

ambition 18y ago
I'm not sure, but perhaps he means one index per table per query.

comatose_kid 18y ago

I'm trying PostgreSQL for the first time, with Merb and Rack, to implement an API. The reasons I switched: 1) Built in full text search (tsearch2). No messing with Sphinx or Ferret. 2) Write ahead logging is pretty useful if you're running on EC2 and don't want your DB to go away when an instance goes down. 3) Basic GIS functionality available via module.

gtani 18y ago

#2: Sorry i consider this moot: - the docs for tsearch are dense, not terribly friendly, and acts_as_tsearch appears to be under documented and possibly not maintained/updated. So if tsearch has any deal winners over sphinx and solr (for me, key ones would be custom tokenizers/stemmers, UTF-8, column weights, indexing short words and acronyms, faceted search, indexing instance methods and has_many child records along with what's in parent record) they're not readily apparent. Granted i only tried to understand tsearch for ~20 minutes) and a_a_tsearch did well in benchmarks, see comments: (teh tsearch count for "god" in index by book does indicate a possibly serious problem) http://blog.evanweaver.com/articles/2008/03/17/rails-search-benchmarks/ http://blog.evanweaver.com/articles/2008/03/17/rails-search-... - The four leading search plugins: acts_as_[ferret, solr],ultrasphinx , thinkng sphinx work equally well with MySQL, and postgres, as far as i know. They're all pretty painless installs, if not terribly fast to build indexes, in the case of ferret and solr

Maascamp 18y ago
tsearch is not complicated for anyone who knows enough about a database to install tsearch. It has pretty straight forward documentation and I was able to get my install up and running with differently weighted columns (and stemming) in the ~20 minutes you took to briefly scan the literature.