Explore Bank-Level Physical Isolation for Large Memory Banks

[Sanderhoff-alt]

Summary

Hindsight currently has a clear logical isolation model: tenant isolation is handled at the schema level, and multiple banks inside the same tenant share the same tables while being separated by bank_id. This works well for many small or medium banks, and the current implementation already includes important optimizations such as bank_id filters, bank/fact-type indexes, and per-bank vector indexes on PostgreSQL for non-ScaNN vector backends.

However, for single-tenant deployments or workloads where one bank grows very large, the physical storage layout may become a bottleneck. It may be worth exploring a roadmap for bank-level physical isolation or "large bank promotion", where large banks can receive dedicated physical storage/indexing layouts while small banks continue to share tables.

Current Behavior

From the current architecture:

• A tenant maps to a schema.
• Multiple banks within a tenant share tables such as banks, documents, chunks, memory_units, mental_models, directives, and related link tables.
• Bank-level isolation inside a tenant is enforced by bank_id.
• Retrieval and maintenance queries are generally scoped by bank_id.
• PostgreSQL has several bank_id-aware indexes and per-bank/per-fact-type vector index support for non-ScaNN vector backends.
• Oracle appears to go further by using automatic list partitioning on memory_units.bank_id, allowing partition pruning for bank-scoped queries.

This design is simple and effective for shared multi-bank deployments, but it leaves the largest bank in a tenant competing inside the same physical table family as every other bank.

Problem

For very large banks, logical bank_id filtering may not be enough. The main concerns are:

• Very large memory_units tables can make vector, text, temporal, tag, and maintenance indexes harder to keep efficient.
• Bank deletion, archival, or reindexing can require large DELETE or index maintenance operations instead of dropping or rebuilding isolated physical structures.
• Per-bank vector indexes help, but they do not fully isolate table bloat, vacuum pressure, text indexes, tag indexes, document/chunk rows, observation rows, or graph/link tables.
• A single oversized bank can affect shared table statistics and maintenance behavior for the rest of the tenant.
• There is no explicit lifecycle for promoting a bank from shared storage to dedicated storage once it crosses a size or activity threshold.

This is not necessarily a bug in the current design. It is more of a scalability and operations question: should the database layout reflect Hindsight's logical hierarchy more strongly for large-bank workloads?

Proposed Direction

Consider adding a bank-level physical isolation strategy with a tiered layout:

1. Small banks stay in shared tenant tables.
2. Large banks can be promoted to dedicated physical storage.
3. The promotion policy can be automatic, manual, or both.
4. Query routing remains transparent to API users.
5. Operational tooling can manage migration, reindexing, archival, and deletion.

The key design goal is:

Large banks should not impose disproportionate read, write, indexing, vacuum, archival, or deletion costs on the rest of the tenant.

Possible Implementations

Option A: PostgreSQL list partitions by bank_id

Use partitioning so each large bank has its own partition, while smaller banks may remain in a default/shared partition.

Pros:

• Natural fit because most queries already include bank_id.
• Enables partition pruning.
• Large bank deletion can become close to dropping a partition.
• Per-bank indexes become physically scoped to the partition.
• Aligns conceptually with the Oracle memory_units bank partitioning approach.

Cons:

• One partition per bank may not scale well if there are many banks.
• Requires DDL on bank creation or promotion.
• Migration from shared table to partitioned layout is non-trivial.
• Related tables such as documents, chunks, memory_links, unit_entities, entity_cooccurrences, and async_operations need a consistent strategy.

Option B: Default shared partition plus dedicated large-bank partitions

Keep all small banks in a default shared partition. Promote only large banks to dedicated partitions.

Pros:

• Avoids partition explosion.
• Targets the banks that actually need physical isolation.
• Provides a practical migration path from the current shared-table model.
• Allows size/activity thresholds, such as memory count, observation count, index size, recall latency, or write volume.

Cons:

• Requires bank lifecycle state, such as storage_layout = shared | dedicated_partition.
• Requires migration tooling and careful query/index validation.
• Planner behavior and statistics need monitoring after promotion.

Option C: Dedicated table/schema/database for very large banks

For extremely large banks, move the bank into a dedicated table set, schema, or even database.

Pros:

• Strongest isolation.
• Simplifies per-bank backup, restore, export, archival, and resource governance.
• Useful for enterprise or high-volume workloads.

Cons:

• More operationally expensive.
• More complex routing.
• Harder to keep migrations and background workers simple.

Option D: Logical tiering without table partitioning

Keep the table layout but add hot/cold memory tiers:

• Active raw memories
• Archived raw memories
• Observations
• Mental models

Default recall would search active data first, while archived data would be searched only with a higher budget or explicit option.

Pros:

• Helps single-bank growth even without partitioning.
• Reduces default recall and consolidation fan-out.
• Complements physical partitioning rather than replacing it.

Cons:

• Does not fully solve table/index bloat.
• Requires recall semantics for archived data.
• Requires policies for when raw memories can be archived without harming provenance.

Suggested First Step

A balanced first step could be:

1. Define a storage layout abstraction at the bank level.
2. Keep the current shared-table layout as the default.
3. Add a "large bank promotion" design for PostgreSQL: default/shared partition for normal banks, dedicated partitions for promoted banks.
4. Start with memory_units, since it is the core growth table and the primary retrieval/consolidation target.
5. Evaluate whether related tables need immediate partitioning or can stay shared with strong bank_id indexes.
6. Add admin tooling to inspect bank size, index size, recall latency, consolidation backlog, and promotion eligibility.

Open Questions

• Should promotion be automatic, manual, or both?
• What threshold should trigger promotion: memory count, observation count, table/index size, recall latency, consolidation backlog, or write rate?
• Should only memory_units be partitioned first, or should documents, chunks, links, and entity tables be included from the beginning?
• How should existing banks be migrated without downtime?
• How should bank deletion, export, import, and backup interact with dedicated partitions?
• Should PostgreSQL follow a similar bank partitioning model to Oracle, or should the PG implementation favor hybrid/default partitions to avoid partition explosion?
• How should ScaNN, pgvector HNSW, pgvectorscale DiskANN, and VectorChord behave under partitioned or promoted layouts?
• Should this be exposed as an operator/admin feature before being automatic?

Expected Benefits

• Better scalability for single-tenant and large-bank deployments.
• More predictable recall and consolidation performance as total tenant data grows.
• Lower operational risk when deleting, archiving, or reindexing a large bank.
• Cleaner path toward per-bank resource governance.
• More explicit alignment between Hindsight's logical memory hierarchy and its physical database layout.

Non-Goals

• This proposal does not suggest replacing the current shared-table model for all banks.
• This proposal does not require every bank to get its own partition or schema.
• This proposal does not change the public API semantics of banks.
• This proposal is not about tenant isolation, which is already schema-based; it is about physical isolation within a tenant for large banks.