Amazon Aurora pgvector meets ltree: ChatScroll's dual-DB architecture for RAG

A hands-on evaluation of ChatScroll's hybrid search architecture, combining PostgreSQL's pgvector, ltree, and tsvector extensions alongside DynamoDB to solve the ephemeral AI chat problem.

The Answer Up Front

ChatScroll is a highly pragmatic blueprint for developers building personal or enterprise knowledge bases. It is designed for teams that need hierarchical folder structures and hybrid semantic-keyword search without the operational overhead of a dedicated vector database.

You should skip this architecture if your dataset is flat, small, or easily handled by a simple SQLite-vec setup. The dual-database synchronization between PostgreSQL and DynamoDB adds unnecessary complexity for low-volume applications.

The bottom line is that ChatScroll demonstrates how a mature database like Amazon Aurora PostgreSQL, when configured with the right extensions, can easily replace specialized vector databases for complex RAG applications.

Methodology

This review evaluates ChatScroll (v0.1, observed June 27, 2026), an open-source personal AI knowledge base built by developer Hamza (known as hamzanabdev) for the AWS H0 Hackathon. The live application is accessible at https://chatscroll.vercel.app.

Our analysis is based on the technical architecture, SQL schema, and database design patterns published by the author. Because we have not run independent load tests on Hamza's live production database, performance claims regarding query latency and scaling are treated as unverified. This review focuses on the architectural validity of combining three specific PostgreSQL extensions alongside a DynamoDB caching layer. We do not cover long-term data consistency between the two databases or edge-case failures of the DynamoDB Time-to-Live (TTL) mechanism.

Scoped semantic search with pgvector

ChatScroll uses the pgvector extension on Amazon Aurora PostgreSQL to store and query 3072-dimensional vector embeddings. When a user saves an AI response (a "Scroll"), the application generates an embedding using Google's gemini-embedding-001 model.

The application runs semantic queries using cosine distance. To prevent irrelevant results from surfacing, the query uses a hardcoded similarity threshold:

-- Semantic search with threshold
WHERE 1 - (embedding <=> $queryVec) > 0.5 
ORDER BY embedding <=> $queryVec 
LIMIT 5

This ensures that only documents with a cosine similarity score greater than 0.5 are returned, ranking the top five closest matches.

Hierarchical folders via ltree

To organize saved Scrolls, ChatScroll uses PostgreSQL's native ltree extension. This extension represents folder hierarchies as dot-separated labels, such as programming.containers.docker.

Using ltree allows the system to perform subtree queries (for example, finding all scrolls within programming and its subfolders) in a single query. This approach avoids the complex recursive Common Table Expressions (CTEs) typically required to query hierarchical data in relational databases.

Keyword ranking with tsvector

While semantic search excels at conceptual matching, it often fails at exact keyword matching (like searching for a specific error code or variable name). ChatScroll addresses this by using the tsvector extension.

The system indexes the text content for full-text search and uses the ts_rank function to score keyword relevance. By combining tsvector with pgvector, the application lays the groundwork for hybrid search, allowing users to find documents using either exact terms or conceptual meaning.

The dual database split

A key architectural decision in ChatScroll is the separation of concerns between Amazon Aurora PostgreSQL and Amazon DynamoDB.

Aurora PostgreSQL handles structured, relational data. This includes user accounts linked via AWS Cognito, the folder hierarchy managed by ltree, and the saved Scrolls with their corresponding 3072-dimensional embeddings.

DynamoDB handles the high-volume, ephemeral chat stream. Each message is stored with a partition key of conversationId and a sort key of timestamp#messageId. Chat messages are configured with a 90-day TTL for auto-expiry and run on pay-per-request billing. This separation keeps the primary relational database lean and optimized for complex search queries.

What is interesting: the ltree integration

The most compelling aspect of ChatScroll is the combination of ltree and pgvector. Most RAG tutorials assume a flat namespace where semantic search queries the entire database. In production, users expect to search within specific folders. By combining these two extensions, ChatScroll can run scoped semantic queries (searching only within a specific folder branch) natively within PostgreSQL.

The dual-database split is also highly practical. Offloading chat history to DynamoDB with a 90-day TTL prevents Aurora's storage and memory from being choked by millions of conversational messages, keeping the expensive relational database reserved for high-value knowledge assets.

What is missing: hybrid fusion details

The primary limitation of the current implementation is the lack of a formal hybrid search fusion algorithm. While the author notes that tsvector and pgvector are both present, the provided code snippets do not show how the semantic and keyword scores are combined. In a production-grade system, simply running these queries separately is insufficient. Teams typically must implement Reciprocal Rank Fusion (RRF) or use a cross-encoder model to re-rank the combined results.

Additionally, the hardcoded cosine similarity threshold of 0.5 is fragile. Embedding models are highly sensitive, and a static threshold often leads to false negatives when using different models or when querying highly technical jargon.

Pricing snapshot

ChatScroll is a hackathon project with a live demo. The underlying AWS infrastructure costs as of June 2026 are structured as follows:

• Amazon Aurora PostgreSQL Serverless v2: Starts at approximately $0.12 per ACU-hour (Aurora Capacity Unit), scaling dynamically based on load.
• Amazon DynamoDB: Pay-per-request tier priced at $0.25 per million write request units and $0.25 per GB-month of storage.
• Google Gemini API: The gemini-embedding-001 model offers a free tier, with pay-as-you-go pricing at $0.025 per million characters.

Verdict

ChatScroll is a highly successful demonstration of how to build a production-grade RAG backend without adopting a complex, single-purpose vector database. By using the mature PostgreSQL ecosystem (specifically pgvector, ltree, and tsvector), Hamza has built a system that handles vector search, hierarchical organization, and keyword matching in a single database engine.

We recommend this architecture for teams already running on AWS who want to keep operational complexity low. Skip this specific dual-DB setup only if your application does not require nested folder structures or if your chat volume is low enough to be safely stored directly in PostgreSQL.

What we would test next

In a v2 evaluation, we would benchmark the latency of the hybrid query when combining ts_rank and pgvector cosine similarity on a dataset of 100,000 documents. We would also test how the 0.5 similarity threshold performs across different domains, such as medical literature versus source code, to determine if a dynamic thresholding mechanism is required. Finally, we would measure the cold-start latency of Aurora Serverless v2 when handling sporadic user queries.

The investor read

ChatScroll highlights a broader market shift: the consolidation of vector search into relational databases. For the vast majority of enterprise use cases, specialized vector databases are an unnecessary operational tax. By demonstrating that pgvector can be paired with ltree and tsvector to handle complex, scoped, and hierarchical queries, this project illustrates why startups are increasingly defaulting to PostgreSQL for RAG. From an investment perspective, pure-play vector databases are losing their moat to mature, multi-model engines. The investable opportunity here lies not in specialized vector storage, but in the orchestration, ingestion, and metadata-management layers that sit on top of unified databases like Amazon Aurora.

Pull quote: “The bottom line is that ChatScroll demonstrates how a mature database like Amazon Aurora PostgreSQL, when configured with the right extensions, can easily replace specialized vector databases for complex RAG applications.”