.jpg)
Optimizing a database with millions of records on a free virtual machine may seem like a pipe dream, but it is possible when precise strategies of indexing, caching, and asynchronous design are applied. In this case, starting from a scenario where 1.7 million rows in PostgreSQL caused 45-second sequential scans and COUNT(*) queries that crashed the planner, the page assembly time was reduced to just 66 ms, all at zero recurring cost. The key was not adding hardware, but rethinking the architecture from the ground up.
The first major change was eliminating real-time COUNT(*) queries. Instead of asking PostgreSQL to count rows every time a paginator or panel needs to be displayed, a lightweight stats_cache table was implemented that stores pre-aggregated counters. Each time an ingestion process updates data or a product changes status, an asynchronous worker increments or decrements the corresponding counter. The user interface makes a 2 ms query (a primary key lookup) instead of triggering a scan of millions of rows. This eventual consistency —the counters may be slightly out of sync with the real state— is perfectly acceptable in a consumer information system, where critical security data remains always accurate.
Another fundamental point was the review of indexes. Having individual indexes per column (category, status, score) proved useless because real queries filter and sort by combining those fields. The solution was to replace them with a single composite index that exactly reflects the order of the WHERE and ORDER BY clauses. This way, PostgreSQL's planner can perform index condition searches without touching the rest of the rows. Going from scanning 1.7 million rows to retrieving only the 20 needed makes the difference between an application that responds in milliseconds and one that gets stuck.
In the realm of multilingual content, on-demand translation via artificial intelligence APIs could skyrocket costs and latency. The alternative was to build a content-addressed cache layer: a SHA-256 hash of the source text is generated and looked up in a translations table. If it already exists, it is returned instantly; if not, a background worker requests the translation from the AI model (Gemini 2.5 Flash Lite) and stores it permanently. Thus, each text is translated only once, and any subsequent request —whether from a user, a search engine, or a replica in another region— is served from local storage in under 5 ms. This approach fits perfectly with the principles of efficient and cost-effective custom applications.
Image management and user-generated content also benefited from database-level constraints. Instead of implementing complex rate-limiting rules in the application layer, a unique constraint was added that prevents the same user from submitting more than one suggestion per product. The database itself guarantees atomicity and prevents abuse without the need for additional logic. This is an example of how custom software can integrate security and integrity from the schema, not from the code.
All this work demonstrates that expensive infrastructure is not needed to deliver excellent performance. With a 6 GB ARM VM on Oracle Cloud Free Tier and zero hosting cost, it is possible to serve 1.7 million products, 41,000 government recall alerts, and over 100,000 community complaints in 7 languages, all in an average of 66 ms per page. Behind these figures are design decisions that prioritize precision where it truly matters and accept deferred consistency where it is reasonable.
If this type of optimization resonates with the challenges your organization faces, at Q2BSTUDIO we offer services ranging from consulting on AWS and Azure cloud services to developing AI for businesses, as well as cybersecurity, business intelligence services with Power BI, and implementation of automated AI agents. Each project is approached with a precise engineering mindset, measuring where real-time accuracy is indispensable and where an asynchronous or eventually consistent solution is the smarter choice.
The most interesting part of this case is that it did not require exotic tools. It only required rigor in defining indexes, courage to change the way of counting records, and discipline in managing the translation cache. And, of course, having the support of a team that understands how to turn resource constraints into opportunities for innovation. If you would like to explore how to apply similar principles to your own infrastructure, feel free to contact us.
