Indexing, partitioning, and the patience data teaches

The system wasn't down, but it was failing. Our new retrieval-augmented generation (RAG) agent, designed to give users answers based on a massive corpus of internal documents, had started to hallucinate. The problem wasn't the model's reasoning; it was its inputs. The retrieval step was so slow that it would time out, feeding the LLM an empty context. My team's first instinct was the modern version of an old reflex: scale the vector database.

It felt like the right move. After all, this was an "AI" problem. But it was also completely wrong. The most expensive, futuristic part of the stack wasn't the bottleneck. The problem was in the most boring, foundational part, and it taught me a lesson I see proven again and again: the principles of patient data architecture are more critical than ever in the age of agentic systems.

The New 'Bigger Box' Fallacy

When a classic web app is slow, the easy answer is to scale the database server. In the AI world, the equivalent is scaling the vector store, adding more replicas, or upgrading to a pricier GPU-powered instance. It’s the path of least resistance. You adjust a slider, the bill gets bigger, and for a short time, the problem seems to go away.

The fallacy, however, remains the same. You're treating the symptom—slowness—without diagnosing the disease. In our case, the disease wasn't the vector search. It was the query we ran just before it: "find all documents matching `tenant_id` and `source_type` from the last 90 days." This query on the document metadata was doing a full scan across a billion-row table. Only after that slow, brutal filter did we pass the resulting IDs to the vector store. We were spending a fortune on an advanced tool while a simple relational query was bleeding us dry.

Finding the Bottleneck with a Boring Index

An index is one of the oldest, most powerful ideas in data management. As Markus Winand's excellent resource Use The Index, Luke! explains, it’s a specialized data structure that allows the database to find rows without reading the whole table. When we looked at the query plan, the truth was embarrassing. The metadata table had no compound index on the columns we were using to filter. The database had no choice but to read everything.

Adding the right index took less than an hour. The impact was immediate. The pre-filter query went from timing out to returning in milliseconds. The end-to-end latency of the RAG agent dropped dramatically, and because it was getting proper context, the quality of its answers soared. We scaled the vector database instance back down, delivering a better system at a lower cost.

Of course, this isn't a free lunch. An index adds a small performance penalty to every write operation and consumes storage. That's the trade-off, and accepting it requires understanding your system's access patterns—a core task of architecture. It’s the earned opinion over the easy fix.

When the Metadata Is the Big Data

For a truly massive system, even a perfect index has limits. As our document table grew by tens of millions of rows a day, the index itself became a colossal B-tree. Maintenance was slow, and queries across wide date ranges still had to traverse a huge amount of the index.

This is where a second, even older principle comes in: partitioning. The idea is to split one giant logical table into many smaller physical tables underneath. For a clear, authoritative explanation, the official PostgreSQL documentation on table partitioning is an excellent primary source. Instead of one ten-terabyte table, you might have hundreds of smaller tables partitioned by date or by tenant.

By partitioning our metadata table by month, a query for the last 90 days became radically more efficient. The query planner knew it only needed to touch three or four small partitions, completely ignoring years of historical data. This required patience. Choosing the wrong partition key can create "hot spots" and make performance worse. It forces you to think deeply about how your data is structured and read, not just how it's written.

Durable Principles for a New Stack

The lesson here isn't just "use indexes." It's that the most durable architectural patterns come from focusing on the fundamentals, especially when working with a trendy new stack. The most sophisticated LLM agent is useless if its deterministic data pipeline is built on a shaky foundation. Its performance, reliability, and cost-curve are all dictated by these "boring" choices.

This is the convergence we're all living through. Building modern AI systems is not a separate discipline from data engineering or software engineering; it is the synthesis of all three. The patient craftsmanship of structuring data well is the bedrock that makes the entire agentic system hold up at 3am.

The Real Takeaway

Before you scale the expensive, fashionable component, look for the bottleneck in the simple, foundational one. Performance issues in complex systems rarely have complex solutions. They almost always trace back to a forgotten first principle.

Master the fundamentals of data structure. Indexing, partitioning, and understanding query plans are not legacy skills. They are the most leveraged tools you have for building performant, cost-effective data and AI systems that last.

Finally, respect the trade-offs. Every architectural decision is a balance of competing needs—read vs. write performance, cost vs. speed, simplicity vs. capability. Honest, clear-eyed analysis of these trade-offs is what separates durable architecture from a temporary fix.