Engineering

Why Most Identity Systems Need (At Least) Four Data Stores

At 09:12 on a Tuesday, login p99 went from 90 milliseconds to just over four seconds and stayed there for twenty-six minutes.

Nothing had been deployed. Traffic was ordinary — the usual European morning ramp, maybe 60 logins per second at the top. The database was not down, not failing over, not out of connections. CPU was pinned at 96% and the slow query log was full of things nobody recognised.

The reconstruction took most of the day, and the answer was three unrelated facts happening at once. A customer administrator had opened the user directory and typed smith into the search box, which became a query joining users to attributes to group memberships with a leading-wildcard LIKE, over a tenant with 180,000 users. Autovacuum had been stuck on the audit table for six hours, which meant the sessions table — churning about 2,000 row updates per second from last_seen bookkeeping — was carrying roughly 40% dead tuples, and its indexes had grown past the point where they stayed resident in shared buffers. And the audit inserts were eating a steady share of the IOPS budget on the same volume.

Four workloads. One Postgres instance. Each individually defensible; the combination was the incident.

The postmortem action item was "add read replicas." That was the wrong fix, and it bought about four months.

This is not an argument about scale

The usual framing for storage sprawl is growth: you start with one database, you get big, you shard and specialise. That framing is comfortable because it implies the split is something you can defer with better hardware.

It isn't, and the tell is that the failure above happened at 60 logins per second on a machine that was 90% idle at midnight. Scale determined when the bill arrived. It did not determine that it would.

What determines that is simpler and more structural. An identity platform has four requirements that are pairwise irreconcilable in a single storage engine:

Strong consistency versus write volume. Configuration must be transactional and correct. Session state must absorb thousands of short-lived writes per second. The mechanisms that give you the first — MVCC, full durability, synchronous commit, foreign keys — are exactly what make the second expensive.
Retention versus cost. Audit must survive for years, under an immutability guarantee. Operational data must stay small enough to migrate, back up, and restore inside your RTO. These are opposite instructions to the same storage.
Query flexibility versus tail latency. Directory search and SCIM filters need arbitrary predicates over arbitrary attributes. The login path needs a bounded, planned, single-digit-millisecond lookup. Flexible queries are unbounded queries, and unbounded queries share a CPU with your p99.
Auditability versus mutability. Evidence must be append-only. Configuration must be editable. A store that permits UPDATE cannot also be the thing you show a regulator as tamper-evident.

Any single store can satisfy one of these pairs well and a second one badly. None satisfies all four. So the stores aren't technology choices you make; they're the shape those constraints force, and you can derive each one without naming a single product.

Let's do that.

Store 1: the record of intent

Start with what an identity platform is actually authoritative for. Tenants, realms, users, credentials, clients and their redirect URIs, policies, group definitions, signing key metadata, entitlements. The declared configuration of the system.

Access pattern: read-mostly by an enormous margin, write-rare. The read amplification is the subject of Identity Platforms Are Mostly Caching Problems; I'll take it as given.
Consistency: strict. "Did the client secret rotation commit?" must have a yes/no answer, not a probability. Multi-object invariants are real here — a client, its allowed grants, and its credential are one logical change.
Durability: absolute. Every row is expensive to reconstruct and some are impossible.
Retention: the lifetime of the tenant, plus tombstones.
Query shape: narrow, keyed, known in advance. A few dozen variants, all of them planned.

That's a normalised relational database with transactions and real backups, and it should be small — tens of gigabytes at most for a substantial deployment. Small matters more than it sounds. It's what lets you migrate inside a change window, restore in minutes rather than hours, and reason about the whole thing.

Everything else in this article is about keeping it small.

Store 2: ephemeral state, and the loss asymmetry nobody writes down

Now sessions, refresh token families, authorization codes, device-binding records, replay counters, rate-limit buckets, MFA challenge state.

This is where most teams' first architectural mistake lives, and it isn't really a performance mistake. It's a classification mistake. Consider what losing each store costs you.

Lose a client secret from store 1 and you have an outage. The credential cannot be reconstructed — you issue a new one and then coordinate with whoever deploys the application that uses it, which for an enterprise integration is a change ticket measured in days.

