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.
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.
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
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.
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
sequel_pg uses GitHub Issues for tracking issues/bugs:
http://github.com/jeremyevans/sequel_pg/issues
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
To build the library from a git checkout, after installing the requirements:
rake build
-
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 fixRbConfig::CONFIG['LDFLAGS']
in that case.
Jeremy Evans <code@jeremyevans.net>