CSV import and export in Rails

An export endpoint that works is not evidence of anything. CSV.generate over 500 orders returns in 12 milliseconds and RSS does not move off 60 MB. The same eleven lines over 200000 orders took the Puma worker measured below from 108 MB to a peak of 663 MB and held the request open for 3.27 seconds before the browser saw a byte, and the failure arrives on the day somebody clicks Export All on a table that grew.

A rails csv export and a rails csv import are the same file travelling in opposite directions, and they fail in opposite ways. The export builds one enormous String. The import parses one enormous file into one enormous Array. Both have a fix that is roughly one line, and both have a second problem underneath that the one line does not touch.

Everything below ran on Ruby 4.0.5, rails 8.1.3.1, csv 3.3.5, pg 1.6.3, puma 7.0.4, rack 3.2.7, against PostgreSQL 17.7 holding 200000 rows in a 33 MB orders table. Peak RSS is sampled every 50 ms from ps -o rss=.

The require that fails before any of this

csv is not in the standard library any more. It became a bundled gem in Ruby 3.4.0, which Gem::BUNDLED_GEMS::SINCE will tell you to your face:

$ ruby -e 'require "rubygems"; p Gem::BUNDLED_GEMS::SINCE.select { |k, _| k == "csv" }'
{"csv" => "3.4.0"}

Outside Bundler nothing happens, because the gem is installed alongside the Ruby. Inside a Rails app it is a hard stop:

$ bundle exec ruby -e 'require "csv"'
-e:1: warning: csv used to be loaded from the standard library, but is not part of the
default gems since Ruby 3.4.0.
You can add csv to your Gemfile or gemspec to fix this error.
bundled_gems.rb:60:in 'Kernel.require': cannot load such file -- csv (LoadError)

That is a LoadError, not a deprecation warning, and the warning line above it says "error" while still being a warning, which does not help. Neither this site's Gemfile nor the LaunchKit boilerplate's declares gem "csv", so an export controller dropped into either one today would raise on the first request and not on boot, because require "csv" is usually written at the top of the controller and controllers load lazily. Add it to the Gemfile first. The current release is csv 3.3.6, from 26 July 2026.

What building the file in memory costs

Eleven lines, and every Rails app has written them:

def buffered
  body = CSV.generate do |csv|
    csv << HEADER
    Order.limit(params[:n].to_i).each { |o| csv << HEADER.map { |k| o[k] } }
  end
  send_data body, type: "text/csv", filename: "orders.csv"
end

Two things are held at once and neither is small. Order.limit(200_000).each instantiates 200000 Active Record objects, each with an attributes hash and a type caster, and holds all of them for the duration of the block. CSV.generate grows one String to the full size of the file, 17475617 bytes here. In a standalone script with no web stack at all, that pairing peaked at 539 MB over a 57 MB baseline.

Inside the Rails app it is worse, and the extra is not Rails being wasteful. send_data sets a Content-Length, which means the body is an Array of one String, which means Rack::ETag at rack-3.2.7/lib/rack/etag.rb:31 matches:

if etag_status?(status) && body.respond_to?(:to_ary) && !skip_caching?(headers)
  body = body.to_ary
  digest = digest_body(body)

So the whole 17 MB gets walked again and SHA digested to produce a W/"cf4f0fbed74be5..." header that no client will ever revalidate against, because the next export differs. Measured on a freshly booted worker sitting at 108 MB:

/buffered   base 108MB  peak 663MB  ttfb 3.268s  total 3.275s  bytes 17475617

555 MB of transient growth for a 17 MB file, and three seconds of a Puma thread doing nothing visible. Five people clicking at once is the incident.

Handing the controller an Enumerator

Rails will accept any object that responds to each as the response body, and an Enumerator is the cheapest one to write:

def enumerated
  headers["Content-Type"] = "text/csv"
  headers["Content-Disposition"] = %(attachment; filename="orders.csv")

  self.response_body = Enumerator.new do |y|
    y << CSV.generate_line(HEADER)
    Order.in_batches(of: 1000) do |rel|
      rel.pluck(*HEADER).each { |r| y << CSV.generate_line(r) }
    end
  end
