The real problem solved here was that I looked at a flow "out of band" and noticed an expected behaviour - finding the problem is half way to fixing it.

I'd set up a prepared statement (in code) but the system did not behave as it should.

Prepared Statements and timeouts - NodeJS and PostgeSQL

In trying to get around 100GB of data from a DC Postgres DBMS to a local one over a (relatively) slow 10mbit/s I had this connection set up (node was running on a LXC container in the DC and my test rig was at home):

// Target for the copy
const pgdst = new Pool({
  user: 'postgres',
  host: 'publicIPv6@home',
  database: 'db',
  port: 5432,
  max: 1,
  idleTimeoutMillis: 1000,
  connectionTimeoutMillis: 5000,
  allowExitOnIdle: true,
  ssl: {
    rejectUnauthorized: false

In order to throttle the "stream" I had a 2 second sleep in after every 100 INSERTS and noticed that when the loop ran again the prepared statement:

      query = {
        name: 'myPrepStat',
        text: 'INSERT INTO targetTable VALUES (' + s + ')',
        values: DataArr

I had intentionally set the idleTimeoutMillis low earlier so that I didn't have to wait too long for the clients to go idle.  Later I set allowExitOnIdle anyway.

... due to the 2s sleep and the 1s idleTimeoutMillis when the loop started again a new pool connection was made, requiring the prepared statement to be prepared again.  There was no in band indication of this (and probably there should not be).  I noticed because I'd turned the SSL off temporariliy and was running:

tcpdump -n -i enp6s0 -s0 -A tcp port 5432 | grep INSERT

Summary / Take Away

If you are using prepared statements and your program takes more than the 10s default (or whatever idleTimeoutMillis is set to) between DB connections you should confirm that they are in fact running as prepared statements!

In the end the prepared statements gave me a negligible / questionable 1% increase in throughput / connection speed.


Enabling SSL also made no difference to througput.  I didn't check CPU use.  Because this was a WAN transfer over IPv6 without a VPN I left SSL enabled.

My Setup

# node -v

"pg": "^8.8.0",


# SELECT version();
 PostgreSQL 13.11 (Debian 13.11-0+deb11u1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit
(1 row)

Time: 1.153 ms