Exporting CSV From the Database Straight to a Gzip File

Two million rows of CSV is 130 megabytes. Three hundred million is tens of gigabytes, and the cost of moving, storing and uploading it matters more than the cost of producing it. CSV is text with a lot of repetition and compresses well, so the easy win is to compress while you export, in the same pipe, without ever writing the uncompressed file. This article does that with gzip and zstd from PostgreSQL, measures size and time at six settings, and covers the part the tutorials skip: what a pipeline does when the export fails halfway.

Pipe it

COPY ... TO STDOUT writes CSV to standard output, and gzip reads standard input. Put a pipe between them:

time psql -c "COPY (SELECT * FROM orders) TO STDOUT WITH (FORMAT csv, HEADER)" | gzip > work/export/o_pipe.csv.gz
ls -l work/export/o_psql.csv work/export/o_pipe.csv.gz | awk '{printf "%-28s %d bytes\n", $9, $5}'
gzip -t work/export/o_pipe.csv.gz && echo "gzip -t: ok"
zcat work/export/o_pipe.csv.gz | wc -l

real	0m4.624s
user	0m4.597s
sys	0m0.238s
work/export/o_pipe.csv.gz    42200899 bytes
work/export/o_psql.csv       130119918 bytes
gzip -t: ok
2000001

The compressed file is 42,200,899 bytes, against 130,119,918 for the same export uncompressed, about a third (32.4%). It took 4.62 s, with gzip -6 the default level. gzip -t confirmed the stream is intact, and zcat | wc -l counted 2,000,001 lines, the header and 2,000,000 rows. The uncompressed CSV never touched the disk.

There is a second way, and it matters when the export runs through \copy:

time psql -c "\copy (SELECT * FROM orders) TO PROGRAM 'gzip > work/export/o_program.csv.gz' WITH (FORMAT csv, HEADER)"
ls -l work/export/o_program.csv.gz | awk '{print $5 " bytes"}'
zcat work/export/o_program.csv.gz | md5sum | cut -c1-12
md5sum < work/export/o_psql.csv | cut -c1-12

COPY 2000000

real	0m4.612s
user	0m4.583s
sys	0m0.202s
42200899 bytes
eed360d7d2fb
eed360d7d2fb

\copy ... TO PROGRAM 'gzip > file' runs the program on the client, where psql runs, and sends it the rows. It produced the same 42,200,899 bytes in 4.61 s. The checksum of the decompressed output, eed360d7d2fb, is the checksum of the uncompressed export from the earlier article: compression changed nothing about the content. The server-side COPY ... TO PROGRAM is a different command that runs a program on the database host and needs elevated privileges; I would not use it.

Which compressor, which level

The pipeline is only as fast as its slowest stage. With gzip at its default level, gzip is the slow stage. Six settings, same export:

for tool in "gzip -1" "gzip -6" "gzip -9" "pigz -6" "zstd -3 -q" "zstd -19 -q -T0"; do
  s=$(date +%s.%N)
  psql -c "COPY (SELECT * FROM orders) TO STDOUT WITH (FORMAT csv, HEADER)" | $tool > work/export/lvl.out
  e=$(date +%s.%N)
  printf '%-16s %10d bytes  %5.1f s\n' "$tool" $(stat -c %s work/export/lvl.out) $(echo "$e - $s" | bc)
done

gzip -1            50559623 bytes    1.9 s
gzip -6            42200899 bytes    4.6 s
gzip -9            41866623 bytes    7.6 s
pigz -6            42246761 bytes    1.4 s
zstd -3 -q         43748171 bytes    1.4 s
zstd -19 -q -T0    35402082 bytes   58.8 s

Going from gzip -6 to -9 saves 0.8% of the size and costs 65% more time. Going the other way, gzip -1 is more than twice as fast and 20% larger. pigz, a parallel gzip, matched the size of gzip -6 in 1.4 s, close to the 1.6 s the export alone takes. And zstd -3 was equally fast and 3.7% larger than gzip -6. The slow option, zstd -19, got to 35.4 MB (a 16% saving over gzip -6) at 13 times the time, which is for archives written once and read many times, not for a nightly export. These numbers come from one table of orders: text columns with short repeated values compress well, and a table of random hashes would not.

Choose by who reads the file. gzip is everywhere: most languages and tools read .gz without extra code. zstd is smaller and faster to decompress where it is supported, which is not everywhere yet. If you do not know the consumer, use gzip, and use pigz when the export is the bottleneck and the machine has cores to spare.

The failure the pipeline hides

