Question

1 Follower
4
Avatar

Alternate uses for PostgreSQL

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 therake 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.

Evan Machnic

Please sign in to add your answer.