<?xml version="1.0" encoding="UTF-8"?>
<rss version="2.0" xmlns:atom="http://www.w3.org/2005/Atom" xmlns:dc="http://purl.org/dc/elements/1.1/">
  <channel>
    <title>DEV Community: tanmay kuamr chaurasia</title>
    <description>The latest articles on DEV Community by tanmay kuamr chaurasia (@tanmay2627).</description>
    <link>https://dev.to/tanmay2627</link>
    <image>
      <url>https://media2.dev.to/dynamic/image/width=90,height=90,fit=cover,gravity=auto,format=auto/https:%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Fuser%2Fprofile_image%2F3988718%2F5f2733c3-92ca-4e47-b708-9e60cf220f8d.jpg</url>
      <title>DEV Community: tanmay kuamr chaurasia</title>
      <link>https://dev.to/tanmay2627</link>
    </image>
    <atom:link rel="self" type="application/rss+xml" href="https://dev.to/feed/tanmay2627"/>
    <language>en</language>
    <item>
      <title>Why Your API Is Slow: Understanding and Fixing N+1 Queries</title>
      <dc:creator>tanmay kuamr chaurasia</dc:creator>
      <pubDate>Sat, 21 Nov 2026 11:01:32 +0000</pubDate>
      <link>https://dev.to/tanmay2627/why-your-api-is-slow-understanding-and-fixing-n1-queries-24o3</link>
      <guid>https://dev.to/tanmay2627/why-your-api-is-slow-understanding-and-fixing-n1-queries-24o3</guid>
      <description>&lt;p&gt;When an API endpoint starts responding in seconds rather than milliseconds, our first instinct as backend engineers is often to blame the infrastructure. We think about throwing more CPU cores at the server, increasing database connection pools, or scaling out instances.&lt;/p&gt;

&lt;p&gt;However, server hardware is rarely the real bottleneck. In most cases, an API is slow because it is making 500 database round trips when it only needed to make one.&lt;/p&gt;

&lt;p&gt;This performance issue is known as the &lt;strong&gt;N+1 query problem&lt;/strong&gt;, and it creeps into almost every application that uses an Object-Relational Mapper (ORM) to handle related database records.&lt;/p&gt;

&lt;h2&gt;
  
  
  What is the N+1 Query Problem?
&lt;/h2&gt;

&lt;p&gt;The pattern usually looks innocent in code. Suppose you want to return a list of 50 books along with each book's author.&lt;/p&gt;

&lt;ol&gt;
&lt;li&gt;You execute &lt;strong&gt;1 query&lt;/strong&gt; to fetch the list of 50 books (&lt;code&gt;SELECT * FROM books;&lt;/code&gt;).&lt;/li&gt;
&lt;li&gt;You loop through the 50 books to serialize them.&lt;/li&gt;
&lt;li&gt;Inside the loop, accessing &lt;code&gt;book.author&lt;/code&gt; triggers &lt;strong&gt;1 additional query per book&lt;/strong&gt; (&lt;code&gt;SELECT * FROM authors WHERE id = ?;&lt;/code&gt;).&lt;/li&gt;
&lt;/ol&gt;

&lt;p&gt;That results in 1 + 50 = &lt;strong&gt;51 separate queries&lt;/strong&gt;.&lt;/p&gt;

&lt;p&gt;Even if each query takes a modest 5ms to execute locally, network latency, connection overhead, and query execution add up quickly in production environments. Fifty sequential queries taking 20ms of round-trip latency each add an entire second of delay to a single HTTP request.&lt;/p&gt;

&lt;p&gt;Here is what the problematic pattern looks like in a Python backend:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight python"&gt;&lt;code&gt;&lt;span class="c1"&gt;# BAD: Triggers 1 query for books, plus N queries for authors&lt;/span&gt;
&lt;span class="k"&gt;def&lt;/span&gt; &lt;span class="nf"&gt;get_books_naive&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt;
    &lt;span class="n"&gt;books&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="nc"&gt;Book&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;objects&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nb"&gt;all&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
    &lt;span class="k"&gt;return&lt;/span&gt; &lt;span class="p"&gt;[&lt;/span&gt;
        &lt;span class="p"&gt;{&lt;/span&gt;
            &lt;span class="s2"&gt;"title"&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="n"&gt;book&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;title&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
            &lt;span class="s2"&gt;"author"&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="n"&gt;book&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;author&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;name&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;  &lt;span class="c1"&gt;# Triggers a DB query on every iteration!&lt;/span&gt;
        &lt;span class="p"&gt;}&lt;/span&gt;
        &lt;span class="k"&gt;for&lt;/span&gt; &lt;span class="n"&gt;book&lt;/span&gt; &lt;span class="k"&gt;in&lt;/span&gt; &lt;span class="n"&gt;books&lt;/span&gt;
    &lt;span class="p"&gt;]&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;h2&gt;
  
  
  How to Fix N+1 Queries Step by Step
&lt;/h2&gt;

&lt;p&gt;Fixing N+1 queries is usually straightforward once you identify them. Here is a practical workflow to catch and fix them.&lt;/p&gt;

