Shared vs Exclusive Locks

Database locking mechanisms ensure consistency by controlling concurrent access to data. Two fundamental lock types—exclusive locks and shared locks—serve complementary purposes in maintaining data integrity.

Exclusive Locks

An exclusive lock grants a single connection sole access to read or update a piece of data—whether a column, row, or other database object. When a connection holds an exclusive lock, no other connection can read or modify that data. This ensures that updates occur in isolation without interference from concurrent operations.

Exclusive locks are essential when modifying data. If a connection needs to update a value, it must guarantee that no other connection is reading potentially stale data during the modification or attempting conflicting updates. The exclusive lock enforces this guarantee: any other connection attempting to access the locked data receives an error until the lock is released.

Shared Locks

A shared lock (also called a read lock) allows a connection to read data while preventing modifications. When a connection holds a shared lock, it guarantees the data remains unchanged for the duration of its transaction, but multiple connections can hold shared locks simultaneously on the same data.

This is useful for long-running queries or reports where consistency matters. If reading a bank account balance during a reporting operation, a shared lock ensures the balance doesn’t change mid-query. Multiple readers can coexist—ten connections can simultaneously hold shared locks on the same value—but no connection can modify the data while any shared lock exists.

The Relationship Between Lock Types

Exclusive locks and shared locks are mutually exclusive. To acquire an exclusive lock, no shared locks can exist on that data. Conversely, to acquire a shared lock, no exclusive lock can exist. This relationship enforces consistency: readers prevent writers, and writers prevent readers.

If seven connections hold shared locks on a value, no connection can acquire an exclusive lock to modify it until all seven shared locks are released. Similarly, if one connection holds an exclusive lock, no other connection can acquire either a shared lock (to read) or another exclusive lock (to write).

Example: Banking Transactions

Consider three users: Alice, Bob, and Charlie, with transactions occurring sequentially over time.

