Questions & Answers Microservices API

Built a Questions and Answers microservice for an e-commerce product page, designed to hold up under realistic catalog traffic. The service handles question listing, answer listing with nested photo URLs, posting, helpful-vote tracking, and content reporting through a RESTful Express API backed by PostgreSQL.

The initial /qa/questions route worked against small datasets but was returning ~1,888 ms on the full production-sized data. Before adding any caching, EXPLAIN ANALYZE pointed at the hot query paths, and adding indexes on those columns brought response time down to ~211 ms - roughly a 9x improvement without touching any application code. That step alone mattered more than anything that came after. Layering Redis on top using a cache-aside strategy pushed the service through a 10,000-client Loader.io test (one minute) with a mean response time of ~62 ms and a 100% success rate. The sequencing is worth noting: Redis was compressing an already-fast query, not rescuing a slow one - a meaningfully different situation for reasoning about where the ceiling actually is.

The main /qa/questions endpoint uses json_build_object, json_agg, and subqueries to return fully nested, API-ready data from PostgreSQL rather than assembling large response objects in JavaScript. That kept the heavy transformation close to the data and made the Express handlers much simpler to reason about.

One rough edge worth naming: inserting answers used SELECT MAX(id) + 1 to generate new IDs, which is unsafe under concurrent writes - two simultaneous POST requests can read the same max and collide. The journal flagged it in real time. The correct fix is a sequence or identity column with RETURNING id, which eliminates the race entirely. Deploying Redis to a separate EC2 instance from the backend turned out to be slower than colocation - network round-trips between the backend and cache added more latency than the separation saved - so the final layout kept Redis on the same instance as the API server.