• Stars
    star
    258
  • Rank 158,189 (Top 4 %)
  • Language
    C
  • License
    Other
  • Created about 14 years ago
  • Updated over 1 year ago

Reviews

There are no reviews yet. Be the first to send feedback to the community and the maintainers!

Repository Details

Faster SELECTs when using Sequel with pg

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]>

More Repositories

1

sequel

Sequel: The Database Toolkit for Ruby
Ruby
4,818
star
2

rodauth

Ruby's Most Advanced Authentication Framework
Ruby
1,550
star
3

home_run

Fast Date/DateTime classes for ruby :: Unmaintained, unnecessary on ruby 1.9.3+
Ruby
466
star
4

erubi

Small ERB Implementation
Ruby
373
star
5

forme

HTML forms library for ruby
Ruby
291
star
6

ruby-warning

Add custom processing for warnings
Ruby
281
star
7

roda-sequel-stack

Application Skeleton For Roda/Sequel stack
Ruby
262
star
8

ruby-refrigerator

Freeze all core ruby classes
Ruby
198
star
9

ruby-american_date

American style month/day/year parsing for ruby 1.9+
Ruby
108
star
10

by

Ruby Library Preloader
Ruby
101
star
11

sequel_postgresql_triggers

Database enforced timestamps, immutable columns, and counter/sum caches
Ruby
97
star
12

minitest-hooks

Around and before_all/after_all/around_all hooks for Minitest
Ruby
89
star
13

rack-unreloader

Rack Application that reloads application files if changed, unloading constants first
Ruby
88
star
14

autoforme

Web Administrative Console for Roda/Sinatra/Rails and Sequel::Model
Ruby
62
star
15

scaffolding_extensions

Ruby Web Admin Front-End :: Unmaintained, use AutoForme instead
Ruby
57
star
16

zozo

Simple $LOAD_PATH management for ruby projects :: Unmaintained
Ruby
49
star
17

aqualung

Advanced music player
C
48
star
18

fixture_dependencies

Sequel/ActiveRecord fixture loader that handles dependency graphs
Ruby
45
star
19

third_base

A Fast and Easy Date/DateTime Class for Ruby :: Unmaintained, use home_run instead.
Ruby
39
star
20

giftsmas

Gift Tracking Website using Roda and Sequel
Ruby
36
star
21

simple_orm_benchmark

A small benchmark test for ruby ORMs.
Ruby
31
star
22

spam

Simple Personal Accounting Manager
Ruby
30
star
23

tilt

Generic interface to multiple Ruby template engines
Ruby
30
star
24

simple_ldap_authenticator

Simple authentication for Ruby using LDAP
Ruby
29
star
25

roda-route_list

List routes when using Roda
Ruby
27
star
26

ruby-pledge

Ruby Interface to OpenBSD pledge(2) system call
Ruby
26
star
27

kaeruera

Simple Error Tracker for Ruby
Ruby
24
star
28

roda-rails

Integration for using Roda as Rack middleware in a Rails app
Ruby
21
star
29

thamble

Create HTML Tables from Enumerables
Ruby
18
star
30

exception_notification

Gemified exception_notification rails plugin, compatible with Rails 2.3.5 with the RailsXss plugin :: Unmaintained
Ruby
17
star
31

roda-message_bus

MessageBus integration for Roda
Ruby
17
star
32

evilr

Do things you shouldn't ::Unmaintained
Ruby
13
star
33

rack-indifferent

Fast indifferent access to request params
Ruby
13
star
34

ruby-subset_sum

Simple Subset Sum Solver with C and Pure Ruby Versions
Ruby
12
star
35

ruby-style

Supervised TCPServer, Yielding Listeners Easily :: Unmaintained, switch to Unicorn or Rainbows!
Ruby
12
star
36

ape_tag_libs

Libaries for reading/writing APEv2 tags in many languages
C
11
star
37

gcit2ghi

Imports issues from Google Code Issue Tracker to GitHub Issues :: Unmaintained
Ruby
11
star
38

rack-deadline

Automatically clears sessions open too long
Ruby
11
star
39

minitest-parallel_fork

Fork-based parallelization for minitest
Ruby
11
star
40

