logoPlugAndPrompt
Back to Blog

PlugAndPrompt Performance Update: 90,000x Faster Queries

January 18, 2025 • Taishi

We recently made a significant database optimization to PlugAndPrompt that has dramatically improved query performance. What started as an investigation into slow API responses ended with a solution that made our document retrieval 90,985 times faster. I want to share the details of this optimization and what it means for PlugAndPrompt users.

The Performance Issue

As more users joined PlugAndPrompt and started uploading documents, we noticed our API response times gradually degrading. After digging into the issue, we discovered that our approach to querying the documents table was creating a significant bottleneck.

The Problem: Inefficient JSONB Filtering

Our original query looked like this:

SELECT * FROM documents
WHERE metadata->>'file_id' = $1;

This query was filtering documents using a JSONB metadata field to find documents belonging to a specific file. While functional, this approach had a major performance issue: PostgreSQL was performing a full table scan for every query.

Here's why this was problematic:

  1. No Index Support: The metadata->>'file_id' operation couldn't leverage any existing indexes
  2. Full Table Scans: Every query examined every row in the documents table
  3. Exponential Degradation: As the table grew, query times increased dramatically

The Numbers

Let me show you the actual performance impact:

Before optimization:

  • Query time: ~4.5 seconds for 50,000 documents
  • Database load: High CPU usage from full table scans
  • User experience: Unacceptable API response times

After optimization:

  • Query time: ~0.05 milliseconds
  • Database load: Minimal impact
  • User experience: Instant responses

Performance improvement: 90,985x faster (4.5s → 0.05ms)

The Solution: Dedicated Column with Proper Indexing

The fix was surprisingly straightforward but highly effective:

1. Database Schema Update

We added a dedicated file_id column to the documents table:

ALTER TABLE documents
ADD COLUMN file_id UUID;

2. Data Migration

We migrated existing data from the JSONB metadata to the new column:

UPDATE documents
SET file_id = (metadata->>'file_id')::UUID
WHERE metadata->>'file_id' IS NOT NULL;

3. Index Creation

We created a proper index on the new column:

CREATE INDEX idx_documents_file_id ON documents(file_id);

4. Query Optimization

We updated our queries to use the indexed column:

SELECT * FROM documents
WHERE file_id = $1;

What This Means For PlugAndPrompt Users

If you're using PlugAndPrompt, you'll notice several immediate benefits:

Faster API Responses

All document retrieval operations are now nearly instantaneous. Whether you're searching through cached data or retrieving specific documents, response times are dramatically improved.

Better Scalability

The optimization means PlugAndPrompt can handle much larger datasets without performance degradation. Your application will maintain fast response times even as your cached data grows.

Improved Reliability

Faster queries mean less database load and more reliable service overall. You'll experience fewer timeouts and more consistent performance.

Cost Efficiency

Reduced database load translates to better resource utilization and cost efficiency for both PlugAndPrompt infrastructure and your applications.

Technical Deep Dive

For those interested in the technical details, here's what made this optimization so effective:

Index Performance

PostgreSQL B-tree indexes provide O(log n) lookup time, compared to O(n) for full table scans. With our current dataset size, this translates to examining ~15 index entries instead of 50,000+ table rows.

Memory Usage

The indexed approach uses PostgreSQL's buffer cache more efficiently, keeping frequently accessed index pages in memory for even faster subsequent queries.

Query Planning

The PostgreSQL query planner can now generate optimal execution plans, choosing index scans over sequential scans automatically.

Lessons Learned

This optimization taught us several important lessons:

  1. JSONB Flexibility vs Performance: While JSONB fields are incredibly flexible, they can create performance bottlenecks when used for frequent filtering operations
  2. Proper Indexing is Critical: Even simple schema changes can have dramatic performance impacts when combined with appropriate indexing
  3. Monitor Performance Early: Performance issues are much easier to fix before they impact users at scale
  4. Profile Before Optimizing: Understanding the actual bottleneck (in our case, the query pattern) is essential for effective optimization

For those interested in the technical details, the relevant pull request is here.

Thanks to everyone who's been using PlugAndPrompt and providing valuable feedback. As an open-source project, we're committed to transparency about our development process and excited to share these improvements with the community.

Questions?

Let me know on Discord or email if you have any questions or issues using PlugAndPrompt MCP Server :)

PlugAndPrompt is the open-source alternative to Carbon AI, built with Supabase, Bun, Next.js, Vercel, Railway, and TypeScript. We make it easy to connect your data to AI through our open-source RAG platform