&lt;h3&gt;
  
  
  Step 1: Detect with Query Logging
&lt;/h3&gt;

&lt;p&gt;Turn on query logging in your development environment or use profiling tools like &lt;code&gt;django-debug-toolbar&lt;/code&gt;, &lt;code&gt;SQLAlchemy&lt;/code&gt; event listeners, or APM services. If you see the exact same query structure repeating tens or hundreds of times with slightly different IDs, you have caught an N+1 query.&lt;/p&gt;

&lt;h3&gt;
  
  
  Step 2: Use Eager Loading
&lt;/h3&gt;

&lt;p&gt;Instead of lazily loading related data item-by-item inside a loop, instruct your ORM to fetch all related records up front in a single database trip using SQL &lt;code&gt;JOIN&lt;/code&gt;s or prefetching.&lt;/p&gt;

&lt;p&gt;In Django, use &lt;code&gt;select_related&lt;/code&gt; for foreign keys and &lt;code&gt;prefetch_related&lt;/code&gt; for many-to-many relationships. In Ruby on Rails, use &lt;code&gt;.includes()&lt;/code&gt;; in SQLAlchemy, use &lt;code&gt;joinedload()&lt;/code&gt;.&lt;/p&gt;

&lt;p&gt;Here is the corrected code:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight python"&gt;&lt;code&gt;&lt;span class="c1"&gt;# GOOD: Executes a JOIN under the hood, retrieving everything in 1 query&lt;/span&gt;
&lt;span class="k"&gt;def&lt;/span&gt; &lt;span class="nf"&gt;get_books_optimized&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt;
    &lt;span class="n"&gt;books&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="nc"&gt;Book&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;objects&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;select_related&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="s2"&gt;"author"&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nb"&gt;all&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
    &lt;span class="k"&gt;return&lt;/span&gt; &lt;span class="p"&gt;[&lt;/span&gt;
        &lt;span class="p"&gt;{&lt;/span&gt;
            &lt;span class="s2"&gt;"title"&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="n"&gt;book&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;title&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
            &lt;span class="s2"&gt;"author"&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="n"&gt;book&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;author&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;name&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;  &lt;span class="c1"&gt;# Data is already loaded in memory&lt;/span&gt;
        &lt;span class="p"&gt;}&lt;/span&gt;
        &lt;span class="k"&gt;for&lt;/span&gt; &lt;span class="n"&gt;book&lt;/span&gt; &lt;span class="k"&gt;in&lt;/span&gt; &lt;span class="n"&gt;books&lt;/span&gt;
    &lt;span class="p"&gt;]&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;By adding &lt;code&gt;select_related("author")&lt;/code&gt;, the ORM performs a join (&lt;code&gt;SELECT books.*, authors.* FROM books JOIN authors...&lt;/code&gt;), reducing 51 network trips down to &lt;strong&gt;1&lt;/strong&gt;.&lt;/p&gt;

&lt;h3&gt;
  
  
  Step 3: Prevent Regressions with Query Count Tests
&lt;/h3&gt;

&lt;p&gt;The hardest part about N+1 queries isn't fixing them—it's preventing someone from reintroducing them during a future refactor.&lt;/p&gt;

&lt;p&gt;Set a query budget for your API endpoints. A list endpoint should rarely need more than 5 to 10 queries, regardless of how many items are returned. You can enforce this directly in your automated test suite:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight python"&gt;&lt;code&gt;&lt;span class="k"&gt;from&lt;/span&gt; &lt;span class="n"&gt;django&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;test&lt;/span&gt; &lt;span class="k"&gt;import&lt;/span&gt; &lt;span class="nc"&gt;TestCase&lt;/span&gt;

&lt;span class="k"&gt;class&lt;/span&gt; &lt;span class="nc"&gt;BookAPITestCase&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="nc"&gt;TestCase&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt;
    &lt;span class="k"&gt;def&lt;/span&gt; &lt;span class="nf"&gt;test_books_list_query_count&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="nb"&gt;self&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt;
        &lt;span class="c1"&gt;# Assert that fetching the book list stays within a fixed query budget&lt;/span&gt;
        &lt;span class="k"&gt;with&lt;/span&gt; &lt;span class="nb"&gt;self&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;assertNumQueries&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="mi"&gt;2&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt;
            &lt;span class="n"&gt;response&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="nb"&gt;self&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;client&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;get&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="s2"&gt;"/api/v1/books/"&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
            &lt;span class="nb"&gt;self&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;assertEqual&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;response&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;status_code&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="mi"&gt;200&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;If a developer accidentally accesses an un-prefetched relationship in the future, the unit test immediately fails in CI before the code hits production.&lt;/p&gt;

&lt;h2&gt;
  
  
  Conclusion
&lt;/h2&gt;

&lt;p&gt;Performance optimization is detective work before it is code work. Before upgrading cloud server specs, log your queries, apply eager loading, and set up query count tests to protect your endpoints.&lt;/p&gt;

&lt;h2&gt;
  
  
  About Me
&lt;/h2&gt;

