AttemptIQ: Systems Thinking From First Principles — The Deep Cut

This document is the companion to sd-book.md. Where that book maps decisions to justifications, this book maps justifications to physics. The test is simple: after reading a chapter, you must be able to explain the WHY at the level of the CPU, the OS scheduler, or the data structure — not just "it's faster."

Every code snippet is copied verbatim from the AttemptIQ codebase.

How to read this book

Each chapter follows the same structure:

1. Physical Reality — what the computer is actually doing at hardware/OS/data-structure level
2. Junior Dev Path — the naive approach, and exactly how it fails (step by step)
3. The Insight — one sentence that contains the whole chapter
4. What AttemptIQ Does — real code, real decisions
5. Full Tradeoff Map — what you gain and what you give up
6. Transferable Mental Model — the abstraction that applies to any system you'll ever build

The WHY DRILL rule: keep asking why until you hit physics, memory, or the OS scheduler. "It's faster" is not an answer. "It avoids a TLB flush" is an answer.

Chapter 1: PostgreSQL Connections and Why pgBouncer Exists

Physical Reality

When you call psql or your app opens a database connection, PostgreSQL does not create a thread. It calls fork().

fork() is a Unix system call that creates a new OS process by duplicating the calling process. Here is what happens at the kernel level when PostgreSQL forks a backend process:

1. Kernel process table entry: The OS allocates a new task_struct (Linux) or process control block. This is a kernel data structure containing the process ID, state, scheduling priority, memory map pointers, open file descriptors, signal handlers, and more. On a 64-bit Linux kernel, a task_struct is approximately 10KB.

2. Address space copy (copy-on-write): The child process gets a copy of the parent's virtual address space. The kernel marks every writable page as copy-on-write — neither parent nor child can write to a shared page without triggering a page fault, at which point the OS copies the physical page and gives each process its own copy. Until that first write, they share physical RAM.

3. File descriptor table: The child gets a copy of the parent's file descriptor table. Each open socket, file, and pipe gets duplicated. This includes the postmaster's listening socket.

4. Shared memory: PostgreSQL allocates a large shared memory segment at startup (controlled by shared_buffers, default 128MB, recommended 25% of RAM). This is mmap()-ed into every backend process. All backends share the same buffer pool. This is how they can coordinate: they read and write the same RAM.

5. Per-backend working memory: Each backend process allocates work_mem (default 4MB) for sort operations, hash joins, etc. This is private to the process, not shared.

6. WAL writer communication: The backend registers itself with the WAL writer so that when it calls COMMIT, the WAL record can be flushed to disk before the backend returns success to the client.

The total fixed cost per PostgreSQL connection is approximately 5-10MB of virtual address space, a kernel process table entry, a file descriptor table, and participation in the shared buffer pool coordination protocol.

Why processes instead of threads?

PostgreSQL predates reliable POSIX threads (pthreads) across all Unix platforms. But more importantly, process isolation provides a safety guarantee: if a client sends a query that triggers a bug and crashes its backend, only that process dies. The postmaster detects the crash and spawns a new backend. With threads, a single crashed thread can corrupt the shared heap and take down the entire database server.

The cost you pay for this safety is context-switch overhead.

Context switching between processes

The OS scheduler runs every process for a quantum (typically 1ms to 10ms on Linux, configurable). When a process's quantum expires, or it blocks on I/O, the scheduler must switch to another process. A context switch involves:

1. Save CPU state: The current process's register file (on x86-64: 16 general-purpose registers + 16 XMM registers + flags register + instruction pointer) is written to the process's kernel stack. Approximately 600 bytes.

2. Load new CPU state: The new process's saved registers are loaded.

3. TLB flush: The CPU's Translation Lookaside Buffer (TLB) is a hardware cache of virtual-to-physical address translations. It makes memory access fast: instead of walking the multi-level page table on every memory access, the CPU checks the TLB first. On x86-64, the TLB has approximately 1,500 entries for 4KB pages. When you switch to a different process with a different virtual address space, most TLB entries are now invalid and must be invalidated (flushed). On older CPUs, every context switch flushes the entire TLB. On modern CPUs with PCID (Process-Context IDentifiers), the OS can tag TLB entries with a process ID and avoid full flushes — but this is Linux kernel version and CPU-model-dependent.

4. Cache cold-start: The L1/L2/L3 CPU caches (warm when the previous process was running) are now filled with the previous process's data. The new process will suffer cache misses on its first memory accesses, causing main-memory reads (~100ns each vs ~4ns for L1 cache).

At 200 PostgreSQL connections, the OS scheduler is managing 200 processes. Even if only 10 are actively running SQL, the scheduler cycles through all of them. Each cycle costs a TLB flush and cache pollution.

The arithmetic

• 200 connections × 10MB = 2GB of virtual address space consumed
• 200 connections × 10KB kernel overhead = 2MB of kernel memory
• Context switching 200 processes: measurable CPU overhead, especially TLB flush cost

At 500 connections (common in naive production deployments), PostgreSQL degrades noticeably — not from query execution time, but from scheduling overhead.

Junior Dev Path

You deploy AttemptIQ. It works fine in development (10 users). You launch. Traffic grows to 500 concurrent users.

Each user's request opens a database connection:

// The naive approach — new connection per request
const { Client } = require('pg');
const client = new Client({ connectionString: process.env.DATABASE_URL });
await client.connect();
const result = await client.query('SELECT ...', [...]);
await client.end();

At 500 concurrent users, 500 fork() calls happen. PostgreSQL now has 500 backend processes.

The first failure mode is max_connections. PostgreSQL defaults to max_connections = 100. At 101 connections, you see:

FATAL: sorry, too many clients already

You raise max_connections to 500. Now PostgreSQL needs ~5GB of RAM for backend processes alone (500 × 10MB), plus shared_buffers (128MB), plus your actual data. On a 8GB server, you're out of memory.

Even if you have enough RAM, the scheduler is now cycling through 500 processes. The TLB flushes alone add 10-20µs of overhead per query. On a query that normally takes 5ms, that's 0.4% overhead — tolerable. But at 500 connections, many are idle (waiting for the next HTTP request). The OS still schedules them. Idle processes still consume TLB entries and cache lines. Active processes get fewer CPU cycles.

The second failure mode is connection setup time. Opening a PostgreSQL connection takes 50-150ms (TCP handshake, authentication, process fork, shared memory setup). At 500 users/second, you're spending 25,000-75,000ms/second just on connection setup. That's 25-75 CPU-seconds per real second — impossible.

You add a connection pool (like pg.Pool). Now your app maintains a fixed number of connections and reuses them. This solves the setup time problem. But if you run 4 PM2 worker processes × 10 connections = 40 real PostgreSQL connections. Still manageable.

But now you scale to 10 services, each with 40 connections = 400 PostgreSQL connections. Back to the original problem.

pgBouncer exists to solve the architectural version of this problem: when you have many services (or many processes of one service), each needing connections, but PostgreSQL cannot sustain that many backend processes.

The Insight

PostgreSQL's per-connection OS process model is not a bug — it's a deliberate safety guarantee — but it means connection count is a finite, expensive resource that must be managed centrally, not by each service independently.

What AttemptIQ Does

// lib/db/client.ts
const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  max: 10,
  idleTimeoutMillis: 30000,
  connectionTimeoutMillis: 10000,
});
export const db = pool;

DATABASE_URL points to pgBouncer on port 6432, not PostgreSQL on port 5432. The pg.Pool in each PM2 process maintains at most 10 connections to pgBouncer. pgBouncer runs default_pool_size = 25, meaning it maintains at most 25 real PostgreSQL backend processes regardless of how many app connections are open.

The topology:

4 PM2 processes × 10 pg.Pool connections = 40 connections to pgBouncer
pgBouncer → 25 real PostgreSQL backend processes

PostgreSQL sees: 25 processes
pgBouncer sees: 40 app connections
App sees: 10 connections per PM2 worker (local pool)

pgBouncer in transaction pooling mode works as follows:

