michaelsilver.us Software, programming and whatever.

February 21, 2007

No Easy Database Choices

Filed under: Database,Programming,Ruby,Ruby on Rails,Web Development — Michael Silver @ 12:49 am

As I try to get NutritionFacts up and running on the web with Ruby on Rails I need to select a database to use. Obviously, in the open source realm, MySQL is the forerunner or at least the first one to look at since it’s use is so widespread.

I have a few criteria that any database must meet:

  1. Transactions
  2. Full-Text Search
  3. Cross-platform (my server runs FreeBSD, I run WindowsXP)
  4. Have bindings for Rails
  5. Open source
  6. Server Process

In taking a closer look, MySQL is not perfect, in fact it’s missing some critical features. If you use the MyISAM storage (MySQL offers numerous methods of storing data, each offering different features), you get full text searching, but no transactions. I have the need for transactions since the data users may enter span numerous tables. If you use the InnoDB storage engine you get transactions (and row locking instead of table locking), but no full text search.

Well perhaps another database would have both transactions and full text searching. I moved my search to SQLite which does have both, but it doesn’t have a server process to manage access, which could mean problems on a busy site. Not good.

Next in line is PostgreSQL. Very impressive feature set but full test search is through an addon called TSearch2.

The standard instructions for TSearch2 are rather complex, but thanks to the Google and the Internets (aka the series of tubes), there are some good guides for setting it up. It still seems like a solution prone to break though, since it requires a trigger and an additional field in the tables to be indexed. I also don’t know about the quality of the tools to access and manage PostgreSQL, although phppgadmin looks decent enough.

Just to rule out an other potential canidates I also looked at Firebird which has transactions
and is cross-platform and has rails bindings. Unfortunately, no full text search.

Next in line: Ingres. I took a quick look and frankly I don’t have the energy to explore it. It doesn’t seem to have as much support in the open source community and I couldn’t find any documentation on full text searches, so I presume it doesn’t offer it.

I am fresh out of databases to explore. Until I can choose a database (and I am leaning towards PostgreSQL) I am going to useFerret and the acts_as_ferret plugin for Rails to preform the full text searches. (Tutorial) While Ferret is not a bad solution, it does appear to be slightly slower than the MySQL full text searching and the potential exists for the index to become out of sync with the database since it is not stored (or even related) to the database. Thankfully, with Rails I can use this solution for now and when I find a better one, switching will be trivial.

Summary:

Database Full Text Search Transactions Server Process
MySQL (MyISAM) Yes No Yes
MySQL (InnoDB) No Yes Yes
SQLite Yes Yes No
PostgreSQL Plugin Yes Yes
Firebird No Yes Yes
Ingres No Yes Yes

Powered by WordPress