&lt;p&gt;Tanmay builds AI agents and automation for businesses and is open to freelance projects. Feel free to connect on &lt;a href="https://www.linkedin.com/" rel="noopener noreferrer"&gt;LinkedIn&lt;/a&gt; to discuss backend optimizations, system architecture, or automation projects.&lt;/p&gt;

</description>
      <category>backend</category>
      <category>database</category>
      <category>performance</category>
      <category>python</category>
    </item>
    <item>
      <title>Stop Using Monolithic Prompts: Move to Multi-Agent Systems</title>
      <dc:creator>tanmay kuamr chaurasia</dc:creator>
      <pubDate>Sat, 21 Nov 2026 10:41:48 +0000</pubDate>
      <link>https://dev.to/tanmay2627/stop-using-monolithic-prompts-move-to-multi-agent-systems-1alp</link>
      <guid>https://dev.to/tanmay2627/stop-using-monolithic-prompts-move-to-multi-agent-systems-1alp</guid>
      <description>&lt;p&gt;If you have built production LLM applications, you have likely encountered the monolithic prompt trap. You start with a simple prompt that answers user queries. Then you add instructions to format output as JSON. Later, you ask it to query a database first, run external search tools when necessary, and format the final output in Markdown.&lt;/p&gt;

&lt;p&gt;Before long, your system prompt grows to thousands of tokens. It works 80% of the time, but the remaining 20% suffers from hallucinations, ignored instructions, and unpredictable failures. &lt;/p&gt;

&lt;p&gt;Expecting a single agent to handle parsing, context retrieval, decision-making, tool execution, and response formatting is the AI equivalent of hiring one developer to handle sales, customer support, and infrastructure operations alone. It works initially—until scale exposes the cracks.&lt;/p&gt;

&lt;h2&gt;
  
  
  The Microservices Parallel
&lt;/h2&gt;

&lt;p&gt;In backend engineering, we solved this problem years ago by moving from monoliths to microservices. Instead of placing every business capability inside a single code deployment, we split functionality into isolated, single-responsibility services that communicate over defined protocols.&lt;/p&gt;

&lt;p&gt;Multi-agent AI systems follow this exact paradigm. Instead of relying on one massive LLM call to handle end-to-end reasoning, you divide the workflow into specialized, lightweight agents. Each agent operates with a narrow system prompt, a restricted toolset, and a strict input/output schema.&lt;/p&gt;

&lt;h2&gt;
  
  
  Deconstructing the Multi-Agent Pattern
&lt;/h2&gt;

&lt;p&gt;A reliable multi-agent architecture splits responsibilities into clear operational layers:&lt;/p&gt;

&lt;ol&gt;
&lt;li&gt;
&lt;strong&gt;Input Parser / Researcher&lt;/strong&gt;: Ingests raw user input, strips noise, and extracts key entities or performs vector retrieval.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Router / Decision Agent&lt;/strong&gt;: Evaluates the structured data and routes the task to the appropriate workflow.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Executor Agent&lt;/strong&gt;: Performs the specific task—such as executing an API call, running a SQL query, or building the final response—using only the relevant context.&lt;/li&gt;
&lt;/ol&gt;

&lt;p&gt;Information flows between agents using standard data structures, such as JSON schemas passed over HTTP or internal queues.&lt;/p&gt;

&lt;h2&gt;
  
  
  Implementing a Multi-Agent Flow in Python
&lt;/h2&gt;

&lt;p&gt;Here is a practical pattern showing how to decouple task routing from execution using Python and Pydantic for schema validation.&lt;/p&gt;

&lt;p&gt;First, define the state contract and the Router Agent:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight python"&gt;&lt;code&gt;&lt;span class="k"&gt;from&lt;/span&gt; &lt;span class="n"&gt;enum&lt;/span&gt; &lt;span class="nf"&gt;import&lt;/span&gt; &lt;span class="nc"&gt;Enum&lt;/span&gt;
&lt;span class="k"&gt;from&lt;/span&gt; &lt;span class="n"&gt;typing&lt;/span&gt; &lt;span class="k"&gt;import&lt;/span&gt; &lt;span class="nc"&gt;Any&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="nc"&gt;Dict&lt;/span&gt;
&lt;span class="k"&gt;from&lt;/span&gt; &lt;span class="n"&gt;pydantic&lt;/span&gt; &lt;span class="k"&gt;import&lt;/span&gt; &lt;span class="nc"&gt;BaseModel&lt;/span&gt;

&lt;span class="k"&gt;class&lt;/span&gt; &lt;span class="nc"&gt;TaskCategory&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="kt"&gt;str&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="nc"&gt;Enum&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt;
    &lt;span class="no"&gt;SUPPORT&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="s2"&gt;"support"&lt;/span&gt;
    &lt;span class="no"&gt;BILLING&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="s2"&gt;"billing"&lt;/span&gt;
    &lt;span class="no"&gt;TECHNICAL&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="s2"&gt;"technical"&lt;/span&gt;

