Optimizing Pagination with Large Datasets in fastapi_pagination

[downdawn]

Hi，@wu-clan

During actual project operations, I've encountered slow performance when using the fastapi_pagination framework for pagination queries as the table data approaches millions of rows. The slowdown is primarily due to the increasing time taken for counting the total number of records as the dataset grows.

In addressing this issue, do you have any suggestions or recommendations?

[wu-clan]

Have you tested upgrading to the latest version to see if anything has changed?

Not just fastapi_pagination, I think pydantic-v2 is also needed

My guess: its computational speed depends on pydantic, as it will be used to format the data

[downdawn]

The version might have some relevance, but I believe the issue lies in the lengthy time it takes to retrieve the total count. This seems to be unavoidable. One approach I'm considering is to split the interface or switch to a NoSQL database, such as Elasticsearch.

Example：

SELECT
  count(*) AS count_1
FROM
  (
    SELECT
      company.id AS id,
      company.name AS name,
    FROM
      company
    WHERE
      company.name LIKE '%led%
  ) AS anon_1

[wu-clan]

Try this mode, change the default query behavior, or improve performance.

https://uriyyo-fastapi-pagination.netlify.app/tutorials_advanced/custom-paginate/

[downdawn]

total = conn.scalar(stmt.with_only_columns(func.count()))

Inevitable

[wu-clan]

Maybe can use sqla to execute SQL text instead of continuing the ORM syntax, what do you think?

[downdawn]

However, ORM ultimately translates into SQL statements, and the 'COUNT' action inevitably leads to slower query speeds.

[wu-clan]

https://docs.sqlalchemy.org/en/20/core/sqlelement.html#sqlalchemy.sql.expression.text

Is it transformed by ORM when executing the original sql? I'm not sure, maybe this is just a way to optimize the query SQL as much as possible to improve performance

[wu-clan]

Reasonable use of NoSQL may be very effective.

Elasticsearch may be more common in large projects, which is heavyweight for our templates

[downdawn]

Yes, I tested migrating several million records to the ES database. Under sufficient resources, the query speed can reach milliseconds, including obtaining the total count. However, obtaining the total count in MySQL takes around 2 seconds.

[wu-clan]

Sounds great.