Design a Basic Messaging Queue Service

To design a basic messaging queue using a database, I would start by defining the core entities. We need a 'messages' table containing an ID, the payload, a status field, and a visibility timestamp. The status can be 'PENDING', 'PROCESSING', or 'COMPLETED'. For producers, inserting a message is straightforward; we set the status to PENDING and leave the visibility timestamp null. Consumers will poll this table periodically. To avoid race conditions where two consumers grab the same message, the consumer runs a transaction: it selects all messages where status is PENDING and the current time exceeds the visibility timestamp, then immediately updates their status to PROCESSING within the same transaction. If a consumer crashes before acknowledging completion, the message remains in PROCESSING state indefinitely. To handle this fault tolerance, we implement a visibility timeout. When a message is picked up, we set its next visible timestamp to now plus a specific duration. A separate maintenance process scans for messages stuck in PROCESSING past this threshold and resets them to PENDING, allowing other consumers to retry them. While this satisfies the requirement for a simple distributed queue using only a database, it introduces latency due to polling and has lower throughput than event-driven architectures. However, for Uber's internal tools handling non-critical, low-volume tasks, this simplicity reduces operational overhead significantly.