postgresql_cursor: streaming large ActiveRecord result sets

A cursor for the result set ActiveRecord will not give you

postgresql_cursor extends ActiveRecord to allow for efficient processing of queries returning a large number of rows, and the readme also notes that it allows you to sort your result set, which is the part that matters when a large table needs an order by to be streamed sensibly.

The mechanism is PostgreSQL's own cursor. The readme describes it plainly: a cursor runs a query, from which you fetch a block of, say, one thousand rows, process them, and continue fetching until the result set is exhausted. By fetching a smaller chunk of data, this reduces the amount of memory your application uses and prevents the potential crash of running out of memory.

The readme frames this as overcoming all the disadvantages of using find_each and find_in_batches, which are ActiveRecord's own answer to the same problem, and the difference is where the result set lives. With find_each, ActiveRecord keeps issuing queries with a keyset condition. With a cursor, the database holds the position of the whole result set and the application walks it, which is a different set of trade-offs on the server side and a different one on the client side.

The extension adds two shapes of iteration. The row methods give you the raw data and the instance methods give you the models, and the readme is clear that the two are not equally fast. An informal benchmark showed that returning instances is a factor of four times slower than returning hashes, and if you can work with the data in the hash form you will find better performance.

Product.where("id>0").order("name").each_row { |hash| Product.process(hash) }

Product.where("id>0").each_instance { |product| product.process! }

The project is MIT licensed, written in Ruby, and the last push to the repository was on 2026-07-15. There are no GitHub releases, so the gem on RubyGems is the artefact.

Hashes are fast, instances are typed, and the choice is yours

The section on hashes against instances is the one to read carefully, because the two methods are not interchangeable in your code.

The each_row method returns the hash of strings, for speed, since this allows you to process a lot of rows. The values are strings, and the readme warns that you must take care of any type conversion yourself. A date field arrives as a string, an integer arrives as a string, and a comparison against a number in your code may behave differently from what you expect.

The each_instance method gives you the ActiveRecord model, and the casting is lazy. ActiveRecord casts those strings into Ruby types, a Time or an integer for example, only when you read the attribute. So a model you never touch stays a string internally, and the cost of casting is paid only for the attributes you actually use.

The readme's recommendation is to use each_instance if you need the types cast for your attributes, and it notes that ActiveRecord's read casting algorithm only casts the values you need and has become more efficient over time.

So the trade is between the raw speed of hashes and the convenience of typed attributes, and the readme's informal benchmark puts the gap at four times. The interesting consequence is that the cost is not in the cursor but in the instantiation, which means the cursor mechanism is not what you should be optimising.

There are also aliases, each_hash and each_hash_by_sql, for the row methods, for people who prefer to say what type of thing they are getting.

The options, including one the documentation says not to touch

All of these methods take an options hash, and the list is short enough to read in one go.

The block size is the number of rows to fetch from the database each time, and it defaults to one thousand. The while and until options control the loop, continuing as long as the block returns a value or until it does. The connection option lets you use a specific connection instead of the current one. The with hold option keeps the cursor open even after a commit, and the cursor name option gives the cursor a name, which is what you need if you want to refer to it in the database.

And then there is the fraction option, with the longest warning in the readme. It sets a value for the cursor tuple fraction variable, and the explanation is that PostgreSQL uses 0.1, which optimises for ten percent of the result set, while this library uses 1.0, which optimises for one hundred percent of the result set. Do not override this value unless you understand it.

That is a fair instruction, and it is worth knowing what it means rather than what to do about it. The tuple fraction is the estimate of how much of the result set the planner assumes will actually be fetched, which is why PostgreSQL defaults to 0.1 and this library raises it to 1.0 when the whole set is going to be read anyway. A user who understands their workload and their server can change it.

The last two notes in the readme are the usage warnings. Use cursors only for large result sets, because they have more overhead with the database than ActiveRecord selecting all matching records. And cursors must be run in a transaction if you need to fetch each row yourself.

Without a block you get an Enumerable that is already lazy

The behaviour that makes the library fit into a Rails codebase is what happens when you do not pass a block.

In that case the cursor is returned, and it mixes in the Enumerable library, so you can pass it around or chain the enumerable methods on it, the map and the reduce. And the cursors already act as lazy, so you can also chain lazy when you want to keep the memory footprint small for the rest of the process.

Product.each_row.map {|r| r["id"].to_i } #=> [1, 2, 3, ...]
Product.each_instance.lazy.inject(0) {|sum,r| sum +  r.quantity } #=> 499500

The distinction between the two is that a lazy enumerable does not materialise the whole list, while map without lazy does, and the readme's example makes the difference visible. The map over rows returns an array of every identifier, which is the thing you would not want for a large table, and the lazy inject over instances folds the numbers as they arrive without ever holding all the products at once.

