sequel_pg

By overwriting the inner loop of the Sequel postgres adapter with a C version, sequel_pg significantly improves performance for row fetching and several Dataset methods, including:

• map
• as_hash/to_hash
• to_hash_groups
• select_hash
• select_hash_groups
• select_map
• select_order_map

It also optimizes model instance creation when loading model datasets.

Install the gem using the standard gem command. To ensure the installation succeeds, the pg_config binary must be in your PATH so the installer can locate the PostgreSQL shared library and header files.

Alternatively, you can specify the locations manually using the POSTGRES_LIB and POSTGRES_INCLUDE environment variables.

gem install sequel_pg

Because the sequel postgres adapter automatically requires sequel_pg if it is available, you should use the require: 'sequel' option in your Gemfile to prevent redundant or incorrect loading.

gem 'sequel_pg', require: 'sequel'

If you are using PostgreSQL 9.2+ on the client, sequel_pg enables streaming support. This allows you to process rows one at a time instead of loading the entire result set into memory.

1. Check if streaming is supported: Sequel::Postgres.supports_streaming?

2. Load the streaming extension into your database connection: DB.extension(:pg_streaming)

3. Use the Dataset#stream method to iterate over rows: DB[:table].stream.each { |row| ... }

To enable streaming for all datasets on a database connection, set DB.stream_all_queries = true.

# Check support
Sequel::Postgres.supports_streaming?

# Enable for a specific database connection
DB.extension(:pg_streaming)
DB[:table].stream.each { |row| puts row }

# Enable for all queries on this connection
DB.stream_all_queries = true

Not all queries can be streamed. The :pg_streaming extension determines if a query is streamable? based on the following rules:

• Streamable: Most standard queries.
• Not Streamable:
  • Queries using cursors (:cursor option is present).
  • Queries using specific optimizations like map, select_map, to_hash, or to_hash_groups (detected via _sequel_pg_type).

Streaming is only triggered if stream_results? is true, which requires either the :stream option to be explicitly set on the dataset or DB.stream_all_queries to be true.

To fetch rows one at a time from PostgreSQL using sequel_pg, load the :pg_streaming extension into your database object.

Note: This extension modifies connections. You must call DB.disconnect after loading the extension so that new connections are initialized with the necessary streaming methods.

Requirements:

• sequel_pg must be loaded.
• The version of libpq in use must support streaming.

DB.extension(:pg_streaming)
# Required to ensure new connections get the streaming methods
DB.disconnect

You must use the ISO PostgreSQL date format. Using SQL, POSTGRESQL, or GERMAN date formats will result in incorrect date/timestamp handling.

Sequel and PostgreSQL both default to ISO, but if you have explicitly disabled it using DB.use_iso_date_format = false, you may encounter issues.

Using a precompiled pg gem can cause issues because it might statically link a libpq version that differs from the system libpq dynamically linked to sequel_pg.

To resolve this, force the pg gem to use the Ruby platform.

# In your Gemfile
gem 'pg', force_ruby_platform: true

# Or via manual install
gem install pg --platform ruby

You can set the stream_all_queries attribute on your database object to true. When this is enabled, all dataset calls will use streaming support automatically, provided the query is streamable.

DB.stream_all_queries = true

Optimized model loading is enabled by default in sequel_pg. While Sequel::Postgres::Database#optimize_model_load is deprecated and always returns true, you can control optimization on a per-dataset basis using Sequel::Postgres::Dataset#with_optimize_model_load.

Note that the mutation method Dataset#optimize_model_load= is deprecated. Use with_optimize_model_load to return a modified copy of the dataset with the setting changed.

# Instead of mutating the dataset:
# dataset.optimize_model_load = false (DEPRECATED)

# Use with_optimize_model_load to get a new dataset instance:
dataset = dataset.with_optimize_model_load(false)