&lt;span class="k"&gt;class&lt;/span&gt; &lt;span class="nc"&gt;RouterOutput&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="nc"&gt;BaseModel&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt;
    &lt;span class="n"&gt;category&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="nc"&gt;TaskCategory&lt;/span&gt;
    &lt;span class="n"&gt;priority&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="kt"&gt;str&lt;/span&gt;
    &lt;span class="n"&gt;extracted_entities&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="nc"&gt;Dict&lt;/span&gt;&lt;span class="p"&gt;[&lt;/span&gt;&lt;span class="kt"&gt;str&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="nc"&gt;Any&lt;/span&gt;&lt;span class="p"&gt;]&lt;/span&gt;

&lt;span class="k"&gt;def&lt;/span&gt; &lt;span class="nf"&gt;router_agent&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;user_input&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="kt"&gt;str&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="o"&gt;-&amp;gt;&lt;/span&gt; &lt;span class="nc"&gt;RouterOutput&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt;
    &lt;span class="c1"&gt;# In production, this uses an LLM call enforcing a JSON schema&lt;/span&gt;
    &lt;span class="k"&gt;return&lt;/span&gt; &lt;span class="nc"&gt;RouterOutput&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;
        &lt;span class="n"&gt;category&lt;/span&gt;&lt;span class="o"&gt;=&lt;/span&gt;&lt;span class="nc"&gt;TaskCategory&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="no"&gt;BILLING&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
        &lt;span class="n"&gt;priority&lt;/span&gt;&lt;span class="o"&gt;=&lt;/span&gt;&lt;span class="s2"&gt;"high"&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
        &lt;span class="n"&gt;extracted_entities&lt;/span&gt;&lt;span class="o"&gt;=&lt;/span&gt;&lt;span class="p"&gt;{&lt;/span&gt;&lt;span class="s2"&gt;"invoice_id"&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="s2"&gt;"INV-9402"&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="s2"&gt;"issue"&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="s2"&gt;"overcharge"&lt;/span&gt;&lt;span class="p"&gt;}&lt;/span&gt;
    &lt;span class="p"&gt;)&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;Next, implement the downstream Executor Agent, which only accepts validated input from the router:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight python"&gt;&lt;code&gt;&lt;span class="k"&gt;class&lt;/span&gt; &lt;span class="nc"&gt;ExecutionResult&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="nc"&gt;BaseModel&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt;
    &lt;span class="n"&gt;success&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="kt"&gt;bool&lt;/span&gt;
    &lt;span class="n"&gt;message&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="kt"&gt;str&lt;/span&gt;

&lt;span class="k"&gt;def&lt;/span&gt; &lt;span class="nf"&gt;billing_executor_agent&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;router_data&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="nc"&gt;RouterOutput&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="o"&gt;-&amp;gt;&lt;/span&gt; &lt;span class="nc"&gt;ExecutionResult&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt;
    &lt;span class="n"&gt;invoice_id&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="n"&gt;router_data&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;extracted_entities&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;get&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="s2"&gt;"invoice_id"&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
    &lt;span class="n"&gt;issue&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="n"&gt;router_data&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;extracted_entities&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;get&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="s2"&gt;"issue"&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;

    &lt;span class="c1"&gt;# Run targeted execution logic or a micro-prompt tailored to billing&lt;/span&gt;
    &lt;span class="k"&gt;print&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;f&lt;/span&gt;&lt;span class="s2"&gt;"Executing billing pipeline for invoice {invoice_id} ({issue})"&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;

    &lt;span class="k"&gt;return&lt;/span&gt; &lt;span class="nc"&gt;ExecutionResult&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;
        &lt;span class="n"&gt;success&lt;/span&gt;&lt;span class="o"&gt;=&lt;/span&gt;&lt;span class="kc"&gt;True&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
        &lt;span class="n"&gt;message&lt;/span&gt;&lt;span class="o"&gt;=&lt;/span&gt;&lt;span class="n"&gt;f&lt;/span&gt;&lt;span class="s2"&gt;"Processed issue for invoice {invoice_id}. Support team alerted."&lt;/span&gt;
    &lt;span class="p"&gt;)&lt;/span&gt;

&lt;span class="c1"&gt;# Pipeline Orchestrator&lt;/span&gt;
&lt;span class="n"&gt;user_query&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="s2"&gt;"I was charged twice on invoice INV-9402, please fix this!"&lt;/span&gt;
&lt;span class="n"&gt;routed_state&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="nf"&gt;router_agent&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;user_query&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;

&lt;span class="k"&gt;if&lt;/span&gt; &lt;span class="n"&gt;routed_state&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;category&lt;/span&gt; &lt;span class="o"&gt;==&lt;/span&gt; &lt;span class="nc"&gt;TaskCategory&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="no"&gt;BILLING&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt;
    &lt;span class="n"&gt;result&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="nf"&gt;billing_executor_agent&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;routed_state&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
    &lt;span class="k"&gt;print&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;result&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;message&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;h2&gt;
  
  
  Why Coordination Beats Intelligence
&lt;/h2&gt;

&lt;p&gt;The primary advantage of multi-agent design is not raw model intelligence—it is deterministic control over system state. &lt;/p&gt;

