Where should session records live for a regional booking site on three instances that already runs a relational database, and why?
answer
- three homes, five axes
- what survives a restart
- a network trip per request
- native expiry or a purge job
- who already runs the tier
basics
~20 sDefault to a table in the existing relational database: durable, queryable by user, no new tier to run. Move to a shared key-value store once session traffic strains the database; process memory strands each record on one instance.
solid answer
~40 sI compare the three homes on durability, per-request cost, expiry and who operates them. **Process memory** is the cheapest lookup, but each record lives on one instance and dies with its process. A **shared key-value store** costs a network round trip per request, offers per-entry expiry natively, and survives its own restart only as well as its persistence and replication settings allow; it is also a new tier-one dependency someone must run. A **relational table** is durable and indexable by user id, but expiry is an `expires_at` column the read path checks plus a scheduled purge job, and every session write is a durable commit. For a regional booking site at modest traffic that already runs the database, I pick the table and revisit when session traffic becomes a real share of database load.
go deeper
Recall the three homes for session records and one property of each: process memory is lost on restart, a key-value store is shared over the network, a relational table survives restarts and can be queried.
Explain the comparison on durability, per-request cost, expiry and operating burden, then pick a home for the stated workload and name the signal that would make you change it.
Show you would operate the choice: monitor the purge job, configure a key-value store's persistence and memory limit deliberately, and measure session load on the database rather than guess it.
Frame it as a dependency decision: whether a new tier earns its operating cost, who owns it, and which signals, such as database load, write rate or tolerance for forced sign-in, trigger the move.
## What is being decided A **session record** is the server-side state behind a session identifier: who the user is, when the session started, when it must end, and whatever small working state the application keeps between requests (on a booking site, a half-built reservation). Once a site runs on more than one instance, any instance may receive the next request, so the record has to be readable by all of them, or the user has to be routed back to the instance that holds it. The design question is therefore not *whether* to store sessions on the server but **where**, and the three usual homes are the server process's own memory, a shared key-value store, and a table in the application's relational database. ## The three homes - **Process memory**: a map inside each running server process. A lookup costs no network trip at all. The record exists on one instance only and disappears when that process restarts, crashes or is replaced by a deploy. - **Shared key-value store**: a separate service, usually holding its data in memory, that every instance reaches over the network by session identifier. It typically supports **per-entry expiry**: the application writes the record with a lifetime, and the store stops returning it after that deadline. Whether records survive a restart of the store depends on its persistence and replication settings. - **Relational table**: one row per session in the database the application already uses, keyed by session identifier and usually indexed by user id. Rows survive restarts of both the application and the database, and are covered by the same backups. In the common case the table has no native row expiry, so each row carries an `expires_at` column that the application checks on every read, and a **scheduled purge job** deletes expired rows. ## Axis by axis | Axis | Process memory | Shared key-value store | Relational table | |---|---|---|---| | Survives an application restart | No | Yes | Yes | | Survives a restart of the store itself | Not applicable | Depends on its persistence and replication settings | Yes, like any committed row | | Cost per request | Cheapest: an in-process lookup | One network round trip, usually fast | An indexed read; each write is a durable commit | | Expiry | An in-process sweeper or a check on read | Native per-entry expiry | A checked column plus a purge job | | Finding one user's sessions | Only instance by instance | Usually an index the application maintains | An ordinary query on an indexed column | | Who operates it | Nobody extra | A new tier someone must run | The team that already runs the database | The cost row is **ordinal and deployment-dependent**: a store in a distant network zone can be slower than a nearby database, and a database that keeps a small, hot table in memory can be quick. What holds generally is that an in-process lookup is cheaper than any network trip, and a durable write costs more than an in-memory one. ## Choosing for the booking site Take the stated workload: three instances, a relational database already in production, and, for a regional booking site, modest traffic and customers who would be annoyed to be signed out halfway through paying. The reasoning runs in order: 1. **Set process memory aside.** With three instances, keeping each record in one process needs sticky routing and still loses that instance's sessions at the next deploy. That cost is the premise of the question, not the decision. 2. **Ask whether a new tier is justified.** A key-value store is one more service to provision, monitor, patch and fail over, and because every authenticated request reads it, it joins the list of tier-one dependencies. 3. **Check the database's headroom.** At modest traffic, one indexed read per request and a write when the record changes are usually a small share of the load the database already carries. 4. **Pick the table**: durable, queryable by user, backed up with everything else, and adding no new dependency to requests that already need the database. 5. **Write down the trigger to revisit**: session reads and writes becoming a real share of database load, or a team already running a shared key-value store that can take the work. ## What changes the answer - **High request volume or frequent session writes**, such as a record rewritten on most requests, shift the weight toward a key-value store, whose in-memory writes are cheaper than durable commits. - **An existing, well-run key-value tier** removes most of the operating cost of that option. - **Tolerance for a forced sign-in** matters: if losing every session costs little, a store run without persistence is acceptable; if it costs a half-finished payment, durability earns more weight. - **Memory pressure** in a key-value store is a separate risk from expiry: a store configured to drop entries when full can remove session records before their deadline, which signs those users out early. ## Common mistakes - Calling a key-value store "durable" or "volatile" without saying how it is run. - Assuming an ordinary table deletes expired rows by itself; expired rows stay until something deletes them, and must be rejected on read in the meantime. - Treating a key-value store as free because it is fast, and forgetting that someone has to operate it.
- When would you choose a shared key-value store over the table for this booking site?When session traffic becomes a real share of database load, for example many reads per second or a record rewritten on most requests, or when a team already runs a key-value tier that can take it. Then in-memory writes are cheaper than durable commits on the primary, and native per-entry expiry removes the purge job. The price is a dependency every authenticated request needs, whose durability must be configured deliberately.
- Does keeping sessions in the application's own database create a new single point of failure?Not for requests that already need the database: if it is down they fail anyway. What it adds is read and write load on the primary, and a database dependency for pages that otherwise would not touch it, such as one that only checks the visitor is signed in. At modest scale that trade is usually acceptable, and worth re-measuring as traffic grows.
- A key-value store is durable simply because it runs as a separate server.
- Any database table drops expired session rows on its own, so no purge job is needed.
- A relational database is too slow for sessions, so a key-value store is always required.
- Per-entry expiry and eviction under memory pressure are the same mechanism.
- A key-value store costs nothing extra to run because its lookups are fast.