Databases for health data (Postgres)

In one line

Health data violates the assumptions most database courses teach. You may not delete. You must keep history. And "when" is at least three different timestamps. Design for that from the start or retrofit it painfully later.

Rule one: never destroy

DELETE and UPDATE are the natural verbs of CRUD, and both are usually wrong here.

A patient's medication is stopped. The instinct is to delete the row. But the patient was on that drug, and that fact must remain true forever — for the clinician reading the history next year, for the adverse-event investigation, for the medico-legal record, and for the retention period the law imposes.

So: soft-delete and append. Mark the row inactive with a reason and a timestamp; never remove it. Update by writing a new version, not by overwriting the old one.

Which means the honest data model isn't a table of current facts. It's a log of what was believed, and when — with "current" as a view over it. That inversion is the single most important idea in clinical data modelling, and it's why FHIR resources carry a versionId and why an EHR feels slower than your CRUD app: it isn't allowed to forget.

Rule two: time is not one column

Ask "when was the blood pressure 140/90?" and you have three answers:

• Valid time — when it was true of the patient. 08:15, at the bedside.
• Transaction time — when the database learned it. 11:40, when the nurse charted.
• Decision time — when someone acted on it.

Most schemas have one created_at and quietly conflate all three. Then someone asks "what did the clinician know at 09:00?" — a question that decides negligence cases — and the honest answer is: we can't reconstruct it, because we recorded when we were told, not when it happened.

Bitemporal modelling keeps valid time and transaction time separately. It is more work and it is the difference between a record and a reconstruction. If you keep one extra column in your career, keep the one that separates when it was true from when we found out.

What Postgres brings

• JSONB — the pragmatic answer to clinical variability. Keep the queried fields as real columns and the long tail as JSONB, indexed with GIN. Storing an entire FHIR resource in JSONB with generated columns for the search parameters is a well-trodden, sane pattern.
• Row-Level Security — authorisation enforced by the database, not just the application. Genuinely valuable in health: a bug in your API can't bypass a policy the database enforces. Note the trap this platform hit — RLS policies alone are not access. On a hardened database the role also needs explicit GRANTs, or queries fail silently with zero rows and no error, which looks exactly like "no data."
• Range types + exclusion constraints — bed occupancy, coverage periods, appointment slots. Let the database refuse to double-book rather than trusting the application to check.
• Extensions — pgcrypto, pg_trgm for fuzzy name matching (relevant to MPI), pgvector for embeddings.

OLTP is not OLAP

The mistake that kills performance: running the population report against the live clinical database. A transactional schema is normalised for correct writes; analytics wants denormalised reads. Fighting that produces a slow ward round and a slow report.

Separate them — replicas, a warehouse, OMOP for research — and be explicit that a replica lags. Reading a replica for a clinical decision means acting on data you know is stale.

Encryption and the residency point

Encryption at rest is table stakes and protects against a stolen disk — not against an application bug or a compromised credential, which is how data actually leaves. Column-level encryption for the most sensitive fields is worth the pain; encrypt everything and you lose indexing and gain complexity.

And remember the boundary from cloud: the backups and the replicas are data too. A resident primary with an out-of-country replica has exported the data through a door nobody looked at.