DEV Community: Prince Singh

Slow SQL query: find a missing index with EXPLAIN

Prince Singh — Mon, 02 Nov 2026 00:00:00 +0000

#The doubt

A page that lists a customer's orders was instant in development. Months later it takes four seconds, and the database CPU is high every time someone opens it. A colleague says "add an index", runs one command, and the page becomes instant again.

Same query, same data, same database. What did an index change?

It changed how the database finds the rows. Without an index, it has to read all of them.

In this post

By the end you will know:

What a full table scan is and why it gets slower as data grows
How to diagnose a slow query by reading its query plan
How to create the right index, including multi-column ones
Why an index is sometimes ignored, and what indexes cost

#Full table scan versus index

Think of a 500-page book with no index at the back. To find every mention of "Smith", you read all 500 pages. With an index, you look up "Smith" and turn straight to pages 12 and 340. A database table works the same way.

The query is simple:

SELECT * FROM orders WHERE customer_id = 42;

With no index on customer_id, the database has only one way to answer: read every row in the table and keep the ones that match. This is a full table scan (a sequential scan in PostgreSQL). With 1,000 rows, that is instant. With 10 million, it reads 10 million rows to return the 15 orders that customer 42 has.

An index is a separate, sorted structure, usually a B-tree (a tree that keeps values in order, so a lookup takes a few steps), that maps a column's values to where the rows are. The database jumps straight to customer_id = 42 and reads only those rows. The work no longer grows with the size of the table, only slowly with its depth.

Diagram: see it on sudoexplain.dev

Figure 1. The scan reads 10 million rows to find customer 42's 15 orders. The index lookup touches a handful.

#Why the index is so much faster

Think of looking up a name in a phone book. You do not read from page one. You open the middle, see you are too early in the alphabet, and jump forward. Each jump throws away most of the remaining pages.

A B-tree index does the same, in levels. Each level is a short, sorted list of directions , like "ids 1 to 100: go this way". Start with a tiny example: customer ids 1 to 1,000, and lists of 10 directions each.

The top level has 10 directions: "ids 1 to 100 are down this branch, 101 to 200 down that one," and so on up to 1,000.
Inside the branch for 1 to 100, the middle level has 10 more directions: "1 to 10, 11 to 20, 21 to 30," and so on.
The bottom level holds the 10 exact ids of one range, each with the location of its rows in the table.

To find customer 42, the database picks the branch for 1 to 100, then the one for 41 to 50, then finds 42 in a list of 10. That is 3 small lists , about 30 directions at most. Without the index it would read up to 1,000 rows.

Diagram: see it on sudoexplain.dev

Figure 2. A tiny index of 1,000 ids. Looking up customer 42 takes three steps.

Now grow the table. Each extra level multiplies the reach by 10. Four levels cover 10,000 ids, five cover 100,000. A thousand times more ids costs only 3 more steps, while a scan would read a thousand times more rows.

Real lists are bigger than 10. A typical one holds hundreds of directions (500 is a fair guess, depending on the column size). Three levels then reach 500 x 500 x 500 = 125 million rows. So the 10-million-row table from Figure 1 needs only about three steps, and so does a table ten times bigger.

That is the whole fix. The scan read 10 million rows, and the index reads three small lists and then the 15 matching orders.

#How to find a slow query and read EXPLAIN

1. Find the slow queries. Turn on the slow-query log. In PostgreSQL, set log_min_duration_statement = 500, which logs every query that takes longer than 500 milliseconds. pg_stat_statements is an extension, an add-on module that ships with PostgreSQL. It ranks queries by total time, but it is not on by default. First add its name to the shared_preload_libraries setting, which lists the extensions loaded when the server starts, and restart the server. Then enable it with CREATE EXTENSION pg_stat_statements;. Often one or two queries account for most of the load. If no single query is slow but one page runs hundreds of them, that is the N+1 query problem, not a missing index.

2. Ask the database how it runs the query. Put the word EXPLAIN ANALYZE in front of your query:

EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 42;

The database does not return the orders. It runs the query, measures it, and prints its query plan : the steps it took to find the rows. Here is an illustrative result for our table with no index:

Seq Scan on orders (cost=0.00..250000.00 rows=12 width=64)
                    (actual time=0.1..4200.5 rows=15 loops=1)
  Filter: (customer_id = 42)
  Rows Removed by Filter: 9999985
Execution Time: 4201.2 ms

Read it from the top, one line at a time:

Seq Scan on orders: the database read the orders table from start to end. This is the full table scan from earlier. On a big table, it is the warning sign.
Filter: (customer_id = 42): for each row, it checked whether customer_id is 42.
Rows Removed by Filter: 9999985: it threw away almost 10 million rows. Only 15 matched (rows=15). This is the wasted work.
Execution Time: 4201.2 ms: the whole query took about 4 seconds.

You can ignore the cost numbers for now. They are the database's own guess in made-up units, useful only for comparing plans with each other.

Warning

EXPLAIN ANALYZE really runs the statement. For an UPDATE or DELETE, the data changes. Wrap it so you can undo it: BEGIN; EXPLAIN ANALYZE UPDATE ...; ROLLBACK;.

3. Compare the estimate with reality. The plan shows two row counts: how many rows the database expected (rows=12 in the first line) and how many it got (rows=15). Here they are close, which is good.

Why check this? Before running a query, the database cannot know how many rows will match. It guesses , using statistics (its notes on how values are spread in each table), and picks the plan that looks cheapest for that guess. If the guess is wrong, the plan can be wrong too.

For example, suppose the notes are out of date and the database expects 12 rows, but 500,000 come back. A plan that is perfect for 12 rows can be very slow for 500,000. Adding an index will not fix that, because the real problem is the bad guess.

So a big gap between expected and actual rows tells you the statistics are stale. Running ANALYZE on the table refreshes them. This is a separate command from EXPLAIN ANALYZE, despite the shared word. If the two counts are close and you still see a Seq Scan on a big table, the index is what is missing.

#Fix 1: create the index

-- PostgreSQL
CREATE INDEX CONCURRENTLY idx_orders_customer_id
    ON orders (customer_id);

CONCURRENTLY builds the index without blocking writes to the table, which matters on a live system. Without it, inserts and updates wait while the index is built. On a busy table with a 5-minute build (illustrative), nobody can place an order, and the waiting requests can drain the connection pool. The price is time: it reads the table twice and waits for running transactions to finish, so it takes longer than a plain CREATE INDEX. That is a fair trade on a live system. (It also has one catch, covered in "When the fix goes wrong".)

The plan after the index:

Index Scan using idx_orders_customer_id on orders
                    (actual time=0.03..0.09 rows=15 loops=1)
  Index Cond: (customer_id = 42)
Execution Time: 0.12 ms

From 4,201 ms to 0.12 ms, with no change to the query.

#Fix 2: match the index to the whole query

Real queries filter and sort on several columns:

SELECT * FROM orders
WHERE customer_id = 42 AND status = 'OPEN'
ORDER BY created_at DESC
LIMIT 20;

A multi-column index serves this in one pass, but column order matters. Put the columns you compare with = first, then the one you sort by:

CREATE INDEX idx_orders_customer_status_created
    ON orders (customer_id, status, created_at DESC);

An index works from the left , like a phone book sorted by last name, then first name. You can look up "Smith", or "Smith, Anna", but not every "Anna" without reading the whole book. In the same way, this index helps queries that filter on customer_id, or on customer_id and status. It usually cannot help a query on status alone. PostgreSQL 18 can sometimes skip over the first column, but only when it has few distinct values, so do not rely on it.

#Fix 3: write the query so the index can be used

An index on created_at is sorted by the stored values. If your query changes the column first, the database cannot use that order.

-- the index cannot help: date() changes every value first
WHERE date(created_at) = '2026-03-01'

Think of the phone book again. It is sorted by name. If you ask for "everyone whose name has 5 letters", the book cannot help. You must read every entry and count. The database does the same here. date() turns each raw timestamp, such as 2026-03-01 14:25:09, into a plain date, 2026-03-01. The index holds the raw timestamps, not these dates, so the database must convert every row first and then compare.

Leave the column alone and compare it with a range instead:

-- the index can jump straight to this range
WHERE created_at >= '2026-03-01'
  AND created_at < '2026-03-02'

The same applies to lower(email) = 'a@example.com'. If you must query that way, create an index on the same expression:

CREATE INDEX idx_users_email_lower
    ON users (lower(email));

