Exporting Large Tables to CSV Without Running Out of Memory

An export that works on a thousand rows and is killed on a hundred million is a familiar incident. The cause is almost always the same: somewhere between the database and the file, the whole result was held in memory. This article measures that on a 2,000,000 row table. It runs six ways of getting the same CSV out of PostgreSQL and three out of MySQL, records time and peak memory for each, and checks that the files agree. The headline is that the fastest way is also the lightest, and the most popular way in Python is the heaviest.

What can go wrong

An export reads rows from the database, turns them into text and writes them out. Memory goes wrong in three places:

1. The driver buffers the result. Most client libraries, by default, fetch the entire result set into client memory when you run the query. fetchall() makes it explicit, but even a loop over a default cursor has the full result waiting behind it.

2. The library builds a table. pandas holds the whole result as a DataFrame with Python objects for the strings, which is several times larger than the CSV.

3. The sort spills. An ORDER BY on a big result makes the database sort 2,000,000 rows first. That is a time cost more than a memory one on the client, and it shows up below.

Each method below sits in one of those categories.

Six ways out of PostgreSQL

/usr/bin/time -f "%e s, peak %M KB" psql -c "\copy (SELECT * FROM orders ORDER BY order_id) TO 'work/export/o_psql.csv' WITH (FORMAT csv, HEADER)"
ls -l work/export/o_psql.csv | awk '{print $5 " bytes"}'

COPY 2000000
3.15 s, peak 11552 KB
130119918 bytes

psql with \copy: 3.15 s, peak 11,552 KB. The server formats the rows as CSV and psql writes the bytes it receives. This is the baseline.

/usr/bin/time -f "%e s, peak %M KB" python lab/export/exp_copy.py work/export/o_copy.csv

2000001 chunks
4.65 s, peak 40040 KB

COPY ... TO STDOUT through psycopg's cursor.copy(): 4.65 s, peak 40,040 KB. The same CSV stream, written by Python in chunks. The "2,000,001 chunks" is one per line, header included. Use this when the export is part of a Python program and you want the server's formatting.

/usr/bin/time -f "%e s, peak %M KB" python lab/export/exp_named.py work/export/o_named.csv

2000000 rows
8.47 s, peak 69660 KB

A named cursor (a server-side cursor) with itersize = 20000 and the csv module: 8.47 s, peak 69,660 KB. Rows come in batches of 20,000, Python formats each one, and memory stays flat. It is slower because Python formats two million rows, and it is the right choice when you need to transform rows in Python on the way out.

/usr/bin/time -f "%e s, peak %M KB" python lab/export/exp_fetchall.py work/export/o_fetchall.csv

2000000 rows
9.18 s, peak 1547332 KB

