Why do I get "server closed the connection unexpectedly"?

That message comes from the client library, not from the database. It means the socket disappeared in the middle of a conversation, and it carries no diagnosis because the client has none. Three families account for nearly all of them: the backend serving you died, something in the network path dropped an idle socket, or the whole server restarted underneath you. The server log distinguishes them immediately.

Reading the three signatures

A backend killed by the operating system for memory is the loudest. The supervisor notices a child exited abnormally, and then it deliberately disconnects every other session and runs recovery, because it cannot know what the dead process left in shared memory. So one oversized query produces a cluster-wide blip and a wave of these errors in applications that had nothing to do with it. In the log it is unmistakable: an abnormal exit followed by every session being told to reconnect.

A restart looks similar but arrives cleanly, with shutdown and startup lines around it.

The third family leaves no server-side trace at all, and that absence is the diagnosis. A firewall, load balancer or address translation device between the client and the server silently discards a connection it considers idle, and neither end finds out until the next packet. The tell is that the failures cluster after quiet periods and never during sustained traffic, and that the database log is serene throughout.

A session deliberately terminated by an administrator is a fourth case and does not produce this wording, since the server says goodbye properly first. Is it safe to kill a Postgres query covers what the polite version looks like from the client side.

What to change once you know which it is

For the idle-socket case, set keepalives shorter than the shortest timeout in the path, on the server as well as the client, and cap the maximum lifetime of a pooled connection below the same number. A pool that recycles on a schedule never hands out a socket a middlebox has already forgotten.

For memory, the arithmetic is the working memory a query may allocate multiplied by how many plan nodes and how many sessions can do it at once, which overshoots the naive estimate badly. Reducing concurrency helps more than reducing the per-node budget.

For the reconnect wave afterwards, the pattern to recognise is a connection storm: every application retrying at once turns a two-second blip into a minute of refused logins. Backoff in the client and a pool in front of the database are the two defences, and connection pooling and PgBouncer covers the second.