What Really Happens When Your App Queries a Database: CPU, Memory, the OS, Async, and Locks Explained

A ground-up mental model of processes, threads, context switches, the Node.js event loop, PostgreSQL internals, and why “waiting” is the hidden theme of almost every performance problem.

You write await prisma.user.findMany(). A few milliseconds later, rows appear. It feels like one step. In reality that line triggers a chain of events across your application process, the operating system, the network, the CPU, memory, and a database server with its own processes, caches, and locks. When something is slow, the cause is almost always hiding in one of those hand-offs.

This article follows a single query on its journey and explains each layer as it appears. You will learn:

• What the CPU, memory, and the operating system (OS) each do, and how they cooperate.
• The difference between processes, threads, and async tasks, and what a context switch costs.
• How Node.js can handle thousands of requests with one JavaScript thread, and how to accidentally break that.
• How PostgreSQL uses processes, shared memory, and locks, and what “waiting on a lock” physically means.
• How to tell whether a slowdown is caused by CPU, memory, disk, locks, or the application itself.

The central idea is simple: almost every system spends most of its time waiting. Performance work is mostly about finding out who is waiting for what, and why.

Scope. Examples use Node.js and PostgreSQL on Linux, because they are common in backend work. The core ideas (scheduling, memory, blocking, locking) apply to almost every language, database, and operating system, though details and numbers vary. Every number here is a rough, typical figure to build intuition, not a benchmark.

Quick glossary

Skim this table first and return to it whenever a term is unfamiliar. Experienced readers can skip it.

Term	Plain-language meaning
Process	A running program with its own private memory. One process cannot directly read another's memory.
Thread	A flow of execution inside a process. Threads of the same process share that process's memory.
Kernel / OS	The privileged core of the operating system. It manages CPU time, memory, devices, and networking on behalf of all programs.
System call (syscall)	A request from a program to the kernel, such as reading from a socket or file.
Context switch	The CPU stopping one thread and starting another, saving and restoring state in between.
Blocking	A thread that cannot continue until something happens (data arrives, a lock is released), so the OS puts it to sleep.
Concurrency	Making progress on several tasks during the same period, possibly by interleaving them.
Parallelism	Actually executing several tasks at the same instant, on different CPU cores.
CPU-bound / I/O-bound	Work limited by computation versus work limited by waiting for disk or network.
Latency / throughput	How long one operation takes versus how many operations complete per second.

1. The big picture: who talks to whom

Here is the cast of characters on a typical backend deployment. The diagram shows an application server and a database server, each running an OS kernel on top of its own CPU, memory, and devices.

Read it as layers. Your code never touches hardware directly. It asks the kernel, through system calls, to send bytes over the network. The kernel decides when each program gets CPU time, hands out memory, and moves data to and from the network card and disks. On the database host, the same kernel services the PostgreSQL processes in the same way.

Why speed differences drive everything

The resources in that diagram differ enormously in speed. This table gives typical latencies and, in the last column, a human-scale analogy: if one nanosecond were one second, how long would each operation feel? The goal is to show relative magnitudes, which explain why systems are designed around avoiding the slow things.

Operation	Typical latency	If 1 ns were 1 second
CPU cache hit (L1)	about 1 ns	1 second
Read from RAM	about 100 ns	under 2 minutes
Read from an NVMe SSD	about 100 µs	about 1 day
Network round trip inside a data center	about 0.5 ms	about 6 days
Read from a spinning hard disk (seek)	about 5 ms	about 2 months
Round trip across a continent	about 100 ms	about 3 years

The lesson: reaching RAM is thousands of times faster than reaching disk or another machine. So caches exist at every level (CPU caches, the OS page cache, the database buffer cache), and so do techniques for doing something useful instead of sitting idle while a slow operation finishes. Async programming, thread scheduling, and connection pooling are all variations on that theme.

2. The CPU: what “executing” really means

A CPU core executes one stream of instructions at a time. A modern CPU has several cores, so several streams can truly run at the same instant. This is parallelism, and the number of cores is its hard upper limit.

Hardware threads (SMT / Hyper-Threading)

Many CPUs expose each physical core as two (or occasionally more) hardware threads. This is called SMT (simultaneous multithreading), and Intel's version is Hyper-Threading. The two hardware threads share one core's execution units, so when one stalls (for example waiting for data from RAM), the other can use the idle units. The gain is typically a modest increase in throughput, not a doubling. The OS sees each hardware thread as a separate logical CPU, which is why a 4-core machine often shows 8 CPUs.

A CPU core typically supports 1 or 2 hardware threads, depending on its architecture.

CPU configuration	Hardware threads per core
Without SMT / Hyper-Threading	1
With SMT / Intel Hyper-Threading	2
Some server CPUs, such as IBM POWER	4 or 8

CPU-bound versus I/O-bound work

The single most useful classification in performance work:

• CPU-bound work keeps the CPU busy computing: image processing, compression, hashing passwords, parsing a giant JSON document, running a complex SQL aggregation. Adding more concurrency does not help once all cores are busy, because the limit is raw computation.
• I/O-bound work spends most of its time waiting for something external: a database reply, a disk read, an HTTP call. The CPU is mostly idle during those waits, so one core can juggle many such tasks.

Typical web APIs are I/O-bound: a request does a little computation, then waits for the database. That is exactly why a single Node.js thread can serve many users. It is also why the database host is often CPU-bound while the application host is nearly idle. Most real systems contain both kinds of work, and the right scaling tool differs for each.

3. Processes, threads, and the scheduler

Processes and threads

The OS gives every process its own private virtual address space, which is a view of memory that other processes cannot touch. This isolation is why one crashing program does not corrupt another. A process can contain one or more threads. Threads in the same process share its memory, which makes sharing data cheap but creates the risk of race conditions (two threads changing the same data at once).

The scheduler and thread states

There are almost always more runnable threads than CPU cores, so the kernel's scheduler decides which thread runs on which core and for how long. A thread is always in roughly one of three states:

• Running: executing on a core right now.
• Runnable (ready): could run immediately but is waiting for a free core. These threads form the run queue.
• Blocked (sleeping): cannot continue until an event occurs, such as network data arriving or a lock being released. A blocked thread uses no CPU time.

The scheduler gives each running thread a short time slice (on the order of milliseconds). If the thread does not finish or block by then, the kernel preempts it: pauses it, puts it back in the run queue, and runs another. This is how a few cores create the illusion that hundreds of programs run simultaneously.

What is a context switch?

When the CPU stops running one thread and starts another, the kernel performs a context switch:

1. Save the outgoing thread's CPU state: registers, the program counter (which instruction comes next), and the stack pointer.
2. Choose the next thread.
3. Restore that thread's saved state.
4. If the new thread belongs to a different process, also switch the memory mapping (the page tables).

Context switches are essential, but they are not free:

• Direct cost: the switching work itself, roughly on the order of microseconds.
• Indirect cost, usually larger: the CPU's caches and address-translation buffers (the TLB) are warmed up for the old thread's data. After a switch, the new thread starts with a partly cold cache and runs slower until its own data is loaded.

So a few context switches per second are harmless, but tens of thousands per second per core mean the CPU spends a meaningful share of its time on bookkeeping and cache refills instead of useful work. Two flavors are worth knowing. A voluntary switch happens when a thread blocks itself (waiting for I/O or a lock); that is normal for I/O-heavy programs. An involuntary switch happens when the scheduler preempts a thread that still wanted to run; a high rate of these means too many threads are competing for too few cores.