1. App sends BEGIN → pgBouncer assigns a real PostgreSQL connection from the pool
2. App executes queries → all routed to that same real connection (because we're in a transaction)
3. App sends COMMIT or ROLLBACK → pgBouncer returns the real connection to the pool
4. The next app transaction gets any available real connection — possibly a different one

This means a single real PostgreSQL connection serves many app transactions sequentially. 25 real connections can handle 40 app connections because most app connections are idle (waiting for the next HTTP request) at any given moment.

The constraint this creates

Transaction pooling mode means the real PostgreSQL connection changes between transactions. Anything stored at the connection level (session-level state) does not survive:

• SET statement_timeout = 5000 at session level → next transaction runs on a different connection, the timeout is gone
• Session-level advisory locks (pg_advisory_lock) → held on connection, released when connection is returned to pool, so they disappear between transactions
• Prepared statements → statement is prepared on one connection, but next request may get a different connection

AttemptIQ's CLAUDE.md documents this constraint explicitly:

pgBouncer in transaction mode: no session-level SET variables, no cross-request advisory locks.

The advisory lock workaround is pg_advisory_xact_lock() — the transaction-scoped variant. It releases automatically on COMMIT/ROLLBACK, which is exactly when pgBouncer returns the connection to the pool. So the lifecycle aligns.

Full Tradeoff Map

When does this break?

If your queries are all long-running (analytics aggregations taking 10+ seconds each), the 25 connections are all busy and app connections queue at pgBouncer. connectionTimeoutMillis: 10000 means the app gives up after 10 seconds. At that point you need read replicas or materialized views — which is exactly what AttemptIQ uses (the 6 MVs refreshed every 2 hours).

Transferable Mental Model

The Shared Resource Funnel: Any resource with a fixed cost per unit (OS processes, file descriptors, sockets) must be managed by a single gatekeeper that multiplexes N consumers onto M resource units. The gatekeeper's protocol must match the resource's lifecycle (transaction-scoped vs session-scoped). When it doesn't match, you lose the ability to use stateful features of the resource.

Apply this anywhere: HTTP/2 multiplexes many streams onto one TCP connection. Thread pools funnel many tasks onto few threads. Connection pools funnel many requests onto few sockets. The pattern is always N >> M with a gatekeeper in between.

Chapter 2: Redis and Why It Is Fast

Physical Reality

To understand why Redis is fast, you must understand the memory hierarchy:

Redis stores all data in DRAM. When you do GET key, the path is:

1. TCP receive: client's bytes arrive at the kernel's network buffer (socket receive buffer)
2. Redis's event loop (ae, built on epoll on Linux) detects the socket is readable
3. Redis reads bytes from the kernel buffer (one read() syscall)
4. Redis parses the RESP protocol: *2\r\n$3\r\nGET\r\n$3\r\nkey\r\n → command=GET, arg=key
5. Redis looks up the key in its global dictionary (a hash table with open addressing)
6. Hash table lookup: compute hash(key) → index into array → check if key matches → follow pointer to value
7. Redis writes the value back as RESP bytes (one write() syscall)
8. Kernel copies bytes to socket send buffer
9. Network card DMA-transfers the bytes to the client

The entire path is in DRAM. No disk access. No page format parsing. No visibility checks.

The hash table lookup detail

Redis uses a custom dictionary (dict.c) with two hash tables for incremental rehashing. Keys and values are stored as robj (Redis objects). For a string value, the robj points directly to the SDS (Simple Dynamic String) containing the bytes. The lookup is:

1. Hash the key (SipHash-1-2, ~4ns for a 10-byte key)
2. Compute slot = hash % table_size (one modulo operation)
3. Follow pointer to bucket (one DRAM access if not in L3 cache: ~60-100ns)
4. Check if key matches (memcmp, ~1ns for short keys)
5. Return pointer to value

Total: ~100-200ns for a hash table lookup on a key that is in DRAM but not in CPU cache. This is the raw speed.

What PostgreSQL does instead

A PostgreSQL query for the same data (SELECT value FROM cache WHERE key = $1) follows this path:

1. Parse the SQL string (tokenize, build parse tree) — or check the prepared statement cache
2. Plan the query (find indexes, estimate row counts, choose join order) — or use cached plan
3. Execute: look up index on key column
   • B-tree index lookup: O(log N) tree traversal. Each level of the tree is a separate 8KB page. If the page is not in shared_buffers, read it from disk (or kernel buffer cache)
   • Follow ctid (physical page + slot offset) to the heap page
   • Read the heap page (8KB) from shared_buffers or disk
   • MVCC visibility check: inspect the tuple's xmin and xmax (the transaction IDs that created and deleted this row version). Check against the current snapshot to determine if this row is visible to this transaction
   • If the row is visible, extract the column values (decode the tuple format: null bitmap, column offsets, data types)
4. Format the result as PostgreSQL wire protocol (PGwire) bytes
5. Send

For a cache hit where the data is in shared_buffers and the index is hot, this might take 200µs. For a cold cache requiring disk access: 1-10ms.

Redis bypasses: SQL parsing, query planning, B-tree traversal, heap page lookup, MVCC visibility, tuple format decoding.

Why Redis cannot replace PostgreSQL

1. No joins: Redis has no relational model. You cannot join two datasets. You can denormalize (store related data together), but that duplicates data and makes updates expensive.

2. No cross-key atomicity: Redis transactions (MULTI/EXEC) prevent other clients from interleaving commands, but they do not have rollback semantics. If command 3 of 5 fails, commands 1 and 2 are already applied. There is no ROLLBACK.

3. Crash safety is optional and costs speed: By default, Redis is crash-unsafe. If the server loses power, you lose the last writes. You can enable:

   • RDB snapshots: Full dump to disk every N seconds. On crash, lose up to N seconds of writes.
   • AOF (Append-Only File): Every write command is appended to a file. On startup, replay the file. With appendfsync always, every write waits for fsync() — disk write — before acknowledging the client. This makes Redis as slow as disk (~1ms per write). Most deployments use appendfsync everysec: sync once per second, accept up to 1 second of loss on crash.

4. Single-threaded command execution: Redis processes commands in a single thread. This eliminates lock contention and context switches between command handlers, which is why it's fast. But it means one slow command blocks all others. KEYS * on a large keyspace blocks Redis for seconds.

5. Memory is finite: All data must fit in RAM. PostgreSQL can store more data than fits in RAM because it pages data to disk. Redis cannot (without Redis Cluster sharding).

Why Redis is perfect for AttemptIQ's use cases

Rate limiting, session caching, and job queues all need:

• Fast reads/writes (user-facing latency)
• Approximate durability (losing one rate-limit window or one cached analytics report is acceptable)
• No relational queries

They do not need:

• Strong consistency across multiple keys
• Crash-proof durability
• More data than fits in RAM

Junior Dev Path

You have an analytics endpoint: GET /api/analytics/subject-stats. It runs a complex aggregation over user_attempts (potentially millions of rows). The query takes 800ms.

With 100 users requesting analytics, that's 100 concurrent 800ms queries. PostgreSQL's 25 connections (via pgBouncer) are all busy running analytics. Practice session creation starts timing out — those requests are queuing behind analytics queries.

Naive fix attempt 1: Cache in the Next.js process memory.

// In-memory cache — naive
const cache = {};
async function getSubjectStats(userId) {
  if (cache[userId]) return cache[userId];
  const result = await db.query('SELECT ...', [userId]);
  cache[userId] = result;
  return result;
}

This fails because:

• Memory grows without bound (no TTL, no eviction)
• With 4 PM2 processes, each has its own cache. User hits process 1 (cache miss, DB query), next request hits process 2 (cache miss again), next hits process 3, next process 4. 4 DB queries for what should be 1.
• On PM2 restart, all in-memory cache is lost. Thundering herd: all requests hit DB simultaneously.

Naive fix attempt 2: Store in PostgreSQL with a cached_at timestamp.

-- Cache table approach
CREATE TABLE analytics_cache (user_id TEXT PRIMARY KEY, data JSONB, cached_at TIMESTAMP);

This fails because:

• Reading from the cache table is still a PostgreSQL query. You replaced an 800ms aggregation with a 5ms cache read, but you still have 100 concurrent PostgreSQL connections doing 5ms cache reads. Slightly better, but you've added a cache invalidation problem and still consumed a PostgreSQL connection per request.
• The data in analytics_cache is now a denormalized copy that can go stale.

The correct path: Redis. One key per user. TTL handles expiry. All PM2 processes share one Redis. Sub-millisecond reads. PostgreSQL is only hit on cache miss (or TTL expiry, at most once per 10 minutes per user).

The Insight

Redis is fast because it stores data in DRAM and answers queries with a hash table lookup, bypassing every layer that makes PostgreSQL correct: SQL parsing, query planning, B-tree index traversal, heap page lookup, MVCC visibility, and tuple format decoding.

What AttemptIQ Does

// lib/redis/client.ts
const redis = new Redis(process.env.REDIS_URL!, {
  maxRetriesPerRequest: 3,
  enableReadyCheck: true,
  lazyConnect: false,
});

The rate limiter uses Redis for exactly the reason described above — speed, plus the sorted set data structure, plus pipeline atomicity:

// lib/rate-limit.ts
const pipeline = redis.pipeline();
pipeline.zremrangebyscore(key, '-inf', windowStart);
pipeline.zcard(key);
pipeline.zadd(key, now, `${now}-${Math.random()}`);
pipeline.expire(key, windowSeconds + 1);
const results = await pipeline.exec();

const countAfterClean = (results?.[1]?.[1] as number) ?? 0;

pipeline.exec() sends all four commands in a single TCP write. Redis executes them atomically from the perspective of other clients (no interleaving). This is not a transaction — if command 3 fails, commands 1 and 2 are applied — but for rate limiting, this is acceptable. The sorted set stores timestamps as scores; ZREMRANGEBYSCORE -inf windowStart removes expired entries; ZCARD counts remaining; ZADD adds current timestamp.

Why sorted set, not a counter?

A simple counter (INCR key) with a fixed window would reset at a hard boundary (e.g., exactly 12:00:00). Users could send 100 requests at 11:59:59 and 100 more at 12:00:01 and bypass the rate limit. A sorted set is a sliding window: it always counts the last N seconds, regardless of when the window started.

The fail-open decision

} catch (err) {
  // Redis failure — fail open so the site stays up, but alert on-call immediately.
  try {
    const Sentry = await import('@sentry/nextjs');
    Sentry.captureException(err, {
      tags: { component: 'rate-limiter', identifier },
      level: 'error',
    });
  } catch {
    console.error('[rate-limit] Redis failure AND Sentry capture failed', err);
  }
  return { allowed: true, remaining: limit, resetInSeconds: windowSeconds };
}

On Redis failure, requests are allowed through (fail open). The alternative is fail closed: on Redis failure, block all requests. Fail closed protects against abuse during Redis downtime. Fail open keeps the product available. AttemptIQ chose availability because rate limiting is a defense against abuse, not against legitimate users — and legitimate users should not be locked out because Redis restarted.

Redis eviction policy

AttemptIQ uses volatile-lru (evict keys with TTL set, using LRU). This is critical:

• BullMQ job keys in Redis do not have TTL (they're permanent until the job completes)
• Analytics cache keys have TTL
• allkeys-lru would evict BullMQ job keys when Redis is under memory pressure — jobs vanish silently, no error, just lost work
• volatile-lru only evicts keys with TTL set, protecting BullMQ's persistent keys

Full Tradeoff Map

Transferable Mental Model

The Layer Skip: The speed difference between two storage systems is usually the difference in which processing layers you skip. Redis skips SQL parsing, query planning, index traversal, MVCC, and tuple decoding. A CDN skips the origin server entirely. A CPU cache skips DRAM access. When designing a caching strategy, ask: which layers does this cache skip? What does skipping those layers make impossible?

Chapter 3: Race Conditions and Locks

Physical Reality

A race condition exists when the correctness of your program depends on the relative timing of operations that can happen concurrently. The "race" is between two threads of execution (processes, threads, goroutines, async tasks) reaching a shared resource.

At the CPU level

Modern CPUs are superscalar — they execute multiple instructions per clock cycle, sometimes out of order. They have multiple cores that run independently. Each core has its own L1 and L2 cache. When two cores write to the same memory address, they compete. The CPU resolves this with cache coherence protocols (MESI on x86): when one core modifies a cache line, it invalidates that cache line in all other cores' caches. The next access by any other core causes a cache miss and reads from L3 or DRAM.

But cache coherence only ensures you see the latest value at the memory level. It doesn't ensure that compound operations (read-then-write) are atomic. Consider:

Core 1: read count = 5  ← reads 5
Core 2: read count = 5  ← also reads 5 (before Core 1 writes)
Core 1: write count = 6 ← increments to 6
Core 2: write count = 6 ← also increments to 6, overwrites Core 1's write

Both cores incremented from 5 to 6. The correct value was 7. This is the classic lost-update race condition.

In AttemptIQ's context: the TOCTOU race

TOCTOU = Time Of Check To Use. The window between checking a condition (is the session limit reached?) and using the result of that check (inserting the session) allows another concurrent operation to invalidate the condition.

Request 1: SELECT COUNT(*) sessions WHERE user_id = 'alice' → count = 1, limit = 2 → OK
Request 2: SELECT COUNT(*) sessions WHERE user_id = 'alice' → count = 1, limit = 2 → OK
Request 1: INSERT INTO sessions ... → inserts, count is now 2
Request 2: INSERT INTO sessions ... → inserts, count is now 3 ← exceeded limit

Both requests passed the check (count = 1 < 2). Both inserted. The limit was bypassed.

This race is real, not theoretical. When a user double-clicks "Start Session" or their client sends duplicate requests (network retry), two requests arrive simultaneously.

Why separate check-then-insert is always wrong

Even inside a single PostgreSQL transaction:

BEGIN;
SELECT COUNT(*) FROM sessions WHERE user_id = $1; -- reads 1
-- Another concurrent transaction inserts here, between your SELECT and INSERT
INSERT INTO sessions ...; -- now count is 3, not 2
COMMIT;

At PostgreSQL's READ COMMITTED isolation level (the default), each statement sees a fresh snapshot. Your SELECT sees 1. By the time your INSERT executes, another transaction may have committed an insert. Your SELECT snapshot is stale.

At REPEATABLE READ, your SELECT snapshot is held for the transaction — but INSERT doesn't check the count at all, so the race window is unchanged.

At SERIALIZABLE, PostgreSQL detects that your two transactions accessed overlapping data and aborts one with a serialization failure. This works but requires retry logic and has performance overhead.

The advisory lock solution

pg_advisory_xact_lock(key bigint) acquires an exclusive application-level lock on an arbitrary integer key. The lock is transaction-scoped: it is automatically released when the transaction commits or rolls back.

The lock is "advisory" because PostgreSQL does not enforce it. You must voluntarily acquire it before the protected operation. Other code that doesn't acquire the lock can still run. But if all code paths that modify the session count first acquire the advisory lock with the same key (the user's ID hash), they are serialized.

How it works internally: PostgreSQL maintains a hash table of lock entries in shared memory. pg_advisory_xact_lock inserts an entry with the key. If another backend has already inserted an entry with the same key, the acquiring backend blocks (waits). When the first backend's transaction ends, the entry is removed, and the waiting backend unblocks.

The lock is on an integer, not on a table row. This is faster than row-level locks (which require finding the row first) and coarser than table locks (which block all operations). You're serializing per-user, which is the right granularity: two different users can create sessions simultaneously (different keys), but two requests from the same user cannot (same key).

Junior Dev Path

You see the session limit requirement: free users get 2 practice sessions lifetime. Your first implementation:

// Naive — do NOT do this
async function createSession(userId: string, isFree: boolean): Promise<string | null> {
  const { rows } = await db.query(
    `SELECT COUNT(*) as count FROM sessions WHERE user_id = $1 AND session_type = 'practice'`,
    [userId]
  );
  const count = parseInt(rows[0].count);
  if (isFree && count >= 2) return null; // limit reached
  
  const { rows: inserted } = await db.query(
    `INSERT INTO sessions (user_id, session_type) VALUES ($1, 'practice') RETURNING id`,
    [userId]
  );
  return inserted[0].id;
}

This fails under concurrent load. The SELECT and INSERT are two separate operations with no atomicity guarantee. Between the SELECT (count = 1) and the INSERT, another request reads count = 1, passes the check, and also inserts. You now have count = 3 with a limit of 2.

First "fix" attempt: Wrap in a transaction.

const client = await db.connect();
await client.query('BEGIN');
const { rows } = await client.query(
  `SELECT COUNT(*) as count FROM sessions WHERE user_id = $1 AND session_type = 'practice' FOR UPDATE`,
  [userId]
);
// ... same problem: there's no row to lock if count = 0

FOR UPDATE locks the rows you've selected. But if count = 0, you selected zero rows. There's nothing to lock. The next request also sees zero rows, also locks nothing, both insert.

Second "fix" attempt: Use a user lock row.

CREATE TABLE user_locks (user_id TEXT PRIMARY KEY);
-- insert a row for each user on signup

const client = await db.connect();
await client.query('BEGIN');
await client.query(`SELECT 1 FROM user_locks WHERE user_id = $1 FOR UPDATE`, [userId]);
// Now the row is locked. Any other transaction trying FOR UPDATE on same user_id blocks.
const count = ...
if (count < 2) { await insert... }
await client.query('COMMIT');

This works but requires a separate table, a row per user, and you must never forget to insert the lock row on signup. It also doesn't work with pgBouncer transaction pooling unless you acquire the lock and complete the entire transaction in one logical request (which is what the advisory lock does anyway).

The actual fix: atomic INSERT with embedded COUNT guard.

INSERT INTO sessions (user_id, session_type, question_ids)
SELECT $1, 'practice', s.qids
FROM selected s
WHERE s.qids IS NOT NULL
  AND array_length(s.qids, 1) > 0
  AND (SELECT COUNT(*) FROM sessions WHERE user_id = $1 AND session_type = 'practice') < 2
RETURNING id