&lt;p&gt;By isolating agent responsibilities:&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;
&lt;strong&gt;Debugging becomes straightforward&lt;/strong&gt;: If routing fails, you adjust the router agent without breaking response generation.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Latency decreases&lt;/strong&gt;: Smaller system prompts consume fewer input tokens and process faster.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Cost control improves&lt;/strong&gt;: You can send routine parsing tasks to lightweight models like GPT-4o-mini and reserve larger models for complex logic.&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;Instead of building hyper-complex prompts, focus on building clean communication layers between small, focused agents.&lt;/p&gt;

&lt;h2&gt;
  
  
  About Me
&lt;/h2&gt;

&lt;p&gt;Tanmay builds AI agents and automation for businesses and is open to freelance projects. If you are building agentic workflows or scaling AI infrastructure, feel free to connect on &lt;a href="https://www.linkedin.com/in/tanmay-kumar-chaurasia/" rel="noopener noreferrer"&gt;LinkedIn&lt;/a&gt;.&lt;/p&gt;

</description>
      <category>ai</category>
      <category>architecture</category>
      <category>python</category>
      <category>backend</category>
    </item>
    <item>
      <title>Why You Should Replace Monolithic Prompts with Multi-Agent Architecture</title>
      <dc:creator>tanmay kuamr chaurasia</dc:creator>
      <pubDate>Sat, 21 Nov 2026 10:37:18 +0000</pubDate>
      <link>https://dev.to/tanmay2627/why-you-should-replace-monolithic-prompts-with-multi-agent-architecture-2fnf</link>
      <guid>https://dev.to/tanmay2627/why-you-should-replace-monolithic-prompts-with-multi-agent-architecture-2fnf</guid>
      <description>&lt;p&gt;When building AI-powered automation, the temptation to create a "god prompt" is strong. You write a 1,000-word prompt instructing an LLM to parse incoming customer requests, classify sentiment, look up account details, decide on an action, and draft a personalized response—all in a single API call.&lt;/p&gt;

&lt;p&gt;At first, it works. But as your business logic expands, this single-agent monolith starts to break down. The model misses instructions, hallucinates edge cases, and consumes massive amounts of token context for simple sub-tasks.&lt;/p&gt;

&lt;p&gt;This is the exact reason software engineering moved away from massive backend monoliths toward microservices: specialized units connected by clear interfaces outperform a single component trying to handle everything.&lt;/p&gt;

&lt;h2&gt;
  
  
  The Problem with Monolithic Prompts
&lt;/h2&gt;

&lt;p&gt;Single-prompt systems force the model to handle context retrieval, input validation, logic branching, and response generation simultaneously. This creates three primary issues:&lt;/p&gt;

&lt;ol&gt;
&lt;li&gt;
&lt;strong&gt;High Latency and Cost&lt;/strong&gt;: Sending a massive context window on every iteration burns tokens unnecessarily.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Brittle Prompt Engineering&lt;/strong&gt;: Adding instructions for a new edge case often breaks existing behavior in unpredictable ways.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Hard to Debug&lt;/strong&gt;: When the final output is wrong, it is difficult to identify whether the failure happened during parsing, reasoning, or execution.&lt;/li&gt;
&lt;/ol&gt;

&lt;h2&gt;
  
  
  The Multi-Agent Fix
&lt;/h2&gt;

&lt;p&gt;A multi-agent system breaks down complex workflows into small, deterministic steps executed by specialized agents. Instead of one agent guessing its way through five tasks, you assign each task to a dedicated agent with a focused prompt and clear output schema.&lt;/p&gt;

&lt;p&gt;A typical multi-agent pipeline splits responsibilities into three distinct roles:&lt;/p&gt;

&lt;ol&gt;
&lt;li&gt;
&lt;strong&gt;Parser/Researcher Agent&lt;/strong&gt;: Extracts and validates raw unstructured input.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Router/Decision Agent&lt;/strong&gt;: Evaluates the structured data against business rules and selects the execution path.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Execution Agent&lt;/strong&gt;: Performs the action or generates the tailored response for that specific path.&lt;/li&gt;
&lt;/ol&gt;

&lt;h2&gt;
  
  
  Implementing a Clean Multi-Agent Pipeline
&lt;/h2&gt;

&lt;p&gt;You do not need heavy frameworks to start building multi-agent systems. A modular Python implementation using typed schemas makes the hand-off between agents explicit and reliable.&lt;/p&gt;

&lt;p&gt;Here is an example showing how structured data is passed between an Analyzer Agent and a specialized Technical Support Agent:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight python"&gt;&lt;code&gt;&lt;span class="k"&gt;from&lt;/span&gt; &lt;span class="n"&gt;typing&lt;/span&gt; &lt;span class="k"&gt;import&lt;/span&gt; &lt;span class="nc"&gt;Literal&lt;/span&gt;
&lt;span class="k"&gt;from&lt;/span&gt; &lt;span class="n"&gt;pydantic&lt;/span&gt; &lt;span class="k"&gt;import&lt;/span&gt; &lt;span class="nc"&gt;BaseModel&lt;/span&gt;