A search that starts with a wildcard, such as name LIKE '%smith', has the same problem, because the index cannot jump to "anything ending in smith". That needs a different kind of index, a GIN index with PostgreSQL's pg_trgm extension, which we leave for another post.

#When the fix goes wrong

An index is not free, and it is not always used. These three cause the most surprises.

1. A failed CONCURRENTLY build. To avoid making inserts and updates wait while the index is built, you used CONCURRENTLY, so building on a live table feels safe. But the build can still fail halfway, for example because of a deadlock (two operations waiting on each other) or a duplicate value in a unique index.

When it fails, PostgreSQL does not clean up. It leaves a half-built index behind, marked invalid. The planner ignores an invalid index, so your query stays slow. Worse, every insert, update and delete still has to keep that useless index up to date. You pay the cost and get nothing back.

To spot it, run d orders in psql. It lists the table's indexes and marks the broken one INVALID. Remove it with DROP INDEX CONCURRENTLY idx_orders_customer_id;, which does not block writes, then run the build again.

One more trap comes from migration tools , the tools that apply your database changes (such as a new index) when you deploy. Many wrap each migration in a transaction, a group of steps that either all succeed or all roll back together. That is usually helpful, but CONCURRENTLY cannot run inside one. It does its work in several separate steps and waits for other running queries between them, so it cannot be rolled back as a single unit. If you try, PostgreSQL stops with an error, and your deploy fails.

The fix is to mark that one migration as "do not use a transaction". Most tools have an option for it: executeInTransaction=false in Flyway, runInTransaction: false in Liquibase, and disable_ddl_transaction! in Rails.

2. Wrong column order. Your query filters only on customer_id. A colleague builds the index as (status, customer_id), because both columns appear in other queries. It looks fine: customer_id is in the index.

But Fix 2 showed that an index works from the left, so it is sorted by status first. Say status has 3 values: NEW, OPEN and DONE. The index then has three groups, and customer 42's orders are scattered, a few in each group. The database has no single place to jump to. It usually ignores the index and scans the whole table, so the page stays slow.

Build it as (customer_id, status) instead. Now all of customer 42's entries sit together. Either way, run EXPLAIN after creating any index, to confirm the database really uses it.

3. An index on a column where most rows share one value. Take a status column where 95% of 10 million orders are DONE (illustrative). Querying status = 'DONE' matches 9.5 million rows. Using the index means 9.5 million lookups, each followed by a read of the table. Reading the table once from start to end is cheaper, so the planner (the part of the database that chooses how to run a query) scans anyway. The index is never used, but it slows every write and uses disk.

The rare value is different. If only 1% of orders are OPEN, an index helps a lot for those. A partial index covers only those rows, so it stays small:

CREATE INDEX idx_orders_open
    ON orders (customer_id) WHERE status = 'OPEN';

The query must include status = 'OPEN', or the database cannot use it.

Indexes like that cost you on every write. To find indexes that queries never use, PostgreSQL keeps a count of how often each one was used:

-- indexes never used since the statistics were last reset
SELECT relname, indexrelname
FROM pg_stat_user_indexes
WHERE idx_scan = 0;

Never drop an index that backs a primary key or unique constraint, even if it shows no uses, because it enforces a rule.

#Which should you use?

Situation	Reach for
Slow lookup by one column	A single-column index on it
Filter on several columns, plus sorting	A multi-column index, equality columns first, sort column last
A function in the `WHERE` clause	Rewrite the condition, or create an expression index
The plan shows a wildly wrong row estimate	Run `ANALYZE` to refresh statistics
A write-heavy table with many indexes	Drop indexes that queries never use

If you are not sure, run EXPLAIN ANALYZE on the slow query first. The plan tells you what to do, and guessing at indexes can slow your writes for nothing.

#The takeaway

Without an index, the database reads every row , so the cost grows with the table. An index lets it jump to the matching rows.
A query that is fast on small data and slow on large data is most often a missing index. A bad plan from stale statistics, or an N+1 pattern, can look the same.
Diagnose with the slow-query log and EXPLAIN ANALYZE : look for a sequential scan and millions of rows removed.
Build indexes to fit the query: equality columns first, sort column last , and write conditions the index can use.
Indexes are not free: they slow writes and use space. Build them with CONCURRENTLY on live tables, and drop the ones nobody uses.

HikariPool-1 Connection is not available: pool exhausted

Prince Singh — Mon, 02 Nov 2026 00:00:00 +0000

#The doubt

The service runs fine all day. Then traffic picks up and every request starts to hang. CPU is low. The database looks nearly idle. There is no crash, just a growing wall of timeouts, and the logs say something like this:

HikariPool-1 - Connection is not available, request timed out after 30000ms.

The database is not busy and the servers are not busy, so what is everyone waiting for?

They are waiting for a database connection , and there are none left to hand out. Picture a rack of rental bikes. When every bike is out, the next customer queues, even though the roads are empty.

In this post

By the end you will know:

Why apps keep a pool of connections, and what "exhausted" means
Three ways a pool runs dry: leaks, hoarding and too many servers
How to diagnose it from pool metrics, a thread dump and the database itself
How to fix it, and why the obvious fix (a bigger pool) often makes it worse

#First, what the pool is for

Opening a connection to a database is slow. It involves a network handshake, often encryption, and a login. Doing that for every request would cost more than the query itself. So an application opens a fixed number of connections at startup and keeps them in a connection pool.

A request borrows a connection, runs its queries, and returns it. Calling close() on a pooled connection does not really close it. It hands the connection back to the pool.

If every connection is borrowed, the next request waits for one to come back. If it waits longer than a timeout, it fails with the error above. The pool is exhausted when the demand for connections is higher than the supply, for long enough that requests start to queue.

The pool size is small on purpose. HikariCP, the usual Java pool, defaults to 10 connections. That is often enough, because a connection is only needed while a query runs.

#Failure 1: a connection leak, and the fix

The simplest cause is a connection that is borrowed and never returned. Look at this code:

Connection c = dataSource.getConnection();
PreparedStatement ps = c.prepareStatement(
        "SELECT * FROM orders WHERE id = ?");
ps.setLong(1, id);
// if this line throws an exception...
ResultSet rs = ps.executeQuery();
// ...use rs...
// ...this line never runs
c.close();

Whenever executeQuery() throws, close() is skipped, and that connection is lost to the pool for good. One error does not matter. But a database hiccup that causes a few hundred errors can drain a pool of 10 in seconds, and the pool never recovers, even after the database is healthy again.

The fix: always return the connection. Let the language do the closing. try-with-resources closes the connection even when an exception is thrown:

try (Connection c = dataSource.getConnection();
     PreparedStatement ps = c.prepareStatement(
             "SELECT * FROM orders WHERE id = ?")) {
    ps.setLong(1, id);
    try (ResultSet rs = ps.executeQuery()) {
        // ...use rs...
    }
}
// c is returned to the pool here, error or not

If you use a framework such as Spring, the framework does this for you, as long as you do not hold a raw connection yourself.

#Failure 2: holding a connection while you wait, and the fix

In Failure 1, connections were lost by mistake. Here nothing is lost: every connection is returned in the end. The problem is that it is held for much longer than needed , and while one request holds it, nobody else can use it.

Think of renting a bike to ride to the shop, and then leaving it parked outside a café for two hours. You did bring it back, but nobody else could ride it in that time.

In code, this usually happens inside a transaction. A transaction is a group of database queries that must all succeed or all fail together. To keep them together, the app uses one connection for the whole group, from the first query until the method ends. Look at this method:

@Transactional
public void placeOrder(Order order) {
    // the transaction takes a connection here...
    orderRepository.save(order);
    // ...and holds it while waiting 2 s on a remote call
    paymentClient.charge(order);
    orderRepository.markPaid(order);
}

Here is what happens, step by step:

save(order) runs a quick query. To do that, the method borrows a connection.
charge(order) calls the payment company over the network and waits 2 seconds. No database work happens now, but the transaction is not finished, so the connection stays borrowed.
markPaid(order) runs another quick query. Only then does the method end and the connection go back to the pool.

The connection did real work for a few milliseconds, but it was held for about 2 seconds. For almost the whole time, it sat idle and nobody else could use it.

Diagram: see it on sudoexplain.dev

Figure 1. The connection sits idle for the whole remote call.

Now see why this drains the pool. It is simple arithmetic:

one connection serves 1 request per 2 seconds
10 connections serve 10 / 2 = 5 requests per second

So a pool of 10 can handle 5 requests per second, and no more. Say 6 requests arrive every second. Every second the pool falls one request further behind. Those requests wait in a queue, the queue only grows, and in the end they time out with the error from the start of this post.

