.jpg)
Optimizing a relational database to serve millions of records from a virtual machine with minimal resources is no simple task. When the goal is to deliver responses in tenths of a second while keeping operational costs near zero, every technical decision matters. This article explores how a consumer intelligence platform managed to assemble pages in 66ms handling 1.7 million products, 41,000 government recalls, and over 100,000 community complaints, all running on a 6GB ARM VM with no recurring cost. The lessons presented here are applicable to any system seeking to scale efficiently, whether through custom applications or cloud-based solutions.
The first typical bottleneck is the COUNT(*) operation on massive tables. In PostgreSQL, due to multiversion concurrency control (MVCC), counting rows involves physically scanning records and checking their visibility for the active transaction. For sets of 1.7M rows, this can extend the response time to 45 seconds, collapsing the server under real load. The alternative recommended by many is to use pg_class estimates, but these do not work with WHERE filters. The effective solution is to abandon real-time counts and adopt a flat statistics cache table (stats_cache) with pre-aggregated counters. An asynchronous ingestion pipeline increments or decrements these counters each time a product's status changes. Thus, a front-end query becomes a 2ms primary key lookup, at the cost of eventual consistency, which is perfectly acceptable in domains where critical data (recalls) remains always accurate. This approach is common in custom software development for applications requiring high concurrency without sacrificing performance.
Another common mistake is relying on single-column indexes for queries that combine multiple conditions. An index on category, another on status, and another on score are rarely used together by the PostgreSQL planner. The engine picks one of them and filters the rest in memory, which amounts to a partial sequential scan of millions of rows. The solution lies in designing composite indexes that exactly reflect the order of filters in real queries. For example, an index on (category, status, score DESC) allows the planner to directly access the 20 needed rows via Index Condition lookups, eliminating any massive scan. This precision in index design is one of the keys of modern database engineering and is often an integral part of custom application projects seeking efficiency.
In the realm of internationalization, serving content in multiple languages without skyrocketing language model API costs is another challenge. Instead of translating each request live, a cache layer based on the original text's hash can be implemented. When a user requests a description in German, the backend computes the SHA-256 of the English text and checks a local table for an existing translation. If it's a hit, the response is sub-5ms with no additional cost. If it misses, an asynchronous worker calls a language model (e.g., Gemini) to translate and stores the result. This way, each unique text is translated only once, and all subsequent requests are free. This pattern is an excellent example of how enterprise artificial intelligence can be efficiently integrated through specialized AI agents operating in the background, without degrading the user experience.
Managing user-generated content (UGC) also requires special care to prevent abuse. An effective strategy is to impose constraints at the database level, such as a unique key limiting one pending suggestion per user and product. This avoids the need for rate-limiting logic in the application, simplifies maintenance, and eliminates race conditions. Suggestions go through an isolated state until an administrator approves them, at which point they are integrated into production data. This robust approach is recommended for any platform handling community contributions and can be complemented with cybersecurity services to protect exposed endpoints.
The numerical results speak for themselves: 1.7M products, 41K recalls, 100K complaints, 7 languages, all served from a single 6GB VM with zero hosting cost and an average page assembly time of 66ms. No exotic infrastructure was required, but rather precision in deciding where real-time consistency is indispensable and where eventual consistency is a rational choice. The database does not need to count rows in real time; indexes must reflect actual query patterns; and external API calls should occur exactly once per unique entry, never more.
For teams looking to implement similar architectures, having support from cloud infrastructure experts can make a difference. For example, leveraging AWS and Azure cloud services allows scaling without worrying about fixed costs, while enterprise artificial intelligence solutions facilitate the integration of advanced capabilities such as automatic translation or sentiment analysis. At Q2BSTUDIO, we offer custom application development, as well as business intelligence services with Power BI to visualize performance metrics, and AI agents that automate complex processes. If your project requires database optimization or cloud migration, feel free to explore our capabilities.
