Production OOM Incident Caused by Incorrect Pagination and How to Fix It

After a production alert indicated an OOM exception that forced a service restart, the author investigated the root cause, which stemmed from a simple data‑processing routine that queried roughly 80,000 rows in pages of 1,000.

The Java method private void testSQLBug() repeatedly called sqlBugDataMapper.queryData(pageSize, pageNumber) and broke only when the returned list was empty or smaller than the page size. Because the SQL used OFFSET #{pageNumber} instead of OFFSET #{pageNumber}*#{pageSize} , the offset increased by one row each iteration, resulting in about 79,000 queries and a continuously growing sqlBugDatas list that eventually exhausted memory.

private void testSQLBug() {
    List
sqlBugDatas = new ArrayList<>();
    int pageSize = 1000;
    int pageNumber = 0;
    while (true) {
        List
data = sqlBugDataMapper.queryData(pageSize, pageNumber);
        if (CollectionUtils.isEmpty(data)) {
            break;
        }
        // filter invalid data
        filterData(data);
        sqlBugDatas.addAll(data);
        if (data.size() <= pageSize) {
            break;
        }
        pageNumber++;
    }
}

The corresponding Oracle/MyBatis mapper used:

select * from sql_bug order by id
offset #{pageNumber} rows fetch first #{pageSize} rows only

Correcting the offset calculation to #{pageNumber}*#{pageSize} fixes the logic:

select * from sql_bug order by id
offset #{pageNumber}*#{pageSize} rows fetch first #{pageSize} rows only

The bug was not caught in testing because the test environment contained far fewer rows, and code reviews focused on business logic rather than pagination details.

While using OFFSET for pagination is common (e.g., MySQL LIMIT #{pageNumber}*#{pageSize}, #{pageSize} ), deep pagination suffers from performance degradation. The article recommends cursor‑based pagination using a primary‑key ID, which avoids large offsets.

private void testSQLBug() {
    List
sqlBugDatas = new ArrayList<>();
    String id = null;
    int pageSize = 0;
    while (true) {
        List
data = sqlBugDataMapper.queryData(id, pageSize);
        if (CollectionUtils.isEmpty(data)) {
            break;
        }
        id = data.get(data.size() - 1).getId();
        filterData(data);
        sqlBugDatas.addAll(data);
        if (data.size() <= pageSize) {
            break;
        }
    }
}

For MySQL the query becomes:

select * from sql_bug
where id < #{id}
order by id
limit #{pageSize}

For Oracle a rownum based approach is suggested:

select * from (
    select * from sql_bug
where id > #{id}
order by id
)
where rownum < #{pageSize}+1

The author prefers the rownum syntax because it is easier to understand and better supported by many domestic databases undergoing “信创” transformation.

In summary, the article presents a real‑world OOM incident caused by a pagination mistake, analyzes why it was missed, and offers safer pagination patterns to improve database performance and reliability.