end

Three separate choices are doing work there. pluck returns Arrays of typed values and never builds a model. in_batches keeps one thousand of those alive at a time instead of two hundred thousand. The Enumerator means the block does not run when the action returns, it runs when the server pulls the next chunk, so the String that exists at any moment is one line long. In the same standalone script that peaked at 539 MB for CSV.generate plus Order.all.each, streaming through find_each peaked at 74 MB and streaming through in_batches plus pluck at 64 MB, over a 57 MB baseline.

Same worker, same rows:

/buffered     base 108MB  peak 663MB  ttfb 3.268s  total 3.275s  bytes 17475617
/enumerated   base 109MB  peak 199MB  ttfb 0.001s  total 6.263s  bytes 17475617

199 MB against 663 MB, and a first byte in one millisecond against 3.27 seconds, for byte identical output. The response goes out transfer-encoding: chunked with no Content-Length and no ETag, because a streaming body does not respond to to_ary and Rack::ETag skips it.

State the cost honestly: the streamed version took 6.26 seconds end to end against 3.27. Chunked transfer, a Rack body pulled one line at a time and 200 batch queries instead of one are all slower than doing it in a single pass. What you buy is a worker that never balloons and a browser that starts saving immediately, and on a shared box that is the trade worth making. If the export is 2000 rows and always will be, build it in memory and stop reading.

The writer worth reusing

CSV.generate_line is the obvious way to produce each line and it is the expensive one. Over 200000 rows of seven columns:

CSV.generate_line x200000:   1.37s
CSV.new(io) << row x200000:  0.37s
row.join(",") + "\n":        0.08s

generate_line constructs a writer, a row separator and an options hash on every call. One CSV writer reused across the loop is roughly four times faster for identical bytes. To use one inside an Enumerator you need something for it to write into, and this is the part with a wrong turn in it. The obvious shim defines write, because that is what an IO does, and csv does not call write:

NoMethodError: undefined method '<<' for an instance of Chunks
    from csv-3.3.5/lib/csv.rb:2508:in 'CSV#<<'

CSV#<< at line 2508 is writer << row, and the writer forwards << to whatever you handed the constructor. So the shim is three lines and the method is <<:

class Chunks
  def initialize(yielder) = @yielder = yielder
  def <<(chunk) = (@yielder << chunk; self)
end

self.response_body = Enumerator.new do |y|
  csv = CSV.new(Chunks.new(y))
  csv << HEADER
  Order.in_batches(of: 1000) { |rel| rel.pluck(*HEADER).each { |r| csv << r } }
end

The 0.08 second version in that table, row.join(","), is a trap and worth showing once so nobody reinvents it. Given ["a,b", 'say "hi"', "line\nbreak"]:

CSV.generate_line  "\"a,b\",\"say \"\"hi\"\"\",\"line\nbreak\"\n"
join(",")          "a,b,say \"hi\",line\nbreak\n"

Three cells become five, and one row becomes two. Customer names contain commas. Notes contain newlines. A CSV writer exists because escaping is the entire job.

When the stream fails halfway

Headers go out with the first chunk, and after that the status code is decided. An exception raised on row 3001 of an export cannot become a 500, because a 200 has already been sent. Both streaming versions were made to raise deliberately after three batches:

/enum_boom   HTTP/1.1 200 OK   3001 lines of 5001   curl exit 56
/live_boom   HTTP/1.1 200 OK   3001 lines of 5001   curl exit 0

The Enumerator version at least breaks the chunked stream without a terminating chunk, so curl reports error 56, "recv failure". The ActionController::Live version closes the stream in its ensure block, which produces a well formed end of response, so curl exits 0. The client is told the download succeeded. Somebody opens 3000 orders in Excel and reconciles a month against it. The exception is in the Rails log and nowhere else:

Read: #<ArgumentError: row 3001: cents is not a number>

