Question 89005 in PostgreSQL Question posted in
The official documentation can be found here.

How to compress data when using psql's copy statement? – Postgresql

My goal is to efficiently import large amounts of data into a Postgres Database. In principle, the raw data could be compressed by a factor of ~20 (e.g. using gzip).

The COPY statement seems to be the best option for a bulk import.

Apart from sslcompression (which is applied after the data is encrypted), is there a way to compress the actual data (content) transferred between client and server, or is that even built-in by default?

Many thanks.

(Should not matter, but I am using golang).

Tags:

2

Answers


  1. 0

    the COPY [TO|FROM] PROGRAM allows to use gzip as a program, if it is installed somewhere accessible to the postgres server process: https://www.postgresql.org/docs/current/sql-copy.html#id-1.9.3.55.10.

    You could also use COPY TO|FROM STDIN and do the de/compression client-side.

    Login or Signup to reply.
  2. 0

    If your bottleneck is network throughput, you will want to send (copy or stream) compressed data to the database machine using something like scp or ssh, then run COPY in a client on the same machine as the database server is running. There are a number of ways this can be orchestrated, but all of them have something other than libpq/PostgreSQL as the conductor of the orchestra.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search