&lt;span class="c1"&gt;# Shared schemas act as communication protocols between agents&lt;/span&gt;
&lt;span class="k"&gt;class&lt;/span&gt; &lt;span class="nc"&gt;TicketAnalysis&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="nc"&gt;BaseModel&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt;
    &lt;span class="n"&gt;category&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="nc"&gt;Literal&lt;/span&gt;&lt;span class="p"&gt;[&lt;/span&gt;&lt;span class="s2"&gt;"billing"&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="s2"&gt;"technical"&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="s2"&gt;"general"&lt;/span&gt;&lt;span class="p"&gt;]&lt;/span&gt;
    &lt;span class="n"&gt;priority&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="nc"&gt;Literal&lt;/span&gt;&lt;span class="p"&gt;[&lt;/span&gt;&lt;span class="s2"&gt;"low"&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="s2"&gt;"high"&lt;/span&gt;&lt;span class="p"&gt;]&lt;/span&gt;
    &lt;span class="n"&gt;summary&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="kt"&gt;str&lt;/span&gt;

&lt;span class="k"&gt;class&lt;/span&gt; &lt;span class="nc"&gt;AgentResponse&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="nc"&gt;BaseModel&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt;
    &lt;span class="n"&gt;action_taken&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="kt"&gt;str&lt;/span&gt;
    &lt;span class="n"&gt;message_to_user&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="kt"&gt;str&lt;/span&gt;

&lt;span class="c1"&gt;# Agent 1: Focused purely on classification and parsing&lt;/span&gt;
&lt;span class="k"&gt;def&lt;/span&gt; &lt;span class="nf"&gt;run_analyzer_agent&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;raw_ticket&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="kt"&gt;str&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="o"&gt;-&amp;gt;&lt;/span&gt; &lt;span class="nc"&gt;TicketAnalysis&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt;
    &lt;span class="c1"&gt;# In practice, call your LLM using structured output tools like Pydantic&lt;/span&gt;
    &lt;span class="k"&gt;return&lt;/span&gt; &lt;span class="nc"&gt;TicketAnalysis&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;
        &lt;span class="n"&gt;category&lt;/span&gt;&lt;span class="o"&gt;=&lt;/span&gt;&lt;span class="s2"&gt;"technical"&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
        &lt;span class="n"&gt;priority&lt;/span&gt;&lt;span class="o"&gt;=&lt;/span&gt;&lt;span class="s2"&gt;"high"&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
        &lt;span class="n"&gt;summary&lt;/span&gt;&lt;span class="o"&gt;=&lt;/span&gt;&lt;span class="s2"&gt;"User unable to connect to database using SSL."&lt;/span&gt;
    &lt;span class="p"&gt;)&lt;/span&gt;

&lt;span class="c1"&gt;# Agent 2: Focused purely on resolving technical issues&lt;/span&gt;
&lt;span class="k"&gt;def&lt;/span&gt; &lt;span class="nf"&gt;run_technical_agent&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;analysis&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="nc"&gt;TicketAnalysis&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="o"&gt;-&amp;gt;&lt;/span&gt; &lt;span class="nc"&gt;AgentResponse&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt;
    &lt;span class="c1"&gt;# Dedicated prompt tailored only for technical resolution logic&lt;/span&gt;
    &lt;span class="n"&gt;response_text&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="n"&gt;f&lt;/span&gt;&lt;span class="s2"&gt;"Investigating DB SSL issue: {analysis.summary}"&lt;/span&gt;
    &lt;span class="k"&gt;return&lt;/span&gt; &lt;span class="nc"&gt;AgentResponse&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;
        &lt;span class="n"&gt;action_taken&lt;/span&gt;&lt;span class="o"&gt;=&lt;/span&gt;&lt;span class="s2"&gt;"escalated_to_devops"&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
        &lt;span class="n"&gt;message_to_user&lt;/span&gt;&lt;span class="o"&gt;=&lt;/span&gt;&lt;span class="n"&gt;response_text&lt;/span&gt;
    &lt;span class="p"&gt;)&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;h2&gt;
  
  
  Orchestrating the Workflow
&lt;/h2&gt;

&lt;p&gt;Once your agents are isolated, an orchestrator function routes data between them based on their structured outputs:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight python"&gt;&lt;code&gt;&lt;span class="k"&gt;def&lt;/span&gt; &lt;span class="nf"&gt;process_incoming_ticket&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;raw_ticket_text&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="kt"&gt;str&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="o"&gt;-&amp;gt;&lt;/span&gt; &lt;span class="nc"&gt;AgentResponse&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt;
    &lt;span class="c1"&gt;# Step 1: Parse and classify input&lt;/span&gt;
    &lt;span class="n"&gt;analysis&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="nf"&gt;run_analyzer_agent&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;raw_ticket_text&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;

    &lt;span class="c1"&gt;# Step 2: Route conditionally based on agent findings&lt;/span&gt;
    &lt;span class="k"&gt;if&lt;/span&gt; &lt;span class="n"&gt;analysis&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;category&lt;/span&gt; &lt;span class="o"&gt;==&lt;/span&gt; &lt;span class="s2"&gt;"technical"&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt;
        &lt;span class="k"&gt;return&lt;/span&gt; &lt;span class="nf"&gt;run_technical_agent&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;analysis&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
    &lt;span class="k"&gt;else&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt;
        &lt;span class="k"&gt;return&lt;/span&gt; &lt;span class="nc"&gt;AgentResponse&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;
            &lt;span class="n"&gt;action_taken&lt;/span&gt;&lt;span class="o"&gt;=&lt;/span&gt;&lt;span class="s2"&gt;"routed_to_general_support"&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
            &lt;span class="n"&gt;message_to_user&lt;/span&gt;&lt;span class="o"&gt;=&lt;/span&gt;&lt;span class="s2"&gt;"Thank you for reaching out. A support representative will respond shortly."&lt;/span&gt;
        &lt;span class="p"&gt;)&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;h2&gt;
  
  
  Why Coordination Beats Raw Intelligence
