
When an SQL Database Makes a Great Pub/Sub - m110
https://threedots.tech/post/when-sql-database-makes-great-pub-sub/
======
inopinatus
There’s a perspective that the transaction log of a typical RDBMS is the
canonical form and the rows & tables merely the event-sourced projection.
After all, if you replay the former, you should always get exactly the same in
the latter.

It’s curious that over those projections, we then build event stores for
CQRS/ES systems with their own projections mediated by application code.

Let’s also mention the journaled filesystem on which the database logs reside.
And the log structure that your SSD is using internally to balance writes.

It’s been a long time since we wrote an application event stream linearly
straight to media, and although I appreciate the separate concerns that each
of these layers addresses, I’d probably struggle to justify them from first
principles to even a slightly more Socratic version of myself.

~~~
marco_craveiro
Indeed, I've also puzzled over this a fair bit. Its almost as if we are
lacking a lower-level interface to the transaction log that enables one to
push events without going via the higher-level representation of tables etc.
However, the implementation details are somewhat beyond me :-) Postgres'
Bottled Water [1] was what made me think about this. I mean, why bother with
exporting to Kafka at all, and instead just use the Postgres transaction log
directly?

[1] [https://github.com/confluentinc/bottledwater-
pg](https://github.com/confluentinc/bottledwater-pg)

~~~
ryeguy
It's much easier to scale kafka than a relational db. There's also an
advantage to offloading the read load to another system instead of hitting the
db for that, too.

~~~
marco_craveiro
Before I proceed, let me just state that I have no particular knowledge of
either RDBMS' internals or Kafka, so this is nothing but my own amateur
musings on the subject.

Now, I wholeheartedly agree that Kafka is more scalable, but I think the key
point here is that there is no particular law of nature as to why that is the
case. It may just be an historical accident of how RDBMS - and PosgreSQL in
particular - have evolved. Further: many of the properties of Kafka are in
fact also desirable properties for the PostgreSQL transaction log.

My take on the inopinatus observation, together with the Samza article [1]
mentioned on this discussion, are as follows. You can think of Postgres as two
"products" (bounded contexts, if you like):

\- a stream-based, possibly replicated, transaction log;

\- a projection of that transaction log into relational calculus, plus all of
the associated machinery.

Thus far we never had the need to think of these as clearly separate
"products", but Kafka makes it obvious that they are. In truth, the amount of
tools processing WAL outside of PostgreSQL were already hinting in this
direction; Kafka just made it obvious.

From this perspective, it seems a tad expensive to take the original
transaction log, convert it to a RDBMS representation, then convert it to
events and, in some cases, then store it as an event stream in Kafka. It would
be much more efficient to simply use the original transaction log directly -
and this is why, to me, even Debezium [2] / Bottled Water [3] appear to be one
layer too many. To the best of my understanding, this line of reasoning is
also line with the observations in the Samza article [1]. Where I believe I
differ from the article is in thinking that the RDBMS representation also adds
a lot of value to applications - I see both having a role (e.g. streaming vs
batch processing sort of thing). I think this would derail the present
discussion too much, so I won't go in to it.

In conclusion: to the untrained eye, it seems that the right thing to do is to
extract the transaction log out of PostgreSQL and make it as scalable as
Kafka. Then, allow for it to log "things" which are not necessarily
"projectable" into the relational plane. PostgreSQL then becomes just a client
of the transaction log, together with other "kinds" of clients. I suspect that
this is what will ultimately happen, but the engineering work required will
probably span a decade or more.

My 2 Angolan Kwanzas, at any rate.

[1] [https://www.confluent.io/blog/turning-the-database-inside-
ou...](https://www.confluent.io/blog/turning-the-database-inside-out-with-
apache-samza/)

[2] [https://debezium.io/](https://debezium.io/)

[3] [https://github.com/confluentinc/bottledwater-
pg](https://github.com/confluentinc/bottledwater-pg)

~~~
ryeguy
Kafka can scale and distribute individual streams across the cluster. That is,
the entire "database" is distributed across nodes. With postgres, your unit of
scalability is the entire database. You can't natively have some tables on one
node and some tables on another node, for example.

Kafka is also just more optimized for what it does. Postgres is a superset of
what kafka does, so kafka is unsurprisingly better able to optimize for its
usecase. It has a zero-copy protocol that can shuttle data to/from disk
to/from the network without bringing it into memory (using the sendfile
syscall). It doesn't wait for disk writes when doing writes, because it
achieves durability via replication.

Also, don't forget the things you'd have to do when implementing consumers.
How will you load balance streams between consumers? Meaning, if you have a
stream and you want multiple consumers to burn it down at a time, how can you
make sure they aren't duplicating work and they can handle the consumer group
growing/shrinking? How will you handle checkpointing where each consumer
tracks what they've done so far? What about streams' data rolling off?

All of this is doable with pg, but you'd have to implement it yourself. With
kafka and its client drivers, this is handled for you.

~~~
marco_craveiro
As I said, I haven't given a lot of thought about this so please take my
opinion with a grain of salt - but I believe that once you split the log out
of PostgreSQL, a lot of functionality of this ilk could start to be
considered. When/if added, I think it would make for a stronger PostgreSQL in
the end. However, I do understand this is an insanely hard amount of work. In
a way, it bears some similarities to splitting GTK out of GIMP, for instance;
extremely difficult thing to do but ultimately it turned out to be a massive
win for both projects. This would be even harder, but ultimately, greatly
advantageous.

------
rmetzler
Really, I wouldn’t teach junior developers that it’s ok to use a database
table when a queue is needed. Sure, you can get away with this and there are
cases when it’s all you need. But I’ve been one of those juniors who forgot to
limit the query, who didn’t have enough indices, who tried to order all
records by date and had full table scans everywhere, who implemented the
worker with a cron job and didn’t synchronize this with a lock.

It might work, but it’s not the general case and you might spend more time to
debug your table then to write the code to use a real queue.

And I’ve also seen people build their own queueing engine for a few hundred
tasks per day. Why don’t they just choose one of the very good open source
solutions?

~~~
andrewstuart
"Because someone might add bugs to the code, or do it wrong" isn't a reason
not to take a particular approach.

~~~
mzz80
Of course it is. When one approach is far more likely to introduce bugs,
complicated interactions, and be more difficult to maintain, it is absolutely
a reason to not take a particular approach. It’s one of factors you need to
consider in everyday software engineering.

If you don’t take this into consideration then you’re detracting from the
business to satisfy another need.

------
zzzeek
The "database as message queue" pattern is quite common and often considered
to be an antipattern, which I tend to agree with but I don't have that strong
of a position on it myself. I've certainly used this pattern for expediency,
but that was before we had all the messaging solutions we do today.
[http://mikehadlow.blogspot.com/2012/04/database-as-queue-
ant...](http://mikehadlow.blogspot.com/2012/04/database-as-queue-anti-
pattern.html) has some good points.

~~~
bradstewart
A lot has changed in the 7 years since that was written.

Polling isn't a huge issue to begin with, and is mitigated with LISTEN/NOTIFY
(on certain DBs). Inserts with indexes are not a performance problem at the
scale of most applications. A separate messaging service won't prevent you
from building a "hugely coupled monster".

Personally, I almost always start with the database as a queue. The
operational overhead of running, updating, and monitoring another entire
service is non trivial. If the messaging rate exceeds the database's
capabilities in the future, I'll migrate then.

~~~
tartoran
If you need just one queue yes. If you have lots of queues it’s worth
investing in a queue service of some sort and there are many of them out there
which is a good thing but could turn into a bad thing quickly. In the past I
worked at a place that had 3 different queueing services implemented by
different developers and it became a pain to manage them or to even know what
was on the queues.

------
zinxq
I've always considered message-queues as a close cousin (if not sibling) of
databases. Arguably performing the same function with different foci. Pub/sub
focusing on the "oplog". DBMS focusing on "state".

(Blockchain another "oplog" that ends up caring a lot about state eventually).

It's no wonder you can use them interchangeably in many common base cases.

------
rmrfchik
Seems like they fall into the same pit as many does: using primary keys with
autoincrement as offset. This leads to skipping messages because there is no
guarantees that primary keys will be available in monotonic order. Because,
you know, transactions.

~~~
Fire-Dragon-DoL
Can you expand a bit on this? From my understanding, autoincrement keys ca mn
have gaps, but are always increasing. Sometimes a message might arrive "late",
so you get a 3,then a 2. This problem cannot really be solved without giant
locks that are not ideal. As far as I'm aware, all messaging systems are
subject to this problem.

Messages will never arrive, arrive out of order and I don't remember the third
one right now (messages will arrive late?)

~~~
nicois
Databases such as postgresql will effectively issue a buffer of keys to each
connection, meaning in some circumstances the sequences will not be monotonic
with respect to time. Also that usually long running transactions will use the
timestamp the transaction was opened, regardless of how many seconds have
passed between then and when the statement is executed.

~~~
Fire-Dragon-DoL
Very interesting details, thanks. So the alternative is have inconsistency, or
"giant locks". One is not performant, the other is inconsistent.

Tough choice, interesting nevertheless

------
linuxhansl
Perhaps it's not so much about pub/sub, but about store-and-forward.

When the "forward" part of "store-and-forward" is most important then Kafka is
a fine solution.

However, when the "store" part - for example you want to be able to stream
historical data again, or interact with the data in different ways - is most
important I have recommended HBase (+ Phoenix) as a better solution in the
past.

------
kiwicopple
For anyone just looking for ‘plug and play’ web socket pub/sub functionality,
I have been developing something that provides the functionality for
PostgreSQL:
[https://github.com/supabase/realtime](https://github.com/supabase/realtime)

It's an Elixir server (Phoenix) that allows you to listen to changes in your
database via websockets. Basically the Phoenix server listens to PostgreSQL's
replication functionality, converts the byte stream into JSON, and then
broadcasts over websockets. The beauty of listening to the replication
functionality is that you can make changes to your database from anywhere -
your api, directly in the DB, via a console etc - and you will still receive
the changes via websockets.

The article suggests Postgres’ native LISTEN/NOTIFY functionality. I tried
that originally and found that NOTIFY payloads have a limit of 8000 bytes, as
well a few other inconveniences.

It's still in very early stages, although I am using it in production at my
company and will work on it full time starting Jan.

~~~
starik36
One way to get around the 8k NOTIFY limit is to only use the capability to
notify only. It would them be incumbent on the client to go fetch the data
from a table somewhere. I ran into a similar limitation with SQL Server 2005
years ago and used this approach with great success.

------
marco_craveiro
MessageDB was doing the rounds in reddit the other day [1]. Looks interesting
for simple use cases...

[1]
[https://www.reddit.com/r/PostgreSQL/comments/ebu6nh/message_...](https://www.reddit.com/r/PostgreSQL/comments/ebu6nh/message_db_event_store_and_message_store_for/)

------
gunnarmorling
That's basically the same pattern as the "outbox pattern", e.g. listed in
Chris Richardson's pattern of microservices patterns.

An alternative implementation is provided by Debezium [1], a general solution
for change data capture for MySQL, Postgres, MongoDB, SQL Server and others,
based on top of Apache Kafka (but can also be used with Pulsar and others).

There's support for outbox coming as part of Debezium out of the box [2].

Disclaimer: I'm working on Debezium.

[1] [https://debezium.io/](https://debezium.io/) [2]
[https://debezium.io/documentation/reference/1.0/configuratio...](https://debezium.io/documentation/reference/1.0/configuration/outbox-
event-router.html)

------
nickjj
If anyone is using Elixir, Oban[0] is a job processor that uses PostgreSQL for
its back-end and state management.

It's incredibly well written and I am using it in a project.

[0]: [https://github.com/sorentwo/oban](https://github.com/sorentwo/oban)

------
TheCowboy
One fun open source software I've played with, that I don't think many have
heard of, is Deepstream.io. It attempts to be a batteries included real-time
web server that works with websockets, and can function as pub/sub server and
client. It has a connector for using PostgreSQL as the database. The frontend
JavaScript library is really easy to get working.

[https://deepstream.io/tutorials/concepts/what-is-
deepstream/](https://deepstream.io/tutorials/concepts/what-is-deepstream/)

[https://github.com/deepstreamIO/deepstream.io](https://github.com/deepstreamIO/deepstream.io)

(I'm not affiliated with the project.)

~~~
_frkl
Thanks, this does really look interesting. I'd like to find something generic,
lightweight to replace Kafka or Pulsar. Not sure this could be it, but it
looks like it'd be worth having a look at...

------
120bits
This could slightly out of context.

I'm working on a module that send notifications to a user when an alert is
generated. I have PostGreSQL as the database and NodeJS is the handler and for
connection pooling. Are there any good pub/sub tools that I can use. Thanks in
advance.

~~~
porsager
How about simply having an after insert trigger on an alerts table that calls
notify, and then you listen for that in node? It's a simple setup with less
moving parts and could probably get you a long way...

~~~
120bits
Thanks, this will probably work for me. However, if the inserts are higher, I
don't want to get notified that frequently. How would I add a periodic alerts
to this? Thank you!

~~~
porsager
You could use a column/table to track notifications. Eg. have an alert_sent_at
column on the alerts table. Then when the node service receives the notify on
an insert you can defer based on any logic you need, and send notifications
when needed by a query that fetches alerts with alert_sent_at = null.

~~~
120bits
Thank you! Thank you so much :)

~~~
porsager
You're welcome. To ensure you're not sending alerts twice either select and
update in a transaction or use a CTE like:

    
    
      with alert as (
        select alert_id from alerts 
        where alert_sent_at is null
      )
      
      update alerts set
        alert_sent_at = now()
      from alert
      returning *

------
vlasky
Meteor provides pub/sub with a MySQL backend using the atmosphere package
vlasky:mysql.

It works by following the MySQL binary log and triggering a reactive query
based on event conditions specified by the programmer, e.g. a change in a
field.

[https://atmospherejs.com/vlasky/mysql](https://atmospherejs.com/vlasky/mysql)

------
slowhand09
Oracle has a very advanced and flexible system for this. It is called Advanced
Queueing.

------
Halluxfboy009
Ever use google's firebase? While not SQL -- I've always felt `tis a nice
solution to persistence+async...

