A shipment booking is a request to arrange the handling or transport of goods. An operator opens the booking list to decide which shipment needs attention. A slow list delays that decision. A fast list is no improvement if it shows another customer’s bookings or an outdated shipment status.
An API, short for application programming interface, is how the screen asks the server—the computer handling its requests—for information. Here, the screen requests a page of bookings and the server reads them from a database. This article walks through a proposed design using Node.js for the server and PostgreSQL for the database. It is an example, not a measured production result.
Check whose bookings the reader may see
A tenant is a customer account whose data must stay separate from other customers’ data. The signed-in account determines that boundary. A customer identifier sent by the browser cannot replace it.
Before reading the database or any saved copy of data, check that the account may read bookings. Then return only the fields this list needs: booking reference, creation time, and current status.
Let the screen request between 1 and 100 bookings at a time, using a whole number. Pagination means splitting a long list into these smaller pages. A continuation token is a small piece of data telling the server where the previous page ended. Reject malformed tokens, tokens for another customer, and tokens that do not match the current search filters. Do not silently start the list again.
A token marks a place in the list; it does not grant access. Check permission again on every request, even if the operator already opened the list. Their permissions may have changed since then.
Find out where the request spends time
Read the four steps from left to right
- Left — permission and waiting: the first two boxes separate checking access from waiting for an available database connection. Red arrows show the order of work, not measured time.
- Right — finding bookings and preparing the answer: the next boxes separate database work from converting the bookings into data the screen can receive. The bracket underneath covers the whole server request, not the network trip or the browser’s work.
- Takeaway: time each part and the whole request before choosing what to change.
A server usually shares a limited set of database connections, called a connection pool. A request may wait for one to become free. Even a quick database lookup can feel slow if this queue is full. Starting the timer only after a connection becomes available hides that wait.
Measure four parts separately: checking the input and permission, waiting for a connection, finding bookings in the database, and preparing the response. Also measure the total from the server receiving the request until it finishes sending the response. Use a clock that measures elapsed time without jumping when the system time changes.
Preparing the response includes converting the returned bookings into the format sent to the screen. This work is called serialization. Its timing does not include network transfer or the browser drawing the list. The operator’s full wait includes those extra steps, so it is a separate measurement.
Record how many rows came back, the response size, and a name for the operation, without recording booking contents or credentials. Compare requests with similar page sizes and customer data sizes. Keep requests that reuse saved data separate from those that must fetch it for the first time; otherwise, different workloads can look like a speed improvement.
Read the illustration from waiting to output
- Left — stopwatch and queue: a hand holds a stopwatch above the entrance, followed by waiting figures. They are a reminder to measure waiting as well as active work.
- Center to right — storage and parcels: a large cylinder with file drawers comes before a conveyor of parcels. The red line connects the stations visually; it is not a measured trace or a literal map of API states.
- Takeaway: investigate the whole journey. The picture does not show which part of this API is slow.
Use the measurements to decide whether to reduce connection waiting, improve the database lookup, or send less data. Adding a cache before finding the delay may leave the real problem untouched.
Continue the list without skipping bookings
Order bookings newest first, using creation time and then a unique booking ID that never changes. The ID breaks ties when two bookings have the same creation time. Both values must be present and stable.
Imagine two bookings created in the same second, but only one fits on the first page. If the next page asks only for older creation times, it skips the other booking. Continuing with both the timestamp and the ID lets the server move past the last returned booking without skipping or repeating its neighbor. Preserve the database’s full timestamp precision; rounding the time can change where a page ends.
Fetch one more booking than the requested page size. If the operator asks for 20, finding 21 tells the server that another page exists. Return only 20, and make the continuation token from the twentieth booking, not the extra one. Using the extra booking would skip it on the next page.
This design supports moving forward reliably, rather than jumping directly to any page number. It does not freeze the list. New bookings inserted ahead of the current position will not appear on later pages, and deletions can make later pages shorter. An export that must represent one fixed moment needs a separate plan to hold that view steady.
Help the database find the right rows
A query is a request to the database, such as finding the next 20 bookings for one customer. An index is an extra lookup structure that can help the database find those rows without reading and sorting the whole table.
For this list, a possible PostgreSQL index groups rows by customer first, then by creation time and unique ID, both newest first. It matches the customer boundary and the order used for page continuation. Send request values separately as query parameters, rather than combining them into the database instruction.
The index is a proposal, not proof of a faster response. A query plan explains how the database chooses to find the rows. Compare its predicted row counts with the rows actually found, rows read and discarded, sorting work, and which data pages it reads or finds already in memory. Use customer data sizes that resemble the intended workload. Reading a whole small table can be a reasonable choice.
The plan’s cost figures are estimates, not milliseconds. Collecting actual execution details runs the query, so this investigation needs an environment where doing so is safe.
Save copies of labels, not live shipment status
A cache is a saved copy of data used to avoid fetching the same information repeatedly. Redis is a store often used for these copies. In this proposal, it holds port labels, not live booking status.
Follow the two routes
- Upper route — live booking status: the API checks access and reads PostgreSQL directly. The red arrow marked “Always live” does not pass through the reference cache.
- Lower route — reusable port labels: the API looks in Redis first. “No entry” leads to the customer-limited PostgreSQL query on the right. The return arrow saves those labels with an expiry. The red arrows show where reads and saved copies go, not how long they take.
- Takeaway: reuse labels only for an agreed period. Do not use this cache for operational booking status.
An operator may act immediately on a status such as whether a shipment is ready. Read the current committed status from PostgreSQL instead of caching the whole booking response. A port label is a narrower candidate for reuse, if the people responsible for it accept a defined delay before a changed label reaches every reader.
Keep separate cache entries for each customer, type of data, language or region setting, and any visibility differences between user roles. The database query must enforce the same access rules. Separating saved copies cannot fix a query that already reads another customer’s data.
Set the expiry according to how long an outdated label is acceptable. Save the value and its expiry together, so replacing a label does not accidentally leave a copy that never expires.
Keep the update order clear:
- Save the changed label in the database first.
- Only after that save succeeds, remove the old cached copy.
- Keep an expiry on every copy. A request that read the old label just before the update may still save it back afterward; expiry limits how long that old copy remains.
If that delay is unacceptable, compare label versions when reading, or skip the cache. Removing an old entry alone does not guarantee that every reader sees the newest label.
Read the saved-copy illustration
- Left and center: a card tray and small display connect to the central storage cylinder. Red pipes connect the objects; they are not arrows specifying a request sequence.
- Right-hand drawers: both hold cards and show a lock and a clock-like dial. They suggest separate, time-limited copies, but do not specify an expiry, prove access checks, or show the live-status route.
- Takeaway: use the labeled diagram above to decide what may be cached. These decorative objects do not define the rules.
If Redis is unavailable, fetch the labels through the same permission-checked database query. This fallback changes where labels come from, not who may see them.
Decide what must remain correct
| Situation | Required result | Why it matters |
|---|---|---|
| Two bookings share a creation time | Continue without skipping or repeating either | The operator can work through the whole list |
| Page size or continuation token is invalid | Reject it before retrieving list data | A bad request cannot silently change the list position |
| Permission is missing or the token belongs to another customer | Deny access before database or cache reads | One customer’s bookings stay separate from another’s |
| A booking status changes | A later read uses the current committed status | Decisions do not rely on an outdated cached list |
| A port label changes | Any delay stays within the agreed freshness rule | Readers know how old a label may be |
| The label cache fails | Use a permission-checked database read | The same visibility rules still apply |
These are proposed outcomes, not completed tests. Claiming a speed improvement would require comparable whole-request measurements under the same workload. A faster response is useful only if customer separation, list order, and freshness still support the operator’s decision.
Further reading: PostgreSQL’s guidance on query plans, multicolumn indexes, and index ordering, plus Redis’s expiration semantics.