Hacker News new | comments | show | ask | jobs | submit login
Show HN: Skor – Drop-in microservice to get Postgres changes as JSON webhooks (github.com)
133 points by mesid 7 months ago | hide | past | web | favorite | 37 comments

Looks 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 retry.

If you want to check out another cool alternative see Debezium[1]. It’s uses PGs logical decoding feature to stream changes. Supports protobufs and JSON output.



I did use Debezium in a Kubernetes cluster to stream Postgres changes into Kafka and then through a custom application that can send them to clients via HTTP or WebSockets. But the entire system had multiple points of failure. So we then decided to write our own thing that connects to the LR slot and sends over WebSockets. Working on open sourcing that too!

can you talk more about the points of failure? and is there a place I could sign up for more information when you open source your websocket solution?

Hi. Sorry for the late reply.

Well the system had Postgres -> Debezium -> Kafka -> CustomApp -> Client. If data stopped flowing through the system, I would have to examine each component to figure the issue out. They'd mostly turn out to be Kubernetes network issues such as Kafka brokers not being able to talk to each other, or Debezium not being able to contact Kafka. This was too painful to run when all we wanted was notifications of changes on Postgres.

If you drop me a note with your email, I'll make sure to get in touch with you when our solution using Postgres LR exposed over WebSockets (and more) is out :)

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.


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_kinesis [2] https://github.com/nickelser/parselogical

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...

RDS supports wal2json.

Interesting. 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 to fetch the full details.

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)

I did something similiar, but directly from postgresql triggers: https://github.com/pramsey/pgsql-http

You just have to be careful with failing queries and set timeout to low.

This 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.

`table -> trigger to log table -> trigger to notify` is exactly the right solution here. You can even use the same stored proc to replay old logs.

Very cool, at my current job we have a realtime ETL process using Maxwell & Apache Kafka to stream replication events to a set of Node servers for a variety of transformations.

Seeing that this isn't hooked into replication is a bit worrying, but it seems they are going to implement it as a replication client which should make it much more robust.

This 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:


You can then push to websockets from rabbit with STOMP


> pg_amqp_bridge

Thanks for posting this. Can already see a couple of weekends burned trying to drive celery from pg.

Do you know if it is used in production somewhere; how battle-tested is it?

I've used it in a couple production deployments and it's solid. It's only ~300 lines of Rust code, so I'm confident it is simple and even if I had a problem, would be easy to understand.

Seems like there are a lot of people here who aren't aware of wal2json and RDS support for wal2json.

Does 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 } [ ... ] ]
in a FOR EACH STATEMENT ... AFTER trigger.

That's great news ! So the trigger can use json_agg to compose the notification with all the modified rows. We'll document it (https://github.com/hasura/skor/issues/8).

Very cool and I see myself using this in the future.

Is an approach like this best practise or rather meant as intermediate hack?

It’s more of an intermediate hack I’d say unless you don’t mind missing the odd event. We’re working on another implementation that uses the replication stream (like dbezium) that will be a more best-practice solution.

(I work at Hasura)

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.

I’d venture to say that is wrong at least in the case of Postgres.

In order to be able to reason with certainty about your data, information should be coming from the source closest to the truth, and that would be your database. You can’t really trust the application, it may have been written wrong.

Postgres’s LISTEN/NOTIFY commands, which are not part of the SQL standard, exist for these cases.

Part of the thought process around that is that most DBs don’t have a good option to do it well.

In larger applications, if you’ve got a need to have a stored procedure handle a complex modification your application will be blind to it.

Plus, at the application level you don’t have any way to enforce that the particular place in your code that modified the data and then emits the change is the ONLY place in your code that can modify that data. This becomes more pronounced over time too.

The problem is amplified if you find something that needs to be done to inbound data that is better handled in another language for some reason. Then you have to remember to duplicate the logic or hook your new code into the application, forcing a new layer in front of your database.

There are some things that the database is better left handling. :)

That was the conclusion I came to as well. If you just want to push data to a data warehouse by listening to the WAL or NOTIFY and then batch a bunch of updates together that's one thing. If you intend it to be application level events then coupling yourself to the schema of some other service (among other issues) seems problematic.

The most reliable implementation of this pattern is to use the replication binary logs of the database. Your application cannot atomically publish a write to both a database and a messaging system (without introducing a massive headache that you will implement incorrectly).

Yeah but I think it's important to consider whether any microservice should be able to listen to any other microservice inserts or updates. I think using the replication log as the underlying mechanism is a good idea, but you should really be choosing which messages to expose because it forms a public API.

The receivers choose which messages to expose by selecting which events to listen for. The broker then only forwards those events. The question is, how do the receivers specify the event type? Do they use the schema of the publisher, or some intermediate interface at the broker?

what if you have multiple applications writing data? The central repository, most authoritative copy of your data is your database.

Cool! 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)

Cool - I didn't expect this, but was curious.

Hi! It's very similar to what we built at https://base.run :) Take a look!

Guidelines | FAQ | Support | API | Security | Lists | Bookmarklet | Legal | Apply to YC | Contact