Postgres 17 Zero-Lock DDL Invariants and Microsecond Partition Pruning

Q: How does adopting this rule prevent AI hallucinations?

This rule explicitly declares what the AI must avoid (Unbounded AccessExclusiveLock Acquisition and Dynamic Partition Filter Degeneration) and prescribes verified positive examples. Grounding the LLM with negative and positive pairs eliminates guesswork.

Q: Which AI coding environments are supported?

This specification is multi-platform compatible: Cursor (.cursor/rules/*.mdc & .cursorrules), Anthropic Claude Code (CLAUDE.md), Windsurf (.windsurfrules), Codex / Cline (AGENTS.md), and GitHub Copilot (.github/copilot-instructions.md).

Q: Can I combine this rule with other technology stacks?

Yes. Use the Interactive Stack Builder or download an Agent Pack to merge this rule with other frontend, backend, database, security, and DevOps rules without syntax collision.

--- description: Postgres 17 Zero-Lock DDL Invariants and Microsecond Partition Pruning - Enforces strict DDL migration patterns and query structures in PostgreSQL 17 to eliminate table-level ExclusiveLocks on high-throughput ledgers. Guarantees run-time and compile-time partition pruning using immutable routing keys while avoiding lock-queue starvation via explicit timeout backoffs. globs: src/db/**/*.ts, migrations/**/*.sql, src/repositories/**/*.ts, src/ledger/**/*.ts alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/postgres-17-zero-lock-partition-pruning-guardrail" --- # Postgres 17 Zero-Lock DDL Invariants and Microsecond Partition Pruning > **Severity**: CRITICAL | **Category**: backend > **Anti-Pattern Warning**: Unbounded AccessExclusiveLock Acquisition and Dynamic Partition Filter Degeneration ## System Constraints You are a Principal Database Engineer enforcing Postgres 17 zero-lock transactional invariants and deterministic partition pruning. You must reject any DDL migration executing without sub-second lock_timeout guards, block any migration executing concurrent indexing or constraint validation inside an ambient transaction block, and forbid SQL queries where the partition key expression prevents compile-time or run-time partition pruning. ## Strict Guidelines - Engineers MUST ALWAYS prepend DDL operations with explicit transaction-level lock guards: `SET LOCAL lock_timeout = '2s';` and `SET LOCAL statement_timeout = '5s';`. - Engineers MUST NEVER execute `CREATE INDEX CONCURRENTLY` or `ATTACH PARTITION` without preliminary unvalidated constraints (`NOT VALID` followed by `VALIDATE CONSTRAINT` in separate transactions). - Code MUST ALWAYS supply exact scalar partition key predicates matching the partitioned boundary definition (e.g., date-truncation wrappers on columns inside WHERE clauses are strictly prohibited). - Services MUST route write transactions using composite keys consisting of `(partition_id, entity_id)` to ensure single-partition execution without cross-partition fanout locks. - Components NEVER execute raw DDL inside application ORMs; all DDL must execute via managed zero-lock migration runners with exponential backoff on lock timeouts. ## Bad vs Good Implementations ### Zero-Lock Partition Attachment and Index Creation in TypeScript Migrations #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Attempting to run CREATE INDEX CONCURRENTLY inside a transaction block raises Postgres exception 25001. Attaching a partition without pre-validated CHECK constraints acquires an AccessExclusiveLock across the entire parent partitioned table to scan and validate range invariants, stalling production ledgers. ```typescript import { Client } from 'pg'; export async function migrateLedger(client: Client): Promise<void> { // Anti-pattern: Holds AccessExclusiveLock indefinitely, blocking all ledger traffic; // executes concurrent index inside transaction block (fails in Postgres). await client.query('BEGIN'); await client.query(` CREATE TABLE ledger_entries_2025_q1 PARTITION OF ledger_entries FOR VALUES FROM ('2025-01-01') TO ('2025-04-01'); CREATE INDEX CONCURRENTLY idx_ledger_2025_q1_user ON ledger_entries_2025_q1 (user_id); `); await client.query('COMMIT'); } ``` #### Production Standard (Correct) > **Standard**: Isolates schema creation, validates range constraints on the standalone child partition before attachment, and uses a 1-second lock_timeout during ATTACH PARTITION. Because the constraint is pre-validated, Postgres 17 skips table-scanning the parent, preventing lock queue starvation. ```typescript import { Client } from 'pg'; export async function migrateLedger(client: Client): Promise<void> { // 1. Create standalone staging table with matching partition bounds await client.query(` CREATE TABLE IF NOT EXISTS ledger_entries_2025_q1 ( id UUID NOT NULL, created_at TIMESTAMPTZ NOT NULL, user_id UUID NOT NULL, amount NUMERIC(18, 4) NOT NULL ); `); // 2. Add NOT VALID check constraint to avoid blocking parent table scans await client.query(` ALTER TABLE ledger_entries_2025_q1 ADD CONSTRAINT check_bounds CHECK (created_at >= '2025-01-01 00:00:00+00' AND created_at < '2025-04-01 00:00:00+00') NOT VALID; `); // 3. Validate constraint asynchronously without holding AccessExclusiveLock await client.query(`ALTER TABLE ledger_entries_2025_q1 VALIDATE CONSTRAINT check_bounds;`); // 4. Build index concurrently outside of explicit transaction blocks await client.query(` CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_ledger_2025_q1_user_created ON ledger_entries_2025_q1 (user_id, created_at); `); // 5. Attach partition under strict lock timeout to guarantee sub-millisecond acquisition await client.query(` SET lock_timeout = '1s'; ALTER TABLE ledger_entries ATTACH PARTITION ledger_entries_2025_q1 FOR VALUES FROM ('2025-01-01 00:00:00+00') TO ('2025-04-01 00:00:00+00'); RESET lock_timeout; `); } ``` ### Deterministic Partition Pruning Execution in Query Repositories #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Wrapping the partition routing key (created_at) inside date_trunc() obfuscates the range boundaries from the PostgreSQL query planner. The engine fails to perform static or run-time partition pruning, executing an exhaustive parallel sequential scan across all historical partitions. ```typescript import { Pool } from 'pg'; export async function getLedgerTransactions( pool: Pool, userId: string, filterDate: Date ): Promise<any[]> { // Anti-pattern: Function invocation on partitioned column 'created_at' in the WHERE clause // completely disables Postgres partition pruning, causing full-table scans across all partitions. const query = ` SELECT id, user_id, amount, created_at FROM ledger_entries WHERE user_id = $1 AND date_trunc('day', created_at) = date_trunc('day', $2::timestamptz); `; const { rows } = await pool.query(query, [userId, filterDate.toISOString()]); return rows; } ``` #### Production Standard (Correct) > **Standard**: Providing explicit lower and upper bounds directly on the partitioned column allows PostgreSQL 17's planner to calculate partition intersection instantly, ensuring single-partition scan execution with zero overhead. ```typescript import { Pool } from 'pg'; export async function getLedgerTransactions( pool: Pool, userId: string, filterDate: Date ): Promise<any[]> { // Invariant: Exact non-wrapped range bounds allow PG 17 planner to achieve microsecond // pruning down to the specific partition during query planning and execution. const startOfDay = new Date(filterDate); startOfDay.setUTCHours(0, 0, 0, 0); const endOfDay = new Date(filterDate); endOfDay.setUTCHours(23, 59, 59, 999); const query = ` SELECT id, user_id, amount, created_at FROM ledger_entries WHERE user_id = $1 AND created_at >= $2::timestamptz AND created_at <= $3::timestamptz; `; const { rows } = await pool.query(query, [userId, startOfDay.toISOString(), endOfDay.toISOString()]); return rows; } ```

Architectural Specification & Invariant Rationale

Architectural Invariant Design: Postgres 17 Zero-Lock DDL Invariants and Microsecond Partition Pruning

Postgres 17 Zero-Lock DDL Invariants and Microsecond Partition Pruning defines an industrial-grade behavioral standard for backend engineering. Enforces strict DDL migration patterns and query structures in PostgreSQL 17 to eliminate table-level ExclusiveLocks on high-throughput ledgers. Guarantees run-time and compile-time partition pruning using immutable routing keys while avoiding lock-queue starvation via explicit timeout backoffs. 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 AccessExclusiveLock Acquisition and Dynamic Partition Filter Degeneration 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 AccessExclusiveLock Acquisition and Dynamic Partition Filter Degeneration	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 prepend DDL operations with explicit transaction-level lock guards

Engineers MUST ALWAYS prepend DDL operations with explicit transaction-level lock guards: `SET LOCAL lock_timeout = '2s';` and `SET LOCAL statement_timeout = '5s';`.

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.