The strange part is that the database looks idle. It is not the busy one. The connections are just waiting for the payment service. A slow query or a lock wait inside the database has the same effect, because it also makes each request hold its connection for longer.

The fix: keep transactions short. Do the slow work outside the transaction, so no connection is held while you wait. Call the payment service first. Then open a short transaction that only writes the results, which takes a few milliseconds:

public void placeOrder(Order order) {
    // no connection is held during the remote call
    PaymentResult result = paymentClient.charge(order);
    // a short @Transactional method
    orderWriter.saveOrderAndPayment(order, result);
}

Two cautions.

1. Put the short method in a separate class. It is tempting to keep it in the same class, like this:

@Service
public class OrderService {
    public void placeOrder(Order order) {
        PaymentResult result = paymentClient.charge(order);
        // called from inside the same class
        saveOrderAndPayment(order, result);
    }

    @Transactional
    public void saveOrderAndPayment(Order o, PaymentResult r) {
        // ...no transaction starts here
    }
}

This looks right, but @Transactional does nothing here, and Spring does not warn you. The reason is how it works. Spring does not change your method. It puts a stand-in object in front of your class. When other code calls your service, the call goes through the stand-in, which opens the transaction, runs your method, and then commits.

A call from inside the same class goes straight to the method. The stand-in is skipped, so no transaction opens. The two saves are no longer all-or-nothing: if the second one fails, the first one stays. The fix is to put saveOrderAndPayment in a separate class, like orderWriter above, so the call comes from outside and goes through the stand-in.

2. Charging first changes what can go wrong. If the save fails after the charge, the customer paid and there is no order. Protect against that with an idempotency key, a unique ID that lets the server recognise and ignore a repeated request, and a way to refund.

The principle: a connection should be held only while the database is actually working. Never across a network call, a file upload or a sleep.

A hidden version of this problem in Spring Boot. Even if you keep your transactions short, Spring Boot can still hold the connection for you. It has a setting called open-in-view, and it is on by default. Its purpose is to make lazy loading work late: it keeps the database connection open for the whole web request, so that even a page template can still load more data.

That means the connection is held from the first database query until the response is sent. It stays held while your code calls other services or builds the page, which is the same problem as before, without you writing any transaction.

To turn it off, set this in your configuration:

spring.jpa.open-in-view=false

Now the connection goes back to the pool when the service method ends. The price is that lazy data can only be loaded inside the service method. So load everything the page needs there, with a fetch join or a projection, as shown in the N+1 query problem post.

#Failure 3: more servers than the database can take, and the fix

Pools exist per server. If each of your servers has a pool of 20, then:

 5 servers x 20 = 100 connections
20 servers x 20 = 400 connections

Databases have a connection limit of their own. PostgreSQL's max_connections is typically 100 by default, for example. Scale out past it and new connections are refused, with errors like FATAL: sorry, too many clients already. The pool then fails to even create connections, and all servers break together.

This is the cruel part: you added servers to cope with load, and that is what took the database down.

The fix: size the pools together. Pick the pool size from the database's limit, not from hope. The total across all servers must stay well under it:

database limit: 100
reserve for admin tools, migrations, monitoring: 20
left for the app: 80

8 servers -> pool of 10 each = 80

Give batch work, such as a nightly report, its own small pool. Otherwise a report that holds 5 connections for an hour can starve the web requests.

A smaller pool is usually faster than a bigger one. HikariCP's pool-sizing guide argues that a small pool often performs best, and it suggests roughly twice the database's CPU core count (plus the number of disks) as a starting point. Treat that as the total across all servers, start small and measure.

If you truly need thousands of connections, use a connection proxy. A connection proxy is a small program that sits between your apps and the database. Your apps connect to the proxy, not to the database. The proxy keeps only a few real database connections and lends them out when they are needed.

Think of a receptionist. A thousand visitors can wait in the lobby, but only 50 fit in the meeting rooms. The receptionist sends each visitor in when a room is free. In the same way, 20 servers with 50 connections each, 1,000 in all, can share 50 real database connections, which fits under a limit of 100.

PgBouncer is a well-known proxy for PostgreSQL. It has pooling modes , which decide how long an app keeps a real connection:

Session mode: the app keeps one for as long as it stays connected. Everything works as normal, but little is shared.
Transaction mode: the app gets one only while a transaction runs, and then it goes to someone else. This shares the most, but the next transaction may run on a different real connection. Anything stored on one connection is lost, such as settings made with SET, temporary tables, LISTEN and session-level locks.

So choose the mode first, and test your app with it. Transaction mode works only if your app does not use the features that it breaks.

#For all three: fail fast with a short timeout

Whatever the cause, set a short connection timeout (a few seconds rather than 30), for example spring.datasource.hikari.connection-timeout=3000. When the pool is exhausted, you want requests to fail fast and free their threads, not queue for half a minute and pile up.

#How to diagnose it

1. Read the pool metrics. Pools expose three numbers: connections active (borrowed), idle (free) and requests pending (waiting). The signature of exhaustion is active equals the maximum, idle is zero, pending is climbing. HikariCP publishes these through Micrometer (a metrics library) as hikaricp.connections.active, hikaricp.connections.idle and hikaricp.connections.pending.

2. Take a thread dump. A thread dump lists what every thread is doing right now. Capture one with jcmd <pid> Thread.print, and take two or three a few seconds apart. If many request threads are sitting inside HikariPool.getConnection, they are all queueing for a connection:

"http-nio-8080-exec-17" waiting on condition
    at com.zaxxer.hikari.pool.HikariPool.getConnection(...)
    at com.example.OrderRepository.save(OrderRepository.java:42)

3. Find who is holding connections. Ask the database what each connection is doing. In PostgreSQL:

SELECT state, count(*), max(now() - state_change) AS longest
FROM pg_stat_activity
GROUP BY state;

Compare this with the pool numbers. If the pool says "all active" but the database shows mostly idle, connections were borrowed and are not being used: a leak, or the app is doing other work while holding them. A large number in idle in transaction means the app opened a transaction and is doing something else with it: Failure 2.

4. Make leaks name themselves. HikariCP can log a stack trace when a connection is held too long. It is off by default, so turn it on in the pool config:

# milliseconds
spring.datasource.hikari.leak-detection-threshold=20000

After that, a connection borrowed for more than 20 seconds produces a warning that shows exactly where it was borrowed. Set the threshold above your slowest legitimate query, or it will warn about connections that are fine.

#When the fix goes wrong

Two pitfalls look like sensible reactions. Both make an outage worse.

1. Raising the pool size. When requests wait for connections, a bigger pool looks like the obvious cure. But more connections do not make the database faster. Past a point they make it slower, because it spends time switching between them. And as Failure 3 showed, a bigger pool on every server can push the total past the database limit.

If the real problem is a leak, a bigger pool only delays the failure. Say a bug leaks one connection per failed request, and 5 requests fail each second. A pool of 10 is gone in 2 seconds. A pool of 200 is gone in 200 / 5 = 40 seconds. The outage is the same, just later.

What to do instead: find out why connections are held, using the pool metrics and a thread dump. Then fix the leak or the long transaction.

2. Retrying on pool timeout. The error says "try again later", so a retry looks natural. But the retry asks the same full pool for a connection. Say the pool has 10 connections, all busy, and 100 requests are waiting. They time out, and each is retried twice more, so 3 attempts each. Up to 3 x 100 = 300 requests now queue for the same 10 connections. The queue gets longer exactly when the system is struggling, and the pool may never catch up.

What to do instead: do not retry on a pool timeout. If a retry is needed, do it at one layer only, with a delay that grows and a limit on attempts.

#Which should you use?

Situation	Reach for
Pool at maximum, but the database is idle	Look for hoarding (slow calls inside transactions) and leaks
Connections in "idle in transaction"	Shorten transactions. Move remote calls outside them
Exhaustion began after an error spike	A leak on the error path. Use `try`-with-resources
Fine at 5 servers, broken at 20	Check the total against the database limit. Consider a proxy
Requests pile up for 30 seconds before failing	A shorter connection timeout, so failures are quick

If you are not sure where to begin, begin with the pool metrics and a thread dump. Together they show whether the problem is waiting, leaking or too many servers in about five minutes.

If the pool drains because queries themselves are slow, look at the slow queries first. A missing index is a common cause, and EXPLAIN shows how the database runs a query. If one page runs hundreds of small queries, each request holds its connection for longer, and the N+1 query problem is the likely cause.

