Large Queries | Philly.rb lightning talk

How I handle large query result sets in ActiveRecord, and introducing my secret weapon, postgresql_cursor.

Transcript

1. Large Queries @allenfair Wednesday, July 11, 2012
2. HUGE “FIND” • Post.find(:all) • Post.where(“id>0”).all • Loads and instantiates all rows in memory! • Process has memory “creep”... and may be reaped Wednesday, July 11, 2012
3. Post.find_each Post.find_in_batches Wednesday, July 11, 2012
4. find_each & find_in_batches • Yields each BATCH of :batch_size||1000 records • Starting point with :start => id? • Only “order by id” (numeric primary key) • find_each: Yields each instantiated row • find_in_batches: Yields array of :batch_size instantiated objects to the block Wednesday, July 11, 2012
5. Examples Post.where(“id>0”).find_each {|post| post.process!} Post.where(“id>0”).find_in_batches do |group| group.each { |post| post.process! } end Wednesday, July 11, 2012
6. find_in_batches • Pagination for Batch Processing! • Re-executes the query for each segment! Wednesday, July 11, 2012
7. postgresql_cursor Wednesday, July 11, 2012
8. Cursor • DECLARE cursorname CURSOR FOR select .... • FETCH 1000 FROM cursorname • CLOSE cursorname • PG Cursor != “DBI” Cursor (an interator) Wednesday, July 11, 2012
9. • gem ‘postgresql_cursor’ • Post.where(...).each_row { |hash| process (hash) } • Post.where(...).each_instance { |m| m.process! } • Post.find_with_cursor(...) { |h| process(h) } • Post.find_by_sql_with_cursor(sql,...) {|h| do (h) } postgresql_cursor Wednesday, July 11, 2012
10. PostgreSQL Tuning • WORK_MEM=100MB • Default: 1MB, increases connection size • CURSOR_TUPLE_FRACTION=1 • Default: 0.1 (Optimizes for 10% of rows) • Can be set by app during transaction • SET ‘work_mem’ TO ‘100MB’ Wednesday, July 11, 2012
11. Now you have... • Your full query • Your sorting • No re-execution of queries • Joy and Happiness!!!!! Wednesday, July 11, 2012
12. Thank you! Wednesday, July 11, 2012