The cover illustrates a fictional store, not a deployed system or real test result.
A customer searches for one part number, yet the system reads nearly the entire product list each time. If that happened at fictional Ready Parts, I would inspect the question sent to the database before adding read replicas. Copying wasteful work may buy time, but leaves its cost and limits in place.
A database must answer both speed and correctness questions. A briefly stale product description is different from stale stock availability when confirming an order. This episode uses PostgreSQL as an example, not a requirement for every site or a promise that its example settings transfer directly to another system.
Start with the real query and realistically sized data
Find which request calls which query, how often it runs, how much data it reads or returns, and where the time goes. A long wait may be reading, computation, a lock, a connection, or transferring the result. Do not lump them together and conclude that database CPU is insufficient.
An N+1 problem occurs when an app reads a main list, then asks for each item’s details separately. One page becomes many queries. Combining reads or selecting only needed data can help, but the result must still respect the same access rights. A combined query that crosses tenant or customer-account boundaries is not an acceptable fix.
A tiny test dataset, or one distributed differently from real data, may produce a different plan. A product search returning a few items differs from one spanning the catalog. Use confidentiality-safe data that reflects actual size, distribution, and query types, including periods with writes and other regular jobs.
Read the execution plan before indexing every column
EXPLAIN shows the database’s chosen plan: how it scans tables, joins them, and estimates rows. PostgreSQL’s EXPLAIN ANALYZE actually executes the statement to collect runtime information. Do not run a write statement against production thinking it only displays a plan. Choose an appropriate environment and safeguards first.
An index: it can help some queries find needed rows without reading them all, but it costs storage, creation, and updates on writes; column order, filters, sorting, and data distribution matter. Having an index does not mean every query uses it or runs faster.
If the store searches by product code and publication status, inspect that query’s plan, including its actual filters and sorting. Do not copy an index merely from column names. Extra indexes can slow receiving stock and editing prices, consume space, and add maintenance. Measure reads and writes together.
Query pattern and calls per page
Representative data, rows read, and wait time
Speed with access rights and correctness
Read less by matching what users actually need
Return only needed columns, limit rows, and design pagination for the data pattern. Skipping far into results with a high offset can cost more under some plans. Keyset pagination, continuing after the previous item, is another option, but requires a stable order and a stated behavior when data changes.
Pagination should not create an expensive exact total-count query on every page. If an exact total is not needed for the decision, change the display to fit the need and be clear about what users get. Quietly removing a required complete count from an accounting report is not an acceptable shortcut.
Maintain statistics and plan database upkeep for the engine in use. The planner depends on estimates and actual table conditions. As data grows or its distribution changes, an old plan may stop fitting. Monitor it rather than assuming an index ends the job forever.
A pool controls entry; it is not new processing capacity
A connection pool shares connections within bounds. It can reduce opening and closing overhead and cap simultaneous entrants. But accepting every request into an endless wait merely moves the queue to the front. Users still wait. Set a maximum wait, cancel work that no longer helps, and return an understandable outcome.
Include every app instance, background worker, reporting tool, and deployment overlap in the pool budget. The previous episode’s hypothetical limit of 20 connections per instance was not a recommendation. The right setting depends on concurrency, query cost, and database limits. A separate pooler can help some systems, but its session and transaction compatibility must be checked.
If a browser cancels a request while the query keeps running unnecessarily, resources may not return as quickly as expected. Align timeouts across layers. Distinguish statements safe to stop from transactions whose result must be checked before retrying, especially orders where another click could create a duplicate.
A read replica can add read capacity without instantly providing every new answer
A replica: a copy receiving data through the database’s replication mechanism, can move some reads away from the writer. But an asynchronously updated copy may lag. After a price edit or order write, customers may see the old value if reads go to that copy without checking the freshness requirement.
Separate queries that can tolerate stale data from those that must see their own writes. Order confirmation and immediate status checks may need to read from a source with the required freshness, or use a mechanism that proves the update has arrived. The choice depends on the database and consistency the task requires, not a rule to route every read to a replica.
Record transactions under the rules
Data takes time to travel and become usable
May still see the pre-edit value, depending on replication
A replica is not a complete recovery plan. Consider corruption that is replicated, how to choose a destination if the primary fails, acceptable data loss, and time to restore service. Backups and restore tests still have their own job. A successful backup report does not prove the data can be restored and used.
| Task | Risk from stale data | What to specify |
|---|---|---|
| Open product description | Briefly see pre-edit text | Acceptable age and update method |
| View a just-placed order | Think it failed and place it again | Read-your-writes behavior and status check |
| Reserve the last unit | Accept more orders than allocable stock | Transaction and write-contention control under business rules |
| Historical report | Totals differ from the referenced period | Data cutoff, completeness, and reconciliation |
Partition data only when you know which contention it will reduce
Partitioning or sharding can help work divided along a suitable boundary, but introduces cross-part queries, transactions, migration, and hotspots. If one product is reserved most often, sharding by product code may still leave it as a single point everyone waits on. More partitions do not necessarily divide work requiring one shared decision.
Before choosing that route, I would ask the team to explain the distribution key and show the worst case, not only an average. A store-based key may work when stores are similarly sized, but the plan must also handle a very large store, data migration, and access rights across partitions.
Sometimes a smaller move is to keep heavy reporting off the order path or prepare read data for specific uses while stating the resulting delay. This still requires data transfer and discrepancy handling, but lets the team solve a measured problem before taking on a whole distributed database.
Check the result through work the customer needs finished
After changing a query or index, compare plans, waits, rows read, and successful transaction times under similar load. Check whether writes slowed, including at concurrent peaks. Speeding one query while creating a long lock for orders should not count as an overall success.
Checking stock before writing still needs contention control
Suppose one unit remains. Two customers read at the same time and each sees one. Separate application checks before writes could make both think they can buy it. Use a transaction and a database-appropriate way to control competing writes, such as a conditional update or scoped lock, with handling for waits and conflicts. A fast read does not prove correct confirmation.
A transaction should cover the work that must succeed together, but no more. Leaving it open while waiting for email or an outside API can hold a lock too long. Later work needs a durable status and handoff. Do not drop completeness requirements merely to make the query timer shorter.
Another distinction matters: partitioning within one database can prune data that need not be read and support age-based upkeep, but does not automatically move computation across machines. Sharding across machines adds coordination and migration. If we cannot name the queries that benefit, extra partitions may add planning work without fixing the bottleneck customers see.
Helps measured query patterns
Space, writes, statistics, and upkeep
My order of work is to make queries read only what is needed, bound simultaneous access, then separate reads or data for measured reasons. At every step, state how fresh the answer must be and what happens when a component fails before calling the database “scaled.”
Once the database is no longer the waiting room for everything, document delivery or slow outside calls that need not finish before the user gets a response can move to background work. The next episode uses a queue for that work and asks who owns it after acceptance, rather than sending a quick response and losing the job behind the scenes.
Read the sources and limits of the example
The following sources explain the concepts and limitations and were checked on October 18, 2026. The fictional store numbers and choices are my synthesis, not provider prices, test results, or guarantees. Check the current version, plan, and terms before applying them.