#The takeaway

A pool is a small, fixed set of connections that requests borrow and return.
Exhausted means demand for connections outlasts supply, so requests queue and then time out.
Three causes: a leak (never returned), hoarding (held during slow work) and too many servers for the database limit.
Diagnose with pool metrics (active at max, pending rising), a thread dump, and the connection states in the database.
Fix it by always returning connections, keeping transactions short and sizing all pools under the database limit. A bigger pool is not the fix.

Stale cache and cache stampede: why servers disagree

Prince Singh — Sun, 01 Nov 2026 00:00:00 +0000

#The doubt

Imagine an online shop. You change a product's price from $20 to $25 and save it. You open the product page and see $25. Good.

Then a customer opens the very same page and still sees $20. They refresh. Now it says $25. They refresh again. $20.

Nothing is broken in the usual sense. There is no error, no crash, and the database holds the right price. The site is just giving different answers to the same question, and it seems to change its mind at random.

The price is saved correctly, so why do some people still see the old one?

The answer is a stale cache. It only makes sense after a few basics, so we start there. I won't assume you know any of these words.

In this post

By the end you will know:

What a cache is, and why almost every app uses one
Why a cache that works on one server goes wrong on two
How to diagnose a stale cache with a few requests
Three ways to fix it, and what each one costs
How a fix can fail under real traffic, and how to stop it

#First, the basics

A cache is a saved copy of an answer, kept close by so you can reuse it without the slow trip to where it came from. Think of a notebook on your desk: you look a phone number up in the library once, write it down, and read it from the notebook after that.

For an app, the library is the database, the notebook is the cache, and the cache usually lives in the server's memory:

function getPrice(productId):
    if cache has productId:
        return cache[productId] # fast: no trip to the database
    price = database.readPrice(productId) # slow: the trip
    cache[productId] = price # write it in the notebook
    return price

function setPrice(productId, newPrice):
    database.writePrice(productId, newPrice)
    cache[productId] = newPrice # keep the notebook up to date

Look at setPrice. It updates the database and its own notebook. On a single server that is perfect, because there is one notebook and one writer, so the two can never disagree.

A cached copy is stale when the real data has changed but the copy has not. A stale answer is not an error. It looks completely normal, which is what makes it hard to spot.

#One server has one notebook. Two servers have two.

Real apps rarely run on one server, so you run several copies behind a load balancer , which hands each incoming request to one of them. (New to that? The sessions post explains it from scratch.)

Here is the catch. Each server is a separate running program, so each one has its own memory and therefore its own notebook. They do not share it, and they do not know what is in each other's.

#The problem: two notebooks that disagree

Watch what happens when you change the price with two servers running:

Diagram: see it on sudoexplain.dev

Figure 1. Server A updates its own notebook and the database. Server B never hears about it, so it keeps serving the old price.

The admin saves $25. The load balancer sends that request to Server A. Server A writes $25 to the database and to its own cache.
A customer opens the product page. The load balancer sends this request to Server B. Server B has $20 in its notebook, so it answers $20 without ever asking the database.

Server B did nothing wrong. It followed the code exactly. It just has no way to know that Server A changed anything.

This explains the random flipping: each refresh may land on a different server, and each server tells its own version of the truth. Like the session bug, this one never shows up on your laptop , because on your laptop there is only one notebook.

#See it yourself

You can reproduce it in a few lines of Java. Two "servers" each get their own cache, and they share one "database":

import java.util.HashMap;
import java.util.Map;

public class StaleCache {
    static Map<Integer, Integer> database = new HashMap<>();

    static class Server {
        Map<Integer, Integer> cache = new HashMap<>();

        int getPrice(int id) {
            return cache.computeIfAbsent(id, database::get);
        }

        void setPrice(int id, int price) {
            database.put(id, price);
            cache.put(id, price);
        }
    }

    public static void main(String[] args) {
        database.put(42, 20);
        Server a = new Server();
        Server b = new Server();

        System.out.println(a.getPrice(42)); // 20
        System.out.println(b.getPrice(42)); // 20
        a.setPrice(42, 25);
        System.out.println(a.getPrice(42)); // 25
        System.out.println(b.getPrice(42)); // 20 <- stale
    }
}

Save it as StaleCache.java and run java StaleCache.java (this needs Java 11 or newer). The last line prints 20 even though the database now holds 25.

#How to diagnose it

When users say "I see old data, but only sometimes", suspect this. Here is how to confirm it.

1. Notice the pattern. The data is correct in the database. The wrong answer comes and goes with refreshes. It started after you added a second server, or it disappears when you run just one.

2. Find out which server answers. Have each server add a header to its responses with its own name, for example X-Served-By: server-a. A header is a labelled extra line of information sent along with the page. Then ask for the same page several times. The tool below is curl, a command-line program that makes web requests for you:

for i in 1 2 3 4 5 6; do
  curl -si https://example.com/api/products/42 | grep -iE "x-served-by|price"
done

3. Read the output. A stale cache looks like this:

x-served-by: server-a
{"id":42,"price":25}
x-served-by: server-b
{"id":42,"price":20}
x-served-by: server-a
{"id":42,"price":25}
x-served-by: server-b
{"id":42,"price":20}

(Over HTTP/2 and HTTP/3, header names are sent in lowercase, which is why grep -i ignores the case.)

The same address gives two different answers, and the answer depends on which server replied. Server A always says 25. Server B always says 20. That pattern is the fingerprint of a stale cache in one server's memory.

4. Check the source of truth. That is the one place that holds the real data, here the database. Look at the price there. If it says 25, the data is fine and one of the notebooks is out of date.

Note

If the answers are random and do not line up with the server names, the cause is something else, such as two requests racing each other. The giveaway for a stale cache is that each server is consistently wrong in its own way.

Also check for caches in front of your servers, in the browser or a CDN (a network of servers that keeps copies of pages close to users). Look at the Cache-Control and Age response headers. A response with a large Age came from such a cache, not from your app.

#Fix 1: let copies expire

The simplest fix is to give every cached copy a time to live , usually shortened to TTL. It is a timer that says "trust this copy for 60 seconds, then throw it away and ask the database again."

function getPrice(productId):
    entry = cache[productId]
    if entry exists and now < entry.expiresAt:
        return entry.price
    price = database.readPrice(productId)
    cache[productId] = { price: price, expiresAt: now + 60 seconds }
    return price

(setPrice must store an expiry time in the same way when it writes to the cache.)

Server B's $20 now lives for at most a minute. After that it fetches 25 from the database and the servers agree again.

The cost: for up to one TTL, people can still see the old price. A shorter timer means fresher data but more trips to the database, which is the thing the cache was meant to avoid. Expiry is a good fit when slightly old data is harmless, such as a view count. It is a poor fit for a price someone just corrected.

#Fix 2: check a version number

Instead of guessing how long a copy stays good, ask whether it is still good. Keep a version number next to the data in the database: a counter that goes up by one every time the data changes. Before trusting its notebook, a server compares versions:

current = database.versionOf("product:42") # cheap: one small number

if cache.version != current: # someone changed it elsewhere
    cache.data = database.readProduct(42) # reload the fresh copy
    cache.version = current

Checking one small number is cheap. Reloading the whole product on every request is not. The version number tells you exactly when a reload is needed, so stale answers become much rarer, and you only pay for the full read when something really changed.

The cost: every request still makes one small trip to the database to read the version. You save the heavy work but not the trip. If the trip itself is the problem, you need the next fix.

#Fix 3: tell the other servers (invalidation)

The third idea is to stop waiting for servers to find out, and tell them. When a server changes the data, it announces the change, and every server throws away its old copy. Removing a cached copy because it is no longer true is called invalidation.

function setPrice(productId, newPrice):
    database.writePrice(productId, newPrice)
    announce("product:" + productId + " changed") # every server hears this

on hearing "product:42 changed":
    cache.remove(42) # next read fetches fresh data

The announcement travels over a message channel , which is a shared pipe that servers can send short notes into and listen to. Many systems provide one, so you rarely build it yourself.

There is a close relative of this idea: instead of each server having its own notebook, keep one shared notebook that all servers read and write, in a separate fast store. This is the same move as the shared session store in the sessions post. There is only one copy, so there is nothing to disagree with.

The cost: more moving parts. Messages can be lost if a server is restarting at the wrong moment, and a shared store is one more thing that must stay running. So real systems usually combine this with a TTL as a safety net: even if an announcement is missed, the copy dies on its own within a minute.

