Amplify - SQL Driver - Non Deterministic - Paging Behavior - Probably a flaw (micro service issues too) - Confirmation hint at and by the form of SQL generation in logs.

[wesleyolis]

Environment information

@aws-amplify/auth-construct: 1.8.1
  @aws-amplify/backend: 1.16.1
  @aws-amplify/backend-ai: Not Found
  @aws-amplify/backend-auth: 1.7.1
  @aws-amplify/backend-cli: 1.8.0
  @aws-amplify/backend-data: 1.6.1
  @aws-amplify/backend-deployer: 2.1.3
  @aws-amplify/backend-function: 1.14.1
  @aws-amplify/backend-output-storage: 1.3.1
  @aws-amplify/backend-secret: 1.4.0
  @aws-amplify/backend-storage: 1.4.1
  @aws-amplify/cli-core: 2.2.1
  @aws-amplify/client-config: 1.8.0
  @aws-amplify/data-construct: 1.16.3
  @aws-amplify/data-schema: 1.21.1
  @aws-amplify/deployed-backend-client: 1.8.0
  @aws-amplify/form-generator: 1.2.4
  @aws-amplify/model-generator: 1.2.0
  @aws-amplify/platform-core: 1.10.0
  @aws-amplify/plugin-types: 1.11.0
  @aws-amplify/sandbox: 2.1.2
  @aws-amplify/schema-generator: 1.4.0
  @aws-cdk/toolkit-lib: 1.1.1
  aws-amplify: 6.15.5
  aws-cdk-lib: 2.214.0
  typescript: 5.9.2
No AWS environment variables
No CDK environment variables

Describe the bug

Hi Guys,

We observed this, then suspect the issues after double check Postgress SQL documents that this can happen, which states yes.

Which means that the programming wrapping/generating the SQL query has to ensure deterministic behavior is enforced the ordering otherwise the database ordering can change.

As you see Amplify is Generating SQL statements.

Now if you go read SQL Postgres driver documentation, the record ordering is not deterministic and intermixed with the current transactions and things in progress based on Deletes and Updates that have recently gone off, can affect ordering.
The database can based the orders of records return by a select statement before and after could vary in the logical record position offset from 0.

This is basically what we have been seeing for our unit test cases against the database, at random times the ordering of records changes in the results set.
It’s the same data in the results set, but the order of the records has changed.
The issues with this is that for unit test break and then try again get stuck return that ordering, then at point in time just swap back to the old ordering.

Additionally, this could also be a result of the SQL database query planning come to think of it chosen different plans to retrieve the information, to use or not to use index or different index, by not having explicit order by clauses, typically based on something similar to the model.list({filter: fields....}) in narrow field choice of index, it not going to be as fast or deterministic.
Query planner runs on statical methods to choose how to build the final dataset, and that doesn’t care about ordering
Can change from query plan to query plan. There no guaranteed ordering from different query plans.
They can change at any time, which plan is being used and changes the final output of the ordering of records breaking pagination, if no order by clause.

Use you can also ways fall back to sorting by primary key, but that is not ideal, as would be massive post ordering if compound index was used. So everything should have sort/order by clause, which should implicitly be filter fields. Because everything needs order by clause for pagination to be predictable.. can't go writing loads special functions all the time for everything, what crazy overhead..

Additional, there algorithms for sorting and that, where some are deterministic and others are not deterministic.
The database could based on the outcome of the desired results make use of non-deterministic algorithms, when
doesn’t need to rely on specific ordering of input for the next input transform stage to speed things up.

So without order by clause, one has left the door wide open to a lot things possible going wrong with database integration.
and the query being optimized, by query planner all the time.

This is fine given, if writing backend API, were know records will never be more than page size to process, but if the table had 500 records, this meaning 5 pages, then there potentially a problem here.
Because post select you break things up into pages and the ordering of records on the boundaries of pages could change, if the ordering is not deterministic.
A record that was in Page1, could now be in Page2 and visa versa on successive API calls, because database re-organized optimized things again between queries.

So basically without amplify explicitly specify order by clause by the primary key implicitly for each table, we have paging problem. We have all our fields mark as id() and also specified .identifier(['id']).

If you doing a webpages you have to write some form of order by clause,
After which you would then limit and offset the results for predictable deterministic behavior, which perfect what we want for pagination.

So since we using and API to get records from the database via an API, which just like webpage report would do, you would have to add some form of ordering, to ensure the pagination is consistent.

If you don’t the following side effects would existing in your current pagination implementation.

