For large databases, pagination with LIMIT ... OFFSET ... queries becomes very slow as you go deeper and deeper into the search results. What options does Meadowlark's usage of OpenSearch, MongoDB, and PostgreSQL provide? What performance characteristics can we expect?
Standard Techniques
LIMIT x OFFSET y
Fetches X number of rows, skipping Y number of rows. The skipping part is what can take a very long time as you go deeper into the result set. There is also the danger data changes between page requests.
Keyset Pagination
A better technique can be to fetch a small number of records, and use a monotonically-increasing key field in a WHERE clause to select the next group of records. This is referred to as either the seek method and keyset pagination, and seems to have been first documented in Paging Through Results. This approach takes full advantage of database indexing.
Cursors
Cursor-based pagination is common in GraphQL queries. As describing in Paginating Requests in APIs, the "cursor" is essentially a pointer to the next record that should be fetched. Thus it is conceptually similar to a keyset approach. However, it might be subject to problems when a new record appears in the sort order before the next cursor.
Meadowlark Database Engines
In the current design, all documents are written to either MongoDB or PostgreSQL for basic transactional support. The data are also written to OpenSearch, and GET ALL and GET by QUERY type requests go against the OpenSearch database with its powerful indexing. However, MongoDB and PostgreSQL also have document indexing capabilities.
Pagination in OpenSearch
OpenSearch supports all three patterns. The cursor-based pattern is poorly documented; in fact, the official documentation only mentions limitations to cursor-based paging, without ever mentioning how to use it. The limitations mention that only basic queries are supported; this fits the potential Meadowlark usage pattern, which would not use sub-queries or joins (complex queries). However, there is a markdown document describing OpenSearch SQL Cursor (Pagination) Support in the source repository.