I just remembered another case where we wanted to use CDC to ensure consistency. This use case is also related to an event or pub/sub solution.
When inserting some data into the table in a transcation, we want to post-process each record afterwards because its a slow step and would benefits of scaling.
With CDC we could feed a queue system like the way PostgreSQL does it with Kafka.
Our currently solution is having a
state column indicating that the record needs post-processing. A daemon scripts fetches these records, processes them and updates the
state. However, this doesn’t scale very well because we cannot lock the records (we could update the record with some time lock column to prevent other processes from fetching the same records - even better would be the
SELECT ... FOR UPDATE SKIP LOCKED syntax).
Perhaps we actually doesn’t need a real CDC feature but more an event/notification feature. Unless, there exists some better best practies?