Lose every session in store 2 and everyone gets logged out. That's a support spike and a lot of people typing their passwords. It is not an outage, and critically, it is self-healing — the state regenerates from user behaviour within one login cycle.

Those failures differ by an order of magnitude in severity and completely in recovery mechanism. Treating them as one durability class is how you end up running synchronous replication and hourly snapshots over data that expires in fifteen minutes — or, worse and more common, running the correctness-critical data at whatever durability you were willing to accept for sessions.

But the asymmetry that actually causes incidents is the write pattern.

Session state is high-churn by construction: created on login, updated on activity, deleted on expiry or logout. In an MVCC database, none of those are cheap. An UPDATE of last_seen doesn't modify a row; it writes a new version, marks the old one dead, and writes an entry into every index on the table. A DELETE doesn't free space; it marks a tuple dead and leaves reclamation to vacuum.

A session table taking 2,000 updates per second therefore generates on the order of 170 million dead tuples a day, plus index churn on each. Vacuum has to keep up, and vacuum's cost is proportional to the size of the table and its indexes — which grow precisely because vacuum is behind. That feedback loop has three consequences that are hard to see from the application:

Bloated indexes stop fitting in the buffer cache. A 300 MB index that served the login path from memory becomes a 3 GB index that serves it from disk, and your p99 moves without any query changing.
The visibility map is never all-visible on a hot table, so index-only scans silently degrade to heap fetches.
Autovacuum has a small, fixed pool of workers — three, by default. If one of them is grinding through an anti-wraparound freeze on a 400 GB audit table, your session table waits.

That third one is the mechanism from the opening story: the specific way stores 2 and 3 sharing a database becomes a login latency problem with no fingerprints on it.

The right store here has TTL as a native primitive — expiry as a property of the record rather than a DELETE ... WHERE expires_at < now() job on a cron — relaxed durability, and no obligation to reclaim space in a way that competes with reads.

One warning, because this is where the caching article and this one meet: a session store is not a cache, even when it's the same software. Apply that article's flush test — if flushing it logs everyone out, it's a database with an eviction policy, and it needs a persistence and failover story chosen on purpose. Calling it "the cache" on your architecture diagram is how it ends up with neither.

Store 3: evidence

Audit events. Written on every meaningful request, immutable, retained for years, and queried almost never — but when they are queried, it's by someone in an incident channel who needs an answer in the next ten minutes.

The economics of this store — ingestion versus storage cost, tiering, why indexing is the expensive part — are the entire subject of The Hidden Cost of Audit Logs, so I won't re-derive them. What matters for storage topology is narrower, and it's three properties that are hostile to every other store in the system:

It's append-only. The moment it lives in a schema where UPDATE and DELETE are available and routinely used by neighbouring tables, its immutability is a convention rather than a guarantee. "We don't update that table" is not something you can show an auditor.

It's the volume. In the deployment from the opening incident, the audit table was 78% of the database by size. Every operational consequence of database size — backup duration, restore time, migration windows, vacuum cost, the failover time your RTO depends on — was being driven by data that nothing in the request path reads.

It's legally distinct, and this shows up as a contradiction you cannot resolve inside one schema. A GDPR erasure request says delete this user's personal data. A financial-services retention rule says preserve the audit trail of privileged actions for seven years, including the actor's identity. Both apply to the same human. In separate stores this is solvable — erase the profile, pseudonymise or legally-hold the audit records under a documented policy. In one schema, with foreign keys from audit rows to the user table, you have a delete that either cascades into your evidence or fails. Teams discover this during their first serious erasure request, and the remediation is a data migration under a statutory deadline.

Store 4: the index

The fourth requirement is the one that's easiest to under-weight, because it arrives disguised as a feature request: administrators want to find users.

Then it stops being a feature request. SCIM makes it a protocol obligation. A compliant client can send:

filter=userName co "smith" and emails.value ew "@acme.com" and active eq true

Arbitrary attributes, arbitrary conjunctions, substring and suffix operators, over a schema where custom attributes are usually key-value rows because tenants define their own. Every predicate becomes another self-join, co is a leading wildcard no B-tree can serve, and the set of queries is open — a customer's integration decides it, not you. Query cost scales with the tenant's directory size, a number your largest customer controls.