Processes, threads, and async tasks compared

Three different units of “things happening at once” are often mixed up. The table compares them across the dimensions that matter: memory sharing, switching cost, and isolation.

	Process	Thread	Async task (event-loop callback or promise)
Memory	Private to the process	Shared with sibling threads	Shared (it runs inside one thread)
Created by	OS (for example fork)	OS (inside a process)	Your runtime (no OS involvement)
Switch cost	Highest (memory mapping changes)	Medium	Very low (an ordinary function call by the runtime)
Isolation	Strong: one crash does not affect others	Weak: one thread can corrupt shared data	None: they share everything and cooperate voluntarily
Can use several cores at once	Yes	Yes	No, not on their own
Example	A PostgreSQL connection backend	Worker threads, a database server's workers in other systems	Every await in Node.js

The row to focus on is the last one: async tasks are cheap precisely because the OS does not know they exist. They are the runtime's own bookkeeping, which is why one Node.js process can keep thousands of requests in flight. The price is that they cannot use more than one core, and that they must cooperate (section 6).

Signs of CPU Saturation

When more threads are ready to execute than there are available CPU cores, some threads must wait for CPU time. Unlike I/O-bound threads, these threads are not waiting for a disk, network, or other external operation; they are waiting for the CPU to become available.

Common signs of CPU contention include:

• A long run queue: Many threads are ready to execute but cannot get CPU time immediately.
• High context-switch rates: The operating system frequently switches between threads to share CPU time.
• Increasing latency under load: Tasks take longer to complete as more threads compete for CPU resources.

The load average reported by tools such as top provides another useful indicator. On Linux, it reflects the average number of tasks that are running, waiting to run, or stuck in uninterruptible sleep, typically due to I/O operations.

If the load average consistently exceeds the number of logical CPUs, it may indicate CPU contention. However, it can also indicate significant I/O waiting, so load average alone cannot tell you which resource is the bottleneck. Section 9 explains how to distinguish CPU saturation from I/O bottlenecks.

4. Memory: virtual, shared, and finite

Virtual memory

Programs do not use physical RAM addresses directly. The OS gives each process virtual memory, divided into fixed-size pages (commonly 4 KB), and maps virtual pages to physical RAM using page tables. This provides isolation (a process only sees its own mappings), flexibility (memory need not be contiguous), and the ability to share pages deliberately.

A few related terms:

• Page fault: a program touches a virtual page that is not currently mapped to RAM. A minor fault is cheap (the data is already in memory and just needs mapping). A major fault requires reading from disk and is thousands of times slower.
• Page cache: spare RAM that the OS uses to cache recently read file data. This is why a database that fits in RAM runs far faster than one that has to hit disk.
• Swap: when RAM runs short, the OS can move rarely used pages to disk. A server that actively swaps is typically in serious trouble, because every swapped page costs a disk-speed delay.
• OOM killer: if memory is truly exhausted, Linux may kill a process to free memory. If it kills a PostgreSQL backend, the database treats it as a crash and restarts all backends in crash recovery, dropping every connection. Memory exhaustion is therefore never a gentle failure.

Private versus shared memory, and fork

Processes are isolated by default, but the OS can map the same physical memory into several processes. This is shared memory, and it is how PostgreSQL's separate processes coordinate.

When PostgreSQL accepts a new client connection, its main process, called the postmaster, creates a separate backend process using fork().

On Linux, fork() uses a technique called copy-on-write (CoW). Instead of immediately copying all the parent's memory, the child process initially shares the same memory pages. A page is copied only when the parent or child modifies it. This reduces the memory and processing overhead of creating a new process.

However, process creation still involves overhead, including process initialization and resource allocation. Creating a new PostgreSQL connection is therefore more expensive than reusing an existing connection from a connection pool, which avoids repeatedly creating backend processes.

How PostgreSQL Uses Memory

To understand PostgreSQL's memory usage, you first need to understand the relationship between a database connection and a backend process.

When a client, such as a Node.js application, connects to PostgreSQL, PostgreSQL typically creates a dedicated backend process for that connection. The backend is the server-side process that handles the client's SQL queries, manages its transactions, and returns results.

For example, if your application opens 20 connections, PostgreSQL will typically have 20 backend processes serving those connections. Even when some connections are idle, their backend processes still consume memory and other system resources.

PostgreSQL memory is divided into two main categories: shared memory, which multiple backend processes can access, and private memory, which belongs to an individual backend or a specific query operation.

The following table explains the main memory areas and why they matter when configuring connection pools and query settings.

Memory area	Shared or private?	Purpose	Why it matters
shared_buffers	Shared	Caches table and index pages read from disk.	Reduces disk I/O by keeping frequently accessed database pages in memory. A common initial configuration is around 25% of RAM on a dedicated database server, but the optimal value depends on the workload and available memory.
WAL buffers	Shared	Temporarily hold write-ahead log (WAL) records before they are written to WAL files.	WAL records describe database changes and are essential for crash recovery. Commit operations may need to wait for the relevant WAL records to be flushed to durable storage, depending on the commit settings.
Lock tables and process state	Shared	Track locks, active transactions, and backend process state.	Allow backend processes to coordinate access to database resources and detect transaction conflicts. Section 8 covers locking in more detail.
work_mem	Private, per operation	Provides working memory for operations such as sorting and hashing.	A single query can use multiple sort or hash operations, each potentially consuming its own allocation. Many concurrent queries can therefore consume substantial RAM.
Backend process overhead	Private	Holds process-specific state, bookkeeping, and other memory allocations.	Each connection has a baseline resource cost, even when it is idle. Actual usage varies with the workload and PostgreSQL configuration.

Why this matters for connection pooling

Consider a Node.js application with 100 database connections. Each connection typically corresponds to a PostgreSQL backend process, so PostgreSQL must maintain resources for 100 backends.

Now suppose many of those connections execute complex queries simultaneously. Each query might perform several memory-intensive operations, such as sorting and hashing. The resulting memory consumption can grow much faster than the connection count alone suggests.

This is why connection limits and per-query memory settings must be configured together. A generous work_mem setting may be safe with a small number of concurrent queries but dangerous when hundreds of queries execute simultaneously.

Two additional details are important:

• PostgreSQL and the operating system both cache data. PostgreSQL uses shared_buffers, while Linux also maintains a filesystem page cache. The same database data can exist in both caches, which is normal. However, the memory implications depend on the database's I/O behavior and configuration.
• work_mem is not a per-connection memory limit. It applies to individual query operations, and one query can have several operations using it. Some operations may also use parallel workers, increasing total memory consumption further.

The practical lesson is that adding database connections does not simply increase the number of clients PostgreSQL can serve. It also increases backend overhead and can increase peak query memory usage. A connection pool helps control concurrency, protect database resources, and prevent memory exhaustion under load.

Memory in your Node.js process

Node.js manages memory with a garbage collector (GC) inside the V8 engine. Memory leaks (data kept reachable forever) grow the heap until the process slows down or crashes. Large garbage-collection pauses also stall the single JavaScript thread, which delays every request in that process. Keeping per-request memory small (for example by paging results instead of loading a million rows) protects both memory and latency.

