Skip to content

Instantly share code, notes, and snippets.

@mlt
Last active February 15, 2023 12:50
Show Gist options
  • Save mlt/5988c3c3fd61858d13bd7c25891b9ecf to your computer and use it in GitHub Desktop.
Save mlt/5988c3c3fd61858d13bd7c25891b9ecf to your computer and use it in GitHub Desktop.
Stream PostgreSQL query with potentially huge result set as CSV using gzip compression in Rails and low memory overhead
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)
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
@imranmalik
Copy link

Cool, thanks for sharing, Mikhail!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment