Postgres 17 Concurrent Hash Index Lock-Starvation Guardrail

--- description: Postgres 17 Concurrent Hash Index Lock-Starvation Guardrail - Enforces strict lock-timeout limits, dead-tuple thresholds, and execution budgets on PostgreSQL 17 concurrent hash index workloads. Prevents high-throughput writer processes from monopolizing bucket-level buffer locks and starving autovacuum cleanup routines, preventing severe table bloat and query latency degradation. globs: src/db/**/*.ts, src/repositories/**/*.ts, src/services/**/*.ts, migrations/**/*.sql alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/postgres-17-hash-index-lock-starvation-guardrail" --- # Postgres 17 Concurrent Hash Index Lock-Starvation Guardrail > **Severity**: CRITICAL | **Category**: backend > **Anti-Pattern Warning**: Unbounded Batch Ingestion Over Hash Indexes Starving Autovacuum Buffer Access ## System Constraints You are a Principal Database Reliability Engineer and Performance Architect enforcing PostgreSQL 17 concurrent hash index lock-starvation guardrails. Hash indexes provide O(1) lookups for equality operations but use bucket-level locks that can starve autovacuum processes during concurrent write bursts, accelerating index page bloat. You MUST enforce deterministic statement and lock timeouts, dead-tuple ceiling checks, and chunked write cadences for any repository or service operating on tables indexed with HASH access methods. ## Strict Guidelines - Engineers MUST ALWAYS enforce explicit `lock_timeout` (<= 2000ms) and `statement_timeout` on database sessions executing concurrent DML on tables utilizing HASH indexes. - Engineers MUST NOT execute continuous, un-chunked batch INSERT/UPDATE/DELETE mutations against hash-indexed tables without yields that allow autovacuum to acquire bucket locks. - Services MUST query `pg_stat_user_tables` to inspect `n_dead_tup` before launching high-throughput batch writes, shedding load or backing off if dead tuples exceed pre-allocated budget ceilings (e.g., dead tuple ratio > 0.08). - Code SHOULD configure table-level storage parameters `autovacuum_vacuum_cost_limit = 2000` and `autovacuum_vacuum_cost_delay = 2` on write-heavy hash-indexed tables to enable autovacuum to complete rapidly between writer lock releases. - Components NEVER execute raw long-running transactions that pin hash index pages while interleaving external asynchronous network I/O calls. ## Bad vs Good Implementations ### Batch Ingestion on Hash-Indexed Table #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Executing thousands of individual inserts inside a long-lived transaction without lock timeouts holds bucket pins and heavy lock contention on Postgres 17 hash index pages. Autovacuum is repeatedly canceled or starved, causing dead-tuple accumulation and severe bucket expansion. ```typescript import { Pool } from 'pg'; export async function ingestDeviceHeartbeats(pool: Pool, records: Array<{ deviceId: string; payload: any }>) { const client = await pool.connect(); try { // ANTI-PATTERN: No session timeouts, single unbounded transaction over a table // indexed via `USING HASH (device_id)`. Continuous locks starve autovacuum. await client.query('BEGIN'); for (const record of records) { await client.query( 'INSERT INTO device_telemetry (device_id, payload) VALUES ($1, $2)', [record.deviceId, record.payload] ); } await client.query('COMMIT'); } catch (error) { await client.query('ROLLBACK'); throw error; } finally { client.release(); } } ``` #### Production Standard (Correct) > **Standard**: Implements chunking, enforces `SET LOCAL lock_timeout`, introduces an explicit yield between batches to prevent bucket pin monopolization, and verifies that dead-tuple ceilings are respected to avoid starving vacuum workers on Postgres 17 hash index pages. ```typescript import { Pool, PoolClient } from 'pg'; interface IngestionOptions { batchSize?: number; maxDeadTupleRatio?: number; lockTimeoutMs?: number; } export async function ingestDeviceHeartbeats( pool: Pool, records: Array<{ deviceId: string; payload: Record<string, unknown> }>, options: IngestionOptions = {} ): Promise<void> { const { batchSize = 500, maxDeadTupleRatio = 0.08, lockTimeoutMs = 1500 } = options; const client: PoolClient = await pool.connect(); try { // Check dead tuple budget ceiling before initiating write workload const bloatCheck = await client.query(` SELECT n_dead_tup, n_live_tup FROM pg_stat_user_tables WHERE relname = 'device_telemetry'; `); if (bloatCheck.rows.length > 0) { const { n_dead_tup, n_live_tup } = bloatCheck.rows[0]; const deadRatio = Number(n_live_tup) > 0 ? Number(n_dead_tup) / Number(n_live_tup) : 0; if (deadRatio > maxDeadTupleRatio) { throw new Error(`Write throttled: Dead-tuple ratio (${deadRatio.toFixed(3)}) exceeds budget (${maxDeadTupleRatio}). Autovacuum requires window.`); } } // Chunked ingestion with strict lock and statement timeouts for (let i = 0; i < records.length; i += batchSize) { const chunk = records.slice(i, i + batchSize); await client.query('BEGIN'); await client.query(`SET LOCAL lock_timeout = '${lockTimeoutMs}ms'`); await client.query(`SET LOCAL statement_timeout = '5000ms'`); const values: any[] = []; const placeholders = chunk.map((r, idx) => { values.push(r.deviceId, JSON.stringify(r.payload)); return `($${idx * 2 + 1}, $${idx * 2 + 2})`; }).join(','); await client.query( `INSERT INTO device_telemetry (device_id, payload) VALUES ${placeholders}`, values ); await client.query('COMMIT'); // Explicit cooperative yield to permit autovacuum bucket maintenance if (i + batchSize < records.length) { await new Promise((resolve) => setTimeout(resolve, 50)); } } } catch (err) { await client.query('ROLLBACK').catch(() => {}); throw err; } finally { client.release(); } } ```

Architectural Specification & Invariant Rationale

Architectural Invariant Design: Postgres 17 Concurrent Hash Index Lock-Starvation Guardrail

Postgres 17 Concurrent Hash Index Lock-Starvation Guardrail defines an industrial-grade behavioral standard for backend engineering. Enforces strict lock-timeout limits, dead-tuple thresholds, and execution budgets on PostgreSQL 17 concurrent hash index workloads. Prevents high-throughput writer processes from monopolizing bucket-level buffer locks and starving autovacuum cleanup routines, preventing severe table bloat and query latency degradation. 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 Batch Ingestion Over Hash Indexes Starving Autovacuum Buffer Access 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 Batch Ingestion Over Hash Indexes Starving Autovacuum Buffer Access	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 ALWAYS enforce explicit `lock_timeout` (<= 2000ms) and `statement_timeout` on database sessions executing concurrent DML on tables utilizing HASH indexes.

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.