Skip to main content

DB Scalability

![[Pasted image 20250914201115.png]]

Phase 1: Optimize Your Single Database​

Before you add more servers, maximize the performance of your current one. These are your first and most important steps.

  1. Indexing: This is critical for your stack.
  • For pgvector: You must create special indexes (like HNSW or IVFflat) on your vector columns. Without them, vector searches will be incredibly slow as your data grows.
  • For Zep & graffiti: Standard PostgreSQL indexes on foreign keys (user_id, session_id) and frequently queried graph properties will ensure that fetching chat history and traversing your graph is fast.
  1. Query Optimization: Use PostgreSQL's EXPLAIN ANALYZE tool to find slow queries. A small change to a query that fetches Zep memory or traverses a graffiti graph can have a huge impact on performance.
  2. Vertical Scaling: This is the simplest scaling method. If you're hitting performance limits, simply upgrade your server's CPU, RAM, and disk speed (IOPS). More RAM is especially beneficial for PostgreSQL as it can cache more data and indexes.
  3. Connection Pooling: Your application will open many connections to the database. PostgreSQL's performance can suffer from managing thousands of connections. A tool like PgBouncer sits between your app and the database, managing a small "pool" of active connections and efficiently handing them out as needed.

Phase 2: Distribute the Read Load​

When a single powerful server isn't enough, the next step is to spread out the work, focusing on read-heavy tasks first.

  1. Replication: This is a core feature of PostgreSQL. You create one or more read-only copies (replicas) of your main (primary) database.
  • Use Case: You can direct all read-heavy operations, like analytics queries on your graffiti graph or vector similarity searches from pgvector, to the replicas. This leaves your primary database free to handle writes (like saving new Zep memories) without getting bogged down.
  1. Caching: Use an in-memory store like Redis or Memcached to store the results of frequent, expensive queries.
  • Use Case: Cache a user's most recent Zep chat history or the results of a common graph query. This avoids hitting the database at all for repeated requests.

Phase 3: Scale for Massive Data and Writes​

When your dataset becomes too large for a single server to store or your write-load is too high, you'll need to partition your data across multiple servers. This is complex and should only be considered when the previous phases are exhausted.

  1. Sharding (Horizontal Partitioning): You split your data across multiple independent databases. Each database (or "shard") holds a subset of the data. For example, you could shard by user_id or tenant_id.
  • Challenge: This is the most complex step. Queries that need data from multiple shards (e.g., a graph query that spans users on different shards) become very difficult to manage. You would likely shard your Zep memory data first, as it's naturally isolated by user.
  1. Vertical Partitioning: Instead of splitting rows, you split tables. You move certain columns into new tables.
  • Use Case: Your pgvector embeddings can be very large. You could move the vector column into its own user_vectors table, separate from a users_core table that has smaller data like user_id and email. This keeps your main user table small and fast for frequent lookups.
  1. Materialized Views: These are pre-computed tables whose contents are the result of a query.
  • Use Case: If you frequently run a complex and slow graffiti query (e.g., "find all users connected by 3 degrees who have discussed 'scalability'"), you can store its result in a materialized view. The view can be refreshed periodically, making the query for your application instantaneous.