CDC ( change data capture ) #769

gedw99 · 2023-01-21T10:21:23Z

CDC is where all changes to a database are created as events such that you can react to those changes.

it’s a common pattern for databases and primarily provides for the following use cases:

Real time queries such that you can update the gui by keeping a presence mapping of which user is viewing which data.

Workflows so that you can build plugins of middle tier packages that react to changes to the database.

micro services what a change in one micro service needs to be told to another microservice.

Clustering a db so that all changes are replicated to another db . Typically this is a master / slave scenario. But you can also make a new master when the original master fails.

There are a few ways of designing a way to handle these change events.
The event is described as a protobuf that is then serialised as json. The protobuf is important because you may have 2 micro services using 2 different sets ions of prestd.

The transport should also be pluggable so that developers can stream the change event over different protocols. NATS, websockets, SSE, etc. I would be biased to use NATS because it can be embedded in prestd or run externally. Then you can write adapters that will convert the evebt to the different protocols.

Security is also a factor because you want to control what change events go to what subscribers. NATS has all this already with full AAA ( accounts, authentication and Authourisation).

For the Clustering scenario NATS can a ts as a proxy over the RESTful connections to the db, can direct the users rest request to the nearest db. If the db transaction is a mutation it can automatically direct the mutation to the Master db.

there are already many libraries that do these parts of this that are worth looking into such as:

https://github.com/maxpert/marmot

uses NATS to do it all
ide for SQLite though.

https://github.com/disintegrator/benthos-pg-listen/

does cdc for postresql
Integrates to benthos in this case to send the evebt to benthos so that the evebt can be reacted to use the many benthos workflow and data transformation aspects.

I know this is an epic issue but that’s partly because CDC is so useful.

there is a temptation to store the events in the db and there is nothing wrong with that but there is a myriad of things that people do with cdc and so o would suggest that tapping into NATS provides the required loose coupling needed to provide the diversity of use cases needed.

Nats also use jwt. Their nsc tool manages that. It will distribute the jwt also.

Nats is designed for cloud and on premise edge so that users can keeep their db on premise and everything else in the cloud. Called a Leaf node in the Nats world. The cloud Nats can then redirect the db mutations to the OnPremise NATS Leaf, and db queries to the OnClood slaves. The cdc under Meath will replicate all Master db changes to the on cloud slaves.

https://github.com/nats-io/nats-server

https://github.com/nats-io/nats.go

NATS can be embedded with prestd. NATS is very fast and fault tolerant.

Reatful all proxy:
https://github.com/nats-io/nats-rest-config-proxy

rest proxy:

https://github.com/wshaddix/http-nats-proxy