4 ms·

Looks interesting, I’ve been investigating Postgres CDC solutions for a while, so I’m curious if this could help my use-case. Can you elaborate on failure mode

by iyn 2y ago

Looks interesting, I’ve been investigating Postgres CDC solutions for a while, so I’m curious if this could help my use-case. Can you elaborate on failure modes? What happens if e.g. NATS server (or worker/replicator) node dies? In principle, how hard is it move data from Postgres not to another PG but e.g. ElasticSearch/ClickHouse?

shayonj 2y ago
re: worker/replicator dying, if the worker dies, nothing is basically reading from subject/queue (NATS), so the changes aren't reaching the destination. The default config is to have one worker. If the replicator dies, the changes from the replication slot isn't getting delivered to NATS, so the WAL size will grow on the source DB. There are some plans to introduce metrics/instrumentation to stay on top of these things, to make it more production ready. Replicator and worker can pause/resume the stream when say shutting down, say during a deploy. re: moving data, I haven't attempted it, but I have seen PeerDB mentioned for moving data to Clickhouse and it seems quite nice. I am starting w/ PostgreSQL <> PostgreSQL to mostly get the fundamentals right first. Would love to hear any use cases you have in mind.
- iyn 2y ago
  I have these use cases: 1. Syncing data in Postgres to ElasticSearch/ClickHouse (which handle search/analytics on the data I store in PG) 2. Invoking my own workflow engine — I have built a system that allows end-users to define triggers that start workflows when some data change/is created. To determine whether I need to start the workflow, I need to inspect every CRUD operation and check it against triggers defined by the users. I'm currently doing that in a duck-tape like way by publishing to SNS from my controllers and having SQS subscribers (mapped to Lambdas) that are responsible for different parts of my "pipeline". I don't like this system as it's not fault-tolerant and I'd prefer to do this by processing WAL and explicitly acknowledging processed changes.
  - shayonj 2y ago
    I think PeerDB is a smart choice if you are looking to sync the data to Clickhouse. They have nice integration support as well. re: 2. I have some plans around a control plane that allows users to define these transformation rules and routing config and then take further actions based on the outcomes. If you are interested in it, feel free to sign up on the homepage. Also, very happy for some quick chats too (shayonj at gmail). Thanks
    - iyn 2y ago
      Yeah, I've looked into PeerDB but in terms of self-hosting it's not really lightweight, as they depend on Temporal [0]. I'm currently optimizing for less complexity/budget, as I have just a few customers. [0] https://docs.peerdb.io/architecture#dependencies https://docs.peerdb.io/architecture#dependencies
      - shayonj 2y ago
        Yeah, totally fair. Are you ok with a NATS dependency ? Happy to work with you in supporting a new destination like ES. Also looking to make NATS optional for smaller/simpler setups (https://github.com/shayonj/pg_flo/issues/21 https://github.com/shayonj/pg_flo/issues/21)
        iyn 2y ago
        Yes, I think NATS is reasonable — I don't have operational experience with it but based on my earlier reading it seems that it can be run on a smaller budget. Is this "regular" NATS or the Jetstream variant?
        shayonj 2y ago
        Perf! From testing on some of my staging workloads, the footprint isn't too high and I can get 5-6k messages/s. Esp. since there is only one worker instance involved (for strict ordering). Yes, it does use NATS JetStream.
      - 2y ago
Onavo 2y ago
You can always use Supabase's realtime CDC tool, it's open source.
- iyn 2y ago
  Thanks for the suggestion. I've seen it on GitHub but haven't really looked into operating it. I was under the impression it's more focused on the real-time aspect than CDC. Do you have any experience managing it? I'm curious how easy is it to self-host it and integrate with rest of my system.

by iyn 2y ago

shayonj 2y ago

re: worker/replicator dying, if the worker dies, nothing is basically reading from subject/queue (NATS), so the changes aren't reaching the destination. The default config is to have one worker. If the replicator dies, the changes from the replication slot isn't getting delivered to NATS, so the WAL size will grow on the source DB. There are some plans to introduce metrics/instrumentation to stay on top of these things, to make it more production ready. Replicator and worker can pause/resume the stream when say shutting down, say during a deploy. re: moving data, I haven't attempted it, but I have seen PeerDB mentioned for moving data to Clickhouse and it seems quite nice. I am starting w/ PostgreSQL <> PostgreSQL to mostly get the fundamentals right first. Would love to hear any use cases you have in mind.

iyn 2y ago
I have these use cases: 1. Syncing data in Postgres to ElasticSearch/ClickHouse (which handle search/analytics on the data I store in PG) 2. Invoking my own workflow engine — I have built a system that allows end-users to define triggers that start workflows when some data change/is created. To determine whether I need to start the workflow, I need to inspect every CRUD operation and check it against triggers defined by the users. I'm currently doing that in a duck-tape like way by publishing to SNS from my controllers and having SQS subscribers (mapped to Lambdas) that are responsible for different parts of my "pipeline". I don't like this system as it's not fault-tolerant and I'd prefer to do this by processing WAL and explicitly acknowledging processed changes.
- shayonj 2y ago
  I think PeerDB is a smart choice if you are looking to sync the data to Clickhouse. They have nice integration support as well. re: 2. I have some plans around a control plane that allows users to define these transformation rules and routing config and then take further actions based on the outcomes. If you are interested in it, feel free to sign up on the homepage. Also, very happy for some quick chats too (shayonj at gmail). Thanks
  - iyn 2y ago
    Yeah, I've looked into PeerDB but in terms of self-hosting it's not really lightweight, as they depend on Temporal [0]. I'm currently optimizing for less complexity/budget, as I have just a few customers. [0] https://docs.peerdb.io/architecture#dependencies https://docs.peerdb.io/architecture#dependencies
    - shayonj 2y ago
      Yeah, totally fair. Are you ok with a NATS dependency ? Happy to work with you in supporting a new destination like ES. Also looking to make NATS optional for smaller/simpler setups (https://github.com/shayonj/pg_flo/issues/21 https://github.com/shayonj/pg_flo/issues/21)
      - iyn 2y ago
        Yes, I think NATS is reasonable — I don't have operational experience with it but based on my earlier reading it seems that it can be run on a smaller budget. Is this "regular" NATS or the Jetstream variant?
        shayonj 2y ago
        Perf! From testing on some of my staging workloads, the footprint isn't too high and I can get 5-6k messages/s. Esp. since there is only one worker instance involved (for strict ordering). Yes, it does use NATS JetStream.
    - 2y ago

Onavo 2y ago

You can always use Supabase's realtime CDC tool, it's open source.

iyn 2y ago
Thanks for the suggestion. I've seen it on GitHub but haven't really looked into operating it. I was under the impression it's more focused on the real-time aspect than CDC. Do you have any experience managing it? I'm curious how easy is it to self-host it and integrate with rest of my system.