5. I/O and waiting: where the time actually goes

Most of a backend's time is spent waiting for I/O: network, disk, or another service. Understanding what happens during a wait explains why blocking is cheap for the CPU but expensive for everything else.

What happens when a thread waits for data

Suppose a thread calls read on a network socket and no data has arrived yet:

1. The thread makes a system call, switching from user mode to kernel mode.
2. The kernel sees there is no data. It marks the thread blocked and removes it from the run queue.
3. The scheduler runs another runnable thread on that core, via a context switch. The CPU keeps doing useful work.
4. Later the network card receives the data and signals the CPU with an interrupt. The kernel copies the data into the socket's buffer and marks the sleeping thread runnable.
5. When the scheduler next picks it, the thread returns from read with its data.

While blocked, the thread consumed no CPU. That is why a machine can show low CPU usage while requests are slow: threads are asleep, waiting on something else.

Waiting is free for the CPU, but not for resources

Blocking costs the CPU nothing, but the waiting task usually still holds something valuable. This table summarizes the most common waits.

What the task waits for	OS state	CPU used while waiting?	What it still holds
A database reply	Blocked (or parked in the event loop)	No	A pool connection, memory for the request, maybe locks
A disk read	Blocked, often in an uninterruptible state	No	Its place in the I/O queue; memory buffers
A lock held by another transaction	Blocked	No	Everything it already locked, plus its connection
A free CPU core	Runnable (not blocked)	No, but it is not progressing either	Its memory

The moral of the table: waiting converts a CPU problem into a resource-holding problem. A request waiting on a slow query holds a connection. If enough requests do that, the pool is exhausted, the queue grows, and the system fails even though CPU graphs look healthy. This is the mechanism behind the connection-pool failures discussed in the companion article on pools and timeouts.

The Disk Path and Durability

When a database modifies data, the changes are initially made in memory. To ensure those changes survive a crash or power failure, PostgreSQL uses a mechanism called write-ahead logging (WAL).

Before a transaction is considered durably committed, PostgreSQL must write its WAL records to stable storage. It typically uses fsync or an equivalent mechanism to ensure the operating system has flushed the required data to the storage device.

With the default synchronous_commit = on, PostgreSQL normally waits for the relevant WAL records to be flushed before confirming the commit to the client. This means commit latency depends partly on how quickly the storage system can complete the flush. When storage is slow, this wait can become a bottleneck.

PostgreSQL's synchronous_commit setting can reduce this wait by allowing a commit to be acknowledged before its WAL records are durably flushed. This improves performance but creates a risk: if the server crashes during that window, recently acknowledged transactions may be lost. This does not, by itself, imply database corruption.

The key takeaway is that durable commits require coordination with persistent storage, which is generally much slower than accessing RAM. This is why storage latency, flush performance, and commit settings can significantly affect database throughput and response times.

6. Concurrency and async in Node.js

Concurrency is not parallelism

Picture a cook with six pots. The cook starts the rice, and while it simmers, chops vegetables, then checks the rice again. One person, many dishes progressing: that is concurrency. Now hire five more cooks, each with their own stove, working at the same instant: that is parallelism. Concurrency is about structure (managing many tasks that overlap in time). Parallelism is about simultaneous execution (which needs several cores).

Node.js is the one-cook kitchen. It runs your JavaScript on a single thread, yet it can keep thousands of requests in progress, because most of each request is spent waiting, and waiting does not need a cook.

The Event Loop

When your JavaScript code awaits a database query, it might look as though execution stops until the database responds. In reality, await suspends the current async function, not the JavaScript thread. This allows Node.js to handle other work while the query is in progress.

Here is what happens step by step:

1. Send the query: Your JavaScript calls the database driver, which sends the SQL query through a network socket. Because the socket uses non-blocking I/O, Node.js does not need to wait for the database response before continuing other work.

2. Register the I/O operation: Node.js uses its I/O engine, libuv, to monitor the socket through operating-system mechanisms such as epoll on Linux, kqueue on macOS, or I/O Completion Ports (IOCP) on Windows.

3. Suspend the async function: When execution reaches await, the function pauses until the promise settles. The JavaScript thread can return to the event loop and process other ready callbacks, timers, or incoming requests.

4. Resume when the result arrives: When the database responds, the operating system signals that the socket has data available. Node.js processes the response, and the database driver's promise is settled. The event loop and microtask scheduling then allow the suspended function to resume after the await with the query result.

This is one of the key reasons Node.js can handle many concurrent I/O operations without creating a separate JavaScript thread for every request.

However, asynchronous I/O does not make CPU-intensive JavaScript non-blocking. If your code performs a long-running calculation on the main JavaScript thread, it can still prevent the event loop from processing other work promptly.

For a step-by-step explanation of the call stack, event loop, and asynchronous execution, read Why Your JavaScript Runs in the Wrong Order.

Three requests interleave on one thread like this:

The diagram shows the key insight. The JS thread is busy only for the short “handle” and “reply” slices. The long waits happen outside it, in the OS, the network, and the database. That is why a single Node.js process can serve many I/O-bound requests, and it is also why that process uses almost no CPU while its requests are slow.

Sequential versus concurrent awaits

A runnable TypeScript script demonstrates the performance difference between sequential await calls and concurrent Promise.all execution using mocked asynchronous operations with 1-second delays.

// Sequential: roughly 3 seconds
const user = await getUser(id);
const orders = await getOrders(id);
const invoices = await getInvoices(id);

// Concurrent: roughly 1 second
const [user, orders, invoices] = await Promise.all([
  getUser(id),
  getOrders(id),
  getInvoices(id),
]);

The sequential version waits for each operation to finish before starting the next, so the total time is approximately the sum of all three durations. In contrast, Promise.all starts all three operations immediately and waits for all of them to settle successfully, making the total time approximately that of the slowest operation.

However, concurrency consumes resources. If each query requires a separate database connection, three concurrent queries can occupy three connections at once. With a connection pool of five, two requests executing this pattern could consume all five connections if each request runs three queries concurrently.

Also, if any promise passed to Promise.all rejects, the combined promise rejects immediately, but the other operations continue running unless they are explicitly cancelled.

The takeaway: concurrency can significantly reduce latency, but in production systems, you must balance it against database connection limits, query load, and failure handling.

What Is Not Running on the JavaScript Thread?

Node.js uses more than the main JavaScript thread to handle certain types of work. One important example is libuv's thread pool, which has four threads by default. You can change its size using the UV_THREADPOOL_SIZE environment variable.

The thread pool handles operations that cannot be handled efficiently through non-blocking operating-system interfaces. Common examples include:

• File-system operations: Many asynchronous file-system APIs use the pool to perform disk operations.
• DNS lookups: dns.lookup() uses the operating system's name-resolution facilities, which typically involve the thread pool.
• Cryptographic operations: Functions such as crypto.pbkdf2() and crypto.scrypt() can use the pool for computationally intensive work.
• Compression: Asynchronous zlib operations can use the pool to perform compression and decompression.

However, network sockets, including database connections, do not use this thread pool to wait for responses. Node.js monitors network I/O through operating-system mechanisms and libuv's event loop instead.

An important edge case: Database connection timeouts

Consider an application that creates new PostgreSQL connections on demand. Before connecting, the database driver may need to resolve the database server's hostname using dns.lookup().

