Flask with DataTables Server-Side and Very Large Dataset

Question

I have a huge dataset (around 1.5 million database records) that I fetch and display with DataTables. I want to be able to fetch all records from the database without the web server going down due to the entire dataset being retrieved.

Below are my DB model, route, and Jinja template, including the JavaScript part.

The Flask-SQLAlchemy Model

class Identity(db.Model):
    __tablename__ = "identity"
    id: so.Mapped[int] = so.mapped_column(primary_key=True, autoincrement=True)
    identity: so.Mapped[str] = so.mapped_column(sa.String(15), index=True, unique=True)
    location: so.Mapped[Optional[str]] = so.mapped_column(
        sa.String(64), nullable=True, index=True
    )
    protected: so.Mapped[bool] = so.mapped_column(sa.Boolean(), default=False)
    timestamp: so.Mapped[datetime] = so.mapped_column(
        index=True, default=lambda: datetime.now(timezone.utc)
    )

    def to_dict(self) -> dict[str, int | str ]:
        return {
            "DT_RowId": f"row_{self.id}",
            "DT_RowClass": "table-row",
            "id": f"{self.id:,}",
            "identity": self.identity,
            "location": self.location,
            "protected": "Yes" if self.protected else "No"
        }

The route

@bp.route("/api/identities")
def api():
    query = sa.Select(Identity)
    total_count = db.session.scalar(
        sa.Select(sa.func.count(Identity.id)).select_from(Identity)
    )
    # Search filter
    search = request.args.get("search[value]")
    if search:
        query = query.filter(
            sa.or_(
                Identity.identity.ilike(f"%{search}%"), Identity.location.ilike(f"%{search}%")
            )
        )
    total_filtered = (
        db.session.scalar(
            sa.Select(sa.func.count(Identity.id))
            .select_from(Identity)
            .filter(
                sa.or_(
                    Identity.identity.ilike(f"%{search}%"),
                    Identity.location.ilike(f"%{search}%"),
                )
            )
        )
        or total_count
    )

    # sorting
    order = []
    i = 0
    while True:
        col_index = request.args.get(f"order[{i}][column]")
        if col_index is None:
            break
        col_name = request.args.get(f"columns[{col_index}][data]")
        if col_name not in ["id", "location", "protected"]:
            col_name = "location"
        descending = request.args.get(f"order[{i}][dir]") == "desc"
        col = getattr(Identity, col_name)
        if descending:
            col = col.desc()
        order.append(col)
        i += 1
    if order:
        query = query.order_by(*order)

    # Pagination
    start = request.args.get("start", type=int)
    length = request.args.get("length", type=int)
    draw = request.args.get("draw", type=int)
    query = query.offset(start).limit(length)
    return {
        "data": [identity.to_dict() for identity in db.session.scalars(query)],
        "recordsFiltered": total_filtered,
        "recordsTotal": total_count,
        "draw": draw,
    }

The jinja template

{% extends "layout.j2" %}

{% block content %}
<table class="table table-bordered table-striped table-hover table-sm align-middle responsive" id="identitiesTable">
    <thead class="sticky-top top-0">
        <tr>
            <th scope="col">#</th>
            <th scope="col">Identity</th>
            <th scope="col">Location</th>
            <th scope="col">Protected</th>
        </tr>
    </thead>
    <tbody class="table-group-divider">
    </tbody>
</table>
{% endblock content %}

{% block js %}
<script type="text/javascript">
    $(document).ready(function () {
        let table = $('#identitiesTable');
        table.DataTable({
            dom: 'Bflipr<"table-responsive-md position-relative"t>i<"mb-2"p>',
            ajax: {
                url: '{{ url_for("main.api") }}',
                type: 'GET',
                dataType: "json",
                data: function (data) {
                    setTimeout(function () { data }, 1000)
                },
            },
            processing: true,
            serverSide: true,
            search: {
                return: true
            },
            paging: true,
            deferRender: true,
            filter: true,
            orderMulti: true,
            stateSave: true,
            responsive: true,
            pagingType: 'numbers',
            columns: [
                { data: 'id', searchable: false, orderable: true },
                { data: 'identity', searchable: true, orderable: false },
                { data: 'location', searchable: true, orderable: true },
                { data: 'protected', searchable: false, orderable: true },
            ],
            buttons: [
                {
                    extend: 'excelHtml5',
                    autoFilter: true,
                    sheetName: 'Identities',
                    exportOptions: {
                        columns: [0, 1, 2, 3]
                    }
                }
            ],
            pageLength: 100,
            lengthMenu: [[10, 50, 100, 500, 1000, 5000, -1], [10, 50, 100, 500, 1000, 5000, "All"]],
            language: {
                emptyTable: "No identities available in table",
                search: "Search:",
                lengthMenu: "Show _MENU_ identities",
                info: "Showing _START_ - _END_ of _TOTAL_ identities",
                infoEmpty: "Showing 0 to 0 of 0 identities",
                infoFiltered: "(filtered from _MAX_ identities)",
                zeroRecords: "No matching indentities were found",
            },
            drawCallback: function () {
                $(".paging_numbers>.pagination").addClass("pagination-sm");
                $(".dataTables_info").addClass("small opacity-75 py-2");
            },
            createdRow: function (row, data, dataIndex, cells) {
                if (data.protected == "Yes") {
                    $(cells[3]).addClass('table-success fw-bold');
                } else {
                    $(cells[3]).addClass('table-warning');
                }
            }
        });
    });
</script>
{% endblock js %}

I tried to mimic Server-side processing (5,000,000 rows) but without using the scroller. Instead, I am using paging. How can I retrieve the entire dataset?