I'm reaching out regarding an intermittent issue in our application where data, despite being present, is occasionally reported as missing. We are currently utilizing Amazon Aurora PostgreSQL v10.21, operating within a Python Flask environment with SQLAlchemy ORM.
Our setup involves Writer and Reader instances; the Writer handles tasks such as writing and deletion, while the Reader is dedicated to reading operations. An issue arises when, after creating specific data (identified by unique identifiers) in the Writer session, the Reader session reports the data as non-existent when attempting to retrieve it.
We start the retrieval process not immediately, but around 6 seconds after the data creation, assuming this time frame is sufficient for replication from Writer to Reader. However, the Reader continues to report the absence of this data. Upon examining the logs and conducting searches, the data is indeed present (and there was no replica lag at the time).
To address this, we implemented a defensive mechanism, performing repeated queries over approximately 24 seconds at 6-second intervals. Unfortunately, this approach hasn't been effective.
We've observed no other issues with DB Write IOPS or CPU usage. Our current suspicion lies with either a potential issue in the DB engine or a bug in the SQLAlchemy ORM we are using. We've thoroughly reviewed our application logic and believe it to be error-free. Furthermore, reprocessing with actual data suggests that if there were a logic problem, it would have manifested consistently across all data.
If anyone has experienced similar issues, I would appreciate insights on how they were resolved or suggestions on what might be the underlying cause. Notably, while the AWS Aurora Postgres patch notes mentioned a resolved issue in version 10.11, we are using version 10.21, leading us to question whether the engine might still be implicated and seeking insights from others' experiences.
Thanks.
++)
FYI.. The version of SQLAlchemy you are using is as follows
SQLAlchemy==1.3.22
Flask-SQLAlchemy==2.4