Thinking about ~1M small banks (one per user) on open-source Hindsight – where would you start?

[pierrecarcellermeunier]

Hi everyone,

First, thank you for Hindsight. I've spent the last few days reading the docs, the release notes and the Memlake write-up.

I'm working on a B2C product where every user gets their own memory. That means one bank per user, and a lot of users: I'm planning for somewhere between 500K and 1M. Each bank would stay small, just one person's memories over time. Isolation between users matters a lot to me, and so does time (knowing what happened and when).

I'm only looking at the open-source version, on Postgres. I did notice that the Enterprise side has answers for scaling on the number of memories, and that's great, but it's not my situation. My challenge is the opposite: not huge banks, but a huge number of tiny ones. I don't have the budget for Enterprise right now. What I do have is time, and I'm ok to build and test things myself.

So my question is a pretty open one: what would you put around Hindsight to get to 500K–1M banks?

A few things I'm wondering about, in case they help frame it:

• Has anyone run a large number of small banks on the Postgres backend? Did you hit walls, and where?
• Would you scale this with one big instance, or with several Hindsight instances behind a routing layer (something like a hash of the user ID picking a shard)? Or is there a smarter way, like partitioning?

Even a partial answer or a "don't do this" would help me a lot. If I end up building something, I'd be glad to write up what I learn and share it here.

[stanleys12]

I haven't run 500K+ banks myself, so this is from reading the code, not from experience. But there is one concrete wall to know about, and a setting that moves it.

By default, every bank gets its own vector indexes the moment it is created. Banks share one memory_units table, and each bank gets three partial indexes, one per fact type (world, experience, observation), each filtered on bank_id and fact_type. The migration that introduced this is here:

hindsight/hindsight-api-slim/hindsight_api/alembic/versions/d5e6f7a8b9c0_add_bank_internal_id_and_per_bank_hnsw.py

Lines 16 to 23 in bc5ea19

	Why per-(bank, fact_type) indexes:
	- fact_type-only partial indexes are never chosen by the planner when bank_id is in the WHERE
	clause, because the idx_memory_units_bank_id B-tree index always wins at planning time.
	- Per-(bank, fact_type) partial indexes have both predicates matching → planner selects them.
	- The global vector index competes for larger partitions (world, observation) and must be dropped.
	- AlloyDB ScaNN uses global vector indexes with filtered vector search instead
	because empty or tiny per-bank indexes cannot be built safely.
	"""

At 1M banks that would be about 3M indexes on one table. The code comments say what that costs. Each index is enumerated and locked at plan time by queries from every other bank, and opened by every write to the table. A small bank's index never repays that, because the (bank_id, fact_type) B-tree plus a top-N sort answers the same query exactly and faster:

hindsight/hindsight-api-slim/hindsight_api/_vector_index.py

Lines 256 to 271 in bc5ea19

	def per_bank_index_min_rows() -> int:
	"""Rows a (bank, fact_type) needs before it earns its own partial vector index.
	``0`` — the default — means the threshold is off entirely: indexes are
	created up front at bank creation, exactly as they were before the threshold
	existed, and no maintenance operation ever runs. Everything below applies
	only when an operator sets a positive value.
	Distinct from :func:`minimum_rows_for_index`, which is ScaNN's *build*
	requirement for its single global index (AlloyDB cannot construct one below
	a floor). This is a cost policy for the per-bank backends: the indexes sit on
	the shared ``memory_units`` table, so each one is enumerated and locked at
	plan time by queries belonging to every *other* bank, and opened by every DML
	statement against the table. A small bank's index cannot repay that — the
	``(bank_id, fact_type)`` B-tree plus a top-N sort answers the same query
	exactly and faster. See issue #3485.

So the first thing I would do is set HINDSIGHT_API_VECTOR_INDEX_MIN_ROWS. The default 0 means eager indexing for every bank. A positive value means a fact type only gets its own index once it reaches that many rows, and the index is dropped again if the bank shrinks well below that. The docs suggest 10000 as a starting point for deployments with thousands of banks:

hindsight/hindsight-docs/docs/developer/configuration.mdx

Line 208 in bc5ea19

| `HINDSIGHT_API_VECTOR_INDEX_MIN_ROWS` | Memories a bank needs, in one fact type, before that fact type gets its own vector index. `0` (the default) turns the threshold **off**: every bank is indexed from the moment it is created. `10000` is a good starting point for deployments with thousands of banks. A bank whose memories a custom store owns is outside this at any value — it has no rows here, so it is never given a per-bank index, and never has an existing one taken away (see `repair-bank` in the [admin CLI](./admin-cli.md)). | `0` |

With per-user banks of a few hundred or thousand memories, most banks would then never get an index, and that is the intended path for small banks.

On the second question, I'd plan for several Hindsight instances, each with its own Postgres, behind a router that maps user ID to shard. Even with the setting above, every bank's rows still land in one shared memory_units table, and the bank-scoped B-tree has to serve all of them. That is my own judgment, not something I measured, so I'd test it at your scale before committing. A router that hashes the user ID is simple, and it keeps bank isolation strict, since each request already targets a single bank and the docs say multi-bank queries are the client's job.

There is also a tenant-schema mechanism in the codebase (the admin CLI has --schema and migrates "the base schema plus all tenant schemas"), but I haven't checked how it behaves at hundreds of thousands of schemas, so I wouldn't assume it solves this.

Practical test plan: create a few hundred thousand synthetic banks with the threshold set, retain a small number of memories into each, then measure recall latency for a random bank while a background writer is active. If recall latency grows with total bank count, shard sooner.

(I looked this up in the code with help from an AI assistant; the links are to the lines I checked.)