Improving bandwidth from RDS postgres

Question

When I do $ time psql -c "select repeat('a', 123456789)" -o /dev/null -U postgres -h hostname I reliably get about 10 seconds, making the transfer speed ~12 MB/sec. I'd like to do better.

I get the same speed when transferring a text row of the same size (don't ask), so I don't think its an issue constructing the string with repeat.

The client is an EC2 instance (m4.4xlarge). It and the RDS instances are in the same AWS region and in the same VPC. Using a count of 1, the time is < 0.1 sec, confirming the latency is low.

The transfer speed of the EC2 instance doesn't seem to be the limit. speedtest-cli confirms bandwidth ~1600 Mbit/sec

The test results are the same on three RDS instances of different sizes: m4.2xlarge, m6i.xlarge, and t3.medium. All of these have >1Gbps networking.

It doesn't seem to be TCP slow start - doubling the count doubles the time.

Disabling SSL via PGSSLMODE="disable" didn't help.

Wrapping the psql command in a while true loop, I can invoke multiple concurrent loops and they will each transfer at 12 MB/sec, suggesting it is not a limit of the client or server.

Any suggestions? Could you try on your server? Thanks!!

Versions:

psql (11.18 (Ubuntu 11.18-1.pgdg22.04+1), server 11.16)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)

Answer 1

psql reads the entire result set into memory and then tries to format it for output. This formatting is relatively slow.

In my hands, your command running locally (without the -h) takes 4 seconds. Now that is a lot less than 10 seconds, but is still a lot and we are using different systems so it is not clear how comparable to expect them to be.

If I turn off most of the formatting by adding the '-A' switch to your psql command line, then it takes about 0.8 seconds which is not far off from what it takes just to construct the repeat string.