6 ms·

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

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.

notretarded 7y ago
Okay...
etaioinshrdlu 7y ago
Maybe all this results in a really durable and foolproof system. I don't see how this is a bad thing. It looks like defense in depth against errors and corruption. Also, to my knowledge, the logs in a DB are not kept forever. Instead they are trimmed as soon as reasonable. It starts to smell a little bit like a https://en.wikipedia.org/wiki/Log-structured_merge-tree https://en.wikipedia.org/wiki/Log-structured_merge-tree
- notduncansmith 7y ago
  It also smells like an example of the https://en.wikipedia.org/wiki/Inner-platform_effect https://en.wikipedia.org/wiki/Inner-platform_effect
dtech 7y ago
> It’s curious that over those projections, we then build event stores for CQRS/ES systems with their own projections mediated by application code. That's really logical. From the view of the application there is no transaction log, only a table. It's an implementation detail of the database. The application wants similar guarantees a log can provide, so they build their own.
corebit 7y ago
Yep yep yep. Amazing how we keep reinventing the same thing over and over and over again.
- adambyrtek 7y ago
  I wouldn't say this is "reinventing the wheel", it's more like applying the same design/architectural pattern to solve a common problem.
migueloller 7y ago
It is curious indeed. The first time I noticed this curiosity is when I saw Martin Kleppman’s Turning the Database Inside Out [1]. It’s a great watch (or read) and I really recommend it! [1] https://www.confluent.io/blog/turning-the-database-inside-out-with-apache-samza/ https://www.confluent.io/blog/turning-the-database-inside-ou...
marco_craveiro 7y ago
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 7y ago
  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 7y ago
    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-out-with-apache-samza/ https://www.confluent.io/blog/turning-the-database-inside-ou... [2] https://debezium.io/ https://debezium.io/ [3] https://github.com/confluentinc/bottledwater-pg https://github.com/confluentinc/bottledwater-pg
sradman 7y ago
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.
chrisseaton 7y ago
> the transaction log of a typical RDBMS is the canonical form Do databases keep the whole transaction log forever? It seems like that could keep growing forever even when the tables stay at constant size.
- MSM 7y ago
  Usually you'll keep the transaction logs for a certain period, say for two weeks. That gives you point in time recovery for any moment in that two weeks (assuming you've set up everything correctly!). Logs past that are usually archived or just dumped, but you'll have full backups that go back further. For your question about the logs growing forever, there are usually points in the process where a transaction log is saved off somewhere else and then can be overwritten, but on a transactional system the logs over a period of a week or two can sometimes be many times larger than the actual data stored at any given time, yes.

notretarded 7y ago

Okay...

etaioinshrdlu 7y ago

Maybe all this results in a really durable and foolproof system. I don't see how this is a bad thing. It looks like defense in depth against errors and corruption. Also, to my knowledge, the logs in a DB are not kept forever. Instead they are trimmed as soon as reasonable. It starts to smell a little bit like a https://en.wikipedia.org/wiki/Log-structured_merge-tree https://en.wikipedia.org/wiki/Log-structured_merge-tree

notduncansmith 7y ago
It also smells like an example of the https://en.wikipedia.org/wiki/Inner-platform_effect https://en.wikipedia.org/wiki/Inner-platform_effect

dtech 7y ago

> It’s curious that over those projections, we then build event stores for CQRS/ES systems with their own projections mediated by application code. That's really logical. From the view of the application there is no transaction log, only a table. It's an implementation detail of the database. The application wants similar guarantees a log can provide, so they build their own.

corebit 7y ago

Yep yep yep. Amazing how we keep reinventing the same thing over and over and over again.

adambyrtek 7y ago
I wouldn't say this is "reinventing the wheel", it's more like applying the same design/architectural pattern to solve a common problem.

migueloller 7y ago

It is curious indeed. The first time I noticed this curiosity is when I saw Martin Kleppman’s Turning the Database Inside Out [1]. It’s a great watch (or read) and I really recommend it! [1] https://www.confluent.io/blog/turning-the-database-inside-out-with-apache-samza/ https://www.confluent.io/blog/turning-the-database-inside-ou...

marco_craveiro 7y ago

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 7y ago
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 7y ago
  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-out-with-apache-samza/ https://www.confluent.io/blog/turning-the-database-inside-ou... [2] https://debezium.io/ https://debezium.io/ [3] https://github.com/confluentinc/bottledwater-pg https://github.com/confluentinc/bottledwater-pg

sradman 7y ago

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.

chrisseaton 7y ago

> the transaction log of a typical RDBMS is the canonical form Do databases keep the whole transaction log forever? It seems like that could keep growing forever even when the tables stay at constant size.

MSM 7y ago
Usually you'll keep the transaction logs for a certain period, say for two weeks. That gives you point in time recovery for any moment in that two weeks (assuming you've set up everything correctly!). Logs past that are usually archived or just dumped, but you'll have full backups that go back further. For your question about the logs growing forever, there are usually points in the process where a transaction log is saved off somewhere else and then can be overwritten, but on a transactional system the logs over a period of a week or two can sometimes be many times larger than the actual data stored at any given time, yes.