You can force it. Trigram GIN indexes work. But now the login path's table carries GIN indexes whose pending-list flushes are write-amplification events on your hottest table, and you've spent your remaining vacuum headroom on admin search.

The requirement forcing a separate store isn't "search is slow." It's that directory search must not be able to consume resources the login path needs. An admin console query is a background-priority operation being served by foreground-priority infrastructure, and a shared store has no way to express that distinction. Separation is the resource isolation; the denormalised document model you get with it is a bonus.

Group membership resolution belongs here too, for the same reason: transitive closure over nested groups is a graph traversal you do not want on the credential-verification path.

The fifth, later: analytics

Eventually someone wants MAU by tenant, login success rates by federation partner, MFA adoption trends, cohort retention. Wide scans, full history, joins across every other store, no latency requirement at all.

The obvious move is to point them at a read replica. Don't, and the reason is specific enough to be worth stating, because it's a mechanism most engineers haven't hit.

A streaming replica shares the primary's WAL and its MVCC horizon. Run a 40-minute analytical scan on a hot standby and one of two things happens. Either the replica cancels the query when conflicting WAL arrives (max_standby_streaming_delay), so your analysts learn it "randomly fails" and start retrying in a loop. Or someone sets hot_standby_feedback = on to stop the cancellations — which tells the primary to hold back vacuum for the duration of the longest-running query on the replica. Now your analyst's afternoon report is the reason store 2 accumulated dead tuples: the bloat mechanism from three sections ago, triggered from a machine that was supposed to be isolated.

A replica isolates capacity. It does not isolate consistency horizon, and for identity the second one is where the coupling bites.

So the analytics sink has to be a copy: extracted on a schedule into a columnar store with its own schema, where long queries are normal and nothing holds a lock or a snapshot on anything in the request path. The lag is a feature — it decouples the analyst's schema from yours, so you can evolve the operational model without breaking a dashboard, and vice versa.

What it looks like assembled

flowchart LR
    Admin["Admin / SCIM"] -->|"writes"| OP[("1 · Operational<br/>config, users, clients<br/>strict · small · backed up")]
    Login["Login / token path"] -->|"reads"| OP
    Login <-->|"read + write"| SESS[("2 · Ephemeral<br/>sessions, refresh families<br/>TTL-native · lossy-tolerant")]
    Login -->|"publish"| BUS(["Event stream"])
    Admin -->|"publish"| BUS
    OP -.->|"outbox"| BUS
    BUS --> AUD[("3 · Audit<br/>append-only · years")]
    BUS --> IDX[("4 · Search index<br/>denormalised · flexible query")]
    BUS --> ANL[("5 · Analytics copy<br/>columnar · lagging")]
    Search["Admin search / SCIM filter"] -->|"reads"| IDX
    Incident["Investigation"] -->|"reads, rarely"| AUD

Two things are worth reading off that diagram. The login path touches exactly two stores. And stores 3, 4 and 5 are all downstream — nothing on the request path waits for them.

The collapse always goes the same direction

Teams do not accidentally end up with five stores. They accidentally end up with one, because consolidating down into the operational database is always the locally cheaper decision. Audit is a table. Sessions are a table. Search is a query. Each of those is a two-hour change with an obvious alternative that costs two weeks.

The failure sequence that follows is consistent enough to be a checklist:

Audit writes compete with login reads for IOPS. Not for CPU, not for connections — for the write bandwidth of the volume the login path also reads from. It shows up as latency with no responsible query.
Session churn bloats tables and starves autovacuum. As above. This is the one that produces incidents nobody can attribute, because the query that got slow is not the query that caused the problem.
Admin search saturates CPU during a login peak. An unbounded query on foreground resources, timed by a human in another timezone who has no idea they're on your critical path.
Retention rules become unapplicable. You cannot set a seven-year policy on audit, a thirty-day policy on sessions, and an erase-on-request policy on profiles when all three sit in one schema with referential integrity between them.
Backup and restore stop fitting the RTO, because the backup is 78% audit data that nothing needs restored quickly.
And then the restore itself is a security regression. You roll back to a snapshot from six hours ago to recover a botched config migration. You have also restored six hours of session state: sessions that were logged out are alive again, refresh tokens that were rotated are valid again, and the reuse-detection lineage that would have caught a stolen token has been rolled back with them. Restoring configuration and restoring credentials-in-flight are different operations that a single-database backup fuses into one.