#When the fix itself breaks: the cache stampede

Fix 1 has a hidden problem that only shows up under real traffic. Every copy gets the same timer, so popular items expire at the same moment.

Picture the product page everyone is viewing. Its cached copy expires at 12:00:00. In the next second a thousand requests arrive, and every one of them finds the cache empty. Each one asks the database for the same thing at the same time.

The database slows down under the sudden load. Requests start timing out, so nobody manages to refill the cache, and the pile-up grows. This is called a cache stampede : a crowd of requests all rushing to rebuild the same missing copy at once. The cache was meant to protect the database, and at that moment it does the opposite.

How you would notice. The database is calm, then load spikes sharply at the instant popular items expire, and responses get slow for a while. If traffic is steady, charts of database load show regular spikes, spaced one TTL apart.

There are two common fixes, and real systems usually use both.

1. Let only one request rebuild. The first request to find the copy missing takes a lock , which is a marker that lets only one request through at a time. It reloads the data. The others either wait a moment, or are handed the old copy until the new one is ready.

function getPrice(productId):
    entry = cache[productId]
    if entry exists and now < entry.expiresAt:
        return entry.price

    if lock.tryAcquire("price:" + productId): # only one request wins
        price = database.readPrice(productId)
        cache[productId] = { price: price, expiresAt: now + ttl }
        lock.release("price:" + productId)
        return price

    return entry.price if entry exists # others serve the old copy
    # (or wait briefly, then read the cache again)

2. Spread out the expiry times. Instead of exactly 60 seconds, use 60 seconds plus a random few. Copies made at the same moment now die at slightly different moments, so the reloads trickle in instead of arriving as one wave.

ttl = 60 seconds + random(0 to 10 seconds) # "jitter"

The lock must be shared by all servers, for example kept in the shared store. A lock held in one server's memory only protects that server. Deleting a popular copy (Fix 3) causes the same stampede, so the lock helps there too. After a deletion there is no old copy to serve, so the other requests must wait for the rebuild.

The cost of the lock is a little more code, and one more thing that can go wrong, such as a lock never released after a crash, so locks are given their own short expiry. The cost of jitter is nothing at all. That is why the TTL in real systems almost always carries some randomness.

#Which should you use?

Situation	Reach for
Being a little out of date is harmless (counters, trending lists)	Expiry (TTL). Simple, nothing clever to get wrong
The data must be correct, and one small database read per request is fine	Version number. Correct as of the version check, still cheaper than reloading
Many servers, very high traffic, and each trip to the database hurts	Invalidation or a shared cache, plus a TTL as a backstop
You are not sure whether you even need a cache	No cache. Measure first, because a cache you do not need is a bug waiting to happen

If you are not sure, start with a short TTL. It is the easiest to understand and the easiest to remove. Move to versions or invalidation when "a minute out of date" turns out to matter to real users.

Two more things are worth looking up once this makes sense. Two updates arriving at the same moment can leave a wrong value in the cache for good, which is a race condition. And a cache with no size limit slowly fills the server's memory. Both are common in production, and each deserves a post of its own.

#The takeaway

A cache is a saved copy that makes things faster. It is only safe while the copy matches the original.
A cache in one server's memory is private to that server. Add a second server and you have two copies that can disagree.
A stale answer looks completely normal, which is why the bug feels random instead of broken.
Diagnose it by asking the same question many times and noting which server answers. Each server being consistently different is the fingerprint.
Fix it by making copies expire , by checking a version , or by telling every server when data changes. Each trades freshness against speed and complexity.
Fixes have failure modes of their own. When popular copies expire together, a stampede hits the database. Let only one request rebuild, and add random jitter to expiry times.

N+1 query problem in Hibernate and JPA: find it and fix it

Prince Singh — Sun, 01 Nov 2026 00:00:00 +0000

#The doubt

An orders page loads in 50 milliseconds on your machine, where the test database holds ten orders. Months later, in production with five thousand, it takes ten seconds. The database is busy answering a flood of requests. Yet when you look at the slow-query log, which lists queries that took too long, nothing is slow : every query finishes in a millisecond or two.

Every single query is fast, so why is the page slow?

Because there is not one query. There are five thousand and one. It is like making 5,000 separate trips to the shop for one item each, instead of taking a single shopping list.

In this post

By the end you will know:

What the N+1 problem is and how loading related data (eager or lazy) creates it without any visible sign in the code
Why it stays hidden in development and arrives with data growth
How to diagnose it, even though no single query is slow
The fixes (fetch joins, batching, projections) and how each can backfire

#First, what an ORM does

Your data is stored in a database. Your Java code works with Java objects. They are two different shapes, so something has to translate between them.

An ORM (object-relational mapper) is that translator. You ask for an Order in Java. The ORM writes the database query for you, runs it, and hands you back a ready Order object. Without an ORM, you would write the query yourself and copy every value into the object by hand.

The catch is that you never see the queries it writes. Keep that in mind, because this post is about what happens when those hidden queries pile up.

You will see two names for this:

JPA is the written set of rules for how Java and a database are matched up. It is only rules, not code that runs.
Hibernate is the ORM that follows those rules and does the actual work. Spring Boot uses it by default, so most Java projects already have it.

#Then, related data: eager and lazy

An Order belongs to a Customer. When you load an order, should the ORM also load its customer? There are two choices:

Eager: load the customer straight away, together with the order, every time.
Lazy: load only the order. Load the customer later, at the moment your code first asks for it.

Lazy exists to save work. A customer has its own addresses, orders and more, so loading all of that with every order could read a lot of data you never use.

You choose with a setting on the link between the two classes:

@ManyToOne(fetch = FetchType.LAZY)
private Customer customer;

Here is the part that surprises people. If you write nothing, JPA picks eager for this kind of link. You might expect eager to be safe, because the customer arrives with the order. But when you load a list of orders, the ORM sends one query for the orders, and then, to fill in each customer immediately, one more query per order, all inside that same call.

So eager does not help. Lazy does not cause a problem by itself, though. If your code never asks for the customers, lazy sends no extra queries, and it saves work.

The trouble starts when your code does ask, for many orders in a row. A page that lists orders with each customer's name asks for every customer, and each ask sends its own query. Lazy only decides when those queries are sent, not how many. Eager sends the same number, just earlier.

Here is lazy at work. Loading the orders does not load their customers. A customer is fetched only when you call order.getCustomer(). So look at what happens when a page shows the customer's name for every order:

// 1 query: loads all the orders
List<Order> orders = orderRepository.findAll();

for (Order order : orders) {
    // each new customer here sends 1 more query
    System.out.println(order.getCustomer().getName());
}

The loop looks innocent. There is no query in sight. But each call to getCustomer() quietly sends one:

SELECT * FROM orders; -- 1 query
SELECT * FROM customers WHERE id = 7; -- one per customer
SELECT * FROM customers WHERE id = 12;
SELECT * FROM customers WHERE id = 31;
...

Diagram: see it on sudoexplain.dev

Figure 1. One query for the list, then one round trip per row.

That is the N+1 problem : 1 query to get the list, plus N more, one for each customer. If there are 5,000 different customers, the page sends 5,001 queries.

One small detail: if two orders belong to the same customer, the ORM remembers that customer and does not ask the database again. So N is the number of different customers. With thousands of customers, that is still thousands of queries.

#Why it appears only in production

The cost grows with the data, and the code never changes:

Orders on the page	Queries	At about 2 ms each
10 (development)	11	22 ms
500	501	1 second
5,000	5,001	10 seconds

Each query is a network round trip to the database. Even when the database answers in a fraction of a millisecond, the trip costs more. Thousands of tiny trips add up to a slow page.

A request that runs thousands of queries also holds its database connection for longer. Under load, that can use up the connection pool, as explained in connection pool exhausted.

This is why the bug ships dormant. In development, the database often runs on the same machine, so trips cost almost nothing, and a handful of rows is fast. It only wakes up when real data arrives.

#How to diagnose it

The usual tool, the slow-query log, finds nothing, because no individual query is slow. Instead, look at how many queries one request makes.

1. Print the SQL. Turn on SQL logging for one request, for example logging.level.org.hibernate.SQL=DEBUG in Spring Boot. Load the page. If you see the same statement repeated with only the ID changing, that is the pattern:

select ... from customers where id=?
select ... from customers where id=?
select ... from customers where id=?
... (hundreds more)

2. Count with Hibernate statistics. Setting spring.jpa.properties.hibernate.generate_statistics=true makes Hibernate collect statement counts, which you can read from its Statistics object or see in the session metrics log. A page that runs thousands of statements is your suspect.