The same Enumerable behaviour is what lets a cursor be used as a collection in a template, which is the third feature of the readme. You can render a partial with the cursor as the collection, and the readme shows it for each of the three shapes, the instances, the rows and the hashes.

render partial: "some_partial", collection: Product.each_instance

That is a small feature with a large reach, because it means the streaming behaviour survives into the view layer instead of forcing a controller to load a relation into memory to hand it to the template.

Pluck is often the better answer, and eager loading is out of reach

Two sections of the readme are about when not to use a cursor, and both are more useful than the sections about when to use one.

The first is pluck. It is described as a great alternative instead of using a cursor, because it does not instantiate the row and builds an array of result values, translating them into Ruby values such as numbers and timestamps. There are also pluck_rows and pluck_instances variants, which return the raw forms, and the readme is careful to say that using the cursor would still allow you to lazy load them in batches for very large sets. So pluck is the right answer when the set fits in memory, and the cursor is the answer when it does not.

The second is the honest limitation. When you use ActiveRecord's select method to limit the columns returned, the cursor respects it, so you can fetch only the identifiers and names you need and let the rest of the row stay in the database.

But the associations section records a hard limit. ActiveRecord performs some magic when eager loading associated rows, and it will usually not join the tables, preferring to load the data in separate queries. This library hooks onto the to_sql feature of the query builder, and as a result it cannot do the join if ActiveRecord decided not to join, nor can it construct the association objects eagerly.

That is not a missing feature, it is the consequence of how the extension works. The moment you need a cursor, you have already chosen a query that ActiveRecord wanted to split, and the extension can only work with the SQL that comes out of the query builder.

FOR UPDATE with a cursor locks one block at a time

The last major feature is the combination of a cursor with row locking, and it is the most interesting thing in the readme.

When you use the AREL lock method, a FOR UPDATE clause is added to the query. This causes the block of rows returned from each FETCH operation, the one controlled by the block size option, to be locked for you to update. The lock is released on those rows once the block is exhausted and the next FETCH or CLOSE statement is executed.

So the pattern is a bounded lock. The example runs through a large table and potentially updates each row, locking only a set of rows at a time to allow concurrent use.

Product.lock.each_instance(block_size:100) do |p|
  p.update(price: p.p

That is a different thing from what a plain lock does. A lock on the whole relation holds every row it touches until the transaction ends, which for a large table is a table wide lock in practice and stops everything else. A cursor with a block size of a hundred locks a hundred rows, processes them, and releases them, and the readme's phrase about allowing concurrent use is the whole point.

For a batch job that has to update a large table while the application is still serving, that is the difference between a job that blocks the application and one that does not, and it is the feature that makes the cursor more than a memory optimisation.

A gem tested against several Rails versions, and an old support statement

The repository layout and the readme's support statement together tell you what kind of project this is.

The support statement is dated. It says the gem supports Rails and ActiveRecord 3.1, with 3.2 recommended, and higher, including 5.0, and Ruby 1.9 and higher, and it notes that not all features work in ActiveRecord 3.1. It also says that support will only be for officially supported versions of ActiveRecord and Ruby, and that others can try older versions of the gem.

That is an honest way of saying that the library has not been updated for the current Rails releases, and it is the first thing to check before you plan to use it, because the readme mentions version 5.0 as the newest it names, and the current ActiveRecord is several versions beyond that.

The repository shows the testing setup. There is an Appraisals file, which is the standard way to test a gem against several versions of its dependencies, and a gemfiles directory with one Gemfile per combination, and a separate test-app directory that is a small Rails application used for the integration tests, plus the test directory for the unit tests.

The build is a Rakefile with a gemspec, and the CI is a Travis configuration, which dates the project as much as the support statement does.

The gem is MIT licensed, and the readme's gem version badge points at the published package, so the version to look at is the one on RubyGems rather than a tag in the repository, since there are no GitHub releases.

Editorial conclusion

postgresql_cursor fits a Rails application that has to walk a table of a few million rows without loading it into memory, because the cursor keeps the position of the result set in the database and the extension fetches it in blocks you choose. It is the wrong choice for a small result set, because the readme is explicit that a cursor has more overhead with the database than letting ActiveRecord select the matching records, and it is the wrong choice if you rely on eager loading, since the library hooks onto the query builder's to_sql feature and cannot join or build association objects the way ActiveRecord would. Before you adopt it, decide between the hash and the instance methods on evidence from your own models, because the readme reports instances at four times the cost of hashes, and read the option list once, because the fraction option in particular is one the documentation tells you not to change.

Frequently asked questions

Official sources

1. afair/postgresql_cursor on GitHub
2. Issues
3. License: MIT
4. README