psql Timing Over a VPN: Separating Network Wait From Query Work

Your report finishes in about a second on the database host and four over the VPN, and the number psql prints points straight at the database. That number is a client-side stopwatch, not a server measurement. It covers the statement on its way out, the wait for a reply, and the rows on their way back. Splitting those three apart is the only way to know which side to tune.

What the psql timer really measures

\timing on starts a stopwatch just before the client writes the statement to the socket and stops when the last row of that result has been read. The server's own execution sits somewhere inside that window, and so does every millisecond the packets spend in the tunnel. Nothing in the output separates them. EXPLAIN also prints a number, but that one is produced by the server and excludes the client's wait. Two numbers, two different questions.

Start one session, leave timing on, and run the statement you care about from inside it. Reusing a session keeps the connect cost out of the first measurement.

psql "host=db.internal port=5432 dbname=appdb user=app" \
  -c "\timing on" \
  -c "select count(*) from orders where created_at > now() - interval '1 day';"

The figure still includes the result transfer, which is what you want here. It no longer includes the connect and authentication exchange that a fresh invocation would add.

Compare one statement across three paths

Run the same statement over the Unix socket, over the LAN address and over the VPN host name, and compare the three. If only the last one is slow, the query is not the variable.

Each invocation connects first and only then times the statement, so the connect cost stays out of the number while the transfer stays in. Keep the WHERE clause, the row count and the client machine identical, or you are comparing workloads instead of paths.

psql -h /var/run/postgresql -U app -d appdb -c "\timing on" -c "select count(*) from orders;"
psql -h 10.40.0.12 -U app -d appdb -c "\timing on" -c "select count(*) from orders;"
psql -h db.vpn.internal -U app -d appdb -c "\timing on" -c "select count(*) from orders;"

On a LAN the difference is usually small enough to ignore; across a VPN it often is not. Sorting the three results then tells you which wait to attack.

A slow local socket means server work or planning. A slow LAN result means the query or the volume it moves. Only the VPN result being slow means latency, MTU or available bandwidth on the path, and no index changes that.

Pull the server's own execution time

The server can report how long it spent, and that figure is the one to hold against the client's. EXPLAIN with ANALYZE runs the statement and prints planning and execution time inside the plan, while BUFFERS adds cache behaviour that often explains a warm run against a cold one. Do not point it at writing statements unless you intend to write. On a read-only replica it is the safest place to begin, and the psql reference documents the timer's client-side scope.

For statements the application already runs, pg_stat_statements aggregates server-side execution without re-running anything. The rows are per normalized query, so one entry can cover several call sites.

select calls, round(mean_exec_time::numeric, 2) as mean_ms, rows
from pg_stat_statements
order by mean_exec_time desc
limit 5;

Column names differ by release: PostgreSQL 13 and later expose mean_exec_time, while older versions call it mean_time, so check with \d pg_stat_statements if that query errors. These values exclude planning and exclude the trip to your client. That exclusion is precisely why they are useful for the comparison.

Count round trips, not just milliseconds

A report built from two hundred small dependent statements multiplies whatever latency the path adds by two hundred. A single statement returning a million rows pays that latency once and then pays bandwidth for a long time. Both look identical in a client timer, and they need opposite fixes.

Measure the statement count instead of estimating it. Read the total, run the report once, then read it again and subtract.

select sum(calls) as calls from pg_stat_statements;
-- run the report here
select sum(calls) as calls from pg_stat_statements;

If the delta is large and each statement is cheap on the server, batching is the fix that pays. Combine dependent queries, use one set-based statement, or drive the loop from a cursor. In interactive psql, \set FETCH_COUNT 1000 makes a large result arrive in batches and changes how the transfer and client memory behave. It does not reduce server work, so it will not rescue a slow query.

Check the tunnel for retransmits and MTU stalls

When small statements are fast and large results stall halfway, the query is usually fine and the path is not. A tunnel whose MTU is lower than the underlying link, combined with blocked ICMP, produces exactly that pattern: the first packets arrive, the big ones vanish, and the transfer hangs until it retries. Client-side retransmission counters show it.

Watch the socket and the kernel counters while a deliberately slow transfer runs. Retransmit totals are cumulative, so note them before and after rather than reading one sample.

ss -tin dst 10.40.0.12
netstat -s | grep -iE "retransmit|segments retransmitted"
ping -M do -s 1400 -c 3 10.40.0.12

Retransmits that climb during the transfer, together with a ping that succeeds at 1400 bytes but fails at 1472, point at an MTU problem rather than a database problem. Lowering the tunnel interface MTU or clamping MSS at the gateway is the usual correction, and the same three commands confirm it afterwards. If retransmits stay flat while the transfer is still slow, look at the bandwidth the tunnel is allowed rather than at loss.

A short sequence covers the next slow report: compare one statement locally, on the LAN and over the VPN; read server execution from EXPLAIN or pg_stat_statements; count calls before and after a single run; and watch retransmits while the large result moves. Each step rules out one side of the path. None of them requires guessing which component to restart.