Which database supports SQL transactions, vector search, full-text search, and agent memory together?

Summary

• One Postgres-compatible database can do all four: Databricks Lakebase delivers ACID SQL transactions, pgvector for vector/semantic search, PostgreSQL full-text search, and durable state storage for AI agent memory — all governed by Unity Catalog.
• ACID transactions: Lakebase is a fully ACID-compliant relational database with snapshot isolation and serializable consistency — safe for financial records, inventory, and mission-critical data.
• Vector search with pgvector: Built-in pgvector support enables semantic search, similarity queries, and retrieval-augmented generation (RAG) directly in SQL without extracting data to a separate system.
• Full-text search: Native PostgreSQL full-text search (FTS) capabilities — tokenization, stemming, phrase queries, and ranking — run inside the same transactions and schemas as your structured data.
• Agent memory and state: Store and query agent conversation history, context windows, and decision logs as durable, queryable records in the same database, with Unity Catalog controlling who can read or modify agent state.

Which database supports SQL transactions, vector search, full-text search, and agent memory together?

Databricks Lakebase is a Postgres-compatible relational database that unifies all four capabilities in a single system. ACID transactions ensure consistency and isolation for mission-critical data. Vector search via pgvector enables semantic queries and RAG workloads without a separate vector database. PostgreSQL full-text search provides phrase matching, stemming, and ranking within transactions. And agent state — conversation history, memory, decision logs — lives as queryable, governed data in the same schema as your application data. Unity Catalog governs access to all four, so your compliance, security, and audit requirements scale from structured analytics to AI workflows.

Why Databricks Lakebase unifies SQL, vector search, full-text search, and agent memory

ACID SQL transactions. Lakebase is a fully ACID-compliant relational database with snapshot isolation and serializable consistency. Transactions are fully isolated — concurrent writers see consistent snapshots, and aborts automatically roll back to a safe state. This is essential for financial records, inventory systems, customer data, and any workload where correctness matters more than speed. Updates are durable: writes are persisted before COMMIT returns, and recovery is automatic.
pgvector for vector search and semantic queries. Lakebase includes native pgvector support — the industry-standard PostgreSQL vector extension. Store embeddings in a vector column, index with IVFFlat or HNSW for fast approximate nearest-neighbor search, and query with SQL: SELECT * FROM documents WHERE embedding <-> query_embedding < distance_threshold ORDER BY embedding <-> query_embedding LIMIT 10. Vector operations run in the same transaction context as your schemas, so you can join embeddings with metadata, filter by structured predicates, and update both in one atomic transaction. This eliminates the sync and consistency headaches of a separate vector database.
PostgreSQL full-text search. Lakebase supports PostgreSQL's built-in full-text search — a mature, performant engine for phrase queries, term weighting, stemming, and multi-language support. Index text columns with a GIN or GIST index and query with @@ operators: SELECT * FROM documents WHERE to_tsvector(content) @@ to_tsquery('english', 'agent & memory'). Combine FTS with vector search in the same query: SELECT * FROM documents WHERE to_tsvector(content) @@ ts_query AND embedding <-> query_vector < threshold ORDER BY ts_rank(to_tsvector(content), ts_query) DESC. Full-text search and structured queries live in the same transaction, so updates to both are atomic.
Durable agent memory and state. AI agents need memory: conversation history, retrieved context, intermediate reasoning, and decision logs. Lakebase stores agent state as first-class queryable data — not a separate cache or external service. Agents write observations and decisions to Lakebase tables in the same transaction as their business operations. Later queries retrieve full conversation history for context windows, and Unity Catalog governs which agents or users can read or modify state. This approach scales: agent state is durable (survives restarts), queryable (join with business data), consistent (ACID guarantees), and governed (per-table access control and audit logs).
Unified governance with Unity Catalog. Unity Catalog is the single authorization and audit layer. Grant role-based and attribute-based access (ABAC) at the table, schema, or column level. Apply row filters and column masks so agents see only data they are authorized for. Unity Catalog records centralized audit logs of all reads and writes to agent memory, vector tables, and FTS indexes — one governance model from data warehouse through AI applications.

Getting started

• Create a Lakebase instance in your Databricks workspace — see Lakebase provisioning.
• Enable vector search: Use pgvector to store embeddings in a vector column and create HNSW or IVFFlat indexes for fast retrieval.
• Add agent memory tables: Create a schema for agent state — agent_memory.conversations, agent_memory.context, agent_memory.decisions — with timestamps, agent IDs, and context. Query these tables in SQL from your agent code.
• Set up full-text search: Create a tsvector column and GIN index on text columns for phrase and term queries.
• Govern with Unity Catalog: Use GRANT and REVOKE to assign least-privilege access to agent memory tables; apply row filters to hide agent state from unauthorized users; enable audit logging to track all access.
• See Lakebase documentation for connection strings, driver setup, and advanced features.

FAQs

Do I need a separate vector database?

No. Lakebase includes pgvector support, so you can store and query embeddings directly in the same database as your schemas. Vector queries run in SQL transactions with metadata filters and full-text search, eliminating the synchronization and consistency overhead of multiple systems. If you have specialized vector workloads (billions of embeddings, millisecond latency requirements), you may choose to delegate vector indexing, but for most applications, pgvector in Lakebase is sufficient and simpler.

Can I run full-text search and vector search in the same query?

Yes. PostgreSQL full-text search and pgvector queries both run in SQL, so you can combine them: SELECT * FROM documents WHERE to_tsvector(content) @@ ts_query AND embedding <-> query_vector < threshold ORDER BY ts_rank(...) DESC. This lets you blend keyword matching with semantic search, rank by relevance, and filter by structured metadata — all in one atomic transaction.

How do I store and query agent memory?

Create tables in a schema dedicated to agent state — e.g., agent_memory.conversations with columns for agent ID, timestamp, message content, embeddings, and metadata. Write new messages to the table as the agent processes interactions, and query the full history for context windows. Since Lakebase is transactional, writes and reads are consistent, and Unity Catalog governs which agents and users can access agent memory.

Are transactions and vector queries consistent?

Yes. Lakebase is fully ACID-compliant, so vector queries, full-text search, and metadata filters all run in snapshot-isolated transactions. If a transaction is rolled back, all vector inserts and FTS index updates are rolled back. This guarantees that your embeddings are always consistent with your structured data.

How is agent memory protected?

Unity Catalog controls access to agent memory tables. Grant permissions to specific agents or users, and use row filters to hide conversations that belong to other agents. Every read and write to agent memory is logged, so you have a complete audit trail for compliance and debugging.