Alice initiates a transaction to deposit $200 in t o h er a cco u n t . T hi sre q u i res an U P D A TE s t a t e m e n t, w hi c hm u s t a c q u i re an e x c l u s i v e l oc k . S in ce A l i ce i s t h eo n l y a c t i v eco nn ec t i o n, t h ee x c l u s i v e l oc k s u ccee d s . * * T h ee x c l u s i v e l oc k p re v e n t so t h erco nn ec t i o n s f ro m re a d in g h er ba l an ce d u r in g t h e u p d a t e * *, a v o i d in g sce na r i os w h ere an o t h erco nn ec t i o n re a d s a p a r t ia ll y u p d a t e d or in co n s i s t e n t v a l u e . A l i c e^{'} s t r an s a c t i o n co m pl e t es q u i c k l y — a c q u i r in g t h e l oc k, in cre a s in g t h e ba l an ce b y$ 200, and committing.

Immediately after, Alice begins a long-running reporting transaction that reads her account balance and performs analytics. Read operations acquire shared locks. Alice’s reporting transaction obtains a shared lock on her balance, signaling that no other connection should modify it during her analysis.

While Alice’s reporting runs, Bob starts his own reporting transaction on his account. Bob acquires a shared lock on his balance. Multiple shared locks coexist without conflict—Alice holds a shared lock on her data, Bob holds one on his data, and both transactions proceed concurrently.

Now Charlie attempts to transfer $300 to Bob’s account. This requires updating Bob’s balance, which means acquiring an exclusive lock on Bob’s account. The exclusive lock acquisition fails because Bob already holds a shared lock. Bob’s reporting transaction is actively reading his balance, and his shared lock prevents any modifications. Charlie’s transaction cannot proceed—it must wait or fail.

After Bob completes his reporting job and commits, he releases the shared lock. Charlie retries the transfer, and this time the exclusive lock acquisition succeeds. With the exclusive lock secured, Charlie’s transaction updates Bob’s balance, transferring the $300, and commits.

Advantages: Ensuring Consistency

Locks are fundamental to maintaining consistency in concurrent systems. Shared locks ensure that data doesn’t change during read operations, critical for long-running queries or reports where mid-query modifications would produce inconsistent results. Exclusive locks ensure that updates occur atomically without interference from concurrent reads or writes.

Banking systems exemplify this necessity. Reading an account balance during a transaction must return a stable value—if the balance changes halfway through a calculation, the result becomes unreliable. Configuration management systems benefit similarly: when updating critical configuration values, preventing concurrent reads ensures all clients eventually see the latest configuration rather than reading stale values during the update process.

Disadvantages: Reduced Concurrency

Locks inherently reduce concurrency. When a connection holds an exclusive lock, all other connections attempting to access that data must wait or fail. When multiple connections hold shared locks, any connection attempting to modify the data is blocked.

This manifests visibly in banking systems. Some banks disable transaction capabilities during overnight reporting jobs—shared locks held during extensive reporting operations prevent customers from acquiring exclusive locks for their own transactions. The system prioritizes consistency over availability during these windows.

The trade-off is unavoidable: consistency and concurrency are opposing forces. Locks sacrifice some concurrency to guarantee consistency, and the balance between these priorities depends on application requirements.

Dead Locks

A deadlock occurs when two or more processes are waiting for each other to release locks on resources, creating a circular dependency where none can proceed.

This is a classic problem in computer science that manifests frequently in database systems. Most modern databases detect deadlocks automatically and resolve them by rolling back one of the involved transactions.

Understanding the Deadlock Scenario

The demonstration uses two client terminals connected to a single PostgreSQL instance. A table called test exists with a primary key field, which enforces uniqueness—only one row can have a given value at any time.

Both clients begin transactions:

-- Client 1
BEGIN transaction;
 
-- Client 2  
BEGIN transaction;

Client 1 inserts a value:

-- Client 1
INSERT INTO test VALUES (20);

This succeeds because value 20 doesn’t exist. Importantly, an exclusive lock is now held on value 20, even though the transaction hasn’t committed yet. No other transaction can insert or modify this value until Client 1’s transaction completes.

Client 2 inserts a different value:

-- Client 2
INSERT INTO test VALUES (21);

This also succeeds, placing an exclusive lock on value 21.

Now the deadlock setup begins. Client 2 attempts to insert value 20:

-- Client 2
INSERT INTO test VALUES (20);

This blocks because Client 1 holds an exclusive lock on 20. Client 2 must wait until Client 1 either commits or rolls back. However, the transaction hasn’t failed yet—it’s simply waiting.

To create the deadlock, Client 1 now attempts to insert value 21:

-- Client 1
INSERT INTO test VALUES (21);

Client 1 now blocks waiting for Client 2 to release its lock on 21, while Client 2 is already waiting for Client 1 to release its lock on 20. This circular dependency is a deadlock: neither transaction can proceed because each is waiting for the other.

Deadlock Detection and Resolution

PostgreSQL detects this deadlock almost immediately—typically within a second. The database resolves the deadlock by rolling back the transaction that entered the deadlock last. In this case, Client 1’s second INSERT (attempting value 21) created the circular dependency, so Client 1’s transaction is automatically rolled back with a deadlock error. Client 2’s transaction succeeds because it was waiting first. Once Client 1 rolls back and releases the lock on 20, Client 2 can proceed with its operations.

This automatic detection and resolution prevents transactions from waiting indefinitely. The database’s deadlock detector monitors for circular lock dependencies and breaks them by sacrificing one transaction.

Non-Deadlock Lock Waiting

Not all blocking scenarios are deadlocks. Consider a simpler case where both clients begin transactions:

-- Client 1
BEGIN transaction;
INSERT INTO test VALUES (30);
 
-- Client 2
BEGIN transaction;
INSERT INTO test VALUES (20);

Both succeed. Now Client 1 attempts to insert 20:

-- Client 1
INSERT INTO test VALUES (20);

Client 1 blocks, waiting for Client 2 to complete the transaction. This is not a deadlock—there’s no circular dependency. Client 1 is simply waiting for Client 2 to release the lock on 20.

If Client 2 rolls back:

-- Client 1
ROLLBACK;

Client 1’s INSERT immediately succeeds because value 20 is now available:

Alternatively, if Client 2 commits instead of rollbacks:

-- Client 2
COMMIT;

Client 1’s INSERT fails with a duplicate key error because value 20 now permanently exists in the table. The uniqueness constraint is violated, and the INSERT cannot proceed:

Key Distinction

The difference between deadlock and simple lock waiting is the presence of circular dependency. Simple blocking occurs when one transaction waits for another to complete—this is expected behavior. Deadlock occurs when transactions wait for each other in a cycle, making progress impossible without external intervention. Databases handle simple blocking by queuing requests and handle deadlocks by detecting the cycle and terminating one transaction to break it.

Two-Phase Locking

Two-phase locking is a concurrency control mechanism that prevents conflicts by dividing lock management into two distinct phases: an acquisition phase where locks are obtained, and a release phase where locks are relinquished. The critical rule is that once a transaction begins releasing locks, it cannot acquire new locks. This protocol prevents common concurrency problems like double booking.

The Double Booking Problem

Consider a cinema booking system where two users attempt to reserve the same seat simultaneously. Without proper locking, both transactions can check seat availability, find it unbooked, and both commit their reservations—resulting in two customers paying for the same seat with the last commit overwriting the first.

The demonstration uses two concurrent transactions attempting to book seat 13. Both transactions begin:

-- Transaction 1
BEGIN transaction;
 
-- Transaction 2
BEGIN transaction;

Both check if seat 13 is available:

-- Transaction 2
SELECT * FROM seats WHERE id = 13;
-- Returns: seat is available
 
-- Transaction 1
SELECT * FROM seats WHERE id = 13;
-- Returns: seat is available

Both transactions see the seat as available because neither has committed yet. Each proceeds to book the seat:

-- Transaction 2
UPDATE seats SET is_booked = 1, name = 'Hussein' WHERE id = 13;
 
-- Transaction 1
UPDATE seats SET is_booked = 1, name = 'Edmund' WHERE id = 13;

In PostgreSQL, Transaction 1’s UPDATE blocks, waiting for Transaction 2 to complete. When Transaction 2 commits:

-- Transaction 2
COMMIT;

Transaction 1 immediately proceeds with the update (still in the left transaction since we have no committed yet):

If we check the result in the Transaction 2, we’ll see that the system has send a confirmation email to user 2:

But, after committing the Transaction 1, the result is that Edmund’s booking overwrites Hussein’s booking. Checking the seat after both commits shows Edmund as the owner, but Hussein also received confirmation:

This is the double booking problem—both users believe they successfully reserved the seat.

Implementing Two-Phase Locking

Two-phase locking solves this by acquiring an exclusive lock during the read operation, preventing concurrent transactions from accessing the row until the lock is released.

Both transactions begin:

-- Transaction 2
BEGIN transaction;
 
-- Transaction 1
BEGIN transaction;

Transaction 2 checks availability but uses SELECT FOR UPDATE:

-- Transaction 2
SELECT * FROM seats WHERE id = 14 FOR UPDATE;

The FOR UPDATE clause acquires an exclusive lock on the returned row. The query returns normally, showing seat 14 is available, but now an exclusive lock is held. This is Phase 1: the growing phase, where locks are acquired.

Transaction 1 attempts the same query:

-- Transaction 1
SELECT * FROM seats WHERE id = 14 FOR UPDATE;

This blocks immediately. Transaction 1 cannot acquire an exclusive lock because Transaction 2 already holds one. Transaction 1 must wait until Transaction 2 releases the lock.

Transaction 2 proceeds with the booking:

-- Transaction 2
UPDATE seats SET is_booked = 1, name = 'Hussein' WHERE id = 14;
COMMIT;

When Transaction 2 commits, it enters Phase 2: the shrinking phase, where locks are released. The moment the commit occurs, the exclusive lock is released, and Transaction 1 immediately unblocks:

Transaction 1’s SELECT FOR UPDATE now executes and returns the row. However, the row now shows is_booked = 1 and name = 'Hussein'. Transaction 1’s application logic detects the seat is already booked and rejects the booking attempt, returning an error to the user.

The Two Phases

Phase 1 (Growing Phase) involves acquiring all necessary locks. In this example, the SELECT FOR UPDATE acquires the exclusive lock on the target row before any modifications occur. The transaction can acquire additional locks during this phase if needed.

Phase 2 (Shrinking Phase) begins when the first lock is released, typically at commit or rollback. Once Phase 2 begins, no new locks can be acquired. This ensures that all necessary data is locked before any modifications are visible to other transactions, preventing scenarios where partial results become visible and cause inconsistencies.

Two-phase locking guarantees serializability: concurrent transactions execute as if they ran sequentially, eliminating race conditions like double booking. The cost is reduced concurrency—transactions hold locks longer, potentially blocking other transactions, but this trade-off ensures correctness.

Solving the Double Booking Problem (Code Example)

Building an online booking system requires preventing double booking—the scenario where two users simultaneously reserve the same seat. This problem manifests as a race condition when concurrent transactions attempt to book identical resources at the same moment.

The Application Setup

The demonstration uses an Express/Node.js application with PostgreSQL as the backend database. The interface displays available seats in green and booked seats in red. Multiple users can access the system concurrently, each viewing the same seat inventory.

When a user books a seat—for example, Ali books seat 3—the seat turns red and displays the owner’s name in a tooltip:

Refreshing other users’ browsers updates their view to reflect the booking. This basic functionality works correctly for sequential bookings where no two users attempt to reserve the same seat simultaneously:

The Flawed Implementation

The initial implementation follows a common pattern: handle the booking via a PUT request that accepts the seat ID and user name, then execute a transaction:

app.put("/:id/:name", async (req, res)) => {
	try {
		const id = req.params.id
		const name = req.params.name;
		
		const conn = await pool.connect();
		// begin transaction
		await conn.query('BEGIN');
		
		// getting the row to make sure it is not booked
		const result = await conn.query(
		  'SELECT * FROM seats WHERE id = $1 AND isbooked = 0',
		  [id]
		);
		
		// if no rows found then the operation should fail
		if (result.rowCount === 0) {
		  res.send({"error": "Seat already booked"})
		  return;
		}
		
		// if we get the row, we are safe to update
		const sqlU = 'UPDATE seats set isbooked = 1, name = $2 where id = $1'
		const updateResult = await conn.query(sqlU, [id, name]);
		
		// end transaction
		await conn.query('COMMIT');
		conn.release();
		res.send('Booked successfully');
	}
	catch(ex) {
		
	}
}

This appears correct: check if the seat is available, reject if already booked, otherwise update and commit. Testing with a single user works perfectly:

attempting to book a non-reserved seat returns “Booked Successfully”
attempting to book an already-reserved seat returns “Seat already booked”.

Suppose user 2 make a reservation for sit 9. As expected, it returns a successful status:

Suppose user 1 make a reservation for sit 9, which is reserved by user 2. As expected, it returns an error status

However, this implementation has a critical flaw. With concurrent requests, both transactions can execute the SELECT statement simultaneously before either commits the UPDATE. Both find the seat available, both pass the availability check, and both execute the UPDATE statement. The last commit wins, but both users receive success confirmations. This is the double booking problem.

Reproducing the Race Condition

Using a debugger demonstrates the vulnerability. When user Rick attempts to book seat 15 (right browser user), a breakpoint pauses execution immediately after the SELECT query but before the UPDATE. At this point, Rick’s transaction has confirmed the seat is available but hasn’t yet reserved it.

While Rick’s transaction is paused, user Edmund attempts to book the same seat 15 (left browser user). Edmund’s transaction executes without debugging: it queries the database, finds seat 15 still available (because Rick hasn’t committed), and completes the booking successfully. Seat 15 is now marked as booked by Edmund.

Resuming Rick’s transaction allows it to continue. It executes the UPDATE statement, overwriting Edmund’s booking with Rick’s name. Both users receive “Booked successfully” messages, but only Rick’s booking persists. This is double booking: two users reserved the same seat, but the system accepted both reservations.

With high traffic—such as popular concert ticket sales or cinema bookings—this race condition occurs naturally without artificial delays. Multiple requests arrive within milliseconds of each other, creating the same overlapping execution scenario.

The Solution: Row-Level Exclusive Locks

The fix uses PostgreSQL’s row-level locking mechanism with SELECT FOR UPDATE. This statement acquires an exclusive lock on the selected row, preventing concurrent transactions from accessing it:

const result = await conn.query(
  'SELECT * FROM seats WHERE id = $1 AND is_booked = 0 FOR UPDATE',
  [id]
);

The FOR UPDATE clause instructs the database to lock the returned row immediately upon selection. Any other transaction attempting to select the same row with FOR UPDATE must wait until the first transaction completes.

This is supported by PostgreSQL, MySQL, Oracle, and other enterprise databases as part of the SQL-92 standard, though syntax and behavior details vary.

Testing the Fixed Implementation

After clearing all bookings and restarting the application, the same concurrent booking scenario is tested. Rick attempts to book seat 15, and execution pauses after the SELECT FOR UPDATE. At this moment, Rick’s transaction holds an exclusive lock on seat 15’s row.

Melissa simultaneously attempts to book seat 15. Her request blocks indefinitely, waiting for Rick’s transaction to complete. The database prevents her SELECT FOR UPDATE from proceeding because Rick already holds the exclusive lock. Her transaction doesn’t fail—it waits.

When Rick’s transaction resumes and commits, the exclusive lock is released. Melissa’s blocked SELECT FOR UPDATE immediately executes, but now it retrieves the updated row showing is_booked = 1 and name = 'Rick'. The rowCount check returns 0 because no row matches is_booked = 0, and Melissa’s transaction returns “Seat already booked.”

Only one user successfully books the seat, and the second user receives an accurate error message. The race condition is eliminated.

Trade-offs and Limitations

The solution is bulletproof against double booking but introduces blocking behavior. PostgreSQL doesn’t support timeout parameters for SELECT FOR UPDATE (unlike Oracle, which allows specifying wait times). A transaction holds the lock until commit or rollback, and waiting transactions have no option to timeout and fail gracefully.

In production, this typically isn’t problematic. Transactions complete in milliseconds under normal conditions, so the wait is imperceptible to users. The blocking duration remains minimal as long as transactions don’t include debugger breakpoints or expensive operations.

The implementation is now race-safe: concurrent bookings for the same seat serialize automatically through row-level locking, ensuring only one succeeds while others fail cleanly with accurate error messages.

Double Booking Problem Part 2 (Alternative Solution and explanation)

The double booking prevention lecture presented SELECT FOR UPDATE as the solution, but students proposed an alternative: skip the SELECT entirely and rely on the UPDATE statement itself to handle locking. While this approach works, understanding why it works requires examining database internals and transaction semantics.

The Alternative Approach

Instead of explicitly locking with SELECT FOR UPDATE, some suggested directly updating the row and checking the result:

UPDATE seats 
SET isbooked = 1, name = 'Hussein' 
WHERE id = 1 AND isbooked = 0;

If the update succeeds (returns 1 affected row), the seat was available and is now booked. If it returns 0 affected rows, the seat was already booked. This appears simpler than the two-step approach of selecting with a lock, then updating.

This alternative does work, but relies on specific database implementation details that aren’t guaranteed across all systems.

Testing the Alternative Approach

Two transactions begin simultaneously:

-- Transaction 1
BEGIN transaction;
 
-- Transaction 2
BEGIN transaction;

Transaction 1 executes the update for Hussein:

-- Transaction 1
UPDATE seats 
SET isbooked = 1, name = 'Hussein' 
WHERE id = 1 AND isbooked = 0;
-- Returns: 1 row updated

The transaction hasn’t committed yet, but an implicit exclusive lock is now held on row 1. This differs from the explicit lock acquired by SELECT FOR UPDATE—the database automatically locks rows being modified by UPDATE statements.

Transaction 2 attempts to update the same seat for Rick:

-- Transaction 2
UPDATE seats 
SET isbooked = 1, name = 'Rick' 
WHERE id = 1 AND isbooked = 0;
-- Blocks, waiting...

Transaction 2 blocks immediately. Understanding why requires examining what happens internally.

What Happens During the Block

When Transaction 2 executes the UPDATE, the database must evaluate the WHERE clause to identify which rows to modify. The WHERE clause includes id = 1 AND isbooked = 0. To evaluate this, the database traverses the index on the id column (typically a B-tree), locates the tuple for ID 1, and discovers that Transaction 1 holds a lock on this tuple. The lock manager signals that this row is locked, forcing Transaction 2 to wait before proceeding. At this moment, Transaction 2 has identified the row but hasn’t yet read its values from the heap. The index traversal revealed the lock, preventing further access.

Now consider: if Transaction 2 could read the heap value, what would isbooked contain? It would still be 0—the original value before Transaction 1’s UPDATE. Transaction 2 started at the same time as Transaction 1 and operates under read committed isolation, meaning it doesn’t see uncommitted changes. From Transaction 2’s perspective, the seat remains unbooked.

This is why Transaction 2 successfully identified the row during the index scan—the WHERE condition isbooked = 0 was satisfied based on the pre-update value. However, attempting to update triggers the lock, causing the block.

What Happens When Transaction 1 Commits

When Transaction 1 commits:

-- Transaction 1
COMMIT;

Two things occur: the exclusive lock on row 1 is released, and the changes become visible to other transactions. The row now shows isbooked = 1 and name = 'Hussein'.

Transaction 2 immediately unblocks. Critically, PostgreSQL refreshes the row values before completing the UPDATE. Rather than blindly applying the update based on the stale values it saw during the initial scan, PostgreSQL re-evaluates the WHERE clause against the current committed state.

The WHERE clause isbooked = 0 is now false—the seat is booked. The UPDATE affects 0 rows and returns that count to the application, which correctly interprets this as “seat already booked.”

This behavior is specific to PostgreSQL’s implementation with read committed isolation. PostgreSQL stores lock information in the heap row itself, so accessing the heap to read isbooked also reveals the lock status. When the lock releases, PostgreSQL re-executes the query logic to fetch fresh values, detecting that the condition no longer holds.

Why This Isn’t Guaranteed Across Databases

This approach works in PostgreSQL but may not work identically in MySQL, SQL Server, or other databases. Each database implements locking and transaction isolation differently:

MySQL might handle row locks and value refreshes differently
SQL Server uses in-memory lock structures rather than storing lock metadata in heap rows
Different databases may optimize combined index lookups (id and is_booked together) differently, potentially avoiding heap access entirely

Without deep knowledge of a specific database’s internals, you cannot guarantee this pattern produces correct results. You’re at the mercy of implementation details that can change between versions or behave unexpectedly under certain conditions.

Trade-offs: Implicit vs. Explicit Locking

The UPDATE-only approach has advantages:

Simpler code: One statement instead of SELECT then UPDATE
Potentially more efficient: No separate lock acquisition step

However, it has significant limitations:

Requires read committed isolation level for the value refresh behavior
Less control: You can only lock what you’re immediately updating
Limited to single operations: If you need to check the seat, then perform additional logic before booking, you must hold the lock across multiple operations—impossible with implicit locking

The explicit SELECT FOR UPDATE approach provides more control. You acquire the lock, perform whatever checks or operations are needed, then update and commit. The lock remains held throughout, serializing access completely. While this might seem less efficient, databases use pessimistic concurrency control anyway—they always acquire locks during updates—so the overhead difference is minimal.

Pessimistic vs. Optimistic Concurrency Control

PostgreSQL and most relational databases use pessimistic concurrency control: they assume conflicts will occur and prevent them by acquiring locks preemptively. This means transactions might block waiting for locks, but when they succeed, they succeed correctly without retries.

Optimistic concurrency control (common in NoSQL systems) assumes conflicts are rare, allowing transactions to proceed without locks. At commit time, the database detects if conflicts occurred and fails the transaction, requiring application-level retry logic.

Pessimistic control is preferred for booking systems because transaction failures due to concurrency create poor user experience and complicate application code. Managing locks is expensive, but it guarantees that successfully completed transactions don’t need retries and fail only for legitimate reasons—not due to concurrent access patterns.

Understanding Database Fundamentals

The key lesson is understanding that databases are software written by humans, making implementation choices based on trade-offs and constraints. Nothing is perfect. Terminology can be intimidating, but beneath the abstractions are code paths making decisions about when to acquire locks, when to refresh values, and how to handle conflicts.

By understanding these fundamentals—isolation levels, lock types, transaction semantics—you become capable of asking the right questions and evaluating whether a solution will work in your specific context. Don’t accept solutions at surface value; dig deeper into why they work, what assumptions they rely on, and whether those assumptions hold for your database and workload.

The alternative UPDATE-only approach works in PostgreSQL under specific conditions, but the explicit SELECT FOR UPDATE approach works reliably across databases because it doesn’t depend on implicit refresh behavior or lock storage details. Choose based on understanding the trade-offs, not convenience.

SQL Pagination With Offset is Very Slow

Using SQL OFFSET for pagination creates severe performance problems that worsen as users navigate deeper into result sets. Understanding why this occurs and knowing the alternative approach is essential for building scalable applications.

How OFFSET Works

Consider a web application with an API that supports paginated news articles. A user requests page 10 with 10 articles per page, generating a GET request that the API server translates into SQL:

Assuming a pool of established TCP connections to the database (avoiding cold start overhead), page 10 translates to OFFSET 100 since pages 1-9 contain 90 articles, plus 10 more to skip for page 10:

The database executes a query like:

SELECT title FROM news 
ORDER BY id DESC 
OFFSET 100 LIMIT 10;

OFFSET by design means “fetch and discard the first X rows.” The database physically retrieves the first 110 rows (100 + 10), discards the first 100, and returns only the final 10. As the offset increases, the database performs progressively more work for the same result size, making the operation increasingly expensive.

The Duplicate Records Problem

OFFSET also causes correctness issues. When a user requests page 11 (OFFSET 110), but new records are inserted into the table before the query executes, the offset shifts existing records. A row that appeared on page 10 might now appear on page 11, causing the user to see duplicate records. The pagination becomes inconsistent because row positions change between page requests.

Performance Testing with PostgreSQL

Testing demonstrates the performance degradation. Starting with a table called news that has an index on the id column:

EXPLAIN ANALYZE 
SELECT title FROM news 
ORDER BY id DESC 
OFFSET 0 LIMIT 10;

With offset 0, the database performs an index scan backward (descending order), retrieving exactly 10 rows. Execution time: 0.2 milliseconds. The index is already sorted, so scanning backward from the highest ID is efficient.

Increasing the offset to 1,000:

OFFSET 1000 LIMIT 10;

Now the database retrieves 1,010 rows (1,000 + 10) from the index, discards 1,000, and returns 10. Execution time: 1 millisecond. The pattern becomes clear—the database does work proportional to the offset, not just the limit.

Increasing to offset 100,000:

OFFSET 100000 LIMIT 10;

The database retrieves 100,010 rows and returns 10. Execution time: 79 milliseconds. The database is performing massive unnecessary work. It scans through 100,000 rows it will immediately discard. This isn’t just a performance problem—in SQL Server, touching this many rows would trigger lock escalation, potentially causing severe concurrency issues. PostgreSQL avoids lock escalation, but the wasted I/O and CPU remain problematic.

Pushing to offset 1,000,000 shows execution times around 620 milliseconds with warm caches. On the first run with cold caches, execution can take 6+ seconds for a single page request.

The Efficient Alternative: Keyset Pagination

Instead of using OFFSET, use the primary key (or another indexed column) as a pagination cursor. The first request retrieves 10 records:

SELECT id, title FROM news 
ORDER BY id DESC 
LIMIT 10;

This returns 10 articles with their IDs:

The application notes the last ID in the result set—for example, ID 100999993.

The next page request uses this ID as a cursor:

SELECT id, title FROM news 
WHERE id < 100999993 
ORDER BY id DESC 
LIMIT 10;

This query tells the database: “Give me the 10 newest articles older than ID 100999993.” The WHERE clause filters using the indexed ID column before applying the limit.

Testing with EXPLAIN ANALYZE:

EXPLAIN ANALYZE 
SELECT id, title FROM news 
WHERE id < 100999993 
ORDER BY id DESC 
LIMIT 10;

The database works with only 10 rows. The index scan identifies rows matching id < 100999993, scans backward through those results, and stops after retrieving 10 rows. No matter how deep the pagination goes, the database only processes the rows needed for that page.

Even when navigating to “page 1000” (which is very very old), the database still processes approximately 10 rows plus a small amount of overhead for the index filter:

EXPLAIN ANALYZE 
SELECT id, title FROM news 
WHERE id < 1000 
ORDER BY id DESC 
LIMIT 10;

The result set size from the index remains 10, the heap access retrieves 10 titles, and the limit returns 10 rows. The I/O and CPU work remain constant regardless of pagination depth.

Key Differences (Added by Claude Sonnet 4.5)

With OFFSET, the database must:

Scan through OFFSET + LIMIT rows
Physically retrieve all those rows from the index
Discard the OFFSET rows
Return the LIMIT rows

Work scales linearly with page number.

With keyset pagination, the database must:

Use the WHERE clause to filter efficiently via the index
Retrieve only LIMIT rows
Return those rows immediately

Work remains constant regardless of page number.

The critical insight is that LIMIT 10 alone tells the database it only needs 10 rows, allowing early termination. Adding OFFSET 100000 forces the database to retrieve 100,010 rows before applying the limit, negating the optimization entirely.

Implementation Requirements (Added by Claude Sonnet 4.5)

Keyset pagination requires:

An indexed column for cursor values (typically the primary key)
Clients to track the last seen ID and include it in the next page request
Adjusting the WHERE clause direction based on pagination direction (forward vs. backward)

The trade-off is additional application logic, but the performance gain is substantial. For deep pagination or large tables, keyset pagination is the only scalable approach.

Database Connection Pooling

Connection pooling is a pattern that creates a shared pool of available connections—typically TCP connections—allowing multiple clients to reuse them rather than establishing new connections for each request. This pattern becomes essential when connection establishment and teardown are expensive, which is particularly true for database connections. Connection pooling is also critical when the database server has a limited number of available connections but must serve many clients.

The Traditional Stateless Approach

The traditional REST API pattern establishes a new database connection for every request. When a GET request arrives, the backend:

Opens a TCP connection to the database (three-way handshake)
Performs database protocol handshake
Executes the query
Retrieves results
Closes the connection (releases file descriptors and resources)

This approach is stateless—each request is independent—but extremely inefficient. Every request pays the full cost of connection establishment and teardown, which involves network round-trips, authentication, and resource allocation.

Testing with a PostgreSQL database and a simple Express endpoint demonstrates this. The endpoint queries an employees table with SELECT * FROM employees (a deliberately poor unbounded query used only for demonstration). Each request reports how long the operation took, including connection overhead.

The implementation creates a new client for every request:

const app = require("express")();
const {Client} = require("pg");
 
app.get('/all', async (req, res) => {
  const fromDate = new Date();)
  
  const client = new Client({
    host: 'localhost',
    port: 5432,
    user: 'postgres',
    database: 'husseindb',
    password: 'postgres',
  });
  
  await client.connect();
  const result = await client.query('SELECT * FROM employees');
  console.table(results.rows)
  client.end();
  
  const toDate = new Date()
  const elapsed = toDate.getTime() - fromDate.getTime()
  
  res.send({"rows": results.rows, "elapsed": elapsed, "method": "old"})
});