Nothing in HTTP fixes this after the fact. The two honest answers are to make the streamed query incapable of raising, which means no per row Ruby that can fail and no lazily loaded association, or to stop streaming to the browser at all: write the file in a background job, attach it, and mail a link, which is what Action Mailer in production is about, and which also survives the user closing the tab. Streaming directly to the browser is right when the export is a read of columns that already exist. It is wrong when generating a row can fail.

COPY, when the rows are already a table

PostgreSQL will format CSV itself, and when the export is a plain projection of columns with no Ruby in the middle, it is not close:

raw = ActiveRecord::Base.connection.raw_connection
raw.copy_data("COPY (SELECT reference, customer_name, cents, placed_at FROM orders) TO STDOUT WITH CSV HEADER") do
  while (chunk = raw.get_copy_data)
    yielder << chunk
  end
end

Same 200000 rows: 0.1 seconds and an 11 MB peak, against 2.5 seconds for CSV.generate and 3.6 for the pluck loop. The server formats, the client copies bytes through, and no Ruby object is built per row.

Two things to know before reaching for it. The formatting is Postgres formatting, not Ruby formatting, and timestamps are where you notice:

AR/CSV : ORD-100000,Marie Dupont,500,2026-09-24T14:23:03Z
COPY   : ORD-100000,Marie Dupont,500,2026-09-24 14:23:03.607397

A space instead of a T, microseconds instead of seconds, no zone suffix. If anything downstream parses that column, it has to be told. Second, copy_data insists you drain it. Breaking out of the loop early raises:

PG::NotAllCopyDataRetrieved: Not all COPY data retrieved

which on a streamed response means a client disconnect turns into an exception on a connection that is now in an unusable state. Checking the connection back in after that is the caller's problem.

Reading the file back without loading it

CSV.read returns a CSV::Table of CSV::Row objects, all of them, before your first line of code runs. Over the 16875617 byte file this export just produced:

CSV.read(f, headers: true)              200000 rows   peak 275 MB   1.3s
CSV.parse(File.read(f), headers: true)  200000 rows   peak 275 MB   0.7s
CSV.foreach(f, headers: true)                         peak  29 MB   0.9s

248 MB against 2 MB above a 27 MB baseline, for the identical work. The rails csv import that runs fine against the 200 row file a colleague emailed is the same code that gets OOM killed against the export a customer sends back.

CSV.foreach reads and parses one line at a time, yields a CSV::Row, and lets it go. There is no option to configure and no gem to install, and on any file whose size you do not control it is the only correct choice. The single behavioural difference is that you cannot index backwards or ask for rows.size up front, which is the price of not having read the file yet.

The BOM rule that reverses on you

A UTF-8 BOM is the three bytes EF BB BF at the start of a file. In UTF-8 it signals nothing about byte order, since there is no order to signal. It is a marker, and Excel writes it so that Excel can recognise its own output: per Microsoft's own documentation, a UTF-8 CSV opens correctly in Excel when it was saved with a BOM, and needs Power Query when it was not.

Now the part that catches people. csv 3.3.5 handles the BOM for you in one case and not in the other:

CSV.foreach("bom.csv", headers: true).first.headers
# => ["reference", "customer_name", "cents"]

CSV.parse(File.read("bom.csv"), headers: true).headers
# => ["reference", "customer_name", "cents"]

Second form, row["reference"] is nil. Not an error, not a warning: a nil where a reference number should be, on the first column only, for every row. The rule is that a path gets the stripping and a String or an IO does not, and CSV.new(uploaded_io, headers: true) is the IO case, which is exactly what a controller handed params[:file] reaches for.

The guard that backfires on a careful developer

The mechanism is may_enable_bom_detection_automatically, called from CSV.open at csv.rb:1651 and defined at 1963. CSV.foreach and CSV.read both route through CSV.open, at lines 1389 and 1922, which is why the path forms get it and CSV.parse and CSV.new do not. Five guards decide whether csv quietly adds encoding: "bom|utf-8" for you, and the last line is the only way it ever gets set:

      return if ON_WINDOWS                                       # 1974
    end
    return unless Encoding.default_external == Encoding::UTF_8   # 1976
    return if options.key?(:encoding)                            # 1977
    return if options.key?(:external_encoding)                   # 1978
    return if mode.is_a?(String) and mode.include?(":")          # 1979
    file_opts[:encoding] = "bom|utf-8"                           # 1980

