PostgreSQL

PostgreSQL Connection Closed Unexpectedly During Large Transaction

A connection dropping specifically during a large, long-running transaction usually comes down to one of a few specific limits being hit — a statement timeout, memory exhaustion from the transaction's own size, or a network intermediary silently closing what it perceives as an idle connection during a period of the transaction that involves less active data transfer.

The Problem

A large transaction — a bulk data migration, an extensive update, a large batch insert — fails partway through with:

FATAL:  terminating connection due to administrator command
server closed the connection unexpectedly
    This probably means the server terminated abnormally
    before or while processing the request.

psycopg2.OperationalError: server closed the connection unexpectedly

Smaller transactions of the same general type complete without issue, pointing specifically at the transaction's size or duration as the relevant factor.

Why It Happens

Several distinct limits or behaviors can specifically affect large, long-running transactions differently than typical smaller ones:

  • A configured statement_timeout or idle_in_transaction_session_timeout on the server killing the connection once the transaction exceeds the allowed duration, as an administrative safeguard against runaway queries
  • The transaction consuming enough memory (particularly with a very large number of rows being processed, or large individual row values) to hit work_mem or overall system memory limits, causing the backend process to be terminated by the OS or by PostgreSQL's own internal limits
  • A network intermediary (a load balancer, a firewall, certain cloud networking configurations) silently dropping a connection it perceives as idle, if the transaction involves a phase with no active network traffic for an extended period, even though the transaction itself is still legitimately in progress server-side
  • The PostgreSQL server itself running out of disk space partway through the transaction, particularly relevant for very large inserts or updates generating substantial WAL (write-ahead log) data

The Fix

First, check PostgreSQL's own logs for the specific reason the connection was terminated, rather than only seeing the generic client-side error, which doesn't distinguish between these different underlying causes:

tail -100 /var/log/postgresql/postgresql-*.log

Check current timeout settings that might be affecting long-running transactions:

SHOW statement_timeout;
SHOW idle_in_transaction_session_timeout;

If a timeout is genuinely too short for legitimately long operations, adjust it specifically for the session running the large transaction, rather than changing the global default for every connection:

SET statement_timeout = '30min';
-- then run your large transaction within this same session

For very large operations, consider breaking them into smaller, separately committed chunks rather than one enormous transaction, which reduces exposure to any single timeout or memory limit, and also means a failure partway through loses less progress than one giant all-or-nothing transaction would:

DO $$
DECLARE
    batch_size INTEGER := 10000;
    rows_affected INTEGER;
BEGIN
    LOOP
        UPDATE large_table SET processed = true
        WHERE id IN (SELECT id FROM large_table WHERE processed = false LIMIT batch_size);
        GET DIAGNOSTICS rows_affected = ROW_COUNT;
        COMMIT;
        EXIT WHEN rows_affected = 0;
    END LOOP;
END $$;

If a network-level idle timeout is the suspected cause (particularly plausible for transactions with a phase of heavy computation but minimal network traffic, or transactions run over a connection through a load balancer or proxy), configure TCP keepalive settings so the connection sends periodic traffic even during quieter phases, preventing an intermediary from perceiving it as abandoned:

# Client-side connection string parameter (varies by client library)
keepalives=1
keepalives_idle=30
keepalives_interval=10
keepalives_count=3

Check disk space on the PostgreSQL server directly, particularly the location housing the WAL directory, since a very large transaction generates substantial WAL data, and running out of space here specifically can terminate an in-progress transaction with an error that might otherwise look like a generic connection drop:

df -h /var/lib/postgresql/*/main/pg_wal

Still Not Working?

If the connection drop happens at a consistent, predictable duration regardless of what specific operation the transaction is performing at that moment, suspect a network-level timeout enforced by an intermediary you don't directly control (a managed database proxy, a cloud load balancer with its own connection duration limits) rather than anything configurable within PostgreSQL itself. In that case, the fix may require either configuring that specific intermediary's own settings (if you have access to do so) or restructuring the workload into genuinely shorter transactions that complete comfortably within whatever external duration limit exists, since some managed infrastructure limits aren't adjustable from the PostgreSQL configuration side at all.

You might also like