Every call to this endpoint establishes and tears down a complete database connection, making it the slowest possible approach.

The Connection Pool Approach

Connection pooling eliminates this overhead by maintaining a pool of pre-established connections that requests can reuse. The pool is created once when the server starts, not for every request:

const app = require("express")();
const {Pool} = require("pg");
 
app.get('/all', async (req, res) => {
  const fromDate = new Date();)
  
  const pool = new Pool({
    host: 'localhost',
    port: 5432,
    user: 'postgres',
    database: 'husseindb',
    password: 'postgres',
	max: 20,                    // Maximum number of connections in pool
	connectionTimeoutMillis: 0, // Wait time for available connection (0 = wait forever)
	idleTimeoutMillis: 0    // Time before idle connection is destroyed
  });
  
  await client.connect();
  const result = await pool.query('SELECT * FROM employees');
  console.table(results.rows)
  
  const toDate = new Date()
  const elapsed = toDate.getTime() - fromDate.getTime()
  
  res.send({"rows": results.rows, "elapsed": elapsed, "method": "pool"})
});

The max parameter determines how many connections the pool maintains. The default is typically 10, but this can be adjusted based on database capacity and application load. When all connections are busy, new requests must wait until a connection becomes available.

The connectionTimeoutMillis parameter controls how long a request waits for an available connection. Zero means wait indefinitely. Setting a finite timeout allows requests to fail gracefully rather than blocking forever.

