title: Transaction processing - Lookup millions of documents to validate transactions - Cost effective approach
body: <p>We recently made a shift from a relational to NoSQL (Couchbase). We are facing challenges scaling our backend to handle increasing number of users(reps). One of our product masters has over 50+ million documents and more than a million documents get modified on a day-to-day basis. There are few transactions that are processed daily which have product details in them, roughly 100,000 such transactions are processed with about 50 products in them. There is a need to check if these products are available in the product master which is a validation against 50+ million documents in Couchbase.</p>
  <p>Our existing approach to lookup against 50+ million documents is not cost effective and increases the overall runtime of our service. We are exploring options to cache this locally or find an alternate way to perform this operation in a more efficient way.</p>
  <p>Here is an overview of our existing data models.
  Data structure 1 – Products authorized for a rep</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>RepID</th>
  <th>ProductId</th>
  <th>Prod Start Dt</th>
  <th>Prod End Dt</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>1001</td>
  <td>1</td>
  <td>2022-05-15</td>
  <td>9999-12-25</td>
  </tr>
  <tr>
  <td>1001</td>
  <td>2</td>
  <td>2022-05-15</td>
  <td>9999-12-25</td>
  </tr>
  <tr>
  <td>1001</td>
  <td>3</td>
  <td>2022-05-15</td>
  <td>9999-12-25</td>
  </tr>
  <tr>
  <td>1001</td>
  <td>4</td>
  <td>2022-05-15</td>
  <td>9999-12-25</td>
  </tr>
  <tr>
  <td></td>
  <td></td>
  <td></td>
  <td></td>
  </tr>
  <tr>
  <td>1002</td>
  <td>1</td>
  <td>2022-05-15</td>
  <td>9999-12-25</td>
  </tr>
  <tr>
  <td>1002</td>
  <td>2</td>
  <td>2022-05-15</td>
  <td>9999-12-25</td>
  </tr>
  <tr>
  <td>1002</td>
  <td>5</td>
  <td>2022-05-15</td>
  <td>9999-12-25</td>
  </tr>
  <tr>
  <td>1002</td>
  <td>6</td>
  <td>2022-05-15</td>
  <td>9999-12-25</td>
  </tr>
  <tr>
  <td></td>
  <td></td>
  <td></td>
  <td></td>
  </tr>
  <tr>
  <td>1003</td>
  <td>1</td>
  <td>2022-05-15</td>
  <td>9999-12-25</td>
  </tr>
  <tr>
  <td>1003</td>
  <td>2</td>
  <td>2022-05-15</td>
  <td>9999-12-25</td>
  </tr>
  <tr>
  <td>1003</td>
  <td>8</td>
  <td>2022-05-15</td>
  <td>9999-12-25</td>
  </tr>
  <tr>
  <td>1003</td>
  <td>9</td>
  <td>2022-05-15</td>
  <td>9999-12-25</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>Similarly, 15,000 reps have an average of 3000-4000 documents totally to 50 million documents.</p>
  <p>Transaction document model</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>Order</th>
  <th>RepID</th>
  <th>ProductId</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>1234</td>
  <td>1001</td>
  <td>1</td>
  </tr>
  <tr>
  <td>1234</td>
  <td>1001</td>
  <td>2</td>
  </tr>
  <tr>
  <td>1234</td>
  <td>1001</td>
  <td>3</td>
  </tr>
  <tr>
  <td>1234</td>
  <td>1001</td>
  <td>4</td>
  </tr>
  <tr>
  <td>1234</td>
  <td>1001</td>
  <td>5</td>
  </tr>
  <tr>
  <td></td>
  <td></td>
  <td></td>
  </tr>
  <tr>
  <td>100</td>
  <td>1002</td>
  <td>1</td>
  </tr>
  <tr>
  <td>100</td>
  <td>1002</td>
  <td>2</td>
  </tr>
  <tr>
  <td>100</td>
  <td>1002</td>
  <td>3</td>
  </tr>
  <tr>
  <td></td>
  <td></td>
  <td></td>
  </tr>
  <tr>
  <td>1111</td>
  <td>1003</td>
  <td>1</td>
  </tr>
  <tr>
  <td>1111</td>
  <td>1003</td>
  <td>2</td>
  </tr>
  <tr>
  <td>1111</td>
  <td>1003</td>
  <td>3</td>
  </tr>
  <tr>
  <td>1111</td>
  <td>1003</td>
  <td>4</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>In the above example, rep 1001 isn’t authorized to sell product 5. Rep 1002 is not authorized to sell product 3 and rep 1003 is not authorized to sell products 3,4.
  What is the most efficient way to validate these products in the order transaction comparing against millions of documents for Authorized products
  One approach suggested was to use a HashMap with a list of reps and then another map for each of the reps’ authorized products. This would be in-memory cache and updated as and when new products are added to product master.</p>
  <p>Second approach suggested was to use GraphQL or Postgres SQL as it might be effective for this kind of operation.
  This is a simple problem, and the only challenge is to accomplish this in a cost-effective way without multiple DB calls or even eliminate all DB calls when transactions are processed by keeping this detail in memory. I would really appreciate if someone who has already tackled similar problems could share their experience on the technology stack, tools used.</p>