A deep dive into building a patient-scoped FHIR cache on object storage.

1. The problem: one database, one shape of read

Our fhir resource store holds the FHIR resources that everything else in the product reads. It sits on a single Postgres database that is the largest + most expensive one we operate.

The dominant load is patient-scoped: "give me every Condition for patient X," "every Observation for patient X between these dates." Our recommendation engine's heaviest pipeline runs this pattern thousands of times every week. Each run pulls a patient's full clinical history.

That workload was already straining the database at our current member count. Scaling Postgres reads horizontally is not a thing Postgres does (Well I know Multigres is coming, but not yet stable for production imho :p). Read replicas help with isolation but not with the cost of re-scanning the same patient's rows on every request. Sharding by patient was the way we didn't want to build yet (blockers like e.g. not all reads are patient-scoped, migration needs downtime, etc.).

So the question we posed was narrow and boring on purpose:

Most of our reads are "all rows for one patient, filtered by a predicate." What is the cheapest store that serves that read without touching Postgres?

2. The road not taken: LanceDB

The obvious 2026 answer is "put it in a serverless database." We tried that first with LanceDB. LanceDB stores all the data in GCS, so it should be highly horizontally scalable.

However, later I found that the read latency under concurrency was worse than plain Postgres. Same query mix, same worker count, and the columnar store's median was slower than the database we were trying to escape - tbh I don't know why and is new to LanceDB. But instead of further drilling into the rabbit hole, I'm trying to find another way out.

3. The insight: object storage as a read replica

Looking at the access pattern again: if the read is always scoped to one patient, then "the data for one patient" is a natural unit of storage. Not a partition of a table — a file.

The design that fell out of that:

  1. Export the whole resource table out of BigQuery (which already mirrors our Postgres data), sorted by patient_id.
  2. Split the export into one compressed file per patient per resource type in a GCS bucket: 
 gs://<bucket>/fhir-cache/<resource_type>/<patient_id>.<format>

To read patient X's Conditions: one GET of one object, decompress, filter in Python with the same predicate logic the SQL query used, return.

4. How it works: the hybrid read path

The reader doesn't trust the cache to be fresh — it measures how fresh it is, per patient on every request. Four steps, strictly serial, per patient per request:

 1. fetch_patient_rows one HTTP GET of the patient's blob; decompress; parse 2. high_water_mark max(date_modified) over the cached rows themselves 3. changed_rows_since ONE Postgres query: rows for this patient with date_modified >= high_water_mark (minus a small overlap buffer), soft-deleted rows included 4. cache_resources merge: tail rows supersede cached rows by id; the union is filtered by the request predicate and returned

Architecture at a glance 

 ┌──────────────┐ │ BigQuery │ │ fhir resource│ │ store │ └──────┬───────┘ │ EXPORT DATA ... ORDER BY patient_id ▼ ┌──────────────┐ │ Dataflow │ │ map-only │ │ splitter │ └──────┬───────┘ │ one file per patient per resource ▼ ┌────────────────────────────────────────────────┐ │ gs://<bucket>/fhir-cache/ │ │ <resource_type>/<patient_id>.<format> │ └──────┬─────────────────────────────────────────┘ │ GET + parse + python predicate ▼ ┌──────────────┐ ┌──────────────────┐ │ GCS cache │◀────────▶│ Postgres tail │ │ reader │ concat │ │ │ date_modified│ │ date_modified > │ │ ≤ HWM │ │ HWM │ └──────┬───────┘ └────────┬─────────┘ │ │ ▼ ▼ caller (e.g. Recommendation Engine)

TL;DR

  • Our FHIR resource store behind our recommendation engine — sat on the largest, most expensive single Postgres database we run. The dominant workload is per-patient reads, fetching every FHIR resources for one patient, thousands of patients at a time.
  • We tried a serverless columnar store (LanceDB) first. It lost to Postgres on read latency under concurrency.
  • The winning design is dead simple: one file per patient per resource type in Google Cloud Storage (GCS), built by a two-stage BigQuery-export + map-only Beam pipeline.
    • The read path is one HTTP GET, a decompress, and an in-process Python filter — plus a small "tail" query against Postgres.
  • The key property is that the cache is allowed to be stale. A missing / outdated file doesn't corrupt anything; it just falls back to Postgres for that patient. No cache-invalidation protocol (avoiding the biggest problem ;D), and rollback is removing one line of configuration.
  • Postgres CPU load during our largest recommender run dropped to about a third of baseline. And it causes roughly a 15× reduction in database row scans.

We named this the "cache," but the more honest description is a read replica with an unusual consistency model: every read re-derives its own freshness. Staleness as a first-class measured quantity — is what let a short spike become production infrastructure that took two-thirds of the load off the database it was built to protect.