ruby-deprecate_public

Warn when calling private methods via public interface
Ruby
11
star
41

cspvr

Content-Security-Policy Violation Recorder
Ruby
10
star
42

unicorn-lockdown

Helper library for running Unicorn on OpenBSD with chroot, privdrop, fork+exec, and pledge
Ruby
9
star
43

visibility_checker

Detect method visibility changes
Ruby
9
star
44

minitest-shared_description

Support for shared specs and shared spec subclasses for Minitest
Ruby
8
star
45

jpm

Password manager using openssl/signify
Ruby
8
star
46

sequel-mongo

Proof of Concept MongoDB Driver for Sequel (Do not use in production)
Ruby
7
star
47

quinto

Quinto server and client
Ruby
7
star
48

capybara-validate_html5

Validate HTML5 for each page accessed when testing with capybara
Ruby
7
star
49

minitest-global_expectations

Support minitest expectation methods for all objects
Ruby
6
star
50

simple_mailer

Simple email library with testing support
Ruby
6
star
51

roda-opal-example

Example of Using Roda with Opal
Ruby
6
star
52

rodauth-demo-rails

Rodauth's demo site ported to Rails, showing Rodauth/Rails integration
Ruby
5
star
53

enum_csv

Create CSV from Enumerables
Ruby
5
star
54

tilt-pipeline

Easily construct rendering pipelines using tilt
Ruby
5
star
55

lila_shell

Simple chat app using roda-message_bus
JavaScript
5
star
56

ruby-scgi

Simple support for using SCGI in ruby apps, such as Rails :: Unmaintained
Ruby
5
star
57

ruby-string-crypt

Backward compatible implementation of String#crypt
C
4
star
58

gg2ghd

Import Extract from Google Groups into GitHub Discussions
Ruby
4
star
59

tagged-ruby-bugs

Tagged Open Ruby Bugs
Ruby
3
star
60

openbsd-ruby-ports

Ports for Ruby Versions Removed from OpenBSD ports tree
Makefile
3
star
61

ruby-vorbis_comment

Ruby library for reading/writing vorbis comments
C
3
star
62

erubis

Git repository for http://www.kuwata-lab.com/erubis/
HTML
3
star
63

hs-SubsetSum

Subset sum problem solver for haskell
Haskell
3
star
64

tilt-rails_erb

Adds support for Rails' ERB templates to Tilt
Ruby
2
star
65

slab

Tutorial for building an OCR web application
Ruby
2
star
66

jeremyevans.github.com

Personal Website
HTML
2
star
67

sacruby

Various code related to the Sacramento Ruby Meetup
Ruby
2
star
68

sequel_validation_helpers_block

Allows easy determination of which validation rules apply to a given column, at the expense of increased verbosity
Ruby
2
star
69

sequel-impala

Support for Sequel to access the Impala database
1
star
70

mp3applygain

Apply gain information to mp3 audio data
C
1
star
71

openbsd-postgresql-ports

Ports for old PostgreSQL versions removed from the OpenBSD ports tree
Shell
1
star
72

faster_html_escape

Archive of old faster_html_escape gem
C
1
star
73

sequel-unsplit

Ruby code rewriter that replaces symbols containing embedded qualification/aliasing with equivalent Sequel code
Ruby
1
star
74

aqualung-scrobbler

last.fm scrobbler for Aqualung
Shell
1
star
75

rubyconflt2016-presentation

Presentation Source Code for my RubyConfLT 2016 presentation on Rodauth (runs on my showoff fork)
CSS
1
star
76

tilt-indirect

Adds indirection for tilt templates
Ruby
1
star
77

jeremyevans_github_hook_processor

Processor for GitHub webhooks for my repositories
Ruby
1
star
78

falcomcdcatalog

Falcom CD Catalog - English Edition
HTML
1
star
79

serverside

Ruby web framework from which Sequel was extracted
Ruby
1
star
80

tame_libs

programming language wrappers for OpenBSD's tame(2) system call
Ruby
1
star
81

capybara-restore_state

Restore capybara state after block execution
Ruby
1
star
82

openbsd-mariadb-ports

Ports for newer versions of MariaDB for OpenBSD
Makefile
1
star