Loading a Gzipped CSV Into PostgreSQL Without Unpacking It

A 42 MB gzip file expands to 130 MB of CSV, and the instinct is to unpack it, load it and delete the copy. That works, and it costs disk and time you do not need to spend. gunzip -c writes the decompressed bytes to standard output, and COPY ... FROM STDIN reads standard input, so the two connect in one line. This article times that against unpacking first, shows the two ways to wire it, and spends most of its length on the thing that goes wrong: a pipeline that reports success when its first stage failed. The surrounding procedure is in the guide to loading a CSV into PostgreSQL.

The load

The shortest form pipes the decompressed stream into COPY ... FROM STDIN. psql sends what it reads on standard input as the data of the command:

gunzip -c orders-2m.csv.gz | psql -c "COPY bench FROM STDIN WITH (FORMAT csv, HEADER true)"

The alternative runs the decompressor from inside psql. \copy ... FROM PROGRAM starts the command on the client, reads its output and loads it, and it checks how the command ended:

psql -c "\copy bench FROM PROGRAM 'gunzip -c orders-2m.csv.gz' WITH (FORMAT csv, HEADER true)"

Server side COPY ... FROM PROGRAM, described in the COPY reference, also exists, runs on the database host, and needs the pg_execute_server_program role. That role lets the database run arbitrary commands as its operating system user, so I have never given it to anything but an administrator, and the roles page of the manual is clear that it is for trusted users. A non-privileged role is refused with permission denied to COPY to or from an external program, with a hint that \copy works for anyone, which is the answer:

ERROR:  program "gunzip -c /tmp/none.gz" failed
DETAIL:  child process exited with exit code 1
NOTICE:  role "loader" does not exist, skipping
ERROR:  permission denied to COPY to or from an external program
DETAIL:  Only roles with privileges of the "pg_execute_server_program" role may COPY to or from an external program.
HINT:  Anyone can COPY to stdout or from stdin. psql's \copy command also works for anyone.

How long does it take

I timed five ways of loading the same data, three runs each, with the table emptied between runs:

python time_loads.py

method                                median s
gunzip -c | COPY FROM STDIN               2.13   rows loaded: 2000000
\copy FROM PROGRAM 'gunzip -c'            2.15   rows loaded: 2000000
pigz -dc | COPY FROM STDIN                2.12   rows loaded: 2000000
gunzip to disk, then \copy                2.84   rows loaded: 2000000
\copy of the plain file                   1.98   rows loaded: 2000000

Piping gunzip -c into COPY FROM STDIN took 2.13 s, \copy FROM PROGRAM 2.15 s and pigz -dc, the parallel implementation of gzip, 2.12 s. Unpacking to a file and then loading took 2.84 s, since it adds the time to write and read 130 MB. The plain file with no compression took 1.98 s. So the compression costs about 0.15 s on this machine and the load is the slow part, which is why pigz has nothing to speed up: gunzip was already producing rows faster than PostgreSQL could take them.

All five loaded exactly 2,000,000 rows. The conclusion is easy to state: do not unpack, and do not bother with pigz for this. If your database is the bottleneck, decompression is free; if the decompression is the bottleneck, which happens with xz and bzip2 and with spinning disks, switching to zstd or to parallel tools helps.

The failure the pipe hides

A pipeline has an exit status, and it is the status of the last command. If gunzip fails and psql receives a clean end of input, the shell reports success. Whether a damaged archive causes a visible problem depends on where it is damaged.

I cut the archive at 20 MB, which is about half, so that gunzip stops with unexpected end of file:

head -c 20000000 orders-2m.csv.gz > truncated.csv.gz
psql -q -c "TRUNCATE bench"
echo "--- pipe without pipefail"
gunzip -c truncated.csv.gz | psql -c "COPY bench FROM STDIN WITH (FORMAT csv, HEADER true)"
echo "pipeline exit status: $?"
psql -qAt -c "SELECT 'rows now in the table: ' || count(*) FROM bench"

--- pipe without pipefail

gzip: truncated.csv.gz: unexpected end of file
ERROR:  missing data for column "zip"
CONTEXT:  COPY bench, line 950418: "950417,95"
pipeline exit status: 1
rows now in the table: 0

gunzip printed unexpected end of file, and the load failed with missing data for column "zip" at line 950418, where the last row was cut after 950417,95. The table is empty afterwards. That worked out well for a reason that is easy to miss: the truncation happened to cut a row in half, and a half row is a structural error. The pipeline's exit status was 1, since psql failed and psql was the last command.

Now the case that does not fail. If the cut falls inside the last field of a row, the half row is a valid row with a shorter value. I simulated it by sending 999 good rows and then a final line cut in the note column, through a pipe:

