Stream PostgreSQL query with potentially huge result set as CSV using gzip compression in Rails and low memory overhead

some_controller.rb

headers['X-Accel-Buffering'] = 'no'
headers['Cache-Control'] = 'no-cache'
headers['Content-Type'] = 'text/csv; charset=utf-8'
headers['Content-Disposition'] = 'inline; filename="data.csv"'
headers['Content-Encoding'] = 'gzip'
sql = "select * from something;"
self.response_body = SqlToCsvStreamer.new(sql)

sql_to_csv_streamer.rb

class SqlToCsvStreamer
  def initialize(sql)
    @sql = sql
  end

  def each(&block)
    @block = block
    begin
      gz = Zlib::GzipWriter.new(self)
      conn = ActiveRecord::Base.connection.raw_connection
      conn.copy_data("COPY (#{@sql.chomp(';')}) TO STDOUT WITH (FORMAT CSV, HEADER TRUE, FORCE_QUOTE *, ESCAPE E'\\\\');") do
        while row = conn.get_copy_data
          gz.write row
        end
      end
    ensure
      gz.close
    end
  end

  def write(row)
    @block.call row
  end
end

Cool, thanks for sharing, Mikhail!