1. Duplicate Records could be return, was in Page 1 now its in Page 2, due to the database re-ordering.
2. Missing Records, because what was in Page 2, after Page 1 is read is now in Page1, so when never read the records that between API calls moved
   from page 2 to page 1.
   [Amplify Gen2 Schema Model Complexity Leads to TS2590 Error in TypeScript Client - Handling Large Models and Improvements for TS](Amplify Gen2 Schema Model Complexity Leads to TS2590 Error in TypeScript Client amplify-data#424 (comment))

So you can imagine, that basically every backend call one makes we going to have to add order by clause, but that basically means custom query in amplify for every single thing. Because there is not ability to just add sort to any list command.
The issues now with this is probably run into the CloudFormation appsync 2500 resource limit very quickly, as we have seen how all of this is being wired, hit limits already, this would be another new AppSync endpoint and another CloudFormation resource for each order by custom query.
[How to split Amplify project into multiple stacks. Error - Limit on the number of resources in a single stack operation exceeded](aws-amplify/amplify-cli#13536)

Could you please advise and provide some reasonable mitigation measure and what next steps might need to look like from AWS and what clients could do in the mean time as mitigation or would Amplify release small hotpatch allowing list({orderby:}) field and also implicit look at adding order by the primary key,id or identifier, field of a model to get deterministic behavior of record ordering be return from the database, such pagination would be reliable and deterministic then.

Reproduction steps

I guess you would write simulation against Postgress database, with rapidly, writing, update, delete records.
Then running API against the database, ensure that all the records are present for the full table spread across all the pages. I would imaging say target 200, 300 or 2000 records and then validate pagination algorithm, but check expected dataset against, after updates, deletes are made to via API calls. That the record re-ordering doesn't results record changing pages on the boundaries, less pages more chance maybe of swapping pages, but who knows, you need insights into the database algorithms and code implementation for better hunch than that, what cause to trigger more often than not to expose the no deterministic behavior. Ensure that you have other tracking additional fields added, so you can correlate the order of the records changing.

[wesleyolis]

I know there is this. .secondaryIndexes((index) => [index("type").sortKeys(["createdAt"])])
Can you imagine having to do this every time for all permutations of indexes I have many.
Still doesn't deal with new records being add or removed during period of start to finish.
Unless running database record snapshot versioning different database type, migration potentially, if recall from memory, like MSSQL has if I recall, looked this when worked at Mercedes.
https://learn.microsoft.com/en-us/sql/relational-databases/sql-server-transaction-locking-and-row-versioning-guide?view=sql-server-ver17
but have to have reference that could live cross connections and have lifespan, when expired, have start over with backend operation or extend it debounce.

I do see this as more flaw around backends and in general backends that have write algorithms that have assemble and retrieve data using pagination schemas.
Micro services, they have to use pagination when integrating with one another, I think there more gaps in this, than the industry has realized in our thinking of things in much board space too.

My argument would be that the design of the API pagination would be flawed, because its support token for the next page,

I shouldn’t have to worry about pagination bug and have to go specify sort orders and create custom secondary indexes.
I therefore see this as slight design flaw, as if put pagination token in, then code should have been written in the background,
to ensure no missing or duplicates records.

Not as simple as just offsets, little more tricky actually, have keep reference to last item keys, to computer resume position,
encase delete, when have enforce specific ordering.
Then you will have to ensure new records added between queries have to be added on the tail of things,
Tail generated auto keys.
The thing is that’s fine for breaking dataset up into smaller dataset, if you have a primary key with the guarantee.

Basically if using standard SQL, you would have to dump everything to snapshot file,memcache
from which you page content out into the backend.
Your only option unless enhance Postgres database source code, to be able to have
static reference deterministic snapshot cursor, can be paused and resumed
or use more advanced technical versioning, Postgres transaction visibility modes like READ UNCOMMITTED,
But typically be CURSOR reference, that have to be cached and database would have to return reference,
that allow another require to resume using a reference to get more data on a new connection.

Just GraphQL opens the problem to this across all system, as suppose integrate multiple systems, you see all the gaps
that are forming now with pagination on GraphQL that might be implemented with other systems then and cross standardizing.

One would have to write up integration specification on how Pagination would need to work for backend algorithms to be written using pagination and then make that first class citizen in all API and database support.

Problem with backend, is needs be like direct connection to the database.
If I am writing an algorithm, I can’t have records moving between pages and things like that, need a shots shot.
Normally in a backend, I would directly work with the database and pull all the data for algorithm.
Now in the way amplify is designed, I pull 100 record snap shots, which would break any algorithm one writes.

[pahud]

Hi @wesleyolis,

Thank you for the comprehensive analysis of this pagination behavior. This appears to be a valid concern.

The Core Issue

You've correctly identified that without explicit ORDER BY clauses in the generated SQL, PostgreSQL makes no guarantees about result ordering. The database can and will reorder results based on:

• Query planner decisions
• Physical storage changes from concurrent operations
• Index selection variations
• Transaction visibility

This leads to the problems you've observed:

• Records appearing in multiple pages
• Records being skipped entirely
• Intermittent test failures

Current API Limitations

Looking at the list operation signature, there's a sortDirection parameter but no way to specify which field to sort by. This means even if ordering is applied, it's not clear what field is being used.

Your Workaround and Its Limitations

You mentioned using .secondaryIndexes() with .sortKeys(), but as you noted, this approach:

• Requires creating indexes for every query pattern
• Adds CloudFormation resources (contributing to the 2500 resource limit)
• Still doesn't solve the snapshot consistency problem for records added/deleted between pagination calls

The Deeper Challenge

Your second comment highlights an even more fundamental issue: true deterministic pagination requires snapshot isolation. Without it:

• New records inserted between page requests can shift boundaries
• Deleted records can cause gaps
• Backend algorithms processing data in chunks can't rely on consistent datasets

This is particularly problematic for microservices and backend processing where you need to iterate through entire datasets reliably.

Potential Paths Forward

This appears to require architectural consideration at the Amplify Data layer:

1. Short-term: Add implicit ORDER BY on identifier fields for all list operations
2. Medium-term: Enhance the API to support explicit orderBy field specification
3. Long-term: Consider cursor-based pagination with snapshot consistency guarantees (similar to database cursors or versioned snapshots)

Next Steps

This seems like a significant enough concern that it warrants. I am bring this up to the team for inputs and we welcome any ideas from the community to help us move this path forward.