4 ms·

Postgres Webhooks with Pgstream

160 points by mebcitto 2y ago

jitl 2y ago
The GitHub repo README gives a better sense of the capabilities of the pgstream system: https://github.com/xataio/pgstream?tab=readme-ov-file#architecture https://github.com/xataio/pgstream?tab=readme-ov-file#archit... For deployments at a more serious scale, it seems they support buffering WAL events into Kafka, similar to Debezium (the current leader for change data capture), to de-couple the replication slot reader on the Postgres side throughput from the event handlers you deliver the events to. Pgstream seems more batteries-included compared to using Debezium to consume the PG log; Kafka is optional with pgstream and things like webhook delivery and OpenSearch indexing are packaged in, rather than being a “choose your own adventure” game with Kafka Streams middleware ecosystem jungle. If their offerings constraints work for your use-case, why not prefer it over Debezium since it seems easier? I’d rather write Go than Java/JVM-language if I need to plug into the pipeline. However at any sort of serious scale you’ll need the Kafka in there, and then I’m less sure you’d make use of the other plug and play stuff like the OpenSearch indexer or webhooks at all. Certainly as volume grows webhooks start to feel like a bad fit for CDC events at the level of a single row change; at least in my brief Debezium CDC experience, my consumer pulls batches of 1000+ changes at once from Kafka. The other thing I don’t see is transaction metadata, maybe I shouldn’t worry much about it (not many people seem to be concerned) but I’d like my downstream consumer to have delayed consistency with my Postgres upstream, which means I need to consume record changes with the same transactional grouping as in Postgres, otherwise I’ll probably never be consistent in practice: https://www.scattered-thoughts.net/writing/internal-consistency-in-streaming-systems/ https://www.scattered-thoughts.net/writing/internal-consiste...
- eminano 2y ago
  That's a great summary! You're right, at scale, Kafka allows you to parallelise the workload more efficiently, as well as supporting multiple consumers for your replication slot. However, you should still be able to use the plug and play output processors with Kafka just as easily (implementation internally is abstracted, it works the same way with/without Kafka). We currently use the search indexer with Kafka at Xata for example. As long as the webhook server has support for rate limiting, it should be possible to handle a large amount of event notifications. Regarding the transaction metadata, the current implementation uses the wal2json postgres output plugin (https://github.com/eulerto/wal2json https://github.com/eulerto/wal2json), and we don't include the transactional metadata. However, this is something that would be easy to enable, and integrate into the pgstream pipeline if needed, to ensure transactional consistency on the consumer side. Thanks for your feedback!
adamcharnock 2y ago
A quick google didn’t reveal much about pgstream’s delivery semantics. Do these webhooks get called at most once, or at least once? I hope the latter, but in absence of information I’d guess it is the former.
- simonw 2y ago
  It looks to me like it's at-most-once. This code here https://github.com/xataio/pgstream/blob/bab0a8e665d37441351cf206c3de6843c079193e/pkg/wal/processor/webhook/notifier/webhook_notifier.go#L182-L193 https://github.com/xataio/pgstream/blob/bab0a8e665d37441351c... logs an error if a delivery fails but does not appear to queue it for a second attempt.
  - jitl 2y ago
    Sad! Suitable for driving “best effort” use cases like reactive UI updates or stale-while-revalidate cache refreshes, but not usable for “business logic” like updating user access lists, billing, sending emails, or maintaining a secondary index of any sort.
    - eminano 2y ago
      The current implementation of the webhook notifier will call the webhook URL at most once. There's no real reason for it beyond it being a first iteration of the webhook output plugin. I've created an issue in the repo to support retries , since this is a valuable feature (https://github.com/xataio/pgstream/issues/67 https://github.com/xataio/pgstream/issues/67). Worth mentioning that the retry strategy is defined on a processor level. The opensearch indexer for instance does handle retries, making it "at least once" delivery. Thanks for your feedback!
simonw 2y ago
Implementing webhook deliveries is one of those things that's way harder than you would initially imagine. The two things I always look for in systems like this are: 1. Can it handle deliveries to slow on unreliable endpoints? In particular, what happens if the external server is deliberately slow to respond - someone could be trying to crash your system by forcing it to deliver to slow-loading endpoints. 2. How are retries handled? If the server returns a 500, a good webhooks system will queue things up for re-delivery with an exponential backoff and try a few more times before giving up completely. Point 1. only matters if you are delivering webhooks to untrusted endpoints - systems like GitHub where anyone can sign up for hook deliveries. 2. is more important. https://github.com/xataio/pgstream/blob/bab0a8e665d37441351cf206c3de6843c079193e/pkg/wal/processor/webhook/notifier/webhook_notifier.go#L49-L51 https://github.com/xataio/pgstream/blob/bab0a8e665d37441351c... shows that the HTTP client can be configured with a timeout (which defaults to 10s https://github.com/xataio/pgstream/blob/bab0a8e665d37441351cf206c3de6843c079193e/pkg/wal/processor/webhook/notifier/config.go#L22C26-L22C28 https://github.com/xataio/pgstream/blob/bab0a8e665d37441351c... ) From looking at https://github.com/xataio/pgstream/blob/bab0a8e665d37441351cf206c3de6843c079193e/pkg/wal/processor/webhook/notifier/webhook_notifier.go#L182-L193 https://github.com/xataio/pgstream/blob/bab0a8e665d37441351c... it doesn't look like this system handles retries. Retrying would definitely be a useful addition. PostgreSQL is a great persistence store for recording failures and retry attempts, so that feature would be a good fit for this system.
- maxbond 2y ago
  Really any time you're using a distributed system, you should ask yourself, do I need retries? Do I need circuit breakers? Do I need rate limits? The answers are probably either "yes" or "yes, but not yet."
- tasn 2y ago
  They should defo use Svix[1], I'll reach out to the blog author, this looks like a cool blog post and use-case. 1: https://www.svix.com https://www.svix.com
- kgeist 2y ago
  >a good webhooks system will queue things up for re-delivery with an exponential backoff and try a few more times before giving up completely. Microsoft's Graph API also requires consumers to re-register webhooks every N days, a kind of "heartbeat" to make sure the webhooks aren't sent to a dead server forever.
canadiantim 2y ago
Xata putting out some cool pg things. Is PG roll still looking good these days? Seemed pretty sexy last time I looked at it
- gulcin_xata 2y ago
  pgroll is under active development, feel free to let us know if you have any feature requests here: https://feedback.xata.io/ https://feedback.xata.io/
tazu 2y ago
Curious if there's a reason this extension is written in Go and not Zig? The blog posted [1] a few months ago about their cool Zig library for making extensions and I've been playing around with it. Is Zig just not mature enough? [1]: https://xata.io/blog/introducing-pgzx https://xata.io/blog/introducing-pgzx
- gobblegobble2 2y ago
  It's not an extension. It's a standalone app that connects to postgres and listens to a replication stream.
oars 2y ago
Postgres webhooks could be very usefulness for me. Thanks for sharing.
newb76 2y ago
This is very nice! I came across this through an internal ref. It appears that there is a lot of movement in the CDC space lately - pgstream, sequinstream, bemi, pg_flo (Saw some guy on X posting videos of CDC tools too) Are there real world use cases being solved with this, or is it just hobby tool?
- whizzter 2y ago
  We maintain a two projects for a customer where data for the applications is held in secondary databases, can't count how many sync issues we've had with these projects. Right now there's is a continuous sync that processes tons of redundant data (with a certain latency due to the amount of data). CDC would enable the processes to focus what's changed instead of continually processing the entire database. I feel that DB support isn't necessarily the biggest issue for adopting these things, rather it's that it's somewhat more complicated to debug event driven systems for "regular" developers rather than request-response or loop based code. In that sense delivering the events via webhooks is a good idea since it allows them to work with their regular request-response debugging tools.

160 points by mebcitto 2y ago

jitl 2y ago

The GitHub repo README gives a better sense of the capabilities of the pgstream system: https://github.com/xataio/pgstream?tab=readme-ov-file#architecture https://github.com/xataio/pgstream?tab=readme-ov-file#archit... For deployments at a more serious scale, it seems they support buffering WAL events into Kafka, similar to Debezium (the current leader for change data capture), to de-couple the replication slot reader on the Postgres side throughput from the event handlers you deliver the events to. Pgstream seems more batteries-included compared to using Debezium to consume the PG log; Kafka is optional with pgstream and things like webhook delivery and OpenSearch indexing are packaged in, rather than being a “choose your own adventure” game with Kafka Streams middleware ecosystem jungle. If their offerings constraints work for your use-case, why not prefer it over Debezium since it seems easier? I’d rather write Go than Java/JVM-language if I need to plug into the pipeline. However at any sort of serious scale you’ll need the Kafka in there, and then I’m less sure you’d make use of the other plug and play stuff like the OpenSearch indexer or webhooks at all. Certainly as volume grows webhooks start to feel like a bad fit for CDC events at the level of a single row change; at least in my brief Debezium CDC experience, my consumer pulls batches of 1000+ changes at once from Kafka. The other thing I don’t see is transaction metadata, maybe I shouldn’t worry much about it (not many people seem to be concerned) but I’d like my downstream consumer to have delayed consistency with my Postgres upstream, which means I need to consume record changes with the same transactional grouping as in Postgres, otherwise I’ll probably never be consistent in practice: https://www.scattered-thoughts.net/writing/internal-consistency-in-streaming-systems/ https://www.scattered-thoughts.net/writing/internal-consiste...

eminano 2y ago
That's a great summary! You're right, at scale, Kafka allows you to parallelise the workload more efficiently, as well as supporting multiple consumers for your replication slot. However, you should still be able to use the plug and play output processors with Kafka just as easily (implementation internally is abstracted, it works the same way with/without Kafka). We currently use the search indexer with Kafka at Xata for example. As long as the webhook server has support for rate limiting, it should be possible to handle a large amount of event notifications. Regarding the transaction metadata, the current implementation uses the wal2json postgres output plugin (https://github.com/eulerto/wal2json https://github.com/eulerto/wal2json), and we don't include the transactional metadata. However, this is something that would be easy to enable, and integrate into the pgstream pipeline if needed, to ensure transactional consistency on the consumer side. Thanks for your feedback!

adamcharnock 2y ago

A quick google didn’t reveal much about pgstream’s delivery semantics. Do these webhooks get called at most once, or at least once? I hope the latter, but in absence of information I’d guess it is the former.

simonw 2y ago
It looks to me like it's at-most-once. This code here https://github.com/xataio/pgstream/blob/bab0a8e665d37441351cf206c3de6843c079193e/pkg/wal/processor/webhook/notifier/webhook_notifier.go#L182-L193 https://github.com/xataio/pgstream/blob/bab0a8e665d37441351c... logs an error if a delivery fails but does not appear to queue it for a second attempt.
- jitl 2y ago
  Sad! Suitable for driving “best effort” use cases like reactive UI updates or stale-while-revalidate cache refreshes, but not usable for “business logic” like updating user access lists, billing, sending emails, or maintaining a secondary index of any sort.
  - eminano 2y ago
    The current implementation of the webhook notifier will call the webhook URL at most once. There's no real reason for it beyond it being a first iteration of the webhook output plugin. I've created an issue in the repo to support retries , since this is a valuable feature (https://github.com/xataio/pgstream/issues/67 https://github.com/xataio/pgstream/issues/67). Worth mentioning that the retry strategy is defined on a processor level. The opensearch indexer for instance does handle retries, making it "at least once" delivery. Thanks for your feedback!

simonw 2y ago

Implementing webhook deliveries is one of those things that's way harder than you would initially imagine. The two things I always look for in systems like this are: 1. Can it handle deliveries to slow on unreliable endpoints? In particular, what happens if the external server is deliberately slow to respond - someone could be trying to crash your system by forcing it to deliver to slow-loading endpoints. 2. How are retries handled? If the server returns a 500, a good webhooks system will queue things up for re-delivery with an exponential backoff and try a few more times before giving up completely. Point 1. only matters if you are delivering webhooks to untrusted endpoints - systems like GitHub where anyone can sign up for hook deliveries. 2. is more important. https://github.com/xataio/pgstream/blob/bab0a8e665d37441351cf206c3de6843c079193e/pkg/wal/processor/webhook/notifier/webhook_notifier.go#L49-L51 https://github.com/xataio/pgstream/blob/bab0a8e665d37441351c... shows that the HTTP client can be configured with a timeout (which defaults to 10s https://github.com/xataio/pgstream/blob/bab0a8e665d37441351cf206c3de6843c079193e/pkg/wal/processor/webhook/notifier/config.go#L22C26-L22C28 https://github.com/xataio/pgstream/blob/bab0a8e665d37441351c... ) From looking at https://github.com/xataio/pgstream/blob/bab0a8e665d37441351cf206c3de6843c079193e/pkg/wal/processor/webhook/notifier/webhook_notifier.go#L182-L193 https://github.com/xataio/pgstream/blob/bab0a8e665d37441351c... it doesn't look like this system handles retries. Retrying would definitely be a useful addition. PostgreSQL is a great persistence store for recording failures and retry attempts, so that feature would be a good fit for this system.

maxbond 2y ago
Really any time you're using a distributed system, you should ask yourself, do I need retries? Do I need circuit breakers? Do I need rate limits? The answers are probably either "yes" or "yes, but not yet."
tasn 2y ago
They should defo use Svix[1], I'll reach out to the blog author, this looks like a cool blog post and use-case. 1: https://www.svix.com https://www.svix.com
kgeist 2y ago
>a good webhooks system will queue things up for re-delivery with an exponential backoff and try a few more times before giving up completely. Microsoft's Graph API also requires consumers to re-register webhooks every N days, a kind of "heartbeat" to make sure the webhooks aren't sent to a dead server forever.

canadiantim 2y ago

Xata putting out some cool pg things. Is PG roll still looking good these days? Seemed pretty sexy last time I looked at it

gulcin_xata 2y ago
pgroll is under active development, feel free to let us know if you have any feature requests here: https://feedback.xata.io/ https://feedback.xata.io/

tazu 2y ago

Curious if there's a reason this extension is written in Go and not Zig? The blog posted [1] a few months ago about their cool Zig library for making extensions and I've been playing around with it. Is Zig just not mature enough? [1]: https://xata.io/blog/introducing-pgzx https://xata.io/blog/introducing-pgzx

gobblegobble2 2y ago
It's not an extension. It's a standalone app that connects to postgres and listens to a replication stream.

oars 2y ago

Postgres webhooks could be very usefulness for me. Thanks for sharing.

newb76 2y ago

This is very nice! I came across this through an internal ref. It appears that there is a lot of movement in the CDC space lately - pgstream, sequinstream, bemi, pg_flo (Saw some guy on X posting videos of CDC tools too) Are there real world use cases being solved with this, or is it just hobby tool?

whizzter 2y ago
We maintain a two projects for a customer where data for the applications is held in secondary databases, can't count how many sync issues we've had with these projects. Right now there's is a continuous sync that processes tons of redundant data (with a certain latency due to the amount of data). CDC would enable the processes to focus what's changed instead of continually processing the entire database. I feel that DB support isn't necessarily the biggest issue for adopting these things, rather it's that it's somewhat more complicated to debug event driven systems for "regular" developers rather than request-response or loop based code. In that sense delivering the events via webhooks is a good idea since it allows them to work with their regular request-response debugging tools.