3. Ask the database how often each query ran. The database can keep a tally of every query it receives. In PostgreSQL this is done by an extension called pg_stat_statements. It treats queries that differ only in their values as one query, writing the value as a placeholder like $1. So 5,000 lookups of different customers show up as a single line with a count of 5,000.

It is switched off by default. To turn it on:

Add pg_stat_statements to shared_preload_libraries in the PostgreSQL settings, then restart PostgreSQL.
Run CREATE EXTENSION pg_stat_statements; once in your database.

Then reset the tally, load the slow page once, and list the queries by how many times they ran:

SELECT pg_stat_statements_reset(); -- start counting from zero

-- now load the page once, then run:
SELECT calls, query
FROM pg_stat_statements
ORDER BY calls DESC
LIMIT 5;

The reset may need extra privileges. If you cannot run it, load the page twice and compare the counts. The result looks like this (illustrative):

 calls | query
-------+-----------------------------------------
  5000 | select ... from customers where id = $1
     1 | select ... from orders

One page load ran one query for the orders and 5,000 for the customers. That huge count on a cheap query is the N+1 problem.

4. Look at an APM trace. APM means application performance monitoring, a tool that records what each request did. Examples are Datadog, New Relic and Elastic APM, or OpenTelemetry with the free Jaeger viewer. They show one request as a tree of spans , the timed steps inside it. Open the trace of the slow page. A request with hundreds of identical, tiny database spans is a textbook N+1.

#Fix 1: fetch everything in one query

Tell the ORM to load the orders and their customers together, in one query, instead of asking for each customer later. This is called a fetch join :

@Query("select o from Order o left join fetch o.customer")
List<Order> findAllWithCustomer();

This sends one query that returns every order with its customer already attached. Looping over the orders now sends no more queries. In Spring Data you can get the same effect with @EntityGraph(attributePaths = "customer"). Five thousand and one queries become one.

#Fix 2: fetch in batches

When you cannot join, tell the ORM to load related rows in groups. Instead of one query per customer, it asks for many at a time:

spring.jpa.properties.hibernate.default_batch_fetch_size=50

SELECT * FROM customers WHERE id IN (7, 12, 31, ... 50 ids);

Five thousand queries become roughly a hundred. It is not as good as one, but it is a one-line change, and it applies to every association without rewriting queries. Hibernate also has a @BatchSize annotation for finer control.

#Fix 3: load only what the page needs

Often the page does not need the whole Customer. It only shows the order number and the customer's name. So ask the database for just those two values. This is called a projection.

First, a small interface that lists the two values you want:

public interface OrderSummary {
    Long getOrderId();
    String getCustomerName();
}

Then a query that returns only those two values:

@Query("select o.id as orderId, c.name as customerName "
     + "from Order o left join o.customer c")
List<OrderSummary> findSummaries();

This sends one query, and the database returns only two columns instead of full order and customer rows. It is usually the fastest option, because it moves the least data.

#When the fix goes wrong

Each fix can create a new problem if you use it in the wrong place. These are the common ones.

1. Joining two lists in one query (Fix 1). A fetch join is safe when each order has one related object, such as one customer. But an order can also have lists: several items, and several payments. Joining both lists in one query goes wrong.

The database can only answer with a flat table, so it pairs every item with every payment. Take 2 orders, where each order has 3 items and 4 payments (illustrative). These are the rows for order 1:

order | item | payment
------+------+--------
  1 | pen | card-A
  1 | pen | card-B
  1 | pen | card-C
  1 | pen | card-D
  1 | book | card-A
  1 | book | card-B
  1 | book | card-C
  1 | book | card-D
  1 | lamp | card-A
  1 | lamp | card-B
  1 | lamp | card-C
  1 | lamp | card-D

Order 2 has the same shape, so it adds 12 more rows. That is 3 × 4 = 12 rows for each order, 24 rows in all. But the real data is only 6 items and 8 payments, 14 pieces in total. Most rows are repeats: "pen" appears four times, once with each payment.

It gets much worse as the lists grow. With 10 items and 10 payments per order, each order gives 10 × 10 = 100 rows, so 100 orders become 10,000 rows. Hibernate has to sort them all back into orders, and the page becomes slow. Often Hibernate refuses and throws MultipleBagFetchException instead.

What to do instead. Split the work into separate queries, so the two lists never meet. It takes three steps:

Join only the items. Load the orders together with their items, in one query. Each row is now just one item of one order, so 100 orders with 10 items each give 100 × 10 = 1,000 rows, not 10,000.
Leave the payments out of that query. The page still needs them, so they stay lazy: Hibernate loads an order's payments when your code first reads them. If each order did that on its own, you would be back to the N+1 problem, with 100 more queries.
Switch on batch fetching (Fix 2). Now, when the code reads the payments of one order, Hibernate loads the payments of 50 orders in a single query. 100 orders need only 2 of these queries. The batch size counts orders, not rows, so 50 orders with 10 payments each means 500 rows per query.

For our 2 orders, the items query returns 6 rows, and the payments query returns 8 rows (both orders fit in one batch):

items query payments query
order | item order | payment
------+------ ------+--------
  1 | pen 1 | card-A
  1 | book 1 | card-B
  1 | lamp 1 | card-C
  2 | mug 1 | card-D
  2 | cap 2 | card-E
  2 | bag 2 | card-F
                           2 | card-G
                           2 | card-H

That is 2 queries and 6 + 8 = 14 rows, with no repeats, instead of 24 rows before. The gap grows quickly with size. With 100 orders, each with 10 items and 10 payments:

Query 1, the items: 100 orders × 10 items = 1,000 rows.
Queries 2 and 3, the payments: 50 orders × 10 payments = 500 rows each, 1,000 in total.

That is 3 queries and about 2,000 rows, instead of 10,000 rows in one query.

In short: a join saves queries but can multiply rows, and batching keeps the rows low but sends more queries. Joining one list and batching the other keeps both numbers small.

2. Joining a list and paging (Fix 1). Paging means showing a long list a little at a time, for example 20 orders per page. Normally the database does this for you: you ask for "the first 20 rows" and it stops after 20.

That works when there is one row per order. But when you join a list, such as the items, there is one row per item. Say each order has 3 items. The first 20 rows are then only about 7 orders, and the last of them is cut in half, with some of its items missing.

The database cannot know how many rows make 20 whole orders, so Hibernate does not ask it to stop. It loads every order with all of its items into memory, and then picks out 20 orders in Java. With 100,000 orders, that is hundreds of thousands of rows in memory just to show one page. Hibernate writes a warning in the log when it does this.

Instead, do it in two steps:

Run one query for just the 20 orders on this page, with nothing joined.
Run a second query that loads the items of those 20 orders.

Now the database only ever returns what the page needs.

3. Making everything EAGER. It looks like an easy shortcut: always load the customer together with the order, so nobody has to think about it. But as shown earlier, eager does not remove the extra queries. Loading a list of orders still sends one query per customer.

The new problem is that eager applies everywhere. Say one page lists 5,000 orders and shows only the order number and the total. It never uses the customer.

With a lazy link, that page sends 1 query.
With an eager link, it sends up to 5,001 queries, to load customers that nobody looks at.

The same happens in every report, search and export that loads orders. So one setting can make pages slower that were fine before. Instead, keep the links lazy, and let each page fetch what it needs with Fix 1, 2 or 3.

4. Silencing LazyInitializationException. Remember that lazy data is fetched later, when your code first touches it. To fetch it, Hibernate needs its session : the open connection between your code and the database. In many applications the session closes when the method that loaded the data finishes.

If your code touches lazy data after that, there is no session left to ask the database, and Hibernate throws this error:

// the session closes when findAll() returns
List<Order> orders = orderService.findAll();

// error: there is no session left to load the customer
orders.get(0).getCustomer().getName();

The error message ends with "could not initialize proxy - no Session". It is annoying, so people silence it in one of two ways:

Switch the link to EAGER. This brings back problem 3.
Keep the session open for the whole request. Spring Boot's "open session in view" setting does this, and it is on by default in web applications. Lazy loading then works anywhere, even in a controller or a page template, so a loop can quietly send hundreds of queries again.

Both hide the error and keep the N+1 problem. The error is actually telling you something useful: you are asking for data you did not load. Instead, fetch what you need before the method finishes, with Fix 1, 2 or 3.

5. A huge batch size (Fix 2). The batch size is how many IDs go into one query. With a batch size of 50, loading 100 customers takes 2 queries, each ending in WHERE id IN (...50 IDs...). That is fine.

