sequel_pg

sequel_pg overwrites the inner loop of the Sequel postgres adapter row fetching code with a C version. The C version is significantly faster than the pure ruby version that Sequel uses by default.

Real world difference

The speed up that sequel_pg gives you depends on what you are selecting, but it should be noticeable whenever many rows are selected. Here’s an example that shows the difference it makes on a couple of models:

Track.count # => 202261
Album.count # => 7264

Without sequel_pg:

puts Benchmark.measure{Track.each{}}
# 3.400000   0.290000   3.690000 (  4.005150)
puts Benchmark.measure{10.times{Album.each{}}}
# 2.180000   0.120000   2.300000 (  2.479352)

With sequel_pg:

puts Benchmark.measure{Track.each{}}
# 1.660000   0.260000   1.920000 (  2.287216)
puts Benchmark.measure{10.times{Album.each{}}}
# 0.960000   0.110000   1.070000 (  1.260913)

sequel_pg also speeds up the following Dataset methods:

  • map

  • as_hash/to_hash

  • to_hash_groups

  • select_hash

  • select_hash_groups

  • select_map

  • select_order_map

Additionally, in most cases sequel_pg also speeds up the loading of model datasets by optimizing model instance creation.

Streaming

If you are using PostgreSQL 9.2+ on the client, then sequel_pg should enable streaming support. This allows you to stream returned rows one at a time, instead of collecting the entire result set in memory (which is how PostgreSQL works by default). You can check if streaming is supported by:

Sequel::Postgres.supports_streaming?

If streaming is supported, you can load the streaming support into the database:

DB.extension(:pg_streaming)

Then you can call the Dataset#stream method to have the dataset use the streaming support:

DB[:table].stream.each{|row| ...}

If you want to enable streaming for all of a database’s datasets, you can do the following:

DB.stream_all_queries = true

Installing the gem

gem install sequel_pg

Make sure the pg_config binary is in your PATH so the installation can find the PostgreSQL shared library and header files. Alternatively, you can use the POSTGRES_LIB and POSTGRES_INCLUDE environment variables to specify the shared library and header directories.

Running the specs

sequel_pg is designed to replace a part of Sequel, so it shold be tested using Sequel’s specs (the spec_postgres rake task). There is a spec_cov task that assumes you have Sequel checked out at ../sequel, and uses a small spec suite for parts of sequel_pg not covered by Sequel’s specs. It sets the SEQUEL_PG_STREAM environment variable when running Sequel’s specs, make sure that spec/spec_config.rb in Sequel is set to connect to PostgreSQL and use the following additional settings:

DB.extension(:pg_streaming)
DB.stream_all_queries = true

Reporting issues/bugs

sequel_pg uses GitHub Issues for tracking issues/bugs:

http://github.com/jeremyevans/sequel_pg/issues

Contributing

The source code is on GitHub:

http://github.com/jeremyevans/sequel_pg

To get a copy:

git clone git://github.com/jeremyevans/sequel_pg.git

There are only a few requirements, which you should probably have before considering use of the library:

  • Rake

  • Sequel

  • pg

  • libpq headers and library

Building

To build the library from a git checkout, after installing the requirements:

rake build

Known Issues

  • You must be using the ISO PostgreSQL date format (which is the default). Using the SQL, POSTGRESQL, or GERMAN date formats will result in incorrect date/timestamp handling. In addition to PostgreSQL defaulting to ISO, Sequel also manually sets the date format to ISO by default, so unless you are overriding that setting (via DB.use_iso_date_format = false), you should be OK.

  • Adding your own type conversion procs only has an effect if those types are not handled by default.

  • You do not need to require the library, the sequel postgres adapter will require it automatically. If you are using bundler, you should add it to your Gemfile like so:

    gem 'sequel_pg', :require=>'sequel'

  • sequel_pg currently calls functions defined in the pg gem, which does not work on Windows and does not work in some unix-like operating systems that disallow undefined functions in shared libraries. If RbConfig::CONFIG['LDFLAGS'] contains -Wl,--no-undefined, you’ll probably have issues installing sequel_pg. You should probably fix RbConfig::CONFIG['LDFLAGS'] in that case.

Author

Jeremy Evans <[email protected]>