Pagination Bug With 1 Million Rows On A Tiny DB.T3.Micro

So, an issue came in on a small logging server the other day. The issue was with a timeout and response times taking too long.

Initially, I thought the issue with this request was the size of each page they were requesting. It was something like 500 items per page.

So, I brought the default down to 50 per page and implemented a limit.

This actually didn’t fix the issue.

The RDS instance was still having a rediculous number of read iops on any request.

It turns out the issue was the micro RDS instance running a count() on the table for the pagination.

You could get around this by just making the database smaller and archiving old rows. Or, increasing the size of the RDS engine.

But, I think the best solution is to change pagination to a more fluid model.

For example, Stripe’s api (which deals with even more data points) uses starting_after and ending_before for their pagination. I think is an elegant solution that’s relatively easy to implement and builds even more optimized queries.

Stripe Example

Anyway, the DB.T3.Micros works well now with the new api. No more timeouts.