311–320 of 328 posts
Many here recommend using Kafka or RabbitMQ for real-time notifications. While these tools work well with a relatively stable, limited set of topics, they become costly and inefficient when dealing with a large number of dynamic subscribers, such as in a messaging app where users frequently come and go. In RabbitMQ, queue bindings are resource-intensive, and in Kafka, creating new subscriptions often triggers expensi…
Earlier quoted context omitted.
> you can make the recipient poll the table(s) of record for relevant state That is tricky due to transactions and visibility. How do you write the poller to not miss events that were written by a long/blocked transaction? You'd have to set the poller scan to a long time (e.g. "process events that were written since now minus 5minutes") and then make sure transactions are cancelled hard before those 5minutes.
I'd say that the most reliable way is to use some mutable lifecycle metadata other than times to identify work. An indexed query will find the "new and unclaimed" work items and process them, regardless of their potentially backdated temporal metadata. Updates of the lifecycle properties can also help coordinate multiple pollers so that they never work on the same item, but they can have overlapping query terms so th…
Earlier quoted context omitted.
Rails fully supports constraints and encourages you to use them. You can either execute SQL in your migration or use add_check_constraint.
Back when I used Rails the sentiment was: You don't need foreign keys, this is all handled by ActiveRecord.
Earlier quoted context omitted.
It’s not a thing.
i don’t understand. is the serialized write global lock a thing or no?
Honestly this article is ridiculous. Most people do not have tens of thousands of concurrent writers. And most applications out there are read heavy, not write. Which means you probably have read replicas distributing loads. Use LISTEN/NOTIFY. You will get a lot of utility out of it before you’re anywhere close to these problems.
That means for moderate cases you do not even have to care about this. 99% of PostgreSQL instances out there are not big "scale".
As a sr. engineer is your responsibility to make a decision if you will build for "scale" from day zero or ignore this as you are mindful that this will not affect you until a certain point.
RBDMS are not designed for write-heavy applications, they are designed for read-heavy analysis. Also, an RDBMS is not a message queue or an RPC transport. I feel like somebody needs to write a book on system architecture for Gen Z that's just filled with memes. A funny cat pic telling people not to use the wrong tool will probably make more of an impact than an old fogey in a comment section wagging his finger.
RDBMS are an old fogey tool. It takes a really old fogey to suggest storing records at fixed byte intervals directly on the disk - is that your proposed alternative? Or perhaps you grew up in the microservices era and that's already become old fogey.
Earlier quoted context omitted.
People have been using RDBMS' for write-heavy workflows for forever. Some people even use stored procs or triggers for getting complicated write operations to work properly. Databases can do a lot of stuff, and if you're not hurting for DB performance it can be a good idea to just... do it in the database. The advantage is that, if the DB does it, you're much less likely to break things. Putting data constraints in a…
That is the same logic that led every failed design I've seen in my career to take months (if not years) and tons of money to fix. "YOLO engineering" is simple at first and a huge pain in the ass later. Whereas actually correct engineering is slightly painful at first and saves your ass later. The people who design it walk away after a few years, so they don't give a crap what happens. The rest of us have to struggle…
Transactional databases are not really the best tool for writing tons of (presumably) immutable records. Why are you using it for this? Why not Elastic?
Multi-master transactional databases are an open area of research, as far as I'm aware, but read-only replication is a solved problem. Therefore your write traffic, including your transaction overhead, has to fit within one server's capacity, while your read traffic can scale horizontally as much as you like.
Earlier quoted context omitted.
It actually depends on the workload. Sending webhooks, as an example, often has zero need to go back and update the database, but I've seen that exact example take down several different managed databases ( i.e., not just postgres ).
Yes that's true but in good implementations you will want to surface to the recipient via some dashboard if delivery consistently fails. So at some point a message on the exception queue will want to update the db.
Don't get me wrong. Correctness is a great default, much easier to reason about.
Earlier quoted context omitted.
I'd say that the most reliable way is to use some mutable lifecycle metadata other than times to identify work. An indexed query will find the "new and unclaimed" work items and process them, regardless of their potentially backdated temporal metadata. Updates of the lifecycle properties can also help coordinate multiple pollers so that they never work on the same item, but they can have overlapping query terms so th…
The problem is that you now have to poll based on an index (maybe BRIN isn't too bad though) and you have to overwrite the row afterwards and update the index. That means you are creating a dead tuple for every row (and one more if you mark it to be "completed").
When trying to make good use of RDMBS transactional semantics, I think an important mental shift is to think of there being multiple async processing domains rather than a single magical transaction space. DB transactions are just communication events, not actual business work. This is how the relational DB can become the message broker.
The agents need to do something akin to 2-phase commit protocols to record their "intent" and their "result" across different business resources. But, for a failure-prone, web style network of agents, I would not expose actual DB 2-phase commit protocols. Instead, the relational model reifies the 2-phase-like state ambiguity of particular business resources as tuples, and the agents communicate important phases of their work process with simpler state update transactions.
It's basically the same pattern as with safe use of AMQP, just replacing one queue primitive with another. Both approaches require delayed acknowledgement patterns, so tasks can be routed to an agent but not removed from the system until after the agent reports the work complete. Either approach has an lost or orphaned task hazard if naively written to dequeue tasks earlier in the work process. An advantage of the RDBMS-based message broker is that you can use also use SQL to supervise all the lifecycle state, or even intervene to clean up after agent failures.
In this approach, don't scale-up a central RDMBS by disabling all its useful features in a mad dash for speed. Instead, think of the network of async agents (human or machine) and RDMBS message broker(s) to make for their respective traffic. This agent network and communication workload can often be partitioned to reach scaling goals. E.g. specific business resources might go into different "home" zones with distinct queues and agent pools. Their different lifecycle states do not need to exist under a single, common transaction control.