Performance issues due to offset/limit paradigm on PostgreSQL database about fastapi-pagination

[uriyyo]

@alexdyck Thanks, will take a closer look and will try to implement approach as you described

[uriyyo]

@alexdyck For those who have a performance issue with the default limit-offset implementation can now use row-number pagination.

row-number pagination available for sqlmodel and sqlalchemy extenations.

Usage example:

@app.get("/users", response_model=Page[UserSchema])
def route(db: Session = Depends(get_db)):
    return paginate(db, select(User), query_type="row-number")

I am planing to release a new version in 1-2 weeks.

[dexax]

Just to add something into the topic of the LIMIT-OFFSET on Postgres.
The Postgres documentation describes what the @alexdyck is doing wrong

Thus, using different LIMIT/OFFSET values to select different subsets of a query result will give inconsistent results unless you enforce a predictable result ordering with ORDER BY

you don't enforce any ordering this is the problem. Adding ORDER BY id will for sure make the query almost instant because DB will just use pk index to pick requested rows

[uriyyo]

@dexax Thanks for the explanation.

I am closing this issue as far as we have found the root cause, and it's not a library issue.