Point six is the cleanest argument for the split, because it's a correctness problem rather than a performance one, and no amount of hardware addresses it.

When one store is genuinely the right answer

All of which is a poor reason to start with five stores. The counterpoint deserves its full weight, because an article that only makes the case above produces worse systems than the one it's arguing against.

Every store you add is a failure domain, and availability multiplies: five components at 99.95% is 99.75% if they're all on the request path. The topology above deliberately keeps three of them off it, but the discipline to maintain that is not free. Every store is a consistency lag you now have to define, measure, and degrade against. Every store has a patch cadence, a backup policy, a capacity model, and a 3am page. Every schema change is potentially five migrations that have to be ordered. And debugging gets harder: "this user's group membership is wrong" now has five possible locations and a propagation delay between them.

A single Postgres instance is a genuinely excellent identity backend, and the honest guidance is to stay on it longer than architectural instinct suggests. One store is likely right below roughly 20 sustained logins per second, single region, audit volume in the low single-digit gigabytes per day, directories in the tens of thousands of users, and admin search over a set small enough that a sequential scan is fine.

Treat the following as illustrative rather than as thresholds to measure against — the point is the shape of the signal, not the number:

Dead-tuple ratio on the session table sits above 20% during business hours, or autovacuum on it is being starved by a worker on a larger table.
The audit table crosses about half the database by size, or restore time crosses your stated RTO.
Login p99 correlates with admin console activity. If you can see the morning admin shift in your authentication latency graph, store 4 is already overdue.
A schema migration no longer completes in a maintenance window because of one enormous table.
The first customer SCIM filter using co on a custom attribute. That one is hard; there's no index that saves you.

Split in the order the pressure arrives, which is usually 3, then 4, then 2. Audit leaves first because it's the biggest and the easiest to make asynchronous. Search leaves second because it's the most disruptive to the login path. Sessions leave last because they're the most entangled with correctness, and because below real volume a relational session table is fine.

The bill for splitting: derived state

The moment you split, everything outside store 1 is a projection. You've traded a storage problem for a distributed systems problem, and that's a real trade rather than a free one.

You need a propagation mechanism, which is the event bus every identity team eventually builds — including the transactional outbox that keeps the state change and the event atomic, because a dual write here means your search index and your audit trail disagree with your database permanently, with nothing to reconcile them. And you need the reconciliation discipline from Building Identity Like Kubernetes: projections converge from desired state rather than depending on every notification landing, so a dropped event is a delay rather than permanent divergence.

Then the question those articles set up and this one has to answer: what does the request path do when projected state is missing?

The default must be fail secure. Not "fall back to a synchronous read from store 1" — that fallback is exercised approximately never in normal operation, which means it is untested and unoptimised, and it activates for the first time during exactly the incident where stampeding your operational database is the worst available move. Missing projected state isn't a cache miss to be filled. It's an assertion you cannot make, and the correct response to an assertion you cannot make is to refuse.

The platform I work on, ClavionX, takes the strict version of this as a recorded decision rather than a guideline: per ADR-0002 the runtime never synchronously calls the control plane, state arrives by event and projection only, and a missing projection fails secure rather than reaching back for a fresh read. Each tenant gets its own OIDC issuer and signing keys, so tenant isolation survives a projection being wrong. That's one worked example of the rule, not an argument for a product — plenty of platforms arrive at the same place independently, for the same reasons.

Where this lands

The stores aren't five technologies. They're four requirement pairs that cannot be satisfied simultaneously, plus one deferred copy for the people who ask questions in aggregate.

Which means the useful design exercise isn't "which databases do we need." It's the boring one: for each category of state you hold, write down its access pattern, its consistency requirement, its durability requirement, its retention obligation, and its query shape. Five columns. Then look at which rows contradict each other.

Rows that contradict each other cannot share a store. Not because of scale, and not eventually — structurally, on day one. The only variable is how long the contradiction stays cheap, and the answer to that is always shorter than the team building it expects, because the bill arrives as a latency graph with no responsible query on it, on an ordinary Tuesday morning.