Knowledge Base/Community Forums/Best Practices

Alternate uses for PostgreSQL

Evan Machnic
posted this on May 02, 2012 05:10 PM

Alternate uses for PostgreSQL

 

As applications evolve and become larger, many times they require extra services to offload some of the responsibilities so the application will still perform well. Some of the responsibilities may be full text searching and job processing.


Using separate services for these is often the best choice depending on the scale of the application but if the needs are light, it may be easier to just use some of the built-in features of your PostgreSQL database. We’ll talk a bit about full-text search and job processing here and then open this up for further discussion about some more ways to utilize PostgreSQL.

 

Full text search


In full text searching, all of the text in the stored documents is examined to match a search criteria. One of the little-known features of PostgreSQL is the ability to easily add such capability.


Fully searching your database in a Rail application can be as quick and easy as adding a search on your model:

 

# app/models/article.rb
def self.full_text_search query
where “title @@ :q or body @@ :q”, q: query
end

 

We can then access this method in our application like so:

 

Article.full_text_search ‘ford prefect’


Using the `@@` symbols, tells PostgreSQL to perform a full text search for the title or body on the query parameters. This is a simple way of adding the full text searching but not the prettiest. We can, however, get the same thing using a gem called
`texticle`, written by Aaron Patterson. 
 
To use `texticle`, just add it to your Gemfile: 


gem ‘texticle’, ‘~> 2.0’, require: ‘texticle/rails’


 Then our models have access to the `search` method which defaults to searching your `:string` columns:
 

Article.search ‘dolphins’ # searches all :string columns for ‘dolphins’
Article.search title: ‘trillian’ # searches for articles with ‘trillian’ in the title

 

Aside from being easy to implement, using the database itself for full text searching has the advantage of working with other SQL queries. The `full_text_search` method is really just an ActiveRecord scope and can be chained with other scopes.

 

The other advantage over something like Sphinx is that everything stays in sync all of the time. If new records are added to the database, there’s no need to re-index before being able to search them.

 

Next, we’ll discuss using PostgreSQL as a queue to process background jobs.

 

Job processing

 
There are many times that applications will need to do something, like send an email, that should be done in the background so that the Rails application is not affected. Many people use Delayed Job for this but that has the disadvantage of  blocking the database and performance will still suffer. Ryan Smith wrote something called Queue Classic that uses features already in PostgreSQL, like listen/notify, to process jobs in a way that doesn’t block the database.

 

Queue Classic requires a little more setup but I think the advantages greatly outweigh this.

 

First, add the gem to the Gemfile of your application:
 

gem ‘queue_classic’, ‘~> 2.0.0rc14’


 Then create a Rakefile with the Queue Classic rake tasks:
 

# lib/tasks/queue_classic.rake
require ‘queue_classic’
require ‘queue_classic/tasks’


All of the configuration for Queue Classic is done through environment variables in the `queue_classic.rb` initializer file. On Engine Yard Cloud, the best thing to do would be to write this file using a custom Chef recipe. We need to at least make sure and set the `DATABASE_URL` variable (the username, password, database_host, and database_name can be found in the generated `database.yml` file):

 

ENV[‘DATABASE_URL’] = “postgres://username:password@database_host/database_name”

 

After this has been set correctly, we can set everything up using the`rake qc:create` or `rake qc:drop` rake tasks or through the following migration:

 

# db/migrations/add_queue_classic.rb

require ‘queue_classic’

class AddQueueClassic < ActiveRecord::Migration
def self.up
QC::Setup.create
end

def self.down
QC::Setup.drop
end
end

 
With everything wired up correctly, we can add stuff to our queue like so:

 

QC.enqueue ‘puts’, ‘So long and thanks for all the fish’
QC.enqueue ‘puts’, ‘We apologize for the inconvenience’


We can then process those jobs by using the `rake qc:work` rake task.

 

One way to make job processing more responsive is to use the listen/notify feature of PostgreSQL, which provides publish/subscribe notifications. Our worker can then listen on a channel and then lock the job when it receives a notification. This can be
turned on by setting the following shell variable.
 

$ export QC_LISTENING_WORKER=‘true’

 

Conclusion

 
As you can see, sometimes we can use our databases in very interesting ways that may not have originally been intended. I urge you to try out these ideas instead of adding another component to your architecture if you may not need the extra power.

 

Please post any experiences you may have had with alternate uses of PostgreSQL as well as something that may not be listed.