cur.fetchall() then writerows: 9.18 s, peak 1,547,332 KB. The whole result is a list of tuples in memory before the first row is written. That is about 770 bytes per row (psycopg's client-side cursors fetch the full result on execute), so at the same row width, 100 million rows would need roughly 77 GB: the extrapolation is mine, and the point is that it grows linearly with the table and the process dies when it passes what you have.

/usr/bin/time -f "%e s, peak %M KB" python lab/export/exp_pandas.py work/export/o_pandas.csv

/batches/csv-0001-0200/lab/export/exp_pandas.py:4: UserWarning: pandas only supports SQLAlchemy connectable (engine/connection) or database string URI or sqlite3 DBAPI2 connection. Other DBAPI2 objects are not tested. Please consider using SQLAlchemy.
  df = pd.read_sql("SELECT * FROM orders ORDER BY order_id", conn)   # the whole table as a DataFrame
2000000 rows
14.08 s, peak 2028184 KB

pd.read_sql then to_csv: 14.08 s, peak 2,028,184 KB. It is the slowest and the heaviest, and pandas also warned that it prefers an SQLAlchemy connection. The reasons are the whole result in memory, the conversion to a DataFrame and then the conversion back to text.

/usr/bin/time -f "%e s, peak %M KB" python lab/export/exp_pandas_chunks.py work/export/o_chunks.csv

/batches/csv-0001-0200/lab/export/exp_pandas_chunks.py:5: UserWarning: pandas only supports SQLAlchemy connectable (engine/connection) or database string URI or sqlite3 DBAPI2 connection. Other DBAPI2 objects are not tested. Please consider using SQLAlchemy.
  for i, chunk in enumerate(pd.read_sql("SELECT * FROM orders ORDER BY order_id", conn, chunksize=100000)):
2000000 rows
12.36 s, peak 693328 KB

pd.read_sql(..., chunksize=100000) writing each chunk as it goes: 12.36 s, peak 693,328 KB. Better, and not flat. The chunks bound the DataFrame, but the cursor under them was a client-side one, so the driver had already pulled the full result into memory. That is my reading of the number; the fix is to give pandas a server-side cursor, at which point you have written the named-cursor script above with extra steps.

The files are not all the same

A fast export that writes different bytes is not a fast export of the same thing:

cd work/export
wc -l o_psql.csv o_named.csv o_copy.csv o_fetchall.csv o_pandas.csv o_chunks.csv | head -n 6
md5sum o_psql.csv o_copy.csv | cut -c1-12,33-
for f in o_psql o_named o_copy o_fetchall o_pandas o_chunks; do printf '%-12s' $f; sed -n 2p $f.csv; done

  2000001 o_psql.csv
  2000001 o_named.csv
  2000001 o_copy.csv
  2000001 o_fetchall.csv
  2000001 o_pandas.csv
  2000001 o_chunks.csv
eed360d7d2fb  o_psql.csv
eed360d7d2fb  o_copy.csv
o_psql      1,22313,99154,103.10,2025-11-22 13:08:24,shipped,GB,rush order
o_named     1,22313,99154,103.10,2025-11-22 13:08:24,shipped,GB,rush order
o_copy      1,22313,99154,103.10,2025-11-22 13:08:24,shipped,GB,rush order
o_fetchall  1,22313,99154,103.10,2025-11-22 13:08:24,shipped,GB,rush order
o_pandas    1,22313,99154,103.1,2025-11-22 13:08:24,shipped,GB,rush order
o_chunks    1,22313,99154,103.1,2025-11-22 13:08:24,shipped,GB,rush order

All six files have 2,000,001 lines. The psql and COPY files have the same MD5 checksum, so they are byte-identical. The first data row of the pandas files has 103.1 where the others have 103.10: pandas read the numeric(10,2) column as a float (read_sql converts decimals to floats by default, through its coerce_float option), and wrote it back without the trailing zero. For an amount that is a cosmetic difference you may never notice. For a column that is really text, a zip code or an account number, the same path can drop leading zeros. If the consumer compares with the source, or parses decimals as strings, the database's own formatting is the safer one. The dates and booleans article covers the other values that change on the way out.

Making it faster

Two cheap levers, both measured:

/usr/bin/time -f "%e s, peak %M KB" psql -c "\copy (SELECT * FROM orders) TO 'work/export/o_noorder.csv' WITH (FORMAT csv, HEADER)"

COPY 2000000
1.59 s, peak 11540 KB

Removing ORDER BY order_id halved the time: 1.59 s against 3.15 s. If the consumer does not need an order, do not ask for one. If it does, order on an indexed column so the database can read in index order, and for a repeatable file, order by a unique key.

rm -f work/export/part_*.csv
start=$(date +%s.%N)
for i in 0 1 2 3; do
  lo=$((i * 500000 + 1)); hi=$(((i + 1) * 500000))
  psql -qc "\copy (SELECT * FROM orders WHERE order_id BETWEEN $lo AND $hi ORDER BY order_id) TO 'work/export/part_$i.csv' WITH (FORMAT csv)" &
done
wait
end=$(date +%s.%N)
printf 'four parallel \\copy runs: %.2f s wall\n' $(echo "$end - $start" | bc)
wc -l work/export/part_*.csv | tail -n 1
(psql --csv -c "SELECT * FROM orders WHERE false" | head -n 1; cat work/export/part_0.csv work/export/part_1.csv work/export/part_2.csv work/export/part_3.csv) | cmp - work/export/o_psql.csv && echo "identical to the single export"

four parallel \copy runs: 0.99 s wall
  2000000 total
identical to the single export

Four \copy processes, each exporting a 500,000-row range of order_id, finished in 0.99 s of wall time. Concatenated in order, the four parts were identical to the single export (cmp reported no difference). Each process had its own transaction, which is a risk: rows inserted or updated between the four snapshots can be missed or duplicated. Use it for static data, or export from a snapshot (for example, SET TRANSACTION SNAPSHOT shared across the sessions, which I did not test). It also needs a key that splits evenly.

Compressing on the way out is the other lever, and the gzip article measures it.

MySQL

MySQL's server-side INTO OUTFILE has the formatting problems covered in its own article. From the client:

/usr/bin/time -f "%e s, peak %M KB" python lab/export/mysql_export.py "SELECT * FROM orders ORDER BY order_id" work/export/o_mysql_stream.csv

2000000 rows written to work/export/o_mysql_stream.csv
17.78 s, peak 23300 KB

A pymysql streaming cursor (SSCursor): 17.78 s, peak 23,300 KB.

BUFFERED=1 /usr/bin/time -f "%e s, peak %M KB" python lab/export/mysql_export.py "SELECT * FROM orders ORDER BY order_id" work/export/o_mysql_buffered.csv

2000000 rows written to work/export/o_mysql_buffered.csv
21.24 s, peak 1127232 KB

The default, buffered cursor with the same script: 21.24 s, peak 1,127,232 KB. The same difference as in PostgreSQL: the cursor decides whether the result sits in memory.

/usr/bin/time -f "%e s, peak %M KB" bash -c 'mysql -h mysql -uroot -psecret lab --default-character-set=utf8mb4 --batch --quick -e "SELECT * FROM orders ORDER BY order_id" 2>/dev/null > work/export/o_mysql_batch.tsv'
wc -l work/export/o_mysql_batch.tsv

2.46 s, peak 11008 KB
2000001 work/export/o_mysql_batch.tsv

mysql --batch --quick: 2.46 s, peak 11,008 KB, 2,000,001 lines. --quick fetches rows one at a time instead of buffering them. The output is tab-separated text with MySQL's escaping and not RFC 4180 CSV, as the MySQL article shows, so its speed is only useful if the consumer reads that format.

What I would do

For a PostgreSQL table: \copy or COPY TO STDOUT, with no ORDER BY unless needed, and split by key range if time matters. For MySQL: a streaming cursor in a script that uses a CSV library, or --batch --quick when TSV is acceptable. In Python, a server-side cursor; never fetchall() on a table whose size you do not control. And never pandas for an export whose only purpose is to write a file.

How to tell it worked

Measure peak memory with /usr/bin/time -v (the "maximum resident set size" line) on a table at least ten times larger than the one you are testing for, because the problem only appears at scale. Compare the line count with SELECT count(*). Compare a checksum of the output between two methods before you trust the faster one. And check at least one row for each type of column against the database, by eye.

When the numbers will not transfer

These are one run each on a laptop with both ends on the same machine. A network between the database and the client changes the picture: the transfer can dominate, and the formatting method that sends fewer bytes wins. A table with wide text columns, a busy production database or a replica with replication lag will behave differently. A server-side cursor holds a transaction open (see PostgreSQL's DECLARE), which on a busy primary has a cost in vacuum and locks that this test could not show. Take the ratios and the shape of the memory curves, not the seconds, and repeat the test on your own data and hardware.