Skip to main content
abstractalgorithms.devAbstract Algorithms
← All Problemsarchitecturehard~30 min

Un-Indexed Queries Triggering Sequential Full-Table Scans Under Heavy Concurrency

User notification service querying 'SELECT * FROM notifications WHERE user_id = ? AND is_read = false' on a 50M-row table.

Scope, Requirements & Constraints

User notification service querying 'SELECT * FROM notifications WHERE user_id = ? AND is_read = false' on a 50M-row table. Failure cascade dynamic: Burst → Pool Saturated → Queue Bloat (10k) → STW GC → 💥 504 Timeouts.

Core Functional Requirements

  • ✓Operational Invariant: "High-frequency OLTP queries must utilize covering or selective composite indexes; full table scans are strictly prohibited under concurrency."
  • ✓Production Hardening: Deploy automated query performance monitoring (pg_stat_statements / MySQL performance_schema), establish CI linters enforcing index verification on all ORM queries, and configure q
  • ✓Prevent cascading failure by isolating downstream degradation at the ingress boundary.

Scale & Latency Constraints

  • —Incident Scenario Baseline: User notification service querying 'SELECT * FROM notifications WHERE user_id = ? AND is_read = false' on a 50M-row table.
  • —Failure Cascade Path: Burst → Pool Saturated → Queue Bloat (10k) → STW GC → 💥 504 Timeouts
  • —Sub-millisecond rejection / shed latency for overloaded requests

Reference Architecture & System Blueprint

Deploy automated query performance monitoring (pg_stat_statements / MySQL performance_schema), establish CI linters enforcing index verification on all ORM queries, and configure query cost limits. Invariant: "High-frequency OLTP queries must utilize covering or selective composite indexes; full table scans are strictly prohibited under concurrency."

System Architecture Blueprint
Production Topology
Un-Indexed Queries Triggering Sequential Full-Table Scans Under Heavy Concurrency Architecture Blueprint
Architecture Flow NarrativeIngress → Bounded Admission Controller → Adaptive Concurrency / Queue Gate → Protected Worker Engine; Overload Shed Path → HTTP 429 / Circuit Fallback (<0.1ms).

Hardened Implementation Steps

  1. 1Ingress Boundary Defense: Deploy automated query performance monitoring (pg_stat_statements / MySQL performance_schema), establish CI linters enforcing index verification on al
  2. 2Failure Containment: Eliminate unconstrained buffering and propagate physical backpressure.
  3. 3Invariant Enforcement: High-frequency OLTP queries must utilize covering or selective composite indexes; full table scans are strictly prohibited under concurrency.

Production Code Contrast & Implementation

java
// HARDENED: Redis in-memory atomic decrement via Lua script
String luaScript = 
    "local curr = tonumber(redis.call('GET', KEYS[1]) or '0') " +
    "if curr >= tonumber(ARGV[1]) then " +
    "  redis.call('DECRBY', KEYS[1], ARGV[1]) return 1 " +
    "else return 0 end";
Long granted = (Long) redis.eval(luaScript, List.of("inv:" + itemId), List.of(qty));
// Decoupled reservation drains to PostgreSQL via background batch worker

Key Decisions & Trade-Off Analysis

Architectural comparison of naive unconstrained allocations versus production-hardened mechanical sympathy.

Architectural Decisions & Trade-Offs

  • ⚖Ingress Admission: Naive unconstrained buffering causes queue bloat and GC exhaustion. Mitigation: Bounded ingress gate with fast non-blocking shedding.
  • ⚖Resource Strategy: Static capacity allocations collapse under brownouts. Mitigation: Dynamic adaptive limits scaling to real-time physical bounds.
  • ⚖Outage Impact: Cascading lock convoys and memory saturation. Mitigation: Isolated failure domains keep P99 latency flat and protected.

Alternatives Evaluated

  • →Naive Approach: The query lacks a composite index on (user_id, is_read). 1,000 concurrent requests each trigger a full sequential table scan, pegging database CPU at
  • →Production-Hardened Pattern: Deploy automated query performance monitoring (pg_stat_statements / MySQL performance_schema), establish CI linters enforcing index verification on al

Scale Bottlenecks & Operational Hazards

Operational Hazards & Mitigations

  • ⚠Cascading Failure: The query lacks a composite index on (user_id, is_read). 1,000 concurrent requests each trigger a full sequential table scan, pegging database CPU at 100%, blowing out the buffer pool, and causing cluster-wide connection timeouts.
  • ⚠Breakdown Chain: Burst → Pool Saturated → Queue Bloat (10k) → STW GC → 💥 504 Timeouts
  • ⚠Shedding Failure: Without fast rejection at the boundary, queued dead work triggers multi-second GC pauses, CPU thrashing, and cascading 504 Gateway Timeouts.

Changed Constraints & System Extension

How does the system enforce physical resource boundaries under sudden downstream latency brownouts?

Extension Implementation Steps

  1. 1Track real-time latency gradients and inflight concurrency.
  2. 2Reject excess traffic at the ingress boundary within <0.1ms.
  3. 3Monitor P99 latency and verify autonomous recovery when downstream pressure subsides.

Follow-Up Trade-Offs

  • ⚖Architectural Invariant: "High-frequency OLTP queries must utilize covering or selective composite indexes; full table scans are strictly prohibited under concurrency."
  • ⚖Physical Resource Boundary: Dynamic admission control ensures worker threads never buffer dead requests during downstream brownouts.

Interactive Assessment & Knowledge Check

Test your understanding of the architecture decisions, data patterns, and trade-offs used in Un-Indexed Queries Triggering Sequential Full-Table Scans Under Heavy Concurrency.

Interactive Practice Quiz•Question 1 of 3
advanced

B+ Tree vs LSM-Tree Storage Engine Trade-Offs

Why do high-throughput write-heavy storage engines (like RocksDB and Cassandra) use LSM-Trees instead of traditional B+ Trees?