Now imagine that all four libuv worker threads are busy performing expensive password-hashing operations or file-system tasks. The DNS lookup may have to wait for an available worker. As a result, establishing a new database connection can take longer, even if PostgreSQL itself is healthy and responding normally.

This can produce a misleading symptom: a connection timeout caused by local thread-pool saturation rather than a database failure.

The distinction matters when troubleshooting production systems. If existing database connections work normally but new connections are delayed, investigate DNS resolution and libuv thread-pool contention alongside database health, network latency, and connection-pool configuration. Increasing UV_THREADPOOL_SIZE may help, but first verify that thread-pool saturation is actually the bottleneck.

Blocking the Event Loop

Node.js executes JavaScript callbacks on a single main thread. This makes it efficient for handling concurrent I/O, but it also creates an important limitation: while synchronous JavaScript is running, the event loop cannot execute other callbacks on that thread.

Consider this example:

app.get('/report', (req, res) => {
  const result = hugeArray.map(heavyTransform); // 800 ms of pure CPU
  res.json(result);
});

Suppose hugeArray.map(heavyTransform) takes 800 milliseconds to complete. During that time, the main JavaScript thread is occupied with computation and cannot process other callbacks.

The impact extends beyond the request generating the report:

• Other requests are delayed: Incoming requests cannot have their JavaScript handlers executed until the thread becomes available.
• Database responses are delayed: Data may arrive through network sockets, but the application cannot process the corresponding callbacks promptly.
• Timers run late: A timer scheduled for five seconds is not guaranteed to execute at exactly five seconds. If the event loop is blocked, its callback must wait until JavaScript can run it.
• Health checks may fail: If the process cannot respond to health checks within the configured timeout, an orchestrator such as Kubernetes may restart it, even though the process itself has not crashed.

Common causes include large JSON.parse() or JSON.stringify() operations, computationally expensive regular expressions, synchronous cryptographic functions, and processing enormous arrays or result sets in a single operation.

Measuring event loop delay

One way to detect event loop blocking is to measure event loop delay: how late the event loop processes scheduled work compared with when it would normally be expected to run.

Node.js provides monitorEventLoopDelay() in the built-in node:perf_hooks module.

import { monitorEventLoopDelay } from 'node:perf_hooks';

const histogram = monitorEventLoopDelay({ resolution: 20 });
histogram.enable();

setInterval(() => {
  console.log(
    'event loop delay p99 (ms):',
    histogram.percentile(99) / 1e6,
  );

  histogram.reset();
}, 10_000);

Here's how to interpret the important parts:

• resolution: 20 sets the approximate sampling interval to 20 milliseconds.
• percentile(99) reports the 99th-percentile delay recorded during the measurement window, indicating the delay below which 99% of recorded values fall.
• / 1e6 converts nanoseconds to milliseconds because the histogram reports its values in nanoseconds.
• reset() clears the recorded histogram so the next interval measures a fresh window.

A responsive service typically has low event loop delay, often in the single-digit milliseconds under light load. Sustained delays in the hundreds of milliseconds are a strong warning that the event loop is struggling, although acceptable thresholds depend on the workload and latency requirements.

How to prevent event loop blocking

The right solution depends on what is consuming the CPU or holding large amounts of data:

1. Use worker_threads for CPU-intensive JavaScript. Workers execute JavaScript on separate threads, allowing the main event loop to remain responsive.
2. Move heavy computation to a separate service. This is useful when workloads need independent scaling or process isolation.
3. Process data in chunks. Break large computations into smaller units and yield between chunks so other callbacks can run.
4. Stream instead of buffering. Process large inputs incrementally rather than loading and transforming the entire dataset in memory.
5. Push suitable aggregation into the database. Use SQL filtering, grouping, and aggregation to avoid transferring and processing unnecessary records in Node.js.

The key distinction is that asynchronous I/O allows Node.js to handle waiting efficiently, but it does not automatically make CPU-intensive JavaScript asynchronous. If a computation monopolizes the main thread, every other task that depends on that event loop can experience increased latency.

Which kind of work runs where?

This table summarizes the execution venues. Use it to choose a scaling tool: the wrong one (for example, more pool connections for a CPU-bound handler) does nothing.

Work	Runs on	What limits it	Scale by
Waiting for the DB or an HTTP service	The OS and the event loop (no thread is held)	Connections, remote capacity	Pool tuning, more replicas
JavaScript computation	The single JS thread	One core per process	Worker threads, more processes, offloading
File system, crypto, zlib, dns.lookup	libuv thread pool (4 by default)	Pool size	Larger pool, offloading
SQL execution	Database server processes	DB CPU, memory, I/O, locks	Indexes, query tuning, bigger hardware, replicas

To use several cores, you run several Node.js processes (via containers, a process manager, or the cluster module). Each process has its own pool, which is exactly why the per-process connection arithmetic matters.

Single-threaded does not mean race-free

JavaScript never runs two lines at once in the same process, but an await lets other requests run in the middle of your function. That creates classic check-then-act races:

const stock = await getStock(productId); // Other requests can run here.

if (stock > 0) {
  await setStock(productId, stock - 1);  // And here.
}

At first glance, this code looks correct: it checks whether stock is available before reducing the quantity. However, it contains a race condition because reading and updating the stock are two separate database operations.

Imagine that a product has only one item left and two customers try to buy it simultaneously:

• Request A reads the stock: 1.

• Request B reads the stock: 1.

• Both requests pass the stock > 0 check.

• Both requests update the stock based on their earlier reads.

Both customers may receive a successful purchase confirmation, even though only one item was available. This is known as overselling.

The problem is not that JavaScript executes two statements simultaneously on the same thread. Rather, each await allows other work to proceed while the current function is suspended. Another request can therefore execute its own database operations before the first request finishes.

Where should the fix go?

Because the stock is stored in the database and must remain correct across concurrent requests, the database should enforce the operation's correctness.

One solution is to combine the check and update into a single atomic SQL statement:

UPDATE products
SET stock = stock - 1
WHERE id = $1 AND stock > 0
RETURNING stock;

The database evaluates the condition and updates the matching row as one statement. Concurrent updates to the same row are coordinated by PostgreSQL, preventing both requests from decrementing the same unit of stock successfully.

Your application should check the result:

• One row returned: The stock was successfully decremented.

• No rows returned: The product does not exist or has no stock available.

Other approaches include database constraints and explicit locking within a transaction, depending on the business rules and complexity of the operation. See Section 8 for more about locks.

The same principle applies to in-memory JavaScript state. Variables can become logically inconsistent when multiple asynchronous operations interleave across await points. In addition, in-memory variables are not automatically shared between separate Node.js processes or application replicas.

The key takeaway is that when correctness depends on shared state, enforce the required invariants at the system that owns that state. For inventory, that usually means using atomic database operations, appropriate constraints, and transactions where necessary, rather than relying on application-level checks alone.

Below is a clearer, more cohesive version of Sections 7–12. I've preserved the transaction-locking image, code examples, SQL queries, and diagnostic tables while clarifying the relationships between PostgreSQL processes, resource contention, and application latency.

7. Inside PostgreSQL: What Happens When a Query Arrives?

The PostgreSQL process model

