How a Compound Database Index Cut Query Time from 210ms to Under 1ms

A developer building a rate-limiter for a side-project API noticed severe performance degradation as traffic grew, with a simple COUNT query taking over 200 milliseconds per request. The bottleneck was a missing index on the rate_limit_log table, which forced PostgreSQL to perform a full sequential scan across hundreds of thousands of rows on every request. Adding a compound B-tree index on (user_id, requested_at) allowed the database to instantly narrow results by user and scan only a contiguous time-range segment, reducing execution time to under one millisecond. The fix works because the leading column filters by user while the second column keeps timestamps in sorted order, enabling an efficient index range scan instead of a costly full-table read. The trade-off is slightly slower writes and additional disk usage, but for read-heavy rate-limiting workloads the performance gain far outweighs those costs.