Recently I ran into a scenario when the read operation was not performing well due to auditing requirements. If you need to persist an audit record on every read operation, writing to the database will most likely be the bottleneck.
There are a few improvements listed below that can be done to improve performance. Improvements are ordered by subjective performance gain.
- Defer writing audit records using asynchronous queue
- Consume records from the queue in batches and do bulk inserts
- Separate read and write operations and read data from replica
Detailed descriptions of steps in the flow:
- User requests to get the status of something
- Application registers an event "getStatusCalled" that getStatus request has been performed.
- Application retrieves status from database standby node, thus not loading primary database node. The user receives a response
- Another application node (or same application node, but a different thread) periodically consumes "getStatusCalled" events in batches
- Application performs bulk insert to primary database node. Changes are replicated to the standby node using the database replication process.
Have a feedback or improvement idea? Share it in a comment.
Top comments (0)