The idleTimeoutMillis parameter determines when unused connections are destroyed. Connections consume memory and file descriptors, so destroying idle connections after 10 seconds (the default) frees resources. Setting this to zero keeps connections alive indefinitely.

Using the pool changes the request handler significantly.

No connection establishment or teardown occurs. The pool.query() method automatically selects an available connection from the pool, executes the query, and returns the connection to the pool for reuse. The application never manages individual connections.

For transactions requiring multiple queries on the same connection, the pool can provide a dedicated client:

const client = await pool.connect();
try {
  await client.query('BEGIN');
  await client.query('INSERT ...');
  await client.query('UPDATE ...');
  await client.query('COMMIT');
} finally {
  client.release(); // Return connection to pool
}

This ensures all transaction queries execute on the same connection while still benefiting from connection reuse.

Performance Comparison

To measure the performance difference, both approaches are tested with 1,000 sequential requests against a local PostgreSQL database.

The stateless approach (creating and destroying connections for every request) averages approximately 40 milliseconds per request. This includes the overhead of establishing the TCP connection, authenticating, executing the query, and closing the connection. Over 1,000 requests, this means creating and destroying 1,000 TCP connections, generating substantial overhead.

The connection pool approach with the same 1,000 requests averages approximately 19 milliseconds per request—roughly 50% faster. The connections are already established and authenticated, so each request only pays for query execution and result retrieval.

This performance gap widens significantly for remote databases. Local databases have minimal network latency, so connection overhead is relatively small. With remote databases in cloud environments, network round-trips for connection establishment become substantially more expensive, making connection pooling even more critical.

As the pool size increases and more concurrent requests can be handled without waiting for available connections, throughput continues to improve. The pool efficiently manages connection resources, keeping frequently used connections active while destroying idle ones to conserve memory.

Node.js’s single-threaded, non-blocking architecture complements connection pooling well. Despite running on a single thread, Node.js manages multiple concurrent database operations without blocking, allowing the pool to serve many requests efficiently without thread management overhead.

Connection pooling transforms database access from an expensive per-request operation into a shared resource that amortizes connection costs across many requests, dramatically improving application performance and scalability.

Quartz 4

Explorer

08. Concurrency Control