Also called User Profile; User Preference Storage; User Preference Modeling, Personalization Layer; Contextual Memory; Persistent Memory
Imagine you have a personal assistant who remembers your coffee order, your preferred meeting times, and your communication style—not just for one conversation, but forever, across every interaction. That’s what “User Preference Storage in PostgreSQL” does for an AI agent. It’s a durable, relational database that stores everything the AI learns about a user, so the AI doesn’t forget you between sessions.
CN: 想象你有一个私人助理，他记得你的咖啡口味、你偏好的会议时间、你的沟通风格——不是只在一场对话中记住，而是永远记住，跨越每一次交互。这就是 PostgreSQL 中的”用户偏好存储”为 AI Agent 做的事情。它是一个持久的、关系型数据库，存储 AI 了解到的关于用户的一切，让 AI 不会在会话之间忘记你。
PostgreSQL has become the overwhelming first choice for AI agent memory in production systems. PostgreSQL is not just “a database” anymore. In the AI world, it has evolved into what many call the “memory layer” for enterprise AI systems
What does it include?
3.1 核心架构 (Core Architecture)
EN: User preference storage in PostgreSQL typically involves:
CN: PostgreSQL 中的用户偏好存储通常包括：
| 组件 (Component) | EN Description | CN Description |
|---|---|---|
| Users Table | Stores basic user identity (user_id, created_at, etc.) | 存储用户基本身份信息 |
| Preferences Table | Stores key-value pairs of user preferences | 存储键值对的用户偏好 |
| Memory/History Table | Stores interaction history with timestamps | 存储带时间戳的交互历史 |
| Vector Embeddings (pgvector) | Stores semantic embeddings for similarity search | 存储用于相似性搜索的语义向量 |
3.2 偏好数据类型 (Types of Preferences)
EN: What kinds of preferences do we store?
CN: 我们存储哪些类型的偏好？
| Type | EN Examples | CN Examples |
|---|---|---|
| Explicit | “I prefer Python over R”, “I like detailed answers” | “我偏好 Python 而不是 R”，”我喜欢详细的回答” |
| Implicit | Inferred from behavior (e.g., always asks for code examples) | 从行为推断（例如，总是要求代码示例） |
| Temporal | “I’m usually available after 2 PM” | “我通常在下午 2 点后有空” |
| Contextual | “For data questions, give SQL; for strategy, give summaries” | “数据问题给 SQL；战略问题给摘要” |
Key Takeaways
| 要点 (Key Point) | EN | CN |
|---|---|---|
| PostgreSQL作为长期记忆存储 | PostgreSQL provides durable, ACID-compliant storage for user preferences across sessions | PostgreSQL 为跨会话的用户偏好提供持久的、ACID 合规的存储 |
| JSONB vs EAV | Use JSONB for flexible, schema-less preferences with GIN indexes | 使用 JSONB 实现灵活的、无 Schema 的偏好存储，配合 GIN 索引 |
| pgvector for语义记忆 | pgvector enables semantic similarity search on memory embeddings | pgvector 支持对记忆嵌入进行语义相似性搜索 |
| 异步连接池 | Use asyncpg connection pool for scalable, non-blocking operations | 使用 asyncpg 连接池实现可扩展的非阻塞操作 |
| UPSERT模式 | Use INSERT … ON CONFLICT DO UPDATE for atomic upsert | 使用 INSERT … ON CONFLICT DO UPDATE 实现原子性 upsert |
| 部分更新 | Use jsonb_set() to update individual preference keys without full document replacement | 使用 jsonb_set() 更新单个偏好键，无需完整替换文档 |
| 三层记忆架构 | Preferences (JSONB) + Semantic (pgvector) + Episodic (interactions) | 偏好 (JSONB) + 语义 (pgvector) + 情景 (交互) |
| 生产就绪 | Connection pooling, transaction management, error handling are essential | 连接池、事务管理、错误处理是生产环境必需的 |