Problem Statement

• Higher read rate than write rate

• data is rarely update

• read performance is priority

• parallel read and write

Proposal 1 - Async Query Based Change Capture

• For each data row, keep a updated_at date field, update that field whenever the data row is updated.

• At every set interval, query the database for updated_at >= x
• where x could be
• the last seen latest updated_at from the previous query
• the time of the previous query

• pull based

• cron

• async

• simple - no additional infrastructure other than existing database

• increases database load - compared to an event based push approach
• could be mitigated by using indexes - trade off between write and read latency

• no eventual consistency guarantees between parallel caches - happens when a write updates updated_at to an earlier time and a read happens in between the updated_at and the actual write
• x could be negative offset to reduce chances of inconsistencies - trade off between query performance and inconsistency

• the length of the interval determines the longest possible lag between data update and data syncing across the servers’ in-memory cache

Proposal 2 - Event based message queue

• On database update, sends a message to queue that represents the change event

• consumers consume from the queue and update their cache accordingly

• consumers: listen vs poll

• write-back vs write-through
• write-back might result in loss of data
• write-through increases write latency

• takes load off database

• guaranteed eventual consistency

• less lag than proposal 1

• additional infrastructure

References

1. MediumLess is More: A Case for Query Based Change Data Capture
   

   Less is More: A Case for Query Based Change Data Capture

   Choose simple technology for long term stewardship

   Medium