Read line 1977 again. Passing encoding: "utf-8", which is what a developer types when they think they are being careful about csv encoding, disables the BOM stripping:

explicit encoding: "utf-8"  -> ["reference", "customer_name", "cents"]
no encoding option          -> ["reference", "customer_name", "cents"]
encoding: "bom|utf-8"       -> ["reference", "customer_name", "cents"]

The second guard is the one that makes this a production story. A container started without LANG gets Encoding.default_external == US-ASCII, and the same call on the same file that worked on a laptop becomes:

CSV::InvalidEncodingError: Invalid byte sequence in US-ASCII in line 1.

A Windows-1252 file, which is what a French or German Excel install produces when it is not asked for UTF-8, raises the same class with a different encoding named, and CSV::InvalidEncodingError < CSV::MalformedCSVError < RuntimeError, so a bare rescue CSV::MalformedCSVError catches both and tells the user their file is malformed when it is merely Latin-1. Declaring the source encoding is the fix and it is not a guess:

CSV.foreach(path, headers: true, encoding: "Windows-1252:UTF-8") do |row|
  row["customer_name"] # => "Chloé Lefèvre", UTF-8, valid_encoding? true
end

On the way out, CSV.generate writes no BOM at all: the first bytes of a file starting with "Chloé" are [67, 104, 108, 111], plain C h l o. For an export whose only reader is a script, leave it that way, because a BOM breaks the first header of any strict parser downstream. For an export a human opens in Excel, prepend "" and accept that you have made the file slightly worse for everything else. There is no setting that is right for both audiences, which is why this belongs in a toggle and not in a constant.

Twenty thousand error objects are twenty thousand records

Row validation is where an importer quietly becomes the memory hog it was rewritten to stop being. The shape is always the same: build a model per row, validate it, keep the bad ones to show the user at the end.

bad = []
CSV.foreach(path, headers: true) do |row|
  order = Order.new(row.to_h)
  bad << order unless order.valid?
end

ActiveModel::Error holds a reference to the record it came from. At activemodel-8.1.3.1/lib/active_model/error.rb:102 the constructor is def initialize(base, attribute, type = :invalid, **options) and line 118 is attr_reader :base, and error.base.equal?(order) is true. So bad is not a list of messages. It is a list of Order objects, each holding its attributes hash, its type casters, its errors collection, and through the errors, itself.

Measured with 20000 invalid rows, one process each, GC.start before reading RSS:

kept 20000 Order objects          97 MB above base
kept 20000 [line, message] pairs   9 MB above base

The fix is to stop holding the model the moment you have what you need from it:

bad << [line_number, order.errors.full_messages.join("; ")] unless order.valid?
# => [2, "Reference is invalid; Cents must be greater than 0; Email is invalid"]

Ten times smaller, and it is also the thing you were going to render anyway. Cap it too. Nobody reads error 4000, so collect the first 100 and a count.

One validation deserves singling out. validates :reference, uniqueness: true issues a SELECT per row, and Active Record does not batch it:

5000 uniqueness validations:      5000 queries   0.61s
one pluck over the same 5000:        1 query     0.011s

Fifty times faster as one where(reference: refs).pluck(:reference).to_set per batch. It is also still a race, which Active Record says about itself at active_record/validations/uniqueness.rb:233, "uniqueness checks on the application level are inherently prone to race conditions", so the unique index stays regardless. The uniqueness validation is for the error message. The index is for the guarantee.

Writing the rows in batches

create! per row is one INSERT and one round trip per row. insert_all is one statement per batch:

buffer = []
CSV.foreach(path, headers: true) do |row|
  buffer << row.to_h
  if buffer.size == 1_000
    Import.insert_all(buffer, unique_by: :index_imports_on_reference)
    buffer.clear
  end