PostgreSQL uses a process-based architecture. Its main process, called the postmaster, manages incoming connections and starts a dedicated backend process for each client connection. Other background processes handle tasks such as writing data to disk, maintaining statistics, and cleaning up obsolete row versions.

These processes cooperate through shared memory, but each backend also maintains private memory for its own work.

Process	Responsibility	Why it matters
Postmaster	Accepts connections and starts backend processes.	Creating connections has overhead, which is one reason to use connection pooling.
Backend	Executes queries for one client connection.	Each backend is an operating-system process with private memory and resource costs.
Checkpointer	Ensures modified data pages are periodically written to disk.	Checkpoint activity can cause bursts of disk I/O.
Background writer	Writes dirty shared-buffer pages to disk ahead of demand.	Helps make buffer reuse smoother and reduces the amount of work backends must do themselves.
WAL writer	Writes buffered write-ahead log records to WAL files.	WAL activity is part of PostgreSQL's durability mechanism.
Autovacuum workers	Reclaim reusable space from obsolete row versions and update statistics.	They consume CPU and I/O, and long-running transactions can prevent cleanup of old row versions.

The important distinction is that a database connection is not just a network socket. It is associated with a backend process that must be scheduled by the operating system and has its own memory and execution state. Opening too many connections can therefore increase resource consumption even before those connections execute expensive queries.

The life of a query

When a client sends SQL to PostgreSQL, several stages occur before the result returns to the application.

• Wake-up and CPU scheduling

  The backend may be waiting for data on its network socket. When the query arrives, it becomes runnable. The operating system must schedule it on a CPU core. If all cores are busy, the backend waits in the run queue.

• Parse

  PostgreSQL parses the SQL text and builds an internal representation of the query. This stage checks syntax and resolves database objects and types.

• Plan

  The query planner chooses an execution strategy, including which indexes to use and how to order joins. It relies on table statistics to estimate costs. Stale or inaccurate statistics can lead to a poor plan and unexpectedly slow queries.

• Execute and read data

  The backend retrieves the required table and index pages. If a page is already in shared_buffers, PostgreSQL can access it from memory. Otherwise, it requests the page through the operating system, which may serve it from the filesystem page cache or read it from storage.

• Check visibility and acquire locks

  PostgreSQL determines which row versions are visible to the transaction under MVCC and acquires any locks required by the query. Conflicting locks may cause the backend to wait.

• Write changes to WAL

  For a modifying statement, PostgreSQL generates write-ahead log records describing the changes. These records are buffered before being written to WAL files.

• Commit the transaction

  For a durable commit with the usual synchronous_commit = on setting, PostgreSQL waits for the relevant WAL records to be flushed before acknowledging success. The exact behavior also depends on replication and durability configuration.

• Return the result

  PostgreSQL sends result rows or a command status to the client over the network socket. The application must then receive and process that response.

Not every query performs every stage. A simple SELECT, for example, does not generate a transaction's data-change WAL records or require a commit flush in the same way a modifying transaction does.

Which resource can slow down each stage?

Stage	Main resource	Possible bottleneck
Wake-up	CPU scheduling	No free CPU time; the backend waits in the run queue.
Parse and plan	CPU	Excessive planning work or a poor plan caused by inaccurate statistics.
Read data	Memory and storage	Cache misses that require slower storage reads.
Joins, sorts, and aggregates	CPU and work_mem	CPU saturation or temporary-file I/O when operations spill to disk.
Locking	Shared lock-management structures	Another transaction holds a conflicting lock.
Commit	Storage	Waiting for WAL to be flushed to durable storage.
Send results	Network and client	Network delay or a client that consumes results slowly.

This explains why the phrase slow query is not a diagnosis. A CPU-heavy aggregate may need a better query plan; a storage-bound query may benefit from better indexing or caching; and a lock-bound query may require shorter transactions. Applying the wrong fix can leave the actual bottleneck unchanged.

Why One PostgreSQL Query Usually Uses One CPU Core

To understand PostgreSQL's CPU usage, we need to distinguish between three concepts: database connections, backend processes, and CPU cores. They are related, but they are not the same thing.

1. How PostgreSQL Executes a Query

PostgreSQL uses a process-based architecture. When a client connects to the database, PostgreSQL normally creates a dedicated backend process to handle that connection.

For example, when an application executes this query:

SELECT SUM(total)
FROM orders
WHERE status = 'completed';

The backend process parses, plans, and executes the query. A regular backend process executes its instructions on one logical CPU at any given instant. The operating system may move the process between CPU cores, but the process cannot execute on multiple cores simultaneously.

This does not mean the query continuously uses the CPU. It may spend time waiting for disk I/O, locks, or other resources. While it waits, the CPU can execute other tasks.

2. What Happens When the Database Has 16 Logical CPUs?

Imagine a database server with 16 logical CPUs and 100 incoming CPU-intensive queries.

If each query uses a single backend process, the server can execute approximately 16 such processes simultaneously, assuming all logical CPUs are available and ignoring other workloads.

The remaining queries must wait for CPU time or share it as the operating system schedules runnable processes.

When too many CPU-intensive queries compete for the same processing capacity, three problems can occur:

• Higher latency: Individual queries take longer to finish.

• CPU contention: More runnable processes compete for available CPU time.

• Reduced throughput: Adding more concurrent queries may stop increasing the number of queries completed per second.

The operating system switches between runnable processes rapidly, so more than 16 queries can make progress over a short period. However, if all queries require substantial CPU time, they compete for the same processing capacity.

Sixteen logical CPUs do not guarantee that exactly 16 queries can run efficiently. Background processes, other applications, CPU architecture, and workload characteristics all affect actual capacity.

3. Can One PostgreSQL Query Use Multiple CPU Cores?

Yes. PostgreSQL supports parallel query execution, which allows eligible queries to divide their work among multiple processes.

Consider a query that scans and aggregates millions of rows. PostgreSQL may determine that the query can benefit from parallel execution.

Without parallel execution, one backend process performs the work, typically using one CPU core at a time.

With parallel execution, PostgreSQL can launch parallel worker processes to perform parts of the work concurrently. A leader process coordinates execution and combines the results.

For example, a query using three parallel workers may involve four processes: one leader and three workers. These processes can potentially execute on four logical CPUs simultaneously, depending on the execution plan and available CPU capacity.

Parallel execution is not enabled or beneficial for every query. PostgreSQL considers factors such as estimated query cost, parallel-safety restrictions, the execution plan, and configuration settings.

4. Why Connection Count Does Not Equal CPU Usage

This distinction becomes especially important in production applications and microservices.

Consider an application with 20 PostgreSQL connections:

Scenario	Connections	CPU usage
Most queries are waiting for disk I/O	20	Potentially low
Many queries perform CPU-intensive calculations	20	Potentially high
One query launches parallel workers	1	Work may span multiple CPU cores
All connections are idle in a pool	20	Little or no query-execution CPU usage

A connection pool limits how many database connections an application can use. It does not directly limit the number of CPU cores consumed by a query.

For example, if 10 application replicas each configure a pool with max: 5, they can collectively open up to 50 connections:

10 × 5 = 50 connections

If several connections execute parallel queries, the number of active PostgreSQL processes can exceed the number of client connections.

This is why production capacity planning must consider both connection limits and query execution concurrency.

5. What This Means for Database Connection Pools

