Tony Pitale

PostgreSQL Part 1

Most of my days are filled developing with Ruby in frameworks (mostly Rails). I use a large array of industry standard tools in doing so and my favorite of all, and the one most often overlooked, is the database. Of the databases I've worked with, researched, or been witness to, PostgreSQL is my database of choice. In a series of posts I'd like to cover the reasons I have for this love of mine, some cool things you can do with it, and associated links that I think you might find useful. This first part will cover some potential benefits one might find in using PostgreSQL in web application development, of all places.

Benefits with (insert Ruby Framework here)

Rails with ActiveRecord or Merb with DataMapper are all (with limited exception) designed to be database agnostic. Furthermore, they are intended to abstract away much of the tedium normally associated with dealing in any database. As a result, there is very little direct performance or feature- related benefit to be found by simply using PostgreSQL over say, MySQL.

However, there exist a number of plugins and gems that enhance the features normally provided by our framework of choice. A few, in no particular order: sexy_pg_constraints, acts_as_tsearch, tsearchable, rails_sql_views, and simplified_fk.

Maybe then, the benefits are performance related. Unfortunately, neither I, nor anyone else (so far), have reliable benchmarks on this. From everything I've seen, statistics are done by fans or gurus of one side or another and rarely does someone exist who is talented with all the databases being compared. Even if some such person existed, where does one set the baseline? All of the databases that anyone would want to use have performance tweaks that can be performed at a varied level of difficulty. So, who knows? In my opinion, whether a database is fast enough or faster than a competitor is entirely dependent on the situation and the needs of the problem to be solved.

Features

  • Triggers (on both statement and row changes)
  • Rules
  • Constraints
  • Partitioning
  • Extremely advanced selects, Views
  • Full procedural languages including: ruby, tcl, perl, python, sql
  • Security (basic, ident, kerberos, pam, ldap, and more)
  • Large Copy
  • Built-in Full Text Search
  • Extensive datatype collection, custom types

If you don't need any of these features there are still many reasons to consider using PostgreSQL. Some examples: fantastic documentation for every production version, broad user community, stability, and in certain cases, performance. There's always the little things: bash completion, command line tools for creation/deletion of users and databases, libraries for GIS, every version is completely open source, performance tuning in PostgreSQL is unbelievably flexible for the situation and data in use.

Now that I've gotten all that off of my chest, I hope you are at least intrigued. I know I have yet to share anything of real value but, fear not, as I will be back soon with real examples of usage and some other more useful information.

Read More Recent Posts