It is tempting to think a bigger number is always better, because it means fewer queries. Say you set 5,000 and then load 5,000 customers. You get one query with a list of 5,000 IDs in it. That has problems of its own:

The query text is huge, and the database takes longer to read and plan it.
Some databases refuse it. For example, Oracle allows at most 1,000 values in one IN list.
If a page needs only 3 customers, a very large batch size gives you no extra benefit, only a risk.

Start with a modest number, in the tens, then measure the page with your real data and adjust.

#Which should you use?

Situation	Reach for
One page needs a related object for every row	Fetch join or `@EntityGraph`
Many associations, rarely all needed	Batch fetching (the global default size)
A list or report that shows a few columns	A projection with only those columns
Related data is a collection and you also need paging	Two queries: page the parent, then fetch the children by ID
A loop calls `findById` for each row	`findAllById` once with all the IDs

N+1 also hides in collections, such as order.getItems() inside a loop, and in hand-written loops that call a repository once per row. The fixes above apply to both.

If you are not sure, turn on SQL logging for your three most visited pages and count. You will probably find one. If one query really is slow, that is a different problem, such as a missing index, and EXPLAIN shows how the database runs it.

#The takeaway

The ORM fetches related data one row at a time, either on first use ( lazy ) or straight away ( eager ), so a list can quietly send one query per row: the N+1 problem.
It stays hidden because small data is fast. The cost grows with row count, and the code never changes.
No single query is slow, so the slow-query log misses it. Count queries per request instead.
Fix it with a fetch join , batch fetching or a projection , depending on what the page needs.

Sessions, JWTs and load balancers explained

Prince Singh — Sat, 31 Oct 2026 00:00:00 +0000

#The doubt

Picture a website where you log in. You click "My orders", and it greets you by name. The site remembers who you are. That memory is called a session.

Now picture a busy website that is not run by one computer, but by three identical ones. A gatekeeper stands in front of them and sends each click to whichever computer is free. That gatekeeper is called a load balancer.

Here is the puzzle:

You log in. Your click goes to Computer 1 , which remembers you.
You click "My orders". The gatekeeper sends this one to Computer 2.
Computer 2 was never told about your login. To it, you are a stranger.

If one computer remembers me, how does the next one know who I am?

That was my doubt. Big websites with millions of users clearly solve it, so the answer must exist. It only makes sense after a few basics, so we start there. I won't assume you know any of these words.

In this post

By the end you will know:

What a session is, and what a JWT is
What a load balancer does, and why it breaks naive sessions
How to diagnose the problem when it happens to you
The ways to fix it, and how a JWT fits in

#First, the problem HTTP leaves us with

Your browser talks to a website using HTTP, the set of rules browsers and servers use to exchange pages and data. Each time your browser asks the server for something (a page, an image, your list of orders), that is one request.

HTTP has no memory. Every request arrives as a stranger. The server answers it and forgets it.

Request 1: POST /login (username + password) -> "Welcome, Prince"
Request 2: GET /orders -> "Who are you?"

The server did not forget on purpose. Nothing in request 2 says it came from the person who logged in a moment ago. So after login, the browser has to prove who it is on every single request , without typing the password again. A session and a JWT are two different ways to do that.

#Cookies in 30 seconds

A session ID usually rides on a cookie , and a JWT often does too, though it is also commonly sent in an Authorization header. A cookie is a small piece of text that a website asks your browser to store. After that, the browser attaches it automatically to every request to that website.

Response: Set-Cookie: sid=9f3c2a...; HttpOnly; Secure; SameSite=Lax
Requests: Cookie: sid=9f3c2a...

Those extra words are safety switches. HttpOnly hides the cookie from the page's own JavaScript. Secure sends it only over HTTPS (the encrypted, padlock version of HTTP). SameSite limits when other websites can trigger it. Remember that the browser does the carrying. The only question is what you put inside the cookie.

#See it yourself

You can look at a real one in two minutes. Open a website you are logged into, where logging back in is easy, and press F12 to open the browser's developer tools. Choose the Application tab (called Storage in Firefox), then Cookies , then your site.

Look for a cookie with a long, random-looking value. It is often named something like session, sid or JSESSIONID. That value is the ticket number. Now delete that cookie and refresh the page. You are logged out, because the server can no longer match a ticket to a record. If nothing happens, the site keeps its login in a different cookie, so try another one.

Warning

A real session cookie or token works like a password for your account. Never paste one into an online tool, a chat or an email.

#What is a session?

A session is a record the server keeps about you. After you log in, the server:

Creates a record such as "this is user 42, logged in at 10:00"
Gives that record a long random ID, for example 9f3c2a...
Sends the ID to your browser in a cookie

The cookie holds only the ticket number. Think of a coat check: you hand over your coat and get a numbered ticket. The ticket is meaningless on its own. The coat stays in the cloakroom. On every later request the server takes the ticket, looks up the record, and knows who you are.

Here is the idea in pseudocode:

sessions = an empty table, kept in THIS server's memory

on POST /login:
    id = a random ticket number
    sessions[id] = { userId: 42 }
    send the browser a cookie: sid = id

on GET /orders:
    session = sessions[the sid cookie]
    if there is no such session:
        redirect to /login # unknown ticket: who are you?
    show the orders of session.userId

Notice where the record lives: in a table inside that one server's memory. Keep that in mind, because it is the root of the whole problem.

You may already have used this without noticing. In a Java web app, the servlet container (the program that runs it, such as Tomcat) does exactly this for you: calling request.getSession() creates the record and sends a cookie called JSESSIONID. By default, the table lives in that one server's memory.

A session has a great property: the server is in control. To log someone out, delete their record. To change their permissions, edit it. The next request sees the change.

#What is a JWT?

JWT stands for JSON Web Token (JSON is just a plain-text way of writing labelled values, as in the box below). It takes the opposite approach: the server keeps nothing , and the browser carries the facts about you , sealed so they cannot be changed.

Think of a festival wristband with your ticket details printed on it and a tamper-proof seal. Any gate guard can read it and check the seal. Nobody needs to phone head office.

A JWT is three chunks of text joined by dots: header.payload.signature.

The header says how it was signed (not the same thing as an HTTP header, which comes later)
The payload holds the facts (called claims), such as who you are and when the token expires
The signature is the seal, made with a secret key (a private key, or in simpler setups a shared secret) that only the login service holds

{
  "sub": "42",
  "role": "admin",
  "iat": 1793490000,
  "exp": 1793493600
}