psql -q -c "TRUNCATE bench"
(head -n 1000 orders-2m.csv; printf '1001,5,12345,9.99,2025-01-01 00:00:00,paid,US,rush o') | psql -c "COPY bench FROM STDIN WITH (FORMAT csv, HEADER true)"
psql -qAt -c "SELECT 'last note loaded: ' || note FROM bench ORDER BY order_id DESC LIMIT 1"

COPY 1000
last note loaded: rush o

COPY 1000, and the last note is rush o instead of rush order. No error, exit status 0, and a wrong value in the last row. Truncation inside the last field is not unlikely: it is the longest field of a short row often enough. A pipe cannot tell, and the data cannot tell either, since a CSV has no end marker. What does notice is gunzip, which exits nonzero on a truncated archive, and that is why you need its exit status to count.

Make the failure count

There are two ways. One is the \copy FROM PROGRAM form, which checks the child process. On the same truncated archive it failed on the data and also reported the child's exit code, and the table stayed empty:

psql -q -c "TRUNCATE bench"
psql -c "\copy bench FROM PROGRAM 'gunzip -c truncated.csv.gz' WITH (FORMAT csv, HEADER true)" 2>&1
echo "exit status: $?"
psql -qAt -c "SELECT 'rows now in the table: ' || count(*) FROM bench"

gzip: truncated.csv.gz: unexpected end of file
ERROR:  missing data for column "zip"
CONTEXT:  COPY bench, line 950418: "950417,95"
gunzip -c truncated.csv.gz: child process exited with exit code 1
exit status: 1
rows now in the table: 0

The last line before the status says gunzip -c truncated.csv.gz: child process exited with exit code 1. Here the data error came first. The child's exit code is reported as well, and COPY treats a nonzero exit from the program as an error of its own, which is what makes this form safe against a damaged archive whose cut happens to leave valid rows. That check is the main reason I prefer it.

The other is set -o pipefail in bash, which makes the pipeline's status the first nonzero status of any stage:

psql -q -c "TRUNCATE bench"
bash -c 'set -o pipefail; gunzip -c truncated.csv.gz | psql -q -c "COPY bench FROM STDIN WITH (FORMAT csv, HEADER true)"; echo "pipeline exit status: $?"' 2>&1
psql -qAt -c "SELECT 'rows now in the table: ' || count(*) FROM bench"

gzip: truncated.csv.gz: unexpected end of file
ERROR:  missing data for column "zip"
CONTEXT:  COPY bench, line 950418: "950417,95"
pipeline exit status: 1
rows now in the table: 0

With pipefail the exit status was 1, from either stage. Put set -euo pipefail at the top of any script that pipes into a database. Without pipefail, a failing first stage is invisible.

For the second half of this protection, check the archive before you load it. gzip -t file.gz (see the gzip manual page) reads the whole file and verifies its checksum without writing anything, and it exits nonzero on a damaged archive. It reads the file twice in total, once to test and once to load, which on a 42 MB file is a fraction of a second and on a 40 GB file is minutes you will be glad you spent. Better still, if the sender provides a checksum, compare it before anything else, as in the URL loading article.

Other compression formats

The same pattern works for any decompressor that writes to standard output. zstd -dc file.csv.zst is faster than gzip at the same ratio and is what I would use for files I produce myself; xz -dc and bzip2 -dc compress harder and decompress slower, and they can become the bottleneck. For a zip archive, unzip -p archive.zip member.csv writes one member to standard output. If you have the zstd and pigz binaries, there is nothing PostgreSQL-specific to learn: you are supplying text to a command that wants text.

How to tell it worked

Compare COPY n with the record count you expect. For a compressed file, the cheapest count is a second pass through the decompressor, gunzip -c file.gz | qsv count, which is also a strict read of the file's structure. Compare a sum of a numeric column with the same sum computed from the stream by another tool. And in a script, check the exit status of everything: set -euo pipefail, or \copy FROM PROGRAM.

When the pipe hides a failure

If the load is a one-off and you will look at the result, the simple pipe is fine. If it runs unattended from cron or a workflow engine, a failure that exits 0 is the worst possible outcome, so use \copy FROM PROGRAM or pipefail, and add the count check. If the archive comes from a download and might be partial, the problem starts earlier, with the transfer itself, and the right place to catch it is a checksum or a size check on arrival. And if the file is a tar archive of several CSVs, extract the members you need with tar -xzOf archive.tar.gz member.csv, which writes one file to standard output, and the same rules apply.

Also remember that nothing in this article makes COPY any more tolerant. A gzipped file with a bad row fails as an unpacked file would, and the skipping bad rows article applies unchanged.