Issues we've encountered while building a Kafka based data processing pipeline • Hacker News Board

Re: Issues we've encountered while building a Kafka based data processing pipeline

#91

Post by anentropic » Tue, Oct 19, 2021, 3:05 PM UTC

LgWoodenBadger wrote: post #62

anentropic wrote: post #47

Earlier quoted context omitted.

it's a chicken and egg problem you can either send a kafka message but potentially not commit the db transaction (i.e. an event is published for which the action did not actually occur) or commit the db transaction and potentially not send the kafka message it sounds like they implemented something like the Transactional Outbox pattern https://microservices.io/patterns/data/transactional-outbox.... i.e. you use the d…

Their solution seems like a "produce to Kafka first" but with extra steps. Regarding: When we produce first and the database update fails (because of incorrect state) it means in the worst case we enter a loop of continuously sending out duplicate messages until the issue is resolved I don't understand where either 1) the incorrect state or 2) the need to continuously send duplicate messages come from. Regarding: The…

When we produce first and the database update fails (because of incorrect state) it means in the worst case we enter a loop of continuously sending out duplicate messages until the issue is resolved

the article does not explain things very clearly, but I think this is describing the problem rather than their solution

Our high level idea was:

- Insert “work” into a table that acts like a queue

- “Executor” takes “work” from DB and runs it

...

A Job is an abstraction for a scheduled DB backed async activity

...

How did we solve the #2 state problem?

By recording Jobs in the service database we can do the state update within the same transaction as inserting a new Job. Combining this with a Job that produces the actual Kafka message, allows us to make the whole operation transactional. If either of the parts fails, updating the data or scheduling the job, both get rolled back and neither happens.

I think this is describing basically a Transactional Outbox

i.e. "jobs" are recorded in the postgres db as part of the same db transaction as the business logic actions

the difference from Kafka-first is that if the app decides to rollback the business logic then the message hasn't already sent

Re: Issues we've encountered while building a Kafka based data processing pipeline

#92

Post by zeckalpha » Tue, Oct 19, 2021, 9:56 PM UTC

redis_mlc wrote: post #84

zeckalpha wrote: post #17

Earlier quoted context omitted.

And MySQL didn’t yet support transactions!

Actually, Innodb, which supports transactions, has been bundled with MySQL since 2001, but existed before then. It became the default storage engine in 2010.

> IIRC ~2 decades ago

Re: Issues we've encountered while building a Kafka based data processing pipeline

#93

Post by zbentley » Wed, Oct 20, 2021, 1:23 PM UTC

fernandotakai wrote: post #58

superyesh wrote: post #53

Earlier quoted context omitted.

>My answer to anyone who asks for kafka: Show me that you can't do what you need with a beefy Postgres. Sorry thats just a clickbait-y statement. I love Postgres, try handling 100-500k rps of data coming in from various sources reading and writing to it. You are going to get bottlenecked on how many connections you can handle, you will end up throwing pgBouncers on top of it. Eventually you will run out of disk, star…

at least for me, every system has its place. i love posgresql, but i would not use it to replace a rabbitmq instance -- one is an RDBMS, the other is a queue/event system. "oh but psql can pretend to be kafka/rabbitmq!" -- sure, but then you need to add tooling to it, create libraries to handle it, and handle all the edge cases. with rmq/kafka, there already a bunch of tools to handle the exact case of a queue/event…

I think having ad hoc query capabilities on your job queue/log (not possible with rabbit and only possible by running extra software like KQL with Kafka, and even then at a full-table-scan cost equivalent) is a benefit to using postgres that should not be overlooked. For monitoring and debugging message broker behavior SQL is a very powerful tool.

I say this as someone squarely in the "bigger than can work with an RDBMS" message processing space, too: until you are at that level, you need less tooling (e.g. read replicas, backups, very powerful why-is-it-on-fire diagnostics informed by decades of experience), and get generally higher reliability, with postgres as a broker.

Re: Issues we've encountered while building a Kafka based data processing pipeline

#94

Post by zbentley » Wed, Oct 20, 2021, 1:28 PM UTC

SergeAx wrote: post #83

anotherhue wrote: post #2

I ran a few dozen kafka clusters at MegaCorp in a previous life. My answer to anyone who asks for kafka: Show me that you can't do what you need with a beefy Postgres.

Read 500m messages in less than an hour?

It depends. Are your reads also writes (e.g. simulating a job queue with a "claimed" state update or a "select for update ... skip locked")? If not, 500m should be no sweat, even on crappy hardware with minimal tuning. If so, things get a little trickier, and you may have to introduce other tools or a clustering solution to get that throughput with postgres.

Re: Issues we've encountered while building a Kafka based data processing pipeline

#95

Post by chartpath » Thu, Oct 21, 2021, 11:26 PM UTC

capableweb wrote: post #13

anotherhue wrote: post #2

I ran a few dozen kafka clusters at MegaCorp in a previous life. My answer to anyone who asks for kafka: Show me that you can't do what you need with a beefy Postgres.

> My answer to anyone who asks for kafka: Show me that you can't do what you need with a beefy Postgres. ... You could probably hack any database to perform any task, but why would you? Use the right tool for the right task, not one tool for all tasks. If the right tool is a relational database, then use Postgres/$OTHER-DATABASE If the right tool is distributed, partitioned and replicated commit log service, then use…

Something I find peculiar about this truth is that Postgres itself is based on the write ahead log structure for sequencing statements, replication, audit logging.

It feels like two different lenses onto the same reality.

Re: Issues we've encountered while building a Kafka based data processing pipeline

#96

Post by chartpath » Thu, Oct 21, 2021, 11:30 PM UTC

dionian wrote: post #31

anotherhue wrote: post #2

I ran a few dozen kafka clusters at MegaCorp in a previous life. My answer to anyone who asks for kafka: Show me that you can't do what you need with a beefy Postgres.

Honest question, how do you expire content in Postgres? Every time I start to use it for ephemeral data I start to wonder if I should have used TimescaleDB or if I should be using something else...

Maybe something like https://github.com/citusdata/pg_cron or if it's not a rolling time window, just trigger functions that get called whenever some expression in SQL is true.