&lt;/h2&gt;

&lt;p&gt;Decoupling your AI logic into small agents provides significant operational advantages:&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;
&lt;strong&gt;Easier Debugging&lt;/strong&gt;: Inspect the JSON payload transferred between functions to pinpoint exactly which step failed.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Cost Optimization&lt;/strong&gt;: You can use smaller, faster models for simple parsing tasks and reserve heavier models for complex reasoning steps.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Independent Testing&lt;/strong&gt;: Test each agent's prompts and outputs in isolation without running the entire end-to-end pipeline.&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;The real unlock in production AI automation isn't writing longer prompts—it is designing predictable coordination between specialized agents.&lt;/p&gt;

&lt;h2&gt;
  
  
  About me
&lt;/h2&gt;

&lt;p&gt;Tanmay builds AI agents and automation for businesses and is open to freelance projects. If you want to discuss AI architecture or backend automation, connect with me on &lt;a href="https://www.linkedin.com/in/tanmay-kumar-chaurasia/" rel="noopener noreferrer"&gt;LinkedIn&lt;/a&gt;.&lt;/p&gt;

</description>
      <category>ai</category>
      <category>python</category>
      <category>backend</category>
      <category>architecture</category>
    </item>
    <item>
      <title>Database Indexing Basics That Will Save You From Slow SQL Queries</title>
      <dc:creator>tanmay kuamr chaurasia</dc:creator>
      <pubDate>Sat, 21 Nov 2026 10:12:30 +0000</pubDate>
      <link>https://dev.to/tanmay2627/database-indexing-basics-that-will-save-you-from-slow-sql-queries-4fhg</link>
      <guid>https://dev.to/tanmay2627/database-indexing-basics-that-will-save-you-from-slow-sql-queries-4fhg</guid>
      <description>&lt;p&gt;Your database might not actually be slow—your queries might just be lost. &lt;/p&gt;

&lt;p&gt;It is a common scenario in backend engineering: as your application grows and your database tables expand from thousands to millions of rows, once-lightning-fast APIs start timing out. Developers often jump to upgrading database hardware or adding caching layers like Redis, when the real culprit is simple: missing database indexes.&lt;/p&gt;

&lt;p&gt;When a query runs without an index, the database engine has no choice but to perform a full table scan. That means it reads every single row on disk from start to finish just to return a handful of matching records.&lt;/p&gt;

&lt;p&gt;Here is how database indexing works, how to spot missing indexes, and how to fix slow queries step by step.&lt;/p&gt;

&lt;h2&gt;
  
  
  The Mental Model: Table of Contents vs. Reading Cover-to-Cover
&lt;/h2&gt;

&lt;p&gt;Think of a database table as a 1,000-page technical manual. If you want to find every mention of "B-Tree indexes" without an index, you have to read all 1,000 pages line by line. &lt;/p&gt;

&lt;p&gt;A database index works like the index at the back of that manual. It maintains an organized, sorted data structure (typically a B-Tree) that maps column values directly to their physical location on disk. &lt;/p&gt;

&lt;p&gt;Instead of scanning millions of records sequentially, the database engine uses this lookup structure to jump directly to the exact target rows in milliseconds.&lt;/p&gt;

&lt;h2&gt;
  
  
  Step-by-Step: Fixing a Slow Query
&lt;/h2&gt;

&lt;p&gt;Imagine an e-commerce backend with an &lt;code&gt;orders&lt;/code&gt; table containing millions of records. Users frequently query their order history filtered by &lt;code&gt;user_id&lt;/code&gt; and sorted by &lt;code&gt;created_at&lt;/code&gt;.&lt;/p&gt;

&lt;p&gt;Here is the basic query:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="k"&gt;SELECT&lt;/span&gt; &lt;span class="n"&gt;id&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;total_amount&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;status&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;created_at&lt;/span&gt;
&lt;span class="k"&gt;FROM&lt;/span&gt; &lt;span class="n"&gt;orders&lt;/span&gt;
&lt;span class="k"&gt;WHERE&lt;/span&gt; &lt;span class="n"&gt;user_id&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="mi"&gt;42891&lt;/span&gt;
&lt;span class="k"&gt;ORDER&lt;/span&gt; &lt;span class="k"&gt;BY&lt;/span&gt; &lt;span class="n"&gt;created_at&lt;/span&gt; &lt;span class="k"&gt;DESC&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;Without an index on &lt;code&gt;user_id&lt;/code&gt;, the database scans every single row in the &lt;code&gt;orders&lt;/code&gt; table.&lt;/p&gt;