The COUNT subquery runs inside the INSERT's WHERE clause. PostgreSQL evaluates this atomically within the statement: if the count is already at the limit, the INSERT condition is false and no row is inserted. Two concurrent requests running this statement concurrently: the second INSERT sees the first INSERT's committed rows (at READ COMMITTED, each statement gets a fresh snapshot, and the first INSERT's commit is visible to the second statement). The second INSERT evaluates count = 2 < 2 = false, inserts nothing.

But there is still a window: if both requests execute the INSERT simultaneously on different connections, they both see count = 1 (before either commits), both evaluate 1 < 2 = true, both insert. The window is very small (microseconds), but it exists.

The advisory lock closes this window completely.

The Insight

The only way to make a check-then-act operation race-free is to either (a) make the check and act a single atomic database operation, or (b) serialize all competing actors before the check with an exclusive lock.

What AttemptIQ Does

// repositories/sessions.repo.ts
private async _lockedInsertSession(userId: string, sql: string, params: unknown[]): Promise<string | null> {
  const client = await db.connect();
  try {
    await client.query('BEGIN');
    await client.query('SELECT pg_advisory_xact_lock(hashtext($1::text)::bigint)', [userId]);
    const { rows } = await client.query<{ id: string }>(sql, params);
    await client.query('COMMIT');
    return rows[0]?.id ?? null;
  } catch (err) {
    await client.query('ROLLBACK');
    throw err;
  } finally {
    client.release();
  }
}

Sequence of events for two concurrent requests from user Alice:

1. Request 1: acquires pg_advisory_xact_lock(hashtext('alice')::bigint) — lock granted, no one else holds it
2. Request 2: tries to acquire same lock — blocks (kernel puts it in a wait queue)
3. Request 1: runs INSERT with COUNT guard (count = 1 < 2, inserts)
4. Request 1: COMMIT — advisory lock released, sessions row committed
5. Request 2: unblocks, acquires lock
6. Request 2: runs INSERT with COUNT guard — SELECT COUNT(*) now returns 2 (sees Request 1's committed row), 2 < 2 = false, INSERT inserts nothing
7. Request 2: COMMIT — lock released, returns null (limit reached)

Alice gets exactly one session, not two.

The hashtext()::bigint detail

pg_advisory_xact_lock takes a bigint key. Alice's user_id might be a UUID (TEXT). hashtext() converts any text to an int4 (32-bit integer). Casting to bigint is required for the function signature. There is a theoretical hash collision: two different user IDs could produce the same hash. The probability for 10,000 users is approximately 0.001% (birthday paradox with 2^32 possible values). A collision means two users occasionally serialize against each other — they wait for each other's lock unnecessarily. No data corruption. This is an acceptable tradeoff.

The webhook idempotency race

A different race appears in payment webhooks. The payment gateway retries webhooks if you don't respond quickly (network hiccup, server restart). Two webhook deliveries for the same payment can arrive simultaneously.

// repositories/webhook-events.repo.ts
const { rows } = await db.query<{ is_new: boolean; processed: boolean }>(
  `WITH ins AS (
     INSERT INTO webhook_events (id, gateway, event_type, payload, processed)
     VALUES ($1, $2, $3, $4, false)
     ON CONFLICT (id) DO NOTHING
     RETURNING true AS is_new, false::boolean AS processed
   )
   SELECT is_new, processed FROM ins
   UNION ALL
   SELECT false AS is_new, processed
   FROM webhook_events
   WHERE id = $1
     AND NOT EXISTS (SELECT 1 FROM ins)`,
  [id, gateway, eventType, JSON.stringify(payload)],
);

The ON CONFLICT (id) DO NOTHING clause uses PostgreSQL's unique constraint on the id column as a serialization mechanism. Two concurrent inserts with the same id:

• One wins (inserts the row, conflict detection serializes the other)
• The other loses (the conflict happens, DO NOTHING, INSERT inserts nothing)
• The CTE returns the result of the winning INSERT
• For the losing request: the CTE is empty, UNION ALL runs the SELECT to read the existing row's state

This eliminates the race window that would exist if you did:

// Wrong — race window between INSERT attempt and SELECT
try {
  await db.query(`INSERT INTO webhook_events ...`, [...]);
} catch (e) {
  if (e.code === '23505') { // unique violation
    const row = await db.query(`SELECT processed FROM webhook_events WHERE id = $1`, [id]);
    return row.processed ? 'already_processed' : 'duplicate';
  }
}

Between the failed INSERT and the SELECT, the winning request could update processed = true. Your SELECT reads the post-update state — correct. But between the failed INSERT and the SELECT, the winning request could have crashed without updating. Your SELECT reads processed = false — you return 'duplicate' instead of 'inserted'. The CTE approach reads in one atomic statement, avoiding this gap.

Full Tradeoff Map

Transferable Mental Model

The Atomic Window: Any operation that reads state and then modifies based on that state has a window between read and modify where concurrent operations can invalidate your read. The window cannot be closed by moving the read and write closer together — it can only be closed by making the read and write a single atomic operation, or by acquiring exclusive ownership of the shared state before reading.

Chapter 4: HTTP and Sessions

Physical Reality

HTTP is a stateless protocol. The physical reality: every HTTP request is a sequence of bytes over a TCP connection, and TCP has no memory of previous connections. The server receives bytes, produces response bytes, and forgets.

A TCP connection is a pair of sockets (IP + port on each end) coordinated by a kernel state machine. The state machine maintains sequence numbers, acknowledgment numbers, and a send/receive buffer. When the connection closes (FIN/ACK handshake), all state is destroyed.

HTTP/1.0 closed the TCP connection after each response. HTTP/1.1 introduced Connection: keep-alive (now default): the TCP connection stays open, multiple HTTP requests/responses flow over it. But each request-response pair is still independent from the application's perspective. The server knows nothing about the previous request.

Why stateless?

This was a deliberate design choice. Stateless servers are:

• Horizontally scalable: any server can handle any request (no affinity required)
• Simpler to reason about: each request is isolated
• Easier to recover: if the server crashes, clients just reconnect

The authentication problem

Authentication inherently requires state: we need to remember that user Alice logged in 5 minutes ago. There are two approaches:

1. Client-side state (tokens/JWTs): Store the user's identity in a signed token. The client sends the token on every request. The server verifies the signature and extracts the claims. No server-side storage required. But the token cannot be revoked without a denylist (which reintroduces server-side state).

2. Server-side state (sessions): Generate a random session ID. Store the session ID → user mapping on the server (database, Redis). The client sends the session ID on every request (in a cookie or header). The server looks up the session in storage. Can be revoked instantly (delete the session row).

Cookie mechanics at the protocol level

When the server wants to set a cookie, it includes this header in the response:

Set-Cookie: session_token=abc123; HttpOnly; Secure; SameSite=Lax; Path=/; Max-Age=604800

The browser stores this in its cookie jar (a local database, cookies.sqlite in Firefox, Cookies file in Chrome, using SQLite). On every subsequent request to the same domain, the browser adds:

Cookie: session_token=abc123

The browser does this automatically. Your JavaScript cannot read cookies marked HttpOnly (they're invisible to document.cookie). This prevents XSS attacks from stealing the session token.

SameSite=Lax and CSRF

Cross-Site Request Forgery (CSRF): an attacker's site makes a request to your API using the victim's browser cookies. Example: the victim visits evil.com, which contains <img src="https://attemptiq.com/api/delete-account">. The browser sends this GET request with the session cookie.

SameSite=Lax tells the browser: only send this cookie when the top-level navigation is to the same site, or when the request is a GET (safe method). POST requests from cross-site origins do not include the cookie. This eliminates most CSRF without requiring a CSRF token.

SameSite=Strict is stronger (no cookie on any cross-site request) but breaks legitimate flows like links from email clients.

Junior Dev Path

You implement authentication naively. User sends email+password. You verify against the database. You need to remember they're logged in for the next request.

Attempt 1: URL parameter.

GET /dashboard?user_id=alice&authenticated=true

This is obviously wrong. Anyone can set authenticated=true. The server has no way to verify this claim.

Attempt 2: Store user_id in a cookie.

// Set cookie with user_id directly
response.cookie('user_id', 'alice');

On next request, read user_id from cookie.

This fails because cookies are client-controlled. The browser stores and sends whatever the server sets — but there is nothing stopping a user from opening DevTools and editing user_id to admin. The server reads user_id = admin and grants admin access.

Attempt 3: Sign the user_id (JWT).

const token = jwt.sign({ userId: 'alice', role: 'user' }, SECRET_KEY);
response.cookie('auth', token);

On next request, verify signature. If valid, trust the claims inside.

This works for authentication. But JWTs cannot be revoked. If Alice's token is stolen or she changes her password, the token is still valid until it expires. If you set a 7-day expiry (common for web apps), a stolen token grants 7 days of access.

The full solution: server-side sessions with a random session ID.

1. Generate a cryptographically random 32-byte token (256 bits of entropy, unguessable)
2. Store in database: {session_id: token, user_id: 'alice', expires_at: +7days}
3. Set cookie: session_token=token; HttpOnly; Secure; SameSite=Lax
4. On every request: read cookie, look up session in database, get user_id
5. On logout: delete session row. Token is now useless even if an attacker has it.

The tradeoff: one database query per authenticated request.

The Insight

HTTP's statelessness is not a bug to be fixed — it is a design guarantee that enables horizontal scaling. Authentication state is managed by pairing a random, unguessable token (stored on the client in an HttpOnly cookie) with server-side storage (database) that maps the token to the user identity. Security comes from the token's unguessability, not its content.

What AttemptIQ Does

Better Auth handles session management. From lib/auth/index.ts, the key configurations:

// Cookie config — HttpOnly prevents XSS cookie theft
// SameSite: 'lax' blocks CSRF from cross-site POST
// Secure: only sent over HTTPS
advanced: {
  cookiePrefix: 'ba',
},

Better Auth stores sessions in the ba_sessions table (with ba_ prefix to avoid collision with application tables). Each request to a protected route triggers a session lookup.

Concurrent session limit

// lib/auth/index.ts — before hook
databaseHooks: {
  session: {
    create: {
      before: async (session) => {
        // Enforce max 2 concurrent sessions per user
        const sessions = await auth.api.listSessions({
          headers: new Headers({ cookie: `ba.session_token=${session.token}` }),
        });
        if (sessions.length >= 2) {
          // Revoke the oldest session
          const oldest = sessions.sort((a, b) =>
            new Date(a.createdAt).getTime() - new Date(b.createdAt).getTime()
          )[0];
          await auth.api.revokeSession({ body: { token: oldest.token } });
        }
        return { data: session };
      },
    },
  },
},

This hook runs before a new session is created. If the user already has 2 sessions (phone + laptop), the oldest is revoked. The database deletion makes that session token immediately invalid — no TTL, no wait. This is the revocation advantage of server-side sessions.

Edge middleware (no database)

// middleware.ts
export async function middleware(request: NextRequest) {
  const sessionCookie = getSessionCookie(request);
  if (!sessionCookie) {
    return NextResponse.redirect(new URL('/login', process.env.NEXT_PUBLIC_APP_URL));
  }
  // ... onboarding gate check via aiq_ob cookie
}

The middleware runs on Cloudflare's edge (Next.js Edge Runtime). It cannot connect to PostgreSQL (no Node.js runtime, no pg module). It only checks for the presence of the session cookie — a quick cookie parse, no DB. If the cookie is present, the request proceeds. The actual session validity check happens in the API route handler or page, which runs in the Node.js runtime.

This is a deliberate layering decision: fast rejection (no cookie = not logged in, redirect immediately at the edge) vs. full validation (cookie exists = might be valid, check DB in the server).

Full Tradeoff Map

Transferable Mental Model

The Trust Anchor: HTTP has no memory, so authentication must establish trust on every request. The trust anchor is either (a) a signature that proves the server issued the token (JWT), or (b) a lookup that confirms the server still has the token in its store (session). Signatures cannot be revoked. Database lookups can. Choose based on whether revocation is a requirement.

Chapter 5: Background Jobs and Queues

Physical Reality

The HTTP request-response cycle has physical constraints:

1. Client timeout: Browsers and HTTP clients typically timeout after 30-120 seconds. If the server does not respond within this window, the client gives up.

2. TCP connection resources: The server holds a socket open for each active request. Sockets consume file descriptors (limited by ulimit -n, typically 65535 per process). Long-running requests exhaust file descriptors faster.

3. PM2 worker blocking: Node.js is single-threaded for JavaScript execution. If one request's handler blocks the event loop (e.g., a synchronous CPU-intensive operation), all other requests to that PM2 worker are delayed.

4. No retry on HTTP: If the server crashes or reboots mid-request, the client gets a connection error. There is no built-in retry mechanism in HTTP for server-side processing.

A background job system moves work out of the request-response cycle into a separate, persistent, retryable processing pipeline.

How Redis BLPOP enables worker queues

A job queue is conceptually a list. When a request wants to enqueue work:

1. RPUSH queue_name job_json — push to the right end of the list

When a worker wants to consume work:

1. BLPOP queue_name 0 — blocking left pop, timeout 0 (wait forever)

BLPOP is a blocking command: if the list is empty, the Redis connection blocks (waits at the kernel level). When another client pushes to the list, Redis unblocks all waiting BLPOP clients, returning the new item to one of them (the others continue waiting).

This is efficient because the worker process is idle (not consuming CPU) while waiting. The kernel puts the process in a wait queue. When Redis sends data on the socket, the kernel wakes the process. No polling, no sleep loops.

But BullMQ doesn't use raw BLPOP

BullMQ uses a more sophisticated data structure: Redis sorted sets (ZSETs) for delayed jobs and job states. A job in BullMQ goes through these states:

[waiting] → [active] → [completed]
                    ↘ [failed] → [retry/waiting]

Each state is a Redis key. Job data is stored as a Redis hash. The sorted set for delayed jobs uses the Unix timestamp (in milliseconds) as the score — BullMQ polls this ZSET and moves jobs whose timestamp has passed to the waiting queue.

maxRetriesPerRequest: null — why this matters

Standard ioredis has a maxRetriesPerRequest default of 3. If a command fails (Redis temporarily unreachable), ioredis retries 3 times then gives up and rejects the promise.

For a web request handler, this is fine: GET key fails after 3 retries, you return an error to the client.

For a BullMQ worker, this is catastrophic: BLPOP is a long-running blocking command. ioredis's retry logic treats the blocking timeout as a failure and retries, which disconnects and reconnects the socket. The worker loses its place in the queue. BullMQ requires maxRetriesPerRequest: null to disable this retry logic and let BullMQ manage its own connection lifecycle.

Why BullMQ uses its own ioredis connection

// lib/queues.ts
const connection = {
  host: process.env.REDIS_HOST ?? '127.0.0.1',
  port: parseInt(process.env.REDIS_PORT ?? '6379', 10),
  maxRetriesPerRequest: null as null, // required by BullMQ
};

BullMQ does not accept an existing ioredis instance. It creates its own connections from the config object. This is intentional: BullMQ needs maxRetriesPerRequest: null, but the app's Redis client (for caching) needs maxRetriesPerRequest: 3. If they shared a connection, one config would conflict with the other. Using separate connection configs means separate ioredis instances with independent settings.

Exponential backoff

When a job fails, BullMQ waits before retrying. With backoff: { type: 'exponential', delay: 5000 } and attempts: 3:

• Attempt 1 fails → wait 5,000ms (5s)
• Attempt 2 fails → wait 10,000ms (10s)
• Attempt 3 fails → job moves to failed set

Why exponential and not linear? If a job fails because a dependency is overloaded, retrying immediately makes the overload worse. Exponential backoff spreads retries over time, giving the dependency time to recover. It's the same principle behind TCP's congestion control.

Cron via BullMQ (not PM2 cron)

PM2 can run cron jobs: cron_restart: '0 */2 * * *' in the ecosystem config. But PM2 cron:

• Kills and restarts the process (all in-flight requests die)
• Does not persist job history
• Does not retry on failure
• Does not distribute across PM2 cluster (all 4 workers run it)

BullMQ's upsertJobScheduler (formerly add with repeat option) stores the cron schedule in Redis. Exactly one worker picks up each scheduled job (not all 4). The job has retry logic, history (removeOnComplete, removeOnFail), and the scheduler survives worker restarts.

Junior Dev Path

You need to send a welcome email when a user signs up. Naive approach: send the email in the request handler.

// In the signup API route handler
await sendWelcomeEmail(user.email, user.name); // takes 500ms-2s (SMTP roundtrip)
return Response.json({ success: true }); // user waits 500ms-2s

Problems:

• User waits for email delivery before seeing the signup success page
• If the SMTP server is down, signup fails. But the user was already created in the DB. Now user exists but got no welcome email.
• If signup succeeds and email sends, but response never reaches client (network drop), user tries to sign up again. Now you have a duplicate user.

Add error handling:

try {
  await sendWelcomeEmail(user.email, user.name);
} catch (e) {
  // Swallow email error, signup still succeeds
  console.error('Email failed', e);
}
return Response.json({ success: true });

Better: signup succeeds even if email fails. But if the email service is down for 10 minutes, all signups during that window get no welcome email. No retry. No visibility.

Add a queue:

// In the signup handler
await emailQueue.add('welcome', { userId: user.id, email: user.email, name: user.name });
return Response.json({ success: true }); // instant, no SMTP roundtrip

// In a separate worker process
worker.process('welcome', async (job) => {
  await sendWelcomeEmail(job.data.email, job.data.name);
});

Now: signup is instant (just a Redis RPUSH). Email is sent asynchronously. If SMTP is down, BullMQ retries with exponential backoff. If the worker crashes, the job is still in Redis (it's not deleted from the active set until COMMIT — more precisely, until the worker calls job.moveToCompleted()). On worker restart, the job is picked up again.

The queue also provides observability: you can inspect the Redis keys to see pending, active, failed, and completed jobs. PM2 background eval doesn't give you that.

The Insight

Background queues exist because some work has requirements (retry, persistence, decouple from client lifetime) that HTTP's request-response cycle cannot satisfy. The queue is a durable, ordered buffer that decouples the "when work is requested" from the "when work is executed."

What AttemptIQ Does

// lib/queues.ts
const defaultJobOptions = {
  attempts: 3,
  backoff: { type: 'exponential' as const, delay: 5000 },
  removeOnComplete: { count: 100 },
  removeOnFail: { count: 500 },
};

export const analyticsRefreshQueue = new Queue('analytics-refresh', {
  connection,
  defaultJobOptions,
});
// ... 6 more queues

// workers/analytics-refresh.worker.ts
const MV_SCHEDULE: { name: string; delayMinutes: number; cachePattern: string }[] = [
  { name: 'mv_user_subject_stats',  delayMinutes: 0,  cachePattern: 'analytics:subject_stats:*' },
  { name: 'mv_leaderboard',         delayMinutes: 10, cachePattern: 'leaderboard:*' },
  { name: 'mv_user_trap_stats',     delayMinutes: 20, cachePattern: 'analytics:trap_stats:*' },
  { name: 'mv_user_pressure_stats', delayMinutes: 30, cachePattern: 'analytics:pressure_stats:*' },
  { name: 'mv_user_monthly_solve',  delayMinutes: 40, cachePattern: 'analytics:monthly_solve:*' },
  { name: 'mv_user_topic_stats',    delayMinutes: 50, cachePattern: 'analytics:topic_stats:*' },
];

The 10-minute stagger between MV refreshes is intentional. REFRESH MATERIALIZED VIEW CONCURRENTLY runs a full sequential scan of user_attempts (potentially millions of rows). Running 6 such scans simultaneously would saturate I/O and thrash the PostgreSQL buffer cache. Staggering them distributes the I/O load over the 2-hour window.

The cron_health protocol

Every worker writes to cron_health after each job:

// workers/analytics-refresh.worker.ts
async function writeCronHealth(jobName: string, success: boolean, errorMessage: string | null) {
  await db.query(
    `INSERT INTO cron_health (job_name, last_run_at, last_status, error_message)
     VALUES ($1, now(), $2, $3)
     ON CONFLICT (job_name) DO UPDATE
     SET last_run_at = now(), last_status = $2, error_message = $3`,
    [jobName, success ? 'success' : 'failed', errorMessage],
  );
}

/api/health reads cron_health and alerts if any job hasn't run recently. This is the visibility layer: you know immediately if a cron job silently stopped running.

// workers/analytics-refresh.worker.ts
export function startAnalyticsRefreshWorker() {
  const worker = new Worker('analytics-refresh', processAnalyticsRefresh, {
    connection: redisConnection,
    concurrency: 1, // one MV refresh at a time
  });

concurrency: 1 means one MV refresh job runs at a time in this worker. With multiple workers running (PM2 cluster), each worker has its own concurrency: 1 — but BullMQ's distributed locking ensures a job is only processed by one worker. The concurrency setting controls how many jobs a single worker runs in parallel, not the global limit.

Full Tradeoff Map

Transferable Mental Model

The Work Queue Invariant: Any work that must survive the request lifecycle (because it can fail, retry, or take longer than the client will wait) must be persisted before the request acknowledges success. The queue is the persistence layer. The "job added to queue" operation must be atomic from the caller's perspective — not "I'll add it to the queue if the response also succeeds," but "I add it to the queue, full stop, and then I respond."

Chapter 6: Indexes

Physical Reality

A PostgreSQL table without an index is a heap — a pile of 8KB pages stored on disk, with no ordering. Searching for a row with WHERE user_id = $1 requires reading every page (sequential scan). For a table with 1 million rows at ~100 bytes per row, that's 100MB of data to scan. At disk read speed, that's 100ms-1s. Even with the data in shared_buffers (RAM), sequential scan of 12,500 pages takes measurable time.

How a B-tree index works physically

PostgreSQL's default index type is B-tree (Balanced tree). A B-tree index is a separate file on disk, structured as a balanced tree of fixed-size 8KB pages.

Each internal (non-leaf) page contains:

• N key values, sorted ascending
• N+1 child page pointers (physical page numbers in the index file)

Each leaf page contains:

• Key values, sorted ascending
• For each key: one or more ctid values (physical row location: page number + slot offset in the heap)

To look up WHERE user_id = 'alice':

1. Read the root page of the index (typically cached in shared_buffers — it's accessed frequently)
2. Binary search among root's keys to find which child pointer to follow
3. Read the child page (may or may not be in shared_buffers)
4. Binary search again, follow pointer
5. Repeat until reaching a leaf page (typically 3-4 levels for millions of rows)
6. Read the leaf page, find all matching ctids
7. For each ctid: read the heap page at that location, fetch the tuple

Index lookup complexity: O(log N) page reads, where N is the number of rows. For 1 million rows, that's approximately log₂(1,000,000) ≈ 20 levels. But B-trees are 8KB pages that hold many keys — a B-tree might have only 3-4 levels for millions of rows.

The heap fetch cost

After finding ctids in the leaf, you must fetch the actual heap pages. If the index has high selectivity (returns 1 row from 1 million), you fetch 1 heap page — cheap. If the index has low selectivity (returns 100,000 rows from 1 million), you may fetch 100,000 heap pages — potentially expensive, possibly more expensive than a sequential scan (which reads pages sequentially, triggering OS read-ahead that amortizes disk access cost).

PostgreSQL's query planner estimates selectivity and chooses sequential scan when it believes index lookup + heap fetch costs more than sequential scan.

Index bloat and MVCC

When you UPDATE user_attempts SET is_correct = true WHERE id = $1, PostgreSQL does not modify the row in place. It:

1. Marks the old row as dead (sets xmax to the current transaction ID)
2. Inserts a new row with the updated values
3. Inserts a new index entry pointing to the new row's ctid
4. Leaves the old index entry pointing to the dead heap row

Over time, both the heap and the index accumulate dead tuples. AUTOVACUUM periodically:

1. Scans the heap, marks dead tuple slots as reusable
2. Scans the index, removes entries pointing to dead heap rows

Until autovacuum runs, index size grows beyond the actual live data size. This is index bloat.

Covering indexes

An index covers a query when the query can be answered entirely from the index, without reading the heap:

CREATE INDEX idx_user_subject ON user_attempts(user_id, subject)
INCLUDE (is_correct, time_taken_seconds);

The INCLUDE clause stores extra columns in the index leaf pages. For a query:

SELECT is_correct, time_taken_seconds
FROM user_attempts
WHERE user_id = $1 AND subject = $2;

PostgreSQL can read is_correct and time_taken_seconds directly from the index leaf page, skipping the heap fetch entirely. This is an "index-only scan."

Partial indexes

CREATE INDEX idx_active_sessions ON sessions(user_id)
WHERE is_submitted = false;

This index only includes rows where is_submitted = false. It's smaller than a full index (only active sessions, not all historical ones). Queries that add WHERE is_submitted = false can use this index.

Junior Dev Path

You ship analytics. The subject stats query:

SELECT subject, COUNT(*) as total, SUM(is_correct::int) as correct
FROM user_attempts
WHERE user_id = $1
GROUP BY subject;

In development with 100 test rows, this runs in 1ms. In production with 500,000 rows for a heavy user, it takes 800ms. Why?

Without an index on user_id, PostgreSQL does a sequential scan: reads every row of user_attempts, filters those matching user_id = $1. At 500,000 rows, that's 500,000 comparisons. Even in RAM, this is slow.

You add the index:

CREATE INDEX idx_attempts_user_id ON user_attempts(user_id);

Now the query takes 50ms. Better, but still slow for a dashboard that loads 6 different analytics sections simultaneously.

You try materialized views:

CREATE MATERIALIZED VIEW mv_user_subject_stats AS
SELECT user_id, subject,
       COUNT(*) as total_attempts,
       SUM(is_correct::int) as correct_count,
       ROUND(100.0 * SUM(is_correct::int) / COUNT(*), 1) as accuracy
FROM user_attempts
GROUP BY user_id, subject;

CREATE UNIQUE INDEX ON mv_user_subject_stats(user_id, subject);

Now the query is:

SELECT * FROM mv_user_subject_stats WHERE user_id = $1;

This hits an index on a much smaller table (one row per user per subject, vs millions of attempt rows). Query time: 1-2ms.

But the MV is stale. You need to refresh it. If you refresh on every attempt insert, it defeats the purpose (expensive aggregation runs on every insert). You refresh periodically (every 2 hours) — acceptable for analytics, not for real-time data.

The Insight

An index is a pre-sorted, separate copy of one column (or multiple columns) with pointers back to the full row. Reading the index is faster than reading the table because you skip most pages. A materialized view is a pre-aggregated copy of a query result — it trades staleness for read speed.

What AttemptIQ Does

The materialized views are refreshed by the analytics worker:

// workers/analytics-refresh.worker.ts
const ALLOWED_VIEWS = MV_SCHEDULE.map((mv) => mv.name);

async function processAnalyticsRefresh(job: Job) {
  const viewName = job.data?.viewName as string;

  if (!ALLOWED_VIEWS.includes(viewName)) {
    throw new Error(`Invalid materialized view: ${viewName}`);
  }

  const concurrently = job.data?.concurrently !== false; // default true

  const sql = concurrently
    ? `REFRESH MATERIALIZED VIEW CONCURRENTLY ${viewName}`
    : `REFRESH MATERIALIZED VIEW ${viewName}`;

  try {
    await db.query(sql);

The ALLOWED_VIEWS whitelist prevents SQL injection. Even though the worker is internal code, the view name comes from job data (which comes from Redis, which could be tampered with if Redis is compromised). The whitelist ensures only valid view names are used. This is defense in depth.

REFRESH MATERIALIZED VIEW CONCURRENTLY requires a unique index on the MV. It works by:

1. Computing the full new result into a temporary table
2. Diffing the temporary table against the current MV
3. Applying the diff (inserts, updates, deletes) to the MV

During this operation, readers can still query the MV (they get stale but consistent data). Without CONCURRENTLY, the MV is locked for reads during the refresh.

First MV refresh must not use CONCURRENTLY

REFRESH CONCURRENTLY requires the MV to be non-empty and to have a unique index. The very first refresh (when the MV is empty after creation) must be a plain REFRESH. AttemptIQ's gotchas doc documents this:

First MV refresh: plain REFRESH (not CONCURRENTLY). Subsequent: REFRESH CONCURRENTLY.

The concurrently flag in job data allows the first refresh to be a plain refresh and all subsequent ones to be concurrent.

The weak-area question selection query

-- From repositories/sessions.repo.ts _selectWeakAreaQuestions
WITH weak_topics AS (
  SELECT topic
  FROM mv_user_topic_stats
  WHERE user_id = $1
    AND subject = $2
    AND total_attempts >= 3
  ORDER BY accuracy ASC
  LIMIT 3
),
ranked AS (
  SELECT
    q.id,
    ROW_NUMBER() OVER (ORDER BY
      CASE WHEN last_ua.id IS NULL THEN 0 ELSE 1 END,
      CASE WHEN q.topic IS NOT NULL AND q.topic IN (SELECT topic FROM weak_topics)
           THEN 0 ELSE 1 END,
      RANDOM()
    ) AS rn
  FROM questions q
  LEFT JOIN LATERAL (
    SELECT id, is_correct
    FROM user_attempts
    WHERE user_id = $1 AND question_id = q.id
    ORDER BY attempted_at DESC
    LIMIT 1
  ) last_ua ON true
  WHERE q.is_approved = true
    AND q.subject = $2
    AND (last_ua.id IS NULL OR last_ua.is_correct = false)
)
SELECT id FROM ranked WHERE rn <= $3

This query uses a LATERAL join — for each question row, it runs a correlated subquery to find the last attempt. This looks like an N+1 query (one subquery per question row), but PostgreSQL can optimize lateral joins with an index on (user_id, question_id) on user_attempts. The optimizer can turn this into an index nested-loop join: for each question, one index lookup on user_attempts. Still O(questions) index lookups, but each is fast.

Full Tradeoff Map

Transferable Mental Model

The Pre-computation Tradeoff: Every index and materialized view is a pre-computed answer to a specific question, stored in a different shape than the source data. You pay in storage and maintenance cost (on every write, the pre-computed answer becomes stale and must be updated). You gain in read speed (the answer is already computed). The key question is: how often is the question asked, and how stale can the answer be?

Chapter 7: Transactions and ACID

Physical Reality

A database transaction groups multiple operations so they either all succeed or all fail. The physical mechanisms that make this work are:

Write-Ahead Log (WAL)

Before PostgreSQL modifies any data page, it writes a log record describing the change to the WAL. The WAL is a sequential append-only file on disk. Writing to a sequential file is fast (disk head doesn't need to seek) and can be batched.

When you call COMMIT, PostgreSQL calls fsync() on the WAL file. fsync() is a system call that instructs the kernel to flush its write buffer to the physical disk. The disk acknowledges when the bytes are physically stored (or in the disk's own capacitor-backed write cache). After fsync() returns, PostgreSQL sends the COMMIT acknowledgment to the client.

If the server crashes after fsync() but before modifying the actual data pages, the WAL can be replayed on restart to apply the modifications. The data pages might be stale (pre-modification), but the WAL has the record of what should have changed. This is the durability guarantee.

Why WAL is faster than writing data pages directly

Data pages are 8KB each, scattered randomly on disk (based on where the table was allocated, which pages have free slots, etc.). Writing a page requires a disk seek to the correct location.

WAL records are written sequentially to the end of the WAL file. No seek. Sequential writes are 10-100x faster than random writes on HDD, and 5-10x faster on NVMe SSD.

MVCC (Multi-Version Concurrency Control)

PostgreSQL never overwrites data in place. When you update a row:

1. The old row version is marked as "dead" by setting its xmax to the current transaction ID
2. A new row version is inserted at a new heap location with the updated values

Each row version has two transaction IDs embedded:

• xmin: the transaction that created this version (it's visible to transactions that started after xmin committed)
• xmax: the transaction that deleted/updated this version (it's invisible to transactions that started after xmax committed)

When a transaction reads a row, it checks the snapshot it was given at the start:

• Which transactions had committed at the time this snapshot was taken?
• A row version is visible if xmin committed before the snapshot AND (xmax is not set OR xmax committed after the snapshot)

This gives each transaction a consistent view of the database as it existed at a point in time, without locking. Writers don't block readers, readers don't block writers.

The cost: old row versions accumulate until AUTOVACUUM removes them (called table bloat). And each write creates two row versions (old + new).

Atomicity via rollback

If a transaction aborts (error or explicit ROLLBACK), PostgreSQL marks all changes from that transaction as rolled back. Since rows are not written in place, rollback is cheap: just mark those xmin transactions as aborted in the pg_xact file. Subsequent readers see these rows as invisible. No actual undo of data writes.

The payment transaction

// services/payments.service.ts
await client.query('BEGIN');

const { rows: paymentRows } = await client.query<{ id: string }>(
  `UPDATE payments
   SET dodo_payment_id = $2, status = 'captured', captured_at = now(),
       amount_paise = $3, dodo_session_id = COALESCE(NULLIF(dodo_session_id, 'pending_' || id::text), dodo_session_id)
   WHERE id = $1 AND status = 'pending'
   RETURNING id`,
  [paymentId, dodoPaymentId, totalAmount],
);

if (paymentRows.length === 0) {
  await client.query('ROLLBACK');
  return { status: 'already_processed' };
}

await client.query(
  `UPDATE user_profiles
   SET plan_tier = $3,
       plan_expires_at = GREATEST(COALESCE(plan_expires_at, now()), now()) + make_interval(days => $2)
   WHERE user_id = $1`,
  [metaUserId, planDays, planId],
);

await client.query(
  `UPDATE webhook_events SET processed = true WHERE id = $1`,
  [dodoPaymentId],
);

await client.query('COMMIT');

Three operations in one transaction:

1. Capture the payment (change status from 'pending' to 'captured')
2. Upgrade the user's plan (set plan_tier, extend plan_expires_at)
3. Mark the webhook event as processed

If the server crashes between operation 1 and 2: PostgreSQL replays WAL on restart, rolls back the partial transaction. Payment remains 'pending'. Webhook can be retried. No inconsistency.

If the server crashes between operation 3 and COMMIT: same result. The COMMIT was not written to WAL. Rollback. Webhook retried.

If COMMIT is written to WAL but server crashes before acknowledging to the app: PostgreSQL replays the COMMIT on restart. All three operations are permanent. The webhook gateway retries (it got no acknowledgment) — but insertEvent idempotency check (already_processed) prevents double-processing.

The WHERE status = 'pending' RETURNING id pattern

This is the compare-and-swap pattern. The UPDATE only succeeds if the row's current state matches. If another webhook delivery already processed this payment (set status to 'captured'), this UPDATE finds no rows (0 rows matched). paymentRows.length === 0 → ROLLBACK → return 'already_processed'. No double-capture.

This is the second layer of the idempotency guard (the first is insertEvent's ON CONFLICT). Even if both layers fail (theoretical), this database-level compare-and-swap prevents double-capture.

Junior Dev Path

You implement the payment webhook without a transaction:

// Naive — no transaction
async function handlePayment(paymentId: string, userId: string, planDays: number) {
  // Step 1: mark payment captured
  await db.query(`UPDATE payments SET status = 'captured' WHERE id = $1`, [paymentId]);
  
  // --- CRASH HERE ---
  
  // Step 2: upgrade user plan
  await db.query(`UPDATE user_profiles SET plan_tier = 'monthly', plan_expires_at = now() + interval '30 days' WHERE user_id = $1`, [userId]);
  
  // Step 3: mark webhook processed
  await db.query(`UPDATE webhook_events SET processed = true WHERE id = $1`, [paymentId]);
}

If the server crashes after step 1 (or if step 2 throws a DB error):

• Payment is marked 'captured' in your system ✓
• User's plan is NOT upgraded ✗
• Webhook is NOT marked processed

The payment gateway retries the webhook. Your handler runs again:

• Step 1: UPDATE payments WHERE id = $1 AND status = 'pending' → 0 rows (status is already 'captured')
• You might check the return and think it's already processed
• But you never upgraded the plan!

User paid. Payment recorded. Plan not upgraded. User is furious.

The transaction makes this impossible: either all three succeed, or none do. On retry, the transaction runs cleanly from the beginning (payment still 'pending', can be captured).

Another failure: separate transactions for logically related operations

// Two separate transactions — still wrong
await db.query('BEGIN');
await db.query(`UPDATE payments SET status = 'captured' WHERE id = $1`, [paymentId]);
await db.query('COMMIT');

// --- Another request reads the DB here: payment captured, plan not upgraded ---

await db.query('BEGIN');
await db.query(`UPDATE user_profiles SET plan_tier = ...`, [userId]);
await db.query('COMMIT');

Between the two transactions, the database is in an inconsistent state: payment captured but plan not upgraded. If a request reads at this moment (checking plan status), it sees unpaid access. This inconsistency window is very small but exists.

The one-transaction approach ensures there is no inconsistency window. The state atomically goes from (payment pending, plan free) to (payment captured, plan upgraded).

The Insight

Atomicity means the database has no inconsistency windows: you transition from one valid state to another in a single step. The WAL is the physical mechanism that makes this possible without sacrificing write speed — by writing changes sequentially to a log, then applying them to data pages later.

What AttemptIQ Does

The payment webhook transaction was shown above. The session creation also uses a transaction (via _lockedInsertSession):

private async _lockedInsertSession(userId: string, sql: string, params: unknown[]): Promise<string | null> {
  const client = await db.connect();
  try {
    await client.query('BEGIN');
    await client.query('SELECT pg_advisory_xact_lock(hashtext($1::text)::bigint)', [userId]);
    const { rows } = await client.query<{ id: string }>(sql, params);
    await client.query('COMMIT');
    return rows[0]?.id ?? null;
  } catch (err) {
    await client.query('ROLLBACK');
    throw err;
  } finally {
    client.release();
  }
}

The finally block releases the pooled connection regardless of success or failure. This is critical: if you forget to release on error, the connection is leaked. With 10 connections per PM2 worker, 10 concurrent errors would exhaust the pool, and all subsequent requests would queue waiting for a connection — effectively a self-imposed denial of service.

Full Tradeoff Map

Transferable Mental Model

The Invariant Boundary: A transaction defines the boundaries within which your database invariants must hold. Between your application's BEGIN and COMMIT, the database may be in any intermediate state. Outside those boundaries (to other transactions), the database either shows the full before-state or the full after-state — never the intermediate. Design transactions to enclose all operations that together constitute one logical state transition.

Chapter 8: N+1 Queries

Physical Reality

N+1 is the most common database performance bug. It occurs when you fetch a list of N items and then make one database query per item to fetch related data — total queries: N+1.

Why is each extra query expensive?

Each database query, even a trivially fast one, incurs these physical costs:

1. TCP round-trip: Even on localhost, sending bytes over a loopback TCP socket has overhead: kernel copies bytes from your process's memory to the socket send buffer, the receiving process reads from the socket receive buffer, sends a response, you read the response. Loopback latency: ~50-200 microseconds. Over the network: ~500µs-2ms.

2. Query parse: PostgreSQL parses your SQL string into a parse tree. Even with prepared statements (which cache the parse result), there's a lookup overhead.

3. Query planning: For each query, the optimizer chooses an execution plan (which index to use, how to join tables). Prepared statement caching helps, but complex queries re-plan periodically.

4. Context switching: When your application calls db.query(), it suspends (the Node.js event loop handles other work). When PostgreSQL responds, your process resumes. Each suspend-resume cycle is a context switch between the app process and the PostgreSQL process — not free.

5. pg.Pool coordination: Acquiring a connection from the pool, executing the query, and releasing the connection involves synchronization on the pool's internal state.

At 50µs per query × 100 items = 5ms of pure overhead, ignoring actual query execution time. At 1,000 items, that's 50ms of overhead. At 10,000 items, 500ms — purely from query overhead, before considering actual disk I/O.

Why N separate queries are almost always worse than one query with IN

A single query SELECT * FROM questions WHERE id = ANY($1::uuid[]) sends one TCP packet (or a few, if the array is large), one parse, one plan, one execution that reads the relevant index pages and heap pages, and one response. The overhead is fixed regardless of N (as long as the array fits in memory).

The execution is also more efficient: PostgreSQL can sort the ctids from the index lookup and fetch heap pages in physical order (not random order per request), minimizing disk seeks.

The Map pattern for O(1) lookup

After fetching all results in one query, you need to correlate them with your original list. A loop-inside-a-loop is O(N²):

// O(N²) — wrong
for (const questionId of questionIds) {
  const attempt = attempts.find(a => a.question_id === questionId); // O(N)
}

A Map lookup is O(1) average case (hash table):

// O(N) — correct
const attemptMap = new Map(attempts.map(a => [a.question_id, a]));
for (const questionId of questionIds) {
  const attempt = attemptMap.get(questionId); // O(1)
}

For 100 questions, O(N²) does 10,000 comparisons. O(N) does 100. For 1,000 questions: 1,000,000 vs 1,000. The JavaScript runtime also benefits from Map because it's implemented as a hash table (V8 uses a hybrid flat array + hash table for small maps, full hash table for large).

Promise.all for concurrent queries

Node.js is single-threaded for JavaScript execution but I/O is non-blocking. When you await db.query(...), the Node.js event loop suspends your function and handles other tasks while waiting for PostgreSQL's response. This means you can fire multiple queries simultaneously:

const [attempts, publicQuestions, questionsWithAnswers] = await Promise.all([
  this.attemptsRepo.listBySession(sessionId),
  this.questionsRepo.findAllByIds(questionIds),
  this.questionsRepo.findAllByIdsWithAnswers(questionIds),
]);

All three queries are sent to the database simultaneously (on three connections from the pool). PostgreSQL executes them in parallel on three backend processes. Total time = max(query1, query2, query3), not query1 + query2 + query3.

If each query takes 20ms, sequential execution takes 60ms. Parallel execution takes 20ms. For a user-facing request, this is the difference between fast and slow.

Junior Dev Path

You implement getFullSessionResult naively:

// Naive — N+1
async function getFullSessionResult(sessionId: string, userId: string) {
  const session = await sessionsRepo.findByIdForUser(sessionId, userId);
  const attempts = await attemptsRepo.listBySession(sessionId);
  
  const questions = [];
  for (const attempt of attempts) {
    const question = await questionsRepo.findById(attempt.question_id); // 1 query per attempt
    const answer = await questionsRepo.findAnswerById(attempt.question_id); // 1 more per attempt
    questions.push({ ...question, ...answer, ...attempt });
  }
  
  return questions;
}

For a 100-question mock test:

• 1 query for session
• 1 query for attempts
• 100 queries for questions
• 100 queries for answers
• Total: 202 queries

At 50µs overhead per query: 202 × 50µs = 10.1ms of pure overhead. Plus query execution time. Plus network latency (if DB is on separate host). This is slow and also consumes 202 PostgreSQL connection "slots" over the course of the function.

First fix: batch the obvious loops

const questionIds = attempts.map(a => a.question_id);
const questions = await questionsRepo.findAllByIds(questionIds); // 1 query for all
const answers = await questionsRepo.findAllByIdsWithAnswers(questionIds); // 1 query for all
// Total: 4 queries

But now you have arrays that you need to correlate with attempts. Without a Map:

// Still O(N²)
for (const attempt of attempts) {
  const question = questions.find(q => q.id === attempt.question_id); // O(N)
  const answer = answers.find(a => a.id === attempt.question_id); // O(N)
}

With Map:

const questionMap = new Map(questions.map(q => [q.id, q]));
const answerMap = new Map(answers.map(a => [a.id, a]));

for (const attempt of attempts) {
  const question = questionMap.get(attempt.question_id); // O(1)
  const answer = answerMap.get(attempt.question_id); // O(1)
}

Now total work is O(N) not O(N²).

Second fix: parallelize the batch queries

// Still sequential — each waits for the previous
const attempts = await attemptsRepo.listBySession(sessionId); // wait 20ms
const questions = await questionsRepo.findAllByIds(questionIds); // wait 20ms
const answers = await questionsRepo.findAllByIdsWithAnswers(questionIds); // wait 20ms
// Total: 60ms

vs.

// Parallel — all three in-flight simultaneously
const [attempts, questions, answers] = await Promise.all([
  attemptsRepo.listBySession(sessionId),
  questionsRepo.findAllByIds(questionIds),
  questionsRepo.findAllByIdsWithAnswers(questionIds),
]);
// Total: 20ms (max of three)

The Insight

Every extra database query is a TCP round-trip plus PostgreSQL process overhead. N+1 is not a database problem — it is an application architecture problem: fetching data one item at a time when the database can return all items in one pass.

What AttemptIQ Does

// services/sessions.service.ts
async getFullSessionResult(sessionId: string, userId: string): Promise<FullSessionResult> {
  const session = await this.sessionsRepo.findByIdForUser(sessionId, userId);
  if (!session) throw new NotFoundError('Session not found');
  if (session.session_type !== 'practice') throw new ForbiddenError('Not a practice session');
  if (!session.is_submitted) throw new ForbiddenError('Session not yet submitted');

  const questionIds = session.question_ids ?? [];

  const [attempts, publicQuestions, questionsWithAnswers] = await Promise.all([
    this.attemptsRepo.listBySession(sessionId),
    this.questionsRepo.findAllByIds(questionIds),
    this.questionsRepo.findAllByIdsWithAnswers(questionIds),
  ]);

  const attemptMap = new Map(attempts.map(a => [a.question_id, a]));
  const publicMap  = new Map(publicQuestions.map(q => [q.id, q]));
  const answerMap  = new Map(questionsWithAnswers.map(q => [q.id, q]));

Three queries in parallel. Three Maps built in O(N). Then:

  const questions: FullSessionQuestionResult[] = questionIds.map(qId => {
    const attempt  = attemptMap.get(qId);
    const pub      = publicMap.get(qId);
    const withAns  = answerMap.get(qId);

Building the result array is O(N) — one Map lookup per question, constant time each. For 100 questions, 300 Map lookups. No DB queries.

Why questionsWithAnswers is a separate query from publicQuestions

publicQuestions returns questions without correct_answer and explanation — these are the public-safe fields (no answer revealed before submission). questionsWithAnswers includes correct_answer and explanation. This is enforced at the repository layer:

• findAllByIds() → excludes correct_answer, explanation
• findAllByIdsWithAnswers() → includes all fields

This ensures that even if a future refactor accidentally passes public question objects to the wrong place, the answer is not in the object. Defense in depth through data shaping at fetch time.

The session.question_ids array preserves order

PostgreSQL array question_ids stores questions in the order they were assigned. The questionIds.map(qId => ...) iterates in this order. The result preserves the original question order. If you had done SELECT * FROM user_attempts WHERE session_id = $1, the order might differ (PostgreSQL heap order, not logical session order). Storing question IDs in the sessions row ensures deterministic ordering.

Full Tradeoff Map

The JOIN alternative

You could fetch everything in one query with JOINs:

SELECT a.*, q.body, q.options, qans.correct_answer, qans.explanation
FROM user_attempts a
JOIN questions q ON q.id = a.question_id
JOIN questions qans ON qans.id = a.question_id -- same table twice for different column sets
WHERE a.session_id = $1;

This is 1 query vs 3. But:

• Harder to type-safely decode in TypeScript (mixed columns from multiple tables)
• If attempts and questions are on different database shards (at scale), a JOIN is impossible
• Less modular: the repository interfaces are separate (attemptsRepo, questionsRepo), and joining across them couples them

The 3-query Promise.all approach keeps the repositories independent and achieves similar performance (same wall-clock time, more PostgreSQL-side parallelism).

Transferable Mental Model

The Batch-and-Map Pattern: Whenever you need to correlate two datasets, fetch both in O(1) DB operations (one query per dataset, or parallel queries), then correlate in memory with a hash map in O(N) time. Never correlate by querying inside a loop. The cost of N database round-trips is O(N × latency), which is always worse than O(N × hash_lookup) for any realistic latency.

Synthesis: The Architect's Checklist

After working through these eight chapters, here is the decision framework that should run automatically when you design any system:

On every connection or resource

Ask: What is the fixed cost per unit? (OS process, thread, socket, file descriptor)

Then ask: Can I multiplex N consumers onto M units (M << N)?

If yes: a pool or gatekeeper is required. Define the protocol (transaction-scoped vs session-scoped) and document what stateful features you cannot use.

On every cache

Ask: Which layers does this cache skip?

Then ask: What does skipping those layers make impossible?

If the answer includes "relational queries" or "consistency across keys" or "crash safety," ensure you're only caching data that doesn't require those properties.

On every read-then-write operation

Ask: Is there a window between the read and the write where concurrent operations can invalidate my read?

If yes: either make read-and-write atomic (embed the read's condition in the write's WHERE clause) or acquire exclusive ownership before reading (advisory lock).

On every multi-step state change

Ask: What is the inconsistency state between steps?

If any other actor could observe an inconsistency: wrap all steps in a single transaction.

On every loop that accesses the database

Ask: Am I querying inside this loop?

If yes: extract the IDs, batch fetch outside the loop, build a Map, look up inside the loop.

On every I/O operation that doesn't depend on another

Ask: Can I run this in parallel with Promise.all?

If yes: run it in parallel.

On every job that can fail or must outlive the HTTP request

Ask: Is this in a queue?

If not: can the client wait? Can you afford to lose this work on a crash?

On every design decision

Ask: What assumption am I making that could break at 10x scale?

• Pool of 25 PostgreSQL connections → fine at 1K users, bottleneck at 100K → need read replicas
• Single Redis instance → fine at 1K users, single point of failure at 100K → need Redis Sentinel or Cluster
• Single server → fine at AttemptIQ's current scale, bottleneck at 10K concurrent → need horizontal scaling
• Stale analytics (2hr refresh) → acceptable at current scale → still acceptable at 10x (users don't need real-time analytics)
• Materialized views → fine for read-heavy analytics, bottleneck if write load is 100x → need streaming aggregation

The 10x question is not "what breaks" — it's "what breaks first, and what does fixing that break next?"

Every code snippet in this document is copied verbatim from the AttemptIQ codebase. This document does not modify any other file.