pgx v5 Pipeline Concurrency & Batch Buffer Saturation Guardrail

--- description: pgx v5 Pipeline Concurrency & Batch Buffer Saturation Guardrail - Mandates deterministic chunking, strict result-drain loops, and explicit context timeouts when using jackc/pgx/v5 Batch and Pipeline APIs. Unbounded batch queuing triggers full-duplex TCP socket buffer deadlocks (SO_SNDBUF/SO_RCVBUF saturation) between Go runtimes and PostgreSQL, leading to silent connection-pool starvation. globs: **/*.go alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/pgx-v5-pipeline-batch-saturation-guardrail" --- # pgx v5 Pipeline Concurrency & Batch Buffer Saturation Guardrail > **Severity**: CRITICAL | **Category**: backend > **Anti-Pattern Warning**: Unbounded pgx.Batch Queueing and Deferred Result Consumption Inducing Full-Duplex Socket Deadlocks ## System Constraints You are a Principal Backend Architect enforcing strict execution boundaries on jackc/pgx/v5 batching and pipelining. Unbounded batches or deferred result consumption across TCP sockets inevitably trigger full-duplex socket buffer deadlocks and pool starvation under high throughput. Enforce deterministic chunk sizes (<= 250), immediate batch result closure patterns, and context deadlines. ## Strict Guidelines - Engineers MUST partition large datasets into deterministic chunks (e.g., 100 to 250 statements) before enqueuing them into a pgx.Batch. - Engineers MUST ALWAYS defer BatchResults.Close() immediately after SendBatch to guarantee pool connection return and prevent leakages. - Engineers MUST sequentially consume all BatchResults (via Exec, Query, or QueryRow) corresponding to every queued query; abandoning or prematurely exiting result iteration deadlocks the underlying connection. - Engineers MUST NOT execute unbounded slices or slice sizes determined by raw client inputs directly inside a single pgx.Batch. - Code SHOULD enforce an explicit context.WithTimeout per batch iteration to break connection deadlocks if the socket buffer saturates. - Engineers MUST verify the error returned by BatchResults.Close(), as deferred constraint violations and socket transmission errors only surface upon final buffer flushing. ## Bad vs Good Implementations ### Unbounded Batch Queueing and Incomplete Draining (Deadlock & Starvation) #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Queuing thousands of statements in one batch without chunking saturates the kernel TCP send and receive socket buffers. If PostgreSQL fills its receive buffer while trying to send execution results back over a saturated client receive buffer, a TCP deadlock halts the process. In addition, abandoning the batch on error without closing BatchResults leaves the pool connection permanently locked. ```go package repository import ( "context" "github.com/jackc/pgx/v5/pgxpool" ) type Event struct { ID string Payload []byte } func IngestEventsUnbounded(ctx context.Context, pool *pgxpool.Pool, events []Event) error { // ANTI-PATTERN: Enqueuing thousands of items into a single pgx.Batch fills // kernel TCP send buffers (SO_SNDBUF). Postgres responds with data filling // SO_RCVBUF, causing bidirectional TCP window zero-stalls and deadlocks. batch := &pgxpool.Batch{} for _, ev := range events { batch.Queue("INSERT INTO events (id, payload) VALUES ($1, $2)", ev.ID, ev.Payload) } br := pool.SendBatch(ctx, batch) // ANTI-PATTERN: Missing defer br.Close(). If an error occurs in the loop, // the connection remains occupied and unreturned to pgxpool. for range events { _, err := br.Exec() if err != nil { return err // Leaks underlying pool connection } } return nil } ``` #### Production Standard (Correct) > **Standard**: Splitting workloads into explicit chunks (max 250 queries) ensures that network socket buffers are never overwhelmed, precluding zero-window deadlocks. Wrapping execution in an explicit timeout context bounds connection occupancy. Deferring `br.Close()` immediately ensures proper resource reclamation on error, and asserting `br.Close()` at the end captures deferred protocol-level errors. ```go package repository import ( "context" "fmt" "time" "github.com/jackc/pgx/v5" "github.com/jackc/pgx/v5/pgxpool" ) type Event struct { ID string Payload []byte } const maxBatchChunkSize = 250 func IngestEventsChunked(ctx context.Context, pool *pgxpool.Pool, events []Event) error { // INVARIANT: Bounded chunk sizes prevent TCP socket-buffer saturation for i := 0; i < len(events); i += maxBatchChunkSize { end := i + maxBatchChunkSize if end > len(events) { end = len(events) } chunk := events[i:end] if err := executeBatchChunk(ctx, pool, chunk); err != nil { return fmt.Errorf("executing batch chunk at offset %d: %w", i, err) } } return nil } func executeBatchChunk(ctx context.Context, pool *pgxpool.Pool, chunk []Event) error { chunkCtx, cancel := context.WithTimeout(ctx, 30*time.Second) defer cancel() batch := &pgx.Batch{} for _, ev := range chunk { batch.Queue("INSERT INTO events (id, payload) VALUES ($1, $2)", ev.ID, ev.Payload) } br := pool.SendBatch(chunkCtx, batch) // INVARIANT: Always defer br.Close() to drain unread responses and return conn to pool defer br.Close() for range chunk { if _, err := br.Exec(); err != nil { return fmt.Errorf("executing statement in batch: %w", err) } } // INVARIANT: Must check br.Close() to capture deferred constraint violations or transmission errors if err := br.Close(); err != nil { return fmt.Errorf("closing batch results: %w", err) } return nil } ```

Architectural Specification & Invariant Rationale

Architectural Invariant Design: pgx v5 Pipeline Concurrency & Batch Buffer Saturation Guardrail

pgx v5 Pipeline Concurrency & Batch Buffer Saturation Guardrail defines an industrial-grade behavioral standard for backend engineering. Mandates deterministic chunking, strict result-drain loops, and explicit context timeouts when using jackc/pgx/v5 Batch and Pipeline APIs. Unbounded batch queuing triggers full-duplex TCP socket buffer deadlocks (SO_SNDBUF/SO_RCVBUF saturation) between Go runtimes and PostgreSQL, leading to silent connection-pool starvation. Modern software architectures require strict adherence to deterministic patterns to guarantee high availability, maintainability, and zero security vulnerabilities.

Framework Paradigm Shift & Drift MechanicsLLMs are trained on millions of open-source repositories containing outdated code, legacy idioms, and unpatched antipatterns. When coding assistants generate implementations for this stack, they default to common statistical tokens: Unbounded pgx.Batch Queueing and Deferred Result Consumption Inducing Full-Duplex Socket Deadlocks Without an explicit system instruction file, developers spend hours debugging silent failures.

Failure Modes & Production Blast Radius

Hazard Analysis: What Breaks Without This Rule

The following failure modes have been cataloged from hundreds of hours of production code reviews where AI assistants produced non-compliant implementations:

Failure Mode	AI Symptom (The Anti-Pattern)	Severity	Production Impact & Remedy
AI Hallucination & Deprecation Drift	Unbounded pgx.Batch Queueing and Deferred Result Consumption Inducing Full-Duplex Socket Deadlocks	Critical	Violating this industrial standard introduces regression bugs, security risks, and unexpected runtime failures in production. Enforce explicit behavioral system directives across all AI coding assistants in your repository.
Silent Architecture Inconsistency	AI assistants produce mixed paradigms across files, creating fragmented codebase conventions.	Medium	Increases cognitive load during code reviews and leads to maintenance debt across engineering teams. Pin project conventions in .cursor/rules, CLAUDE.md, and AGENTS.md so all team agents follow identical guardrails.

Architectural Directives in Detail

Step-by-Step Production Directives

Each rule constraint below is injected into the AI agent's system prompt to establish deterministic boundaries:

Engineers MUST partition large datasets into deterministic chunks (e.g., 100 to 250 statements) before enqueuing them into a pgx.Batch.

Rationale:AI models frequently generate unconstrained code here because statistical language distributions lack project-specific architectural context. Mandating this directive ensures that generated code adheres to verified industrial production requirements.