&lt;p&gt;To inspect how the database engine plans to execute this query, run an &lt;code&gt;EXPLAIN ANALYZE&lt;/code&gt; statement:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="k"&gt;EXPLAIN&lt;/span&gt; &lt;span class="k"&gt;ANALYZE&lt;/span&gt;
&lt;span class="k"&gt;SELECT&lt;/span&gt; &lt;span class="n"&gt;id&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;total_amount&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;status&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;created_at&lt;/span&gt;
&lt;span class="k"&gt;FROM&lt;/span&gt; &lt;span class="n"&gt;orders&lt;/span&gt;
&lt;span class="k"&gt;WHERE&lt;/span&gt; &lt;span class="n"&gt;user_id&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="mi"&gt;42891&lt;/span&gt;
&lt;span class="k"&gt;ORDER&lt;/span&gt; &lt;span class="k"&gt;BY&lt;/span&gt; &lt;span class="n"&gt;created_at&lt;/span&gt; &lt;span class="k"&gt;DESC&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;If the execution plan outputs &lt;code&gt;Seq Scan&lt;/code&gt; (Sequential Scan) in PostgreSQL or &lt;code&gt;ALL&lt;/code&gt; in MySQL, it means the database is scanning the entire table.&lt;/p&gt;

&lt;p&gt;To fix this, create a composite index that covers both the filtering column (&lt;code&gt;user_id&lt;/code&gt;) and the sorting column (&lt;code&gt;created_at&lt;/code&gt;):&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="k"&gt;CREATE&lt;/span&gt; &lt;span class="k"&gt;INDEX&lt;/span&gt; &lt;span class="n"&gt;idx_orders_user_created&lt;/span&gt; 
&lt;span class="k"&gt;ON&lt;/span&gt; &lt;span class="n"&gt;orders&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;user_id&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;created_at&lt;/span&gt; &lt;span class="k"&gt;DESC&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;After adding this index, re-running the same &lt;code&gt;EXPLAIN ANALYZE&lt;/code&gt; command will show an &lt;code&gt;Index Scan&lt;/code&gt;. The database engine skips the full table read entirely. The execution time can plummet from several seconds down to single-digit milliseconds on large datasets.&lt;/p&gt;

&lt;h2&gt;
  
  
  Where Should You Add Indexes?
&lt;/h2&gt;

&lt;p&gt;You do not need to index every column in your database schema. Focus on columns that frequently appear in:&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;
&lt;strong&gt;WHERE clauses&lt;/strong&gt;: Fields used repeatedly for filtering (e.g., &lt;code&gt;user_id&lt;/code&gt;, &lt;code&gt;email&lt;/code&gt;, &lt;code&gt;status&lt;/code&gt;).&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;JOIN conditions&lt;/strong&gt;: Foreign key columns connecting relational tables (e.g., &lt;code&gt;account_id&lt;/code&gt;, &lt;code&gt;product_id&lt;/code&gt;).&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;ORDER BY clauses&lt;/strong&gt;: Columns used for sorting query results (e.g., &lt;code&gt;created_at&lt;/code&gt;, &lt;code&gt;updated_at&lt;/code&gt;).&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;High-cardinality columns—columns with a wide variety of unique values like UUIDs or timestamps—benefit the most from indexing. Low-cardinality columns like boolean flags (&lt;code&gt;is_active&lt;/code&gt;) rarely benefit from single-column indexes because the database still has to scan a large portion of the table.&lt;/p&gt;

&lt;h2&gt;
  
  
  The Hidden Cost: Indexes Aren't Free
&lt;/h2&gt;

&lt;p&gt;Indexes improve read performance significantly, but they come with a trade-off.&lt;/p&gt;

&lt;p&gt;Every time you execute an &lt;code&gt;INSERT&lt;/code&gt;, &lt;code&gt;UPDATE&lt;/code&gt;, or &lt;code&gt;DELETE&lt;/code&gt; query, the database must write the change to the table &lt;em&gt;and&lt;/em&gt; update every associated index. &lt;/p&gt;

&lt;p&gt;If you put an index on every column:&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;
&lt;strong&gt;Write performance drops&lt;/strong&gt;: Data modification queries take longer because multiple index trees must be re-balanced.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Memory overhead increases&lt;/strong&gt;: Indexes need to fit into RAM for optimal fast lookup performance.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Disk storage grows&lt;/strong&gt;: Large indexes can match or exceed the size of the underlying table itself.&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;The goal isn't to index everything, but to identify the specific high-impact read paths in your application and support them with targeted indexes.&lt;/p&gt;

&lt;h2&gt;
  
  
  About me
&lt;/h2&gt;

&lt;p&gt;Tanmay builds AI agents and automation for businesses and is open to freelance projects. Feel free to connect with him on &lt;a href="https://www.linkedin.com/" rel="noopener noreferrer"&gt;LinkedIn&lt;/a&gt;.&lt;/p&gt;

</description>
      <category>sql</category>
      <category>database</category>
      <category>backend</category>
      <category>devops</category>
    </item>
  </channel>
</rss>
