CDC ( change data capture )

[gedw99]

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

[avelino]

CDC is fantastic and useful for many purposes, especially for software communication.

@gedw99 I don't think i understand your proposal, you listed some tools that we can use in prestd.

• are you proposing that prestd support cdc?
• if yes, to do what?
• can you elaborate a little more on the idea of having cdc support in prestd?

[fabriziomello]

Postgres support CDC by Logical Decoding and it can be used out of the box together with pREST to get changes using the REST API.

[gedw99]

Excellent link. Requires a golang plugin according to docs.

[fabriziomello]

Postgres is written in C and the most common way to implement Logical Decoding Plugins is using this language. Also you can use either golang (a bit more difficult but is possible using cgo - https://gitlab.com/microo8/plgo, https://github.com/prest/bgworker) or Rust (using pgx - https://github.com/tcdi/pgx). But instead you can simple use an existing wal decoding that decode the changes to JSON like this one: https://github.com/eulerto/wal2json.

[vmesel]

@fabriziomello are we still talking about pREST or are we just talking about CDC tech? I don't see a fit for our project right now, @gedw99.

If you think otherwise, let us know!

[fabriziomello]

@vmesel I'm also not entirely sure the @gedw99 proposal so my comments are more in the sense that actually for Postgres CDC we can do it out of the box using existing Postgres infrastructure and use pREST to provide it as a REST API. Maybe this could be a good use case to write some documentation.

[gedw99]

@fabriziomello like with citus ?

Docs are a good idea because setting this up is not easy for people.