end
Import.insert_all(buffer, unique_by: :index_imports_on_reference) if buffer.any?

Measured against the same file:

create! per row, 20000 rows                   7.6s
insert_all in 1000s + valid? per row, 200000  15.7s
insert_all in 1000s, no validation, 200000    11.8s

20000 rows one at a time cost 7.6 seconds, so 200000 would be about 76. Batched, 15.7. The interesting half of that table is the last line: dropping validation entirely saved 3.9 seconds out of 15.7. Instantiating and validating an Active Record object is not what makes a row import slow. The round trip is. Keep the validation.

Three sharp edges on insert_all, all of them silent or nearly so. First, it is ON CONFLICT DO NOTHING, which the generated SQL says out loud:

INSERT INTO "orders" ("reference","customer_name","cents","currency","status","placed_at")
VALUES ('ORD-100000', 'dup', 1, 'EUR', 'paid', '2026-09-24 14:26:11.741961'),
       ('FRESH-1', 'new', 1, 'EUR', 'paid', '2026-09-24 14:26:11.741962')
ON CONFLICT  DO NOTHING RETURNING "id"

Two rows submitted, one inserted, no exception raised, table count up by one, and the double space in ON CONFLICT DO NOTHING is Active Record's, not a typo here. The only signal is the return value: result.rows came back as [[200004]], one id for two rows. An importer that does not compare result.rows.size to the batch size reports every row imported while silently dropping the conflicts. insert_all! raises instead: ActiveRecord::RecordNotUnique: PG::UniqueViolation, which loses the whole batch rather than the one row, so pick deliberately.

Second, every hash must have identical keys. A CSV where some rows leave an optional column off, which is what happens when a human edits the file, gets ArgumentError: All objects being inserted must have the same keys from insert_all.rb:208. Build each hash from the full header list, not from the row's present keys.

Third, insert_all skips validations and callbacks entirely, which is the point and also the risk. Anything enforced only in Ruby is not enforced here. That is an argument for putting the constraint in the database, and it is the same argument as in Counter caches by hand: the column Rails maintains is a cache, the thing the database enforces is the truth.

The gems, and when one earns a place

Three gems come up, and all three are alive. The csv gem itself is at 3.3.6, released 26 July 2026. smarter_csv is at 1.19.0, released 10 August 2026, and it does chunking and header mapping for you. activerecord-import is at 2.3.0, released 15 August 2026, with 167 million downloads behind it.

None of them is needed for what this post covers. CSV.foreach plus insert_all plus a unique index is the whole import, it is about fifteen lines, and it has no version to track. That is the same call made in Pagination without a gem, for the same reason: the framework already has the mechanism, and the gem is mostly ergonomics around it.

What would change the call: activerecord-import earns its place the moment you need on_duplicate_key_update across several columns with a condition, because hand writing that ON CONFLICT ... DO UPDATE SET through insert_all's :on_duplicate is genuinely unpleasant. smarter_csv earns it when the files are hostile in ways you do not control, with varying headers, mixed separators and junk preamble rows, because its option surface is a list of problems other people already hit. Neither earns it for a clean file with a known header.

What this post does not cover

The LaunchKit boilerplate has no CSV in it. No controller sends one, no job reads one, gem "csv" is not in its Gemfile, and a grep -rn csv app lib across it returns nothing. Everything measured here ran in a single file Rails 8.1.3.1 application and a set of scratch scripts written for this post, against a local PostgreSQL 17.7. That is the honest provenance, and the boilerplate would need the Gemfile line before any of it worked there.

Also absent: COPY FROM STDIN on the import side, which is faster still than insert_all and gives up per row error reporting to get there; Enumerator::Lazy over the reader, which reads the same as foreach and buys nothing here; TSV and semicolon separated files, where col_sep is the whole answer; and compressed exports, where gzipping a streamed body correctly is its own argument with Rack.

No benchmark here is a claim about your data. The shape of the win is stable, since it comes from holding one row instead of all of them, but the absolute numbers move with column count, row width and how much Ruby runs per row. Run the two versions against your own table before quoting anything above in a pull request.