This is the part that costs people a day. A pipe has one exit status, and by default it is the last command's. Here the query fails at row 100 (a division by zero):

BAD="COPY (SELECT order_id, 1 / (order_id - 100) FROM orders ORDER BY order_id) TO STDOUT WITH (FORMAT csv)"
echo "1. no pipefail"
psql -c "$BAD" 2>/dev/null | gzip > work/export/o_fail1.csv.gz; echo "   pipeline status: ${PIPESTATUS[0]} ${PIPESTATUS[1]}"
echo "   gzip -t: $(gzip -t work/export/o_fail1.csv.gz && echo ok); rows inside: $(zcat work/export/o_fail1.csv.gz | wc -l); last row: $(zcat work/export/o_fail1.csv.gz | tail -n 1)"
echo "2. with pipefail"
set -o pipefail
psql -c "$BAD" 2>/dev/null | gzip > work/export/o_fail2.csv.gz; echo "   pipeline status: $?"
set +o pipefail
echo "3. stderr merged into the pipe (2>&1)"
psql -c "$BAD" 2>&1 | gzip > work/export/o_fail3.csv.gz
echo "   last line inside the file: $(zcat work/export/o_fail3.csv.gz | tail -n 1)"

1. no pipefail
   pipeline status: 1 0
   gzip -t: ok; rows inside: 99; last row: 99,-1
2. with pipefail
   pipeline status: 1
3. stderr merged into the pipe (2>&1)
   last line inside the file: ERROR:  division by zero

Three runs.

1. **No pipefail.** The pipeline status was 1 0: psql failed with 1 and gzip succeeded with 0. A script looks at the last one, gzip's, sees success and carries on. The file passes gzip -t, because it is a perfectly valid gzip stream. It holds 99 rows. The last row is 99,-1. Nothing about the file says it is incomplete, and the next job in the chain will take it.

2. **With set -o pipefail** (documented in the Bash manual). The status of the pipeline became 1, the failing command's, and a script with set -e stops. This is the setting that makes pipelines honest, and the one I always add.

3. **With 2>&1 into the pipe.** This merges standard error into the stream, a common thing to write, and the last line inside the compressed CSV is ERROR: division by zero. The error message became a data row. Keep the error stream separate, and send it to a log.

The remedy is the same as for uncompressed files. Write to a temporary name (orders.csv.gz.part), check that every stage succeeded with pipefail, check the file with gzip -t, and rename to the final name last. The nightly export article puts these together in one script. The truncation article covers what a cut-off gzip looks like to a reader: gzip -t fails with unexpected end of file, which is the reason to run it.

Reading the result

Most tools read gzip without a separate step:

python - <<'PY'
import pandas as pd
df = pd.read_csv('work/export/o_pipe.csv.gz')
print('pandas:', len(df), 'rows', list(df.columns)[:3], '...')
PY
duckdb -noheader -list -c "SELECT count(*) FROM read_csv('work/export/o_pipe.csv.gz')"

pandas: 2000000 rows ['order_id', 'customer_id', 'zip'] ...
2000000

pandas inferred the compression from the .gz extension and read 2,000,000 rows, and DuckDB's read_csv read the same file and counted 2,000,000. PostgreSQL can load a gzipped file with \copy ... FROM PROGRAM 'gunzip -c file.gz', as the loading article shows. Name the file .csv.gz and not .gz, so a human or a tool knows what is inside.

What it costs

A gzip file is a single stream. You cannot jump to row 1,000,000 without decompressing the 999,999 before it, and you cannot read it in parallel. If readers want to scan a slice, export in pieces: one file per day, or per key range, and compress each separately. The four-way parallel export in the large table article produces natural parts. You also cannot grep it without zcat, and a corrupt byte in the middle loses the rest of the stream. These are the reasons for the checksum of the compressed file and the decompressed content in the manifest.

How to tell it worked

Run gzip -t on the file. Decompress to a pipe and count lines: zcat file | wc -l should equal the row count plus one. Compare the checksum of the decompressed stream with an uncompressed export, once, to prove the pipeline changes nothing. Then fail the query on purpose, as above, and check that your script stops and that the final file name does not exist. And read the file with the consumer's own tool.

When compression is the wrong trade

When the file is small, compression is not worth the extra step. When the consumer cannot read gzip (some older ETL tools and a lot of manual workflows), the compressed file is a support request. When a downstream system needs to seek, split or stream, as columnar and parallel readers do, a format like Parquet or a set of smaller files fits better than one big .gz. And when the data is already compressed or random, such as hashed ids or encoded blobs, gzip will save little and cost time. Measure on your own table, as I did on mine.