Here sub is the subject (the user's ID), iat is when the token was issued and exp is when it expires, both written as seconds since 1970.

When the token comes back, the server checks the seal. If it matches, the facts are trusted. If someone edited even one letter, the seal no longer matches and the token is rejected.

# At login (once)
facts = { sub: "42", role: "admin", expires: one hour from now }
token = sign(facts, privateKey)

# On every request: fails if tampered with or expired
facts = verify(token, publicKey)

There is no lookup and no table. Just a calculation.

#How can a seal be checked but not forged?

This is the part that confuses most people, so here is an analogy. Think of a postcard with an official stamp on it.

Anyone who picks up the postcard can read it. Nothing is hidden.
Only the post office owns the stamp. Nobody else can make a genuine one.
The stamp is made from the message itself. Change one word, and the old stamp no longer fits, so the postcard is exposed as fake.

A JWT works the same way, and the two keys are the stamp and the stamp-checker:

Key	Postcard analogy	What it can do	Who holds it
Private key	The stamp itself	Create a seal	Only the login service. Keep it secret
Public key	A checking device	Check a seal, but never make one	Anyone. It is safe to hand out

So the login service stamps tokens with its private key. Every other server holds the public key and can confirm "yes, a genuine stamp, and the words have not changed". Those servers can verify logins, but they cannot invent one.

(Some systems use one shared secret for both jobs instead of a pair. Then every server that can check a token can also forge one. The idea is the same, just less safe to spread around.)

#One thing that surprises people

A typical JWT is signed, not encrypted. (Encrypted variants exist but are rare.) Those two words sound alike but mean different things:

Encrypted means scrambled so that only the right key holder can read it, like a locked box.
Signed means readable by everyone, but protected against changes, like the stamped postcard.

A JWT is the postcard, not the locked box. Its chunks are written in Base64, which is only a way of spelling any data with ordinary letters and digits. It is not a secret code. Anyone holding the token can read it. In Java, three lines are enough to open one up, no key needed:

String payload = token.split("\\.")[1];
byte[] bytes = Base64.getUrlDecoder().decode(payload);
System.out.println(new String(bytes, StandardCharsets.UTF_8));
// prints: {"sub":"42","role":"admin",...}

The seal stops people changing the facts. It does not stop people reading them.

Warning

Never put passwords or sensitive data in a JWT payload. Anyone who gets the token can read it.

#Stateful and stateless, in plain words

You will see these two words everywhere, so let us pin them down.

Stateful means someone has to remember things between requests. A session is stateful: the server remembers you.
Stateless means nothing is remembered between requests. Each request carries everything needed to handle it. A JWT is the stateless style: the token carries the facts.

Two misconceptions are worth clearing up now:

"JWT is the modern replacement and sessions are old." They are two answers to the same question: where does the memory live? Neither is free.
"A stateful app cannot be spread over many servers." It can, and this post shows how. The trick is to separate where the state lives from which server answers the request.

#One server is easy. Then you add a second.

Everything above works perfectly with a single server. Real apps rarely stay that way, because one server means:

limited capacity, and it falls over under heavy load
no backup if it crashes
downtime every time you release a new version, because the app has to restart

So you run several copies of the app and put a load balancer in front of them. A load balancer is a traffic cop: it receives every request from users and hands each one to one of the servers, spreading the work out. Users see one website, but several servers do the work behind it.

#The problem: a session kept in one server's memory

Now combine the two ideas. Our session code kept its table in one server's memory. Watch what happens with two servers:

Diagram: see it on sudoexplain.dev

Figure 1. The login lands on Server A, but the next request lands on Server B, which has never heard of it.

You log in. The load balancer sends the request to Server A. Server A creates session S1 in its own memory and gives your browser the cookie sid=S1.
You click a link. The load balancer sends this request to Server B , which is just as likely. Server B receives sid=S1, looks in its own memory, finds nothing, and says: "Who are you? Please log in."

Server B did nothing wrong. It simply never saw your login. The result for users is baffling: they are logged out at random, or have to log in again every few clicks.

This bug is cruel for one more reason: it never shows up on your laptop , because on your laptop there is only one server.

#How to diagnose it

When users report random logouts only in production, suspect this. Here is how to confirm it step by step.

1. Notice the pattern. Logins that work, then vanish for no reason? Does it happen more as you add servers? Does it go away if you run only one server? Those are strong signs.

2. Find out which server answers. Configure each server to add a header to its responses with its own name, for example X-Served-By: server-a. A header is a labelled extra line of information sent along with the page. Then log in once, save the cookie, and call a protected page several times. The tool below is curl, a command-line program that makes web requests for you:

# Log in once and save the cookie
curl -s -c jar.txt -d 'user=demo&pass=demo' \
  https://example.com/login -o /dev/null

# Call a protected page six times and show who answered
for i in 1 2 3 4 5 6; do
  curl -s -b jar.txt -D - -o /dev/null https://example.com/orders \
    | grep -iE "^HTTP/|^x-served-by"
done

If sessions are stuck in one server's memory, you will see something like this:

HTTP/2 200
x-served-by: server-a
HTTP/2 302
x-served-by: server-b
HTTP/2 200
x-served-by: server-a
HTTP/2 302
x-served-by: server-b

A 200 (success) comes from the server that created the session, and a 302 (a redirect to the login page) comes from the other. That settles it.

3. Rule out the lookalikes. Make sure the cookie is actually being sent (check the browser's developer tools, Application tab), that it has not expired, and that the cookie's domain and Secure settings match your site. Those cause random logouts too, but they would break on one server as well.

#Fix 1: sticky sessions

The cheapest fix: tell the load balancer to always send the same user to the same server. This is called a sticky session. Once you land on Server A, you stay on Server A.

It works, but it has real weaknesses:

If Server A crashes or restarts, everyone stuck to it is logged out
Deploying a new version restarts the servers, so users on a restarted server can lose their sessions. Some servers keep them across a clean restart only if you configure it, and a crash loses them either way
The load can become uneven, because some users keep a server busy while others do not

Sticky sessions are a bandage. They are fine for a small setup, and not much more.

#Fix 2: a shared session store

The robust fix: take the table out of the server. Keep sessions in one shared place that every server can read. A database works. So does a fast in-memory store like Redis, which is software built to hold small pieces of data in memory and share them over a network. Distributed caches such as Infinispan or Hazelcast do the same job. You do not need to know these yet. They are just examples. (Keeping copies of data in fast stores has its own failure mode: copies go out of date, as explained in stale cache and cache stampede.)

Diagram: see it on sudoexplain.dev

Figure 2. With a shared session store, either server can answer any request.

The change is tiny. The only thing that moves is where the table lives:

sessions = the shared store # was: a table in this server's memory

on login (any server):
    sessions.save(id, { userId: 42 }, expires in 30 minutes)

on a later request (any server):
    session = sessions.load(id)

Now Server A creates the session, Server B finds it, and everything works. Any server can answer any request, so the servers are interchangeable. You can add, remove or restart them freely.

This answers the question we started with. The application is still stateful, because it remembers you. But the state lives in the shared store, not in the web servers. The servers are effectively stateless, and that is what lets a load balancer spread requests around freely. Stateful system, stateless servers.

Two details matter in practice:

Expiry must be shared too. Store a "last active" timestamp in the shared record, and treat a session as expired when now - lastActive > timeout. If each server tracked time on its own, one server could log you out while another still thinks you are logged in.
There is a cost. Every request now makes a trip to the store, and the store has to be reliable, because if it goes down, nobody can be recognised. That is why these stores usually run as a group of machines holding copies of each other's data, so one failure does not lose everything.

A third option exists, session replication, where servers copy sessions to each other. It works for small clusters, but it gets heavy as you grow, which is why a shared store is more common.

#Fix 3: stop storing sessions on the server at all (JWT)

There is a completely different way to escape the problem: if the server keeps no session, there is nothing to share. That is exactly what a JWT does.

With a JWT, the login hands the browser a signed token carrying the facts. On every request, any server can check the signature using the key, and trust the facts. No shared table, no lookup, no request to a store.

You log in. The token is created and given to your browser.
Your next request goes to Server B. It checks the signature. It matches. You are in.
The request after that goes to Server A. Same check. Same result.

No server needed to have seen your login. This is why tokens are popular when many servers, or many separate services, must all trust the same login. Remember the key pair from earlier: services only need the public key to check tokens, so they can verify logins without being able to create fake ones.

#The catch

A JWT is valid until it expires, and the server is not looking anything up, so it cannot say "actually, this one is no good any more." That hurts when:

a user logs out
an account is disabled
a role is removed
a token is stolen

The usual answer is to make tokens short-lived (5 to 15 minutes) and pair them with a refresh token. When the short token expires, the client trades the refresh token for a new one. Usually the refresh token is checked against a server-side record , so it can be revoked. You have rebuilt a small session, consulted every few minutes instead of every request.

Two more cautions. If you keep the token where the page's own JavaScript can read it (such as localStorage), a script-injection bug can steal it. An HttpOnly cookie stops scripts from reading it, though it then needs protection against forged requests from other sites (SameSite helps). And on every request the server must check the signature, the expiry and the algorithm it expects. Never accept a token that names its own algorithm as "none".

So "stateless" never meant the state vanished. The honest version is: JWTs keep state out of the hot path, and in exchange you get weaker, slower revocation.

#Which should you use?

Situation	Reach for
One web app, one backend, users log in through a browser	Sessions. Simple, easy to revoke, nothing clever to get wrong
Several servers behind a load balancer	Sessions in a shared store , or tokens. Avoid sessions in one server's memory (sticky sessions are a stopgap)
Several services that must trust the same login	Tokens (JWT). Each service verifies locally
Mobile apps or third-party API clients	Tokens. Cookies are awkward to share across apps and third-party clients
You must be able to kick a user out right now	Sessions , or very short tokens with a revocable refresh token

If you are not sure, start with sessions in a shared store. Move to tokens when you actually feel the pain of sharing login state across services, not because a blog post said tokens are modern.

#The takeaway

HTTP forgets you, so every request needs proof of who you are.
A session keeps the memory on the server. A JWT puts the memory in a signed token.
A session kept in one server's memory breaks the moment a load balancer sends you to a different server.
Fix it by moving the state out of the servers (a shared store) or out of the server side entirely (a JWT), rather than relying on pinning users to machines.
"Stateless" does not mean the state disappeared. It means you chose where to keep it, and the moment you need to revoke something, it comes back.