Suppose an application has 10 replicas, each configured with a PostgreSQL pool of 20 connections.

The potential connection count is:

10 × 20 = 200 connections

If the database has 16 logical CPUs, these 200 connections do not mean that 200 queries can execute simultaneously on 200 CPU cores. They represent the maximum number of client connections those pools can collectively open, assuming no other connection limits intervene.

If many connections execute CPU-intensive queries simultaneously, they can create substantial CPU contention. Parallel queries can increase that contention further because a single query may use multiple processes.

Therefore, choosing a pool size should account for:

• Database CPU and memory capacity.

• The number of application replicas.

• Query duration and resource requirements.

• The number of queries executing concurrently.

• Parallel-query worker limits and configuration.

• Whether connection pooling is handled by the application or an external pooler such as PgBouncer.

A larger pool is not always faster. Beyond a certain point, increasing concurrency can increase queueing and resource contention rather than improve throughput.

Key Takeaway

• Connection: A communication channel between the application and PostgreSQL.

• Backend process: The process that normally executes queries for a connection.

• CPU core: A processing resource on which a process executes.

• Parallel query: A feature that allows one query to use multiple cooperating processes and potentially multiple CPU cores.

A regular PostgreSQL query typically uses one CPU core at a time because a single backend process executes it. Parallel query execution is the exception that allows one query to use multiple cores simultaneously.

Because many connections may be idle or waiting for resources, connection count alone is not a reliable measure of CPU demand. Effective database capacity planning requires understanding query concurrency, resource utilization, and parallel execution.

MVCC: Why readers usually do not block writers

PostgreSQL uses multi-version concurrency control (MVCC) to let transactions read consistent versions of data without requiring ordinary reads to block concurrent writes.

When a transaction updates a row, PostgreSQL generally creates a new row version rather than overwriting the old version in place. Each transaction sees the row versions permitted by its visibility rules and isolation level.

The benefit is that ordinary SELECT queries can usually continue reading while other transactions update the same rows. The trade-off is that obsolete row versions remain in the database until they can be reclaimed.

Autovacuum helps maintain this process by cleaning up eligible dead row versions and updating statistics. However, a long-running transaction, including an idle transaction, can hold a snapshot that prevents some old versions from being removed. Over time, this can contribute to table bloat and additional I/O.

8. Database Locks: Why Queries Wait

Why locks exist

When multiple transactions access the same data, PostgreSQL must prevent conflicting operations from producing incorrect results. Locks coordinate these operations by making transactions wait when necessary.

However, a query does not normally lock the entire database. PostgreSQL uses different lock types for different purposes. Thanks to MVCC, ordinary reads take relatively weak table-level locks and generally do not block concurrent updates.

Understanding these lock types helps explain why a database can have low CPU usage while application requests are still slow.

The main types of locks

• Spinlocks and lightweight locks (LWLocks): Protect internal PostgreSQL structures, such as shared-memory data structures and buffer-management state. They are generally held briefly. Under heavy contention, waiting for these internal locks can become a performance bottleneck.

• Heavyweight locks: Coordinate operations such as table access and transaction-level locking. PostgreSQL tracks these locks in shared lock-management structures. Many are held until the transaction ends, making long transactions particularly costly.

• Row-level locks: Prevent conflicting transactions from modifying the same rows simultaneously. When a transaction needs a row locked by another transaction, it may have to wait for that transaction to finish.

• Advisory locks: Let applications coordinate access to resources using application-defined identifiers. PostgreSQL manages the locks but does not enforce what those identifiers mean.

These categories serve different purposes. For example, row-level locks protect data from conflicting modifications, while LWLocks protect PostgreSQL's internal machinery.

Common SQL statements and their table locks

The following table shows the usual table-level lock modes for common operations and their practical effects.

Operation	Table lock mode	Practical effect
SELECT	ACCESS SHARE	Allows ordinary reads and writes; conflicts with ACCESS EXCLUSIVE.
INSERT, UPDATE, DELETE	ROW EXCLUSIVE, plus row-level locks where required	Ordinary reads can continue; conflicting modifications to the same rows may wait.
SELECT ... FOR UPDATE	ROW SHARE, plus row-level locks	Locks selected rows against conflicting modifications until the transaction ends.
CREATE INDEX	SHARE	Blocks ordinary writes to the table while allowing reads.
CREATE INDEX CONCURRENTLY	SHARE UPDATE EXCLUSIVE during relevant phases	Allows ordinary reads and writes, but takes longer and has additional restrictions and phases.
Many forms of ALTER TABLE, DROP TABLE, TRUNCATE, VACUUM FULL	ACCESS EXCLUSIVE	Blocks ordinary access that conflicts with this lock, including SELECT.

The exact lock mode can depend on the SQL operation and its options. In particular, not every form of ALTER TABLE requires ACCESS EXCLUSIVE; check the documentation for the specific change.

The main lesson is that routine application queries are usually compatible with one another, while some schema changes require exclusive access and can disrupt normal traffic.

What happens when a query waits for a row lock?

Consider three transactions accessing the same row:

Suppose transaction A has modified row 5 but has not committed yet. Transaction B wants to modify that same row.

• B discovers that A's transaction must finish before B can proceed.

• B waits for A's transaction to commit or roll back.

• While B waits, its backend is blocked rather than continuously consuming CPU.

• When A finishes, PostgreSQL wakes B, which can become runnable and continue execution.

• Transaction C performs an ordinary read. Under typical READ COMMITTED isolation, it can see the previous committed version without waiting for A's uncommitted change.

This example illustrates MVCC and row locking working together. The exact behavior depends on the statement and transaction isolation level.

Now consider the application-side consequences. B's database connection remains occupied while its query waits, and the HTTP request may remain open in Node.js. If A is stuck waiting for an external API while holding its transaction open, B and other requests targeting the same row can accumulate behind it.

Eventually, enough waiting requests can occupy every connection in the application's pool. New requests then wait for a connection even if the database has plenty of unused CPU capacity.

A lock wait can therefore cause application-wide latency without saturating the CPU.

The lock queue convoy

A particularly surprising problem occurs when a schema change requests an exclusive lock but cannot acquire it immediately.

Consider this sequence:

1. A long SELECT starts and holds ACCESS SHARE.
2. ALTER TABLE requests ACCESS EXCLUSIVE and must wait.
3. New SELECT statements arrive and queue behind the waiting ALTER.
4. The table appears frozen, even though ALTER TABLE has not started.

Why can this happen?

Although ordinary SELECT statements are compatible with one another, PostgreSQL's lock queue must respect conflicting requests and their ordering. A waiting exclusive request can prevent later conflicting requests from bypassing it.

The result is a lock queue convoy: a single blocked schema change causes many otherwise compatible requests to accumulate.

If this continues, the application pool can fill with waiting queries and begin timing out.

Practical safeguards include:

• Set a short lock_timeout for migrations so a migration fails instead of waiting indefinitely and creating a queue.

• Avoid schema changes while long-running queries or idle in transaction sessions are present.

• Prefer lower-impact operations such as CREATE INDEX CONCURRENTLY when appropriate.

• Investigate blockers before retrying a failed migration.

Deadlocks

A deadlock occurs when transactions form a cycle of dependencies: each transaction waits for a resource held by another transaction in the cycle.

For example:

