Gaps while streaming events

[idugalic]

This is a very interesting project. It is demonstrating how SQL can be used for streaming.

I would like to discuss the gaps problem that can occur while reading/streaming events. This blog post explains it nicely https://event-driven.io/en/ordering_in_postgres_outbox/ by utilizing Snapshots ("transaction_id" < pg_snapshot_xmin(pg_current_snapshot()))

Do you find this valuable, and can it improve your model?

Best, Ivan

[marcopeg]

Hi Ivan, thank you very much for your appreciation. There might be performance issues with the snapshot way. Well, when you go into the billions of messages.

I'm currently researching how to go around this using PushIDs stored as binary data as cursors for the next_message issue. I'm kinda on pause for the moment as I'm dealing with a few other issues (coaching frontend people for a couple of months) but I plan to be back on the topic as soon as possible.

Check out this repo, in which I'm publishing more or less all my experiments with Postgres: https://github.com/marcopeg/amazing-postgresql

Also, you may be interested in taking a look at fetchq.com where I've implemented a queue system using Postgres. I use that stuff in production since years and it never failed me once.

Thanks to Postgres of course. Marco

On Thu, Jan 19, 2023 at 2:14 PM Ivan Dugalic @.***> wrote:

This is a very interesting project. It is demonstrating how SQL can be used for streaming.

I would like to discuss the gaps problem that can occur while reading/streaming events. This blog post explains it nicely https://event-driven.io/en/ordering_in_postgres_outbox/ by utilizing Snapshots ("transaction_id" < pg_snapshot_xmin(pg_current_snapshot()))

Do you find this valuable, and can it improve your model?

Best, Ivan

— Reply to this email directly, view it on GitHub https://github.com/marcopeg/postgres-event-sourcing/issues/1, or unsubscribe https://github.com/notifications/unsubscribe-auth/AADIMMNQH5Z74SOVSQSSNILWTE447ANCNFSM6AAAAAAUAKGIX4 . You are receiving this because you are subscribed to this thread.Message ID: @.***>

--

Best regards, Marco Pegoraro

m. @.** C.* +46 73 545 1223

---

This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the system manager. This message contains confidential information and is intended only for the individual named. If you are not the named addressee you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. If you are not the intended recipient you are notified that disclosing, copying, distributing or taking any action in reliance on the contents of this information is strictly prohibited.

[idugalic]

Thanks, Marco!

Yes, indeed, some performance penalties might be expected with snapshots. TransactionId is monotonic but it is pushing you away from having ordering by offset/sequence and makes you use TransactionId ASC instead. Nonetheless, I will spend some time exploring this option and share feedback on this repo. I hope soon ... lets see...

Amazing, thanks for sharing!

fetchq.com looks nice! I will give it a try. Queues are everywhere, indeed. I truly respect this structure. I am not sure what our industry would do without it :) I am focusing on streaming events (it is more topic/partition model) at the moment. It is complementing event sourcing in a nice way IMHO, and I want to learn how far I can get with it by using Postgres as a single platform, minimizing the footprint.

BTW, feel free to close this issue. I haven't found a better way to ask the question.

Best, Ivan

[marcopeg]

This issue looks to become a nice conversation. Let's keep it on :-)

My conviction is that you can take queue and streaming with Postgres quite far. A single table can grow up to 32Tb. Combining partitioning + disk routing... Postgres can potentially take up to hundreds of terabytes.

But for such a workload it would probably be a wrong architectural decision. If I had so much data, I would rather use Kafka and Rabbit myself.

What is interesting, is that Postgres can be used for small/medium projects up to "some" gigabytes of data. This would still span into the billions of tasks or events.

And that would cost a fraction of what it would cost on Kafka or similar.

So my goal is to create an open source offering for kafka and rabbit alternatives that leverage on Postgres to provide a secure data storage, at a very basic spending level.

We'll see where this adventure goes.

What are your thoughts?

On Fri, Jan 20, 2023 at 6:55 PM Ivan Dugalic @.***> wrote:

Thanks, Marco!

Yes, indeed, some performance penalties might be expected with snapshots. TransactionId is monotonic but it is pushing you away from having ordering by offset/sequence and makes you use TransactionId ASC instead. Nonetheless, I will spend some time exploring this option and share feedback on this repo. I hope soon ... lets see...

Amazing, thanks for sharing!

fetchq.com looks nice! I will give it a try. Queues are everywhere, indeed. I truly respect this structure. I am not sure what our industry would do without it :) I am focusing on streaming events (it is more topic/partition model) at the moment. It is complementing event sourcing in a nice way IMHO, and I want to learn how far I can get with it by using Postgres as a single platform, minimizing the footprint.

BTW, feel free to close this issue. I haven't found a better way to ask the question.

Best, Ivan

— Reply to this email directly, view it on GitHub https://github.com/marcopeg/postgres-event-sourcing/issues/1#issuecomment-1398744699, or unsubscribe https://github.com/notifications/unsubscribe-auth/AADIMMP4X3VVIDIYE4QCSSLWTLGSNANCNFSM6AAAAAAUAKGIX4 . You are receiving this because you commented.Message ID: @.***>

--

Best regards, Marco Pegoraro

m. @.** C.* +46 73 545 1223

---

This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the system manager. This message contains confidential information and is intended only for the individual named. If you are not the named addressee you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. If you are not the intended recipient you are notified that disclosing, copying, distributing or taking any action in reliance on the contents of this information is strictly prohibited.

[idugalic]

I'm with you! Let's see where this goes.

I'm more convinced that this can provide more value at a lower cost.

My current focus is on domain modeling (traditional or with event sourcing) at the moment.

• https://github.com/fraktalio/fmodel
• https://github.com/fraktalio/fmodel-ts

I still need to brush the dust from my Postgres expertise ;), but I'm getting there.

Thanks again!

[idugalic]

Hi @marcopeg I just realised that your model is better than I initially tough :)

I will focus on the events table, as this is the only one that you can consider improving.

To elaborate:

• in event sourced model a partition term is rarely used, instead, consider aggregate_id or better stream_id and it can not be *, it should be a specific value coming from the client application.
• another important aspect of event sourcing is optimistic locking. To enable it, an additional column in the events table is required (sequence or aggregate_offset or stream_offset or stream_version or version, whatever you prefer). Essentially, this column is used as a version that client applications need to send within the append/insert event request. In a concurrent scenario (with two transactions) the latter transaction would fail if they both try to append/insert an event on the same version. It will guarantee the correct ordering of events within this partition/aggregate_id as well.
• By enabling optimistic locking and realizing that the partition key is actually aggregate_id/stream_id, the streaming of events to concurrent consumers will be correct (without skipping events)

Event sourcing I guess that you can keep this model general as well, with the naming convention you already have, but please consider enabling optimistic locking of writing events to a particular partition (concurrent writing of events to the same partition should not happen)

Event Streaming, as you have implemented it, will just work IMHO.