Backend API and Database Implementation

Measure before changing anything — four seconds has one dominant cause and
guessing costs more than instrumenting.

STEP 1 — WHERE THE TIME GOES
  Log query count + duration for one request. Three shapes, three fixes:
    Many fast queries (50+ under 5ms)  → N+1
    One slow query (3s+)               → missing index or bad plan
    Few queries, still slow            → it's not the DB. External call
                                         or serialization.

STEP 2 — IF IT'S THE QUERY
  EXPLAIN (ANALYZE, BUFFERS) SELECT ...

  Read it in this order:
    Seq Scan on a big table          → missing index
    rows= estimate far off actual    → stale stats, run ANALYZE
    Nested Loop over many rows       → planner chose wrong, usually stats
    high shared read= vs hit=        → not cached, may be an I/O problem

  Index for the actual predicate, composite in the order the query
  filters:
    CREATE INDEX CONCURRENTLY idx_orders_user_created
      ON orders (user_id, created_at DESC);

  CONCURRENTLY matters — a plain CREATE INDEX takes a write lock and
  turns your slow endpoint into a down endpoint.

STEP 3 — IF IT'S N+1
  Eager-load the relation. In an ORM that's one include; hand-rolled,
  it's one IN query plus a map. Confirm the query count actually
  dropped rather than assuming.

STEP 4 — IF IT'S NOT THE DB
  External call → move it off the request path or set an aggressive
  timeout with a fallback. A 4s p50 usually means a 30s p99 and no
  timeout at all.
  Serialization → check payload size before optimizing anything. 2MB of
  JSON is 2 seconds regardless of how fast the query was.

Paste EXPLAIN output and I'll read the plan.