Transaction A                   Transaction B
UPDATE accounts ... id = 1      UPDATE accounts ... id = 2
UPDATE accounts ... id = 2      UPDATE accounts ... id = 1
   (waits for B)                   (waits for A)

A holds the lock for account 1 and needs account 2. B holds the lock for account 2 and needs account 1. Neither can proceed without the other releasing its lock.

PostgreSQL detects deadlocks using a wait-for graph. By default, it waits until deadlock_timeout—normally one second—before checking for a deadlock, avoiding the overhead of checking immediately for every short-lived lock wait.

If it detects a cycle, PostgreSQL aborts one transaction and reports SQLSTATE 40P01. The application can retry the entire transaction, provided its operations are safe to retry.

To reduce deadlocks:

• Acquire locks in a consistent order, such as always updating the lower account ID first.

• Keep transactions short.

• Lock only the rows and resources actually needed.

• Implement retries for transient deadlock errors where appropriate.

A useful pattern: SKIP LOCKED

Job queues often have multiple workers competing to claim available jobs. Without coordination, workers may wait for the same locked row.

PostgreSQL supports SELECT ... FOR UPDATE SKIP LOCKED, which skips rows that cannot be locked immediately rather than waiting for them.

For example:

SELECT id
FROM jobs
WHERE status = 'pending'
ORDER BY id
FOR UPDATE SKIP LOCKED
LIMIT 1;

Multiple workers can claim different available jobs without waiting on the same locked row. The row lock must be used within an appropriate transaction, and the worker should update or claim the job consistently before releasing that transaction.

The trade-off is that the query intentionally skips locked rows. It does not provide a complete, consistent view of all eligible jobs, so this technique is appropriate for queues but not for every query.

Finding which sessions are blocking others

PostgreSQL exposes session information through pg_stat_activity and provides pg_blocking_pids() to identify blocking sessions.

SELECT a.pid,
       a.application_name,
       a.state,
       now() - a.query_start AS running_for,
       pg_blocking_pids(a.pid) AS blocked_by,
       left(a.query, 80) AS query
FROM pg_stat_activity a
WHERE cardinality(pg_blocking_pids(a.pid)) > 0;

Each returned row represents a session currently blocked by another session. The blocked_by column contains the process IDs of the blocking sessions.

To investigate, inspect those PIDs in pg_stat_activity. A blocker may be running a long query or may be idle in transaction, meaning it is not currently executing a query but still has an open transaction that retains locks.

PostgreSQL provides two ways to intervene:

• pg_cancel_backend(pid) requests cancellation of the current query.

• pg_terminate_backend(pid) terminates the backend session.

Both require appropriate privileges. Terminating a session causes its open transaction to roll back, so these functions should be used only after assessing the impact.

Locks outside the database

An in-process mutex is often unnecessary for simple JavaScript state because Node.js executes JavaScript callbacks on a single main thread. However, race conditions can still occur when asynchronous operations interleave across await points.

Cross-process coordination introduces additional concerns. For example, a Redis-based distributed lock can expire before the protected work finishes, and network failures or crashes can complicate lock ownership.

When the shared state belongs to PostgreSQL, prefer database-native mechanisms where possible:

• Constraints for enforcing data invariants.

• Atomic SQL updates for check-and-modify operations.

• Transactions and row locks for coordinating related changes.

• Advisory locks for application-defined coordination.

Database-native locks are integrated with PostgreSQL's transaction and session lifecycle, which makes them particularly useful for protecting database-owned state.

9. Diagnosing the Bottleneck: CPU, Memory, Disk, Locks, or the Application?

Once you understand how a request moves through the system, you can diagnose slowness by measuring resources rather than guessing.

A useful framework is the USE method, developed by performance engineer Brendan Gregg. For every major resource, examine three things:

• Utilization: How busy is the resource?

• Saturation: Is work accumulating because the resource cannot keep up?

• Errors: Is the resource failing or reporting errors?

Apply this method to CPU, memory, disk, network, and database locks. The goal is to identify which resource is limiting throughput or increasing latency.

Reading vmstat

On Linux, vmstat 1 reports system statistics at one-second intervals. Consider this hypothetical output from a database host with eight logical CPUs:

procs -----------memory---------- ---swap-- -----io---- -system-- ------cpu-----
 r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa st
12  0      0 812340  90112 5120000    0    0    40   120 5000 42000 85 10  4  1  0

The columns most relevant to performance diagnosis are:

• r: Number of runnable tasks, including tasks currently running or waiting for CPU time. Twelve runnable tasks on a host with eight logical CPUs suggests CPU contention.

• b: Tasks blocked in uninterruptible sleep, often because of I/O.

• si and so: Memory swapped in and out. Sustained nonzero values can indicate memory pressure.

• cs: Context switches per second. A high rate can accompany heavy scheduling activity, although it is not proof of a problem by itself.

• us and sy: CPU time spent in user code and kernel code, respectively.

• id: Percentage of CPU time spent idle.

• wa: Percentage of CPU time spent waiting for I/O to complete.

• st: CPU time taken away by the hypervisor on a virtualized host.

In this example, the run queue is larger than the logical CPU count, CPU idle time is only 4%, and I/O wait is 1%. Together, these values suggest CPU saturation rather than a disk bottleneck.

Compare that with a system showing low CPU utilization, high I/O wait, and blocked tasks. That pattern would make a storage bottleneck more plausible.

If CPU utilization is low, I/O wait is low, and the system is not swapping, slow requests may instead be caused by locks, network latency, application-level delays, or connection-pool exhaustion.

Remember that vmstat reports system-wide statistics, not PostgreSQL-only statistics. Confirm the suspected bottleneck with another signal before making changes.

Interpreting common performance patterns

What you observe	Likely interpretation	First action
Database host has almost no idle CPU and a run queue larger than the CPU count.	CPU saturation.	Find expensive queries using tools such as pg_stat_statements; reduce unnecessary concurrency or add CPU capacity.
Database host has low CPU utilization, high I/O wait, and blocked tasks.	Possible storage bottleneck.	Check query plans, cache hit rates, checkpoint activity, indexes, and storage latency.
Sustained si or so activity.	Memory pressure and swapping.	Review work_mem, connection counts, parallel-query usage, and available RAM.
Low CPU and I/O wait, but many sessions waiting on locks.	Lock contention.	Identify blockers and shorten transactions.
Many sessions are idle in transaction.	Applications are holding transactions open unnecessarily.	Fix transaction handling and consider idle_in_transaction_session_timeout.
One application CPU core is near 100%, with high event loop delay.	CPU-intensive JavaScript is blocking the event loop.	Offload computation, chunk work, or use additional application processes.
Database appears healthy, but application connection-pool wait time is high.	Connection exhaustion or poor concurrency management.	Inspect pool sizing, transaction duration, and unbounded parallel queries.
High context-switch rates alongside a large run queue.	Potentially excessive runnable concurrency.	Confirm CPU contention and reduce unnecessary concurrency or increase CPU capacity.

These are diagnostic starting points, not definitive conclusions. Correlate multiple measurements before changing configuration.

Asking PostgreSQL what its sessions are waiting for

PostgreSQL can report what active sessions are waiting on through pg_stat_activity.

SELECT wait_event_type, wait_event, count(*) AS sessions
FROM pg_stat_activity
WHERE state = 'active'
GROUP BY wait_event_type, wait_event
ORDER BY sessions DESC;

This query groups active sessions by their reported wait event.

Common categories include:

• Lock: Waiting for a heavyweight lock.

• IO: Waiting for certain I/O operations.

• LWLock: Waiting for an internal lightweight lock.

• Client: Waiting for client-related communication events.

An active session with a NULL wait event is not currently reporting a wait event. It may be executing on the CPU, but a single snapshot cannot establish how much CPU it is consuming or whether CPU saturation is the system-wide bottleneck.

For that reason, take multiple snapshots and correlate them with CPU, storage, and application metrics. A snapshot dominated by lock waits points toward contention; one dominated by I/O waits suggests investigating storage. Many active sessions with no reported wait events, combined with high CPU utilization, make CPU saturation more plausible.

10. One Request, End to End

The following table brings the pieces together by tracing a request from the application to PostgreSQL and back. It shows which resource is involved at each stage, where the request might wait, and what commonly goes wrong.

Step	What happens	Main resource	Can it wait?	Typical problem
1	An HTTP request arrives and the operating system delivers it to Node.js.	Network and kernel	Yes, before delivery.	Network or load-balancer overload.
2	The event loop executes the request handler.	JavaScript thread and CPU	Yes, if the event loop is busy.	CPU-heavy JavaScript blocks other requests.
3	The application requests a database connection from its pool.	Application resources	Yes, if all connections are occupied.	Pool exhaustion.
4	The driver sends SQL through a network socket.	Kernel network stack	Yes, depending on network buffering and connection state.	Network delays or connection setup overhead.
5	SQL travels to the database host.	Network	Yes, for the round trip.	Latency, packet loss, or network congestion.
6	The PostgreSQL backend becomes runnable and is scheduled.	CPU and operating-system scheduler	Yes, if CPU cores are busy.	CPU saturation.
7	PostgreSQL parses and plans the query.	CPU	Usually little explicit waiting.	Poor query plan or expensive planning.
8	PostgreSQL reads table and index pages.	Memory and storage	Yes, on cache misses or slow I/O.	Storage-bound workload.
9	PostgreSQL acquires required locks.	Lock-management structures	Yes, if conflicting locks exist.	Lock contention or a lock queue convoy.
10	A modifying transaction writes WAL records and commits.	Storage	Yes, when waiting for durable WAL flush.	Slow storage or commit latency.
11	Results travel back and the application processes the response.	Network and JavaScript thread	Yes, if the network or event loop is delayed.	Large result sets or event loop blocking.
12	The application serializes and sends the HTTP response.	JavaScript thread and network	Yes, if computation or network delivery is delayed.	Expensive serialization or slow clients.

Not every request passes through every stage. For example, a read-only query does not normally require the same WAL commit flush as a transaction that modifies data.

Read this table as a diagnostic checklist. End-to-end latency is the result of the work performed and the waits encountered across these stages. Optimizing a stage that contributes little to the total delay will have limited impact, which is why measuring first matters.

11. Practical Rules Derived From This Mental Model

These practices follow directly from how Node.js, connection pools, and PostgreSQL interact.

• Bound database concurrency. Keep the number of simultaneously active queries within a level the database can handle efficiently. Extra concurrency can increase queueing, context switching, and memory usage without increasing throughput. The right limit depends on query cost, CPU capacity, storage performance, and parallel-query usage.

• Keep transactions short. Transactions occupy connections and can hold locks while waiting. Long transactions contribute to pool exhaustion, lock queues, and delayed cleanup of obsolete row versions.

• Never block the event loop with expensive synchronous work. A single JavaScript thread handles callbacks for many requests. Move CPU-heavy work to worker threads or separate processes, or divide it into smaller chunks.

• Use concurrency deliberately. Promise.all() can reduce latency by running independent operations concurrently, but it also increases simultaneous resource demand. Use bounded concurrency when processing large collections.

• Configure server-side timeouts. A client timing out does not necessarily stop the database query or release its locks immediately. Consider statement_timeout, lock_timeout, and idle_in_transaction_session_timeout as safeguards appropriate to your workload.

• Treat schema changes as potential lock events. Use a suitable lock_timeout, check for long-running transactions, and prefer lower-impact operations where possible.

• Scale the layer that is actually constrained. CPU-heavy JavaScript may benefit from more application processes. A saturated database may need query optimization or additional database resources. Lock contention requires transaction and locking changes, not simply more servers.

• Measure each layer independently. Use event loop delay for Node.js, CPU and I/O metrics for the operating system, and pg_stat_activity and blocking-session diagnostics for PostgreSQL. This lets you identify the actual bottleneck instead of guessing.

12. Common Misconceptions

Misconception	Reality
“More threads or connections always mean more speed.”	Concurrency helps only while the system has spare capacity. Beyond that point, additional concurrency can increase queueing, context switching, and memory consumption.
“Asynchronous means parallel.”	Async code lets a thread manage multiple pending operations. JavaScript computation remains single-threaded unless you use workers or additional processes.
“Waiting uses CPU.”	A blocked task generally consumes no CPU while it sleeps, but it can still hold a connection, transaction, or lock.
“Low CPU means the system is healthy.”	A system waiting on storage, locks, or the network can have low CPU utilization and still be very slow.
“A query locks the whole database.”	Most statements use specific table and row-level locking mechanisms. Only certain operations require highly restrictive locks.
“Reads and writes block each other in PostgreSQL.”	Under ordinary MVCC behavior, plain reads and writes generally proceed concurrently, although locking reads and certain DDL operations can block other work.
“A single-threaded program cannot have race conditions.”	Operations can interleave across await points, allowing check-then-act logic to race even on one JavaScript thread.
“The database is slow, so we need a bigger connection pool.”	If the database is already saturated, increasing the pool size can make performance worse. Diagnose the bottleneck first.

Conclusion

A database request is a relay race.

Your application hands work to the operating system, the network carries it to PostgreSQL, a backend process executes the query, and the results travel back to your application. Along the way, work may wait for CPU time, memory access, storage, locks, network delivery, or a free database connection.

Three principles explain most of the performance problems covered in this article.

First, different bottlenecks produce different symptoms. CPU saturation creates runnable queues, memory pressure can cause swapping, storage delays increase I/O waits, and lock contention leaves sessions waiting for other transactions.

Second, concurrency has a cost. Async functions, thread pools, connection pools, and parallel queries help systems handle work efficiently, but they all place demands on finite resources. Increasing concurrency beyond the system's capacity can increase latency rather than reduce it.

Third, waiting can be measured and investigated. Event loop delay, Linux tools such as vmstat, PostgreSQL's pg_stat_activity, and pg_blocking_pids() help answer the most useful diagnostic question: What is waiting, and what is it waiting for?

Once you can answer that question for your own system, practices such as keeping transactions short, bounding connection pools, avoiding event loop blocking, and optimizing slow queries stop being rules to memorize. They become conclusions you can derive from the system's actual behavior.

References

1. Node.js: The event loop, timers, and process.nextTick()
2. libuv: Design overview
3. PostgreSQL: Explicit locking
4. PostgreSQL: Concurrency control (MVCC)
5. PostgreSQL: Resource consumption settings
6. PostgreSQL: The cumulative statistics system (pg_stat_activity)
7. Brendan Gregg: The USE Method