On 31.08.2026 14:26, Andrey Borodin wrote:
Hi,
There have been three substantial attempts to add protocol compression
to PostgreSQL [0][1][2]. They demonstrated useful reductions in network
traffic, but also accumulated questions about negotiation, I/O layering,
codec and direction selection, poolers, and compression before
encryption. Thanks to Konstantin, Daniil, Jacob, and everyone who worked
on and reviewed them.
I have been experimenting with a different design and decided that it's
time to discuss the topic again.
The proposal is:
- Zstandard only.
- An explicitly negotiated _pq_.compression=zstd protocol extension [3].
- Compression starts only after authentication and the first
ReadyForQuery.
- Server to client: DataRow and CopyData.
- Client to server: CopyData during COPY FROM.
- Query, Parse, Bind, parameters, and all control messages remain
ordinary protocol messages.
- One persistent stream per active direction, carried in bounded
CompressedData messages.
- The backend stream ends before ReadyForQuery. The frontend stream ends
before CopyDone or CopyFail.
The wrapper contains only a flushed segment of the compressed stream.
The uncompressed bytes from successive wrappers form an ordinary
protocol message stream, so a large DataRow or CopyData message may span
multiple wrappers. The receiver bounds both the wrapper and the output
produced by each segment, and validates the inner message types and
lengths as they become available.
This is deliberately asymmetric. DataRow and backend CopyData cover
query results, COPY TO, base backup, and WAL streaming. Frontend
CopyData covers COPY FROM and pg_restore. Leaving queries and parameters
visible means that a pooler can still inspect and route queries or
rewrite prepared statement names without decompressing them.
ReadyForQuery also remains an explicit transaction-pooling boundary. A
pooler that passes the negotiation and length-prefixed wrappers through
can relay CompressedData without understanding Zstandard.
The server has protocol_compression = off | zstd, with off as the
default. libpq has compression=off, prefer, and zstd.
Prefer continues without compression when the server rejects the
extension; zstd requires it. A server build without Zstandard does not
expose the zstd GUC value.
I also tested the new libpq against an unmodified PostgreSQL 14
server. prefer fell back and connected normally, while zstd failed with
the expected unsupported-method error.
The server switch is important because compression happens before SSL
or GSS encryption. Compressed lengths may reveal information when
attacker-controlled and secret values share history. Excluding
authentication, queries, and bind parameters reduces the scope but does
not eliminate that property. The administrator can prohibit the
feature, and it is off by default.
Compression state is allocated lazily, and small results can remain
uncompressed.
I deliberately left reporting through pg_stat_* out of the first patch
to keep it small. A follow-up can expose whether a connection uses
compression and its compressed and uncompressed byte counts in each
direction. Other follow-up features I have in mind are additional
codecs and compression levels, and per-direction control. None of these
requires changing the basic CompressedData framing.
Earlier end-to-end prototype measurements reduced mixed DataRow and
COPY TO workloads to about 10% and 14% of their original wire size.
MD5-heavy DataRow and COPY FROM workloads were reduced to about 51%.
I reran this implementation with the default Zstandard level. In a
realistic pg_restore test, client-to-server traffic fell from 264 MB to
21 MB; median local restore times were 3.00 seconds without compression
and 3.04 seconds with it. One-row pgbench medians were 7034 and
7018 TPS. Pseudorandom 32 MB DataRow and COPY TO results were reduced
only to 78% and 81%, and took about 0.5 seconds more CPU time on
loopback. The byte savings on compressible data translated into
corresponding speedups through a bandwidth-limited proxy.
The memory test also found a useful bound. An earlier prototype with an
8 MiB Zstandard window retained about 8.6 MiB per active backend.
Limiting history to 64 KiB reduced the additional retained private
anonymous memory to about 1 MiB in a same-backend test, at a cost of
about 1.8% more compressed bytes for COPY TO and no loss in the other
two measured results.
I would like feedback on three design choices before turning this into a
patch series:
1. Message scope. v1 compresses only bulk-data messages with the clearest
benefit. It leaves authentication, query, parameter, and control
messages ordinary where the benefit is less clear or the
confidentiality concern is more direct. Is this the right future-proof
scope?
2. Stream lifetime. Ending streams before ReadyForQuery and CopyDone or
CopyFail keeps transaction and COPY boundaries explicit, so a pooler
can remap connections without decompressing and recompressing data. Are
these the right boundaries?
3. Policy. The server GUC uses the familiar off | compression-method
pattern. The client combines that with PostgreSQL's opportunistic or
required connection policy: off | prefer | zstd, where naming the
method requires it. Is this interface sufficient?
Thank you!
Best regards, Andrey Borodin.
[0]
https://www.postgresql.org/message-id/flat/aad16e41-b3f9-e89d-fa57-fb4c694bec25%40postgrespro.ru
[1]
https://www.postgresql.org/message-id/flat/ABAA09C6-BB95-47A5-890D-90353533F9AC%40yandex-team.ru
[2]
https://www.postgresql.org/message-id/flat/CACzsqT4cJG0kaCbz24Sd%3DGAEgiQDpzU8yuD6vF25zo870%2B3M6g%40mail.gmail.com
[3] https://www.postgresql.org/docs/devel/protocol-flow.html#PROTOCOL-EXTENSIONS
Hi,
I find this patch interesting and created a script (attached) that tries
to measure the effect of the compression. It uses the ss command and
can be run on a linux Postgres server or on a remote linux machine. It
uses psql. It can use a SELECT statement or \COPY TO or \COPY FROM.
Here is some sample output (using SELECT):
script started on the PostgreSQL server (RHEL 10 VM):
--- without compression ---
psql network data received: 60731.93 KB
psql network data sent: 0.12 KB
runtime: 18.28 seconds
--- with zstd compression ---
psql network data received: 110.19 KB
psql network data sent: 0.15 KB
runtime: 18.32 seconds
script started on another VM on the same host as the PostgreSQL server
(RHEL 9):
--- without compression ---
psql network data received: 60731.93 KB
psql network data sent: 0.12 KB
runtime: 20.03 seconds
--- with zstd compression ---
psql network data received: 110.19 KB
psql network data sent: 0.15 KB
runtime: 18.74 seconds
script started on another system (laptop) on the same LAN as the
PostgreSQL server (Ubuntu 26):
--- without compression ---
psql network data received: 60731.93 KB
psql network data sent: 0.12 KB
runtime: 25.29 seconds
--- with zstd compression ---
psql network data received: 110.19 KB
psql network data sent: 0.15 KB
runtime: 9.78 seconds
We can also reduce the network speed of the network adapter by running
the following command on the postgres server:
tc qdisc replace dev enp0s3 root netem rate 10mbit
we then get the following data when we run the script on a remote machine:
--- without compression ---
psql network data received: 60731.93 KB
psql network data sent: 0.12 KB
runtime: 70.98 seconds
--- with zstd compression ---
psql network data received: 110.19 KB
psql network data sent: 0.15 KB
runtime: 18.98 seconds
To reset the network config: tc qdisc replace dev enp0s3 root fq_codel
Regards
Jochen
--
Mit freundlichen Grüßen
Jochen Bandhauer
+49 (0)176 29390012
www.jbitc.de
#!/usr/bin/bash
#
# Version 1.0 03.09.2026
#
set -euo pipefail
# ===== CONFIG =====
PGHOST="lin8"
PGPORT="5432"
PGDATABASE="mydb"
PGUSER="postgres"
PGPASSWORD="changeme"
TABLE="compression_test"
ROWS=10000
REPEAT_STR="ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789"
REPEAT_CNT=100
export PGPASSWORD
PSQL_ARGS=("-c" "SELECT * FROM $TABLE;")
#PSQL_ARGS=("-c" "\COPY $TABLE TO '/dev/null'")
#PSQL_ARGS=("-c" "BEGIN;" "-c" "TRUNCATE TABLE $TABLE;" "-c" "\COPY $TABLE FROM
'/tmp/data.dmp';" "-c" "COMMIT;")
# ===== Helper (unchanged – used only for setup) =====
run_psql() { psql -h "$PGHOST" -p "$PGPORT" -d "$PGDATABASE" -U "$PGUSER" -c
"$1" -t -A; }
# ===== Ensure test data (unchanged) =====
if [[ $(run_psql "SELECT EXISTS (SELECT 1 FROM information_schema.tables WHERE
table_name='$TABLE');") != "t" ]]; then
echo "Creating table $TABLE..."
run_psql "CREATE TABLE $TABLE (id SERIAL PRIMARY KEY, payload TEXT);
INSERT INTO $TABLE (payload) SELECT repeat('$REPEAT_STR',
$REPEAT_CNT) FROM generate_series(1,$ROWS);" >/dev/null
run_psql "\COPY $TABLE TO '/tmp/data.dmp'" >/dev/null
else
echo "Table $TABLE already exists."
fi
# ===== Measure =====
measure() {
local label="$1"
local comp="$2"
echo "--- $label ---"
local time_file=$(mktemp)
local ss_file=$(mktemp)
# Resolve PGHOST to an IP address (numeric)
local PGHOST_IP=$(getent hosts "$PGHOST" | awk '{print $1}' | head -1)
if [[ -z "$PGHOST_IP" ]]; then
# fallback: use host command (if getent not available)
PGHOST_IP=$(host "$PGHOST" 2>/dev/null | head -1 | awk '{print $NF}')
fi
if [[ -z "$PGHOST_IP" ]]; then
# final fallback: use the hostname as given (might not match ss -n)
PGHOST_IP="$PGHOST"
fi
# Run psql with the query, capture time, and write ss output to file
{
time -p PGPASSWORD=$PGPASSWORD PGCOMPRESSION=$comp \
psql -h "$PGHOST" -p "$PGPORT" -d "$PGDATABASE" -U "$PGUSER" \
"${PSQL_ARGS[@]}" \
-c "\! sleep 1" \
-c "\! ss -t -i -p -n > $ss_file"
} 2> "$time_file" > /dev/null
# Now extract counters from the ss file using the remote endpoint
local psql_block=$(grep -A10 "$PGHOST_IP:$PGPORT" "$ss_file" | grep -A1
"psql")
local bytes_received=$(echo "$psql_block" | grep -oE
'bytes_received:[0-9]+' | head -1 | cut -d: -f2)
local bytes_sent=$(echo "$psql_block" | grep -oE 'bytes_sent:[0-9]+' | head
-1 | cut -d: -f2)
local runtime=$(grep '^real' "$time_file" | awk '{print $2}')
# Print results
echo " psql network data received: $(awk "BEGIN {printf \"%.2f\",
${bytes_received:-0}/1024}") KB"
echo " psql network data sent: $(awk "BEGIN {printf \"%.2f\",
${bytes_sent:-0}/1024}") KB"
echo " runtime: ${runtime:-N/A} seconds"
echo
# Clean up
rm -f "$time_file" "$ss_file"
}
# ===== Run measurements =====
measure "without compression" "off"
measure "with zstd compression" "zstd"
BEGIN:VCARD
VERSION:4.0
N:Bandhauer;Jochen;;;
FN:Jochen Bandhauer
EMAIL;PREF=1;TYPE=work:[email protected]
URL:https://www.jbitc.de
TEL;TYPE=work;VALUE=TEXT:+4917629390012
TZ:Europe/Berlin
END:VCARD
