4 ms·

Perhaps the title of the article doesn't capture its underlying value. PubSubOnSQL Layers rarely make sense compared to dedicated Pub/Sub systems but the trade-

Perhaps the title of the article doesn't capture its underlying value. PubSubOnSQL Layers rarely make sense compared to dedicated Pub/Sub systems but the trade-off is acceptable in the three use cases described: 1. When your dedicated Pub/Sub is ephemeral and you need message durability, 2. When you need distributed transactions across SQL and your Pub/Sub system, and 3. When you need a poor man's heterogeneous SQL replication system. The academic/enterprise database space has been discussing and tackling the types of questions you raise for decades. I don't think that is a useful lens to evaluate this article which is effectively a "tips on when to use our GoLang SQL Pub/Sub layer".

jerf 7y ago
I'd add a #4, when you have such low volume that it just isn't worth it to put up a full system, with the accompanying need for deployment, resources, monitoring, and additional knowledge and skills added to the minimum set of knowledge and skills your team must possess. One must be careful not to use this as an excuse, of course, and keep an eye on the scaling concerns and certain other details. SQL-as-pubsub has certain well-known issues and anyone using it this way ought to be aware of them. But it's a thought worth having. I've got a system I'm managing where Cassandra is the backend. I've got about 5 "documents" (in the MongoDB sense, let's say) I want to store in the system. I don't put up a whole "document DB" for them, I just have a table in Cassandra. I have in my entire system, one distributed lock I'd like to have per certain resource, of which I expect there to be single digit numbers of that resource over the lifetime of the system. Cassandra is not a great distributed locker, but it does work (and as far as I can tell, done properly, is also correct), so rather than install an entire distributed lock server, I use Cassandra. Should this ever turn out to become a mistake, all code that uses either of these functions is cleanly isolated and I can easily swap them out later. I am aware of the possibility that could happen in the future and have prepared for it. In the meantime, I've avoided two entire systems being poorly deployed and understood in favor of the one system that is well-deployed and understood by the team.
- igammarays 7y ago
  > SQL-as-pubsub has certain well-known issues and anyone using it this way ought to be aware of them. Such as? Other than performance.
  - james_s_tayler 7y ago
    The key thing about the performance aspect is SQL produces bi-modal behavior due to there being a query planner / operator execution. If all of a sudden your performance characteristics change dramatically because the query planner produced a different result than normal due to something changing about your dataset, that could be pretty bad. So, not having this risk of bi-modal behavior is a nice feature of dedicated pub/sub and queue software.
  - jerf 7y ago
    In addition to what james_s_taylar said, another one is that you have to watch how the tables are structured internally, because for some databases having a table that writes a lot of rows and deletes them all very quickly is pessimal usage. You can get a table that fills up with tombstones internally. I've seen cases where it literally takes seconds to pull a single message off the queue from an otherwise unloaded server, because the act of "SELECT * FROM messages LIMIT 1" requires a huge table scan across a whole bunch of tombstones, which can be very frustrating to diagnose since it looks like the table is nearly empty. I have an application at work I have to use (not something I wrote) based on an old version of Postgres that was experiencing this until quite recently when they upgraded, and I'm still not 100% sure that it's fixed because it hasn't been enough time. (I do expect it to be fixed, though.) The aforementioned Cassandra is pretty much catastrophic in this use case, from what I've seen; I didn't try to use it as a message queue, all I did was write some unit testing that involved adding some test records during the test and removing them, then running a whole bunch of other tests that would add records to the same primary key and remove them, simply to clean up for the subsequent tests, a very similar pattern as using a table as an event queue from the database's point of view even though we humans see that as quite different, and I very quickly noticed my tests were running noticeably slower every execution, on scales that compared to what Cassandra can nominally handle were very, very tiny. For the testing, I found I could use TRUNCATE, which tells Cassandra "Hey, I'm not just happening to delete every row in this table, I'm actively nuking it, so please clear it out", resulting in Cassandra completely wiping the table tombstones and all, which was fine for this unit test case where they run synchronously relative to each other and I have clear points where I can say "just throw the table away now, tombstones and all", but that's completely unsuitable for an event queue use. Somewhat ironically, you sometimes want the more "primitive" table types for this use care. IIRC there was a time when if you were using MySQL, you'd want to use a MyISAM-type table for the queue table, because it pretty much works as you expect and deleted rows are actually deleted and gone, but InnoDB used the "higher performance" tombstone-based approach, which is must faster, as long as deletes are relatively rare, but much slower if they aren't. Know your DBs!

jerf 7y ago

I'd add a #4, when you have such low volume that it just isn't worth it to put up a full system, with the accompanying need for deployment, resources, monitoring, and additional knowledge and skills added to the minimum set of knowledge and skills your team must possess. One must be careful not to use this as an excuse, of course, and keep an eye on the scaling concerns and certain other details. SQL-as-pubsub has certain well-known issues and anyone using it this way ought to be aware of them. But it's a thought worth having. I've got a system I'm managing where Cassandra is the backend. I've got about 5 "documents" (in the MongoDB sense, let's say) I want to store in the system. I don't put up a whole "document DB" for them, I just have a table in Cassandra. I have in my entire system, one distributed lock I'd like to have per certain resource, of which I expect there to be single digit numbers of that resource over the lifetime of the system. Cassandra is not a great distributed locker, but it does work (and as far as I can tell, done properly, is also correct), so rather than install an entire distributed lock server, I use Cassandra. Should this ever turn out to become a mistake, all code that uses either of these functions is cleanly isolated and I can easily swap them out later. I am aware of the possibility that could happen in the future and have prepared for it. In the meantime, I've avoided two entire systems being poorly deployed and understood in favor of the one system that is well-deployed and understood by the team.

igammarays 7y ago
> SQL-as-pubsub has certain well-known issues and anyone using it this way ought to be aware of them. Such as? Other than performance.
- james_s_tayler 7y ago
  The key thing about the performance aspect is SQL produces bi-modal behavior due to there being a query planner / operator execution. If all of a sudden your performance characteristics change dramatically because the query planner produced a different result than normal due to something changing about your dataset, that could be pretty bad. So, not having this risk of bi-modal behavior is a nice feature of dedicated pub/sub and queue software.
- jerf 7y ago
  In addition to what james_s_taylar said, another one is that you have to watch how the tables are structured internally, because for some databases having a table that writes a lot of rows and deletes them all very quickly is pessimal usage. You can get a table that fills up with tombstones internally. I've seen cases where it literally takes seconds to pull a single message off the queue from an otherwise unloaded server, because the act of "SELECT * FROM messages LIMIT 1" requires a huge table scan across a whole bunch of tombstones, which can be very frustrating to diagnose since it looks like the table is nearly empty. I have an application at work I have to use (not something I wrote) based on an old version of Postgres that was experiencing this until quite recently when they upgraded, and I'm still not 100% sure that it's fixed because it hasn't been enough time. (I do expect it to be fixed, though.) The aforementioned Cassandra is pretty much catastrophic in this use case, from what I've seen; I didn't try to use it as a message queue, all I did was write some unit testing that involved adding some test records during the test and removing them, then running a whole bunch of other tests that would add records to the same primary key and remove them, simply to clean up for the subsequent tests, a very similar pattern as using a table as an event queue from the database's point of view even though we humans see that as quite different, and I very quickly noticed my tests were running noticeably slower every execution, on scales that compared to what Cassandra can nominally handle were very, very tiny. For the testing, I found I could use TRUNCATE, which tells Cassandra "Hey, I'm not just happening to delete every row in this table, I'm actively nuking it, so please clear it out", resulting in Cassandra completely wiping the table tombstones and all, which was fine for this unit test case where they run synchronously relative to each other and I have clear points where I can say "just throw the table away now, tombstones and all", but that's completely unsuitable for an event queue use. Somewhat ironically, you sometimes want the more "primitive" table types for this use care. IIRC there was a time when if you were using MySQL, you'd want to use a MyISAM-type table for the queue table, because it pretty much works as you expect and deleted rows are actually deleted and gone, but InnoDB used the "higher performance" tombstone-based approach, which is must faster, as long as deletes are relatively rare, but much slower if they aren't. Know your DBs!