Does an update of 100 rows result in 100 webhook calls or 1 webhook with a payload of 100 rows?
Show HN: Skor – Drop-in microservice to get Postgres changes as JSON webhooks
21–30 of 39 posts
Re: Show HN: Skor – Drop-in microservice to get Postgres changes as JSON webhooks
#22Earlier quoted context omitted.
Unfortunately, most of these LR programs don't work in AWS (RDS) land, since they require PG plugins, and RDS LR only supports the built-in test decoder. I wrote a similar program that uses LR to stream changes to AWS's Kinesis[1], using a mini library that parses the output of the default test decoder[2] as a result, in case anyone else is in RDS and has the same limitation. [1] https://github.com/nickelser/pg_kines…
IIRC Debezium is RDS compatible since version 0.7.0
That's awesome -- wasn't aware that was the case (wasn't many moons ago :). Thanks for the heads up! But, we don't use Kafka internally so it's sadly a non-option still...
Re: Show HN: Skor – Drop-in microservice to get Postgres changes as JSON webhooks
#23Interesting. A quick look at the code indicates you're just doing a notify on the whole row. Have you tested with large rows? The postgres notify function fails with a payload larger than 8000 bytes, and that limit cannot be changed at runtime, last I looked. I wrote a similar thing to notify on changes, but had to just fire notifies on what changed (id, column names) and there was a second step in the listening code…
Yes. That is correct. It fails with large rows. I'll add this to the limitations. The trigger currently sends the entire row but it can be modified to send just the unique identifier for a row. I'll add the documentation to do this. Tracking this here: https://github.com/hasura/skor/issues/6
(I work at Hasura)
Re: Show HN: Skor – Drop-in microservice to get Postgres changes as JSON webhooks
#24Does an update of 100 rows result in 100 webhook calls or 1 webhook with a payload of 100 rows?
100 webhook calls as the trigger is executed whenever a row is modified.
Postgres's per statement triggers don't let you capture the rows that are modified and skor doesn't do any sort of batching currently.
Re: Show HN: Skor – Drop-in microservice to get Postgres changes as JSON webhooks
#25Cool! Does this handle rate limiting or retries?
Currently there is no support for rate limiting or retries of webhook calls. I've opened an issue (https://github.com/hasura/skor/issues/7)
Re: Show HN: Skor – Drop-in microservice to get Postgres changes as JSON webhooks
#26Does an update of 100 rows result in 100 webhook calls or 1 webhook with a payload of 100 rows?
100 webhook calls as the trigger is executed whenever a row is modified. Postgres's per statement triggers don't let you capture the rows that are modified and skor doesn't do any sort of batching currently.
> Postgres's per statement triggers don't let you capture the rows that are modified
They do these days. Since 10 you can specify:
REFERENCING { { OLD | NEW } TABLE [ AS ] transition_relation_name } [ ... ] ]
Re: Show HN: Skor – Drop-in microservice to get Postgres changes as JSON webhooks
#27This looks cool, and another approach is pg_amqp_bridge to send events like this into rabbit. A nice feature is that the triggers don't block:
https://github.com/subzerocloud/pg-amqp-bridge
You can then push to websockets from rabbit with STOMP
Re: Show HN: Skor – Drop-in microservice to get Postgres changes as JSON webhooks
#28Looks cool. Seems like a good alternative if you can’t use listen/notify for some reason. I’ve used listen/notify with triggers for a while with good success. The flexibility of my apps controlling what they want to listen for instead of having that config elsewhere. Downside to l/n is that if you aren’t listening you aren’t getting changes. Seems like the same is true if the webhook fails here since there isn’t a re…
I remember this old article about Debezium's now-unmaintained predecessor, Bottled Water. Its diagrams may still be useful for those wanting to understand how this might be used. https://www.confluent.io/blog/bottled-water-real-time-integr...
Interesting
Re: Show HN: Skor – Drop-in microservice to get Postgres changes as JSON webhooks
#29This problem is one of my biggest needs with postgres, although its certainly not limited to pg. listen/notify is fragile - you either have a single point of failure or you run multiple workers and have to deal with de-duping. Logical replication might one day be an answer, but not likely to ever work completely and without issue on cloud hosted instances. (maybe im wrong about this... see debezium comments elsewhere in the comments)
What ive been thinking of lately is doing triggers to write to a different table with a timestamp and doing a rolling log - the worker can listen/notify on that but can also keep track of the last message it saw and can go back and catch up when it first starts, so if it fails you dont lose anything. All I really need is a type (table) and the relevant keys. I think I would prefer a way to configure which tables I want to listen on and which columns (keys) I care about.
Really wish there was a standard postgres provided way to notify an external system of changes though. Its certainly not an easy issue.
Re: Show HN: Skor – Drop-in microservice to get Postgres changes as JSON webhooks
#30Very cool and I see myself using this in the future. Is an approach like this best practise or rather meant as intermediate hack?
In general I have not seen the DBMS emitting changes as a best practice. The applications modifying the data should be emitting events or messages appropriately. Someone can correct me if I am wrong.
what if you have multiple applications writing data? The central repository, most authoritative copy of your data is your database.