Messaging with PostgreSQL: Designing Scalable and Reliable Queues and Pub/Sub within your Database

(🎥 RECORDING: https://vimeo.com/1115459661)

The industry has aggressively marketed complex and expensive architectures, including microservices, messaging brokers, and NoSQL databases. As a result, simpler architectures, which are often sufficient, have been vilified by many companies and professionals—architectures such as monoliths and relational databases. Unfortunately, for over a decade, the latter has been dismissed as merely a limited data repository incapable of scaling to millions of users or handling tens of thousands of requests.

These misconceptions about relational databases need to end. That’s why, in this talk, I will demonstrate how we can integrate systems and services without abandoning the relational world, all from the perspective of a software developer. Instead of bearing the high cost and complexity of adopting Kafka or RabbitMQ to implement messaging in systems, we will leverage PostgreSQL’s native capabilities to implement message queuing and Pub/Sub communication in a simple, reliable, and scalable way.

You’ll be surprised at how these types of messaging solutions can be addressed with just a few lines of SQL, distributed locking, and a solid understanding of the features offered by your relational database. By the end of this talk, you’ll realize that PostgreSQL is more than just a data repository—it’s a complete and robust concurrency engine.

Transcript

1. MESSAGING WITH POSTGRES Designing Scalable and Reliable Queues and Pub/Sub within your Database Rafael Ponte | @rponte
5. Hype-Driven Development https://blog.daftcode.pl/hype-driven-development-3469fc2e9b22
7. CACHING
8. CACHING
9. CACHING ASYNC PROCESSING
10. CACHING ASYNC PROCESSING
11. CACHING ASYNC PROCESSING LOAD BALANCING
12. CACHE ASYNC PROCESSING LOAD BALANCING
13. LOAD BALANCING CACHING ASYNC PROCESSING
14. ASYNC PROCESSING LOAD BALANCING CACHING
15. The idea is simple…
16. server
17. db server
18. browser db server
19. db request server browser
20. db response server browser request
21. db response server browser request
22. And it works very well, until…
23. db server response request
24. db server response request
25. db server response request
26. response request server db
27. response request server db
28. response request server db
29. Wait!!
30. db server browser response request
31. db ??? server browser response request
32. db server browser response request
33. queue db server browser response request
34. queue consumer db server browser response request
35. queue consumer db server browser response request
36. queue consumer db server browser response request
37. But which broker?
42. But what is the cost?
43. Security Backup Replication Monitoring Upgrade Recovery Fail-over High-availability Hardware Hiring Specialists But what is the cost?
44. And now?
46. No!
47. How about…
48. PostgreSQL
49. Use your database only as a data repository. “
50. But a developer probably don’t care…
54. Show me your throughput, and I’ll tell you who you are.
55. How you can scale your system using your relational database.
56. /rponte Rafael Ponte Brazilian Software Engineer at aka Prince of the Ocean aka Maharaja of the Legacies 20+ years of experience working with Java and distributed systems Loves working with "boring techs"
57. MESSAGING WITH POSTGRES Designing Scalable and Reliable Queues and Pub/Sub within your Database Rafael Ponte | @rponte
59. Order Checkout
67. @RestController class CheckoutController { @Transactional @PostMapping("/api/orders/checkout") public OrderResponse checkout(@RequestBody OrderRequest request) { // authorize payment with the gateway // reserve or deduct items from inventory // confirm and update order status // generate invoice // send confirmation email/notification return new OrderResponse(order, Status.CONFIRMED); } }
68. @RestController class CheckoutController { @Transactional @PostMapping("/api/orders/checkout") public OrderResponse checkout(@RequestBody OrderRequest request) { // authorize payment with the gateway // reserve or deduct items from inventory // confirm and update order status // generate invoice // send confirmation email/notification return new OrderResponse(order, Status.CONFIRMED); } }
69. @RestController class CheckoutController { @Transactional @PostMapping("/api/orders/checkout") public OrderResponse checkout(@RequestBody OrderRequest request) { // authorize payment with the gateway // reserve or deduct items from inventory // confirm and update order status // generate invoice // send confirmation email/notification return new OrderResponse(order, Status.CONFIRMED); } }
70. @RestController class CheckoutController { @Transactional @PostMapping("/api/orders/checkout") public OrderResponse checkout(@RequestBody OrderRequest request) { // authorize payment with the gateway // reserve or deduct items from inventory // confirm and update order status // generate invoice // send confirmation email/notification return new OrderResponse(order, Status.CONFIRMED); } }
71. @RestController class CheckoutController { @Transactional @PostMapping("/api/orders/checkout") public OrderResponse checkout(@RequestBody OrderRequest request) { // authorize payment with the gateway // reserve or deduct items from inventory // confirm and update order status // generate invoice // send confirmation email/notification return new OrderResponse(order, Status.CONFIRMED); } }
72. @RestController class CheckoutController { @Transactional @PostMapping("/api/orders/checkout") public OrderResponse checkout(@RequestBody OrderRequest request) { // authorize payment with the gateway // reserve or deduct items from inventory // confirm and update order status // generate invoice // send confirmation email/notification return new OrderResponse(order, Status.CONFIRMED); } }
73. @RestController class CheckoutController { @Transactional @PostMapping("/api/orders/checkout") public OrderResponse checkout(@RequestBody OrderRequest request) { // authorize payment with the gateway // reserve or deduct items from inventory // confirm and update order status // generate invoice // send confirmation email/notification return new OrderResponse(order, Status.CONFIRMED); } }
74. @RestController class CheckoutController { @Transactional @PostMapping("/api/orders/checkout") public OrderResponse checkout(@RequestBody OrderRequest request) { // authorize payment with the gateway // reserve or deduct items from inventory // confirm and update order status // generate invoice // send confirmation email/notification return new OrderResponse(order, Status.CONFIRMED); } }
75. @RestController class CheckoutController { @Transactional @PostMapping("/api/orders/checkout") public OrderResponse checkout(@RequestBody OrderRequest request) { // authorize payment with the gateway // reserve or deduct items from inventory // confirm and update order status // generate invoice // send confirmation email/notification return new OrderResponse(order, Status.CONFIRMED); } }
76. @RestController class CheckoutController { @Transactional @PostMapping("/api/orders/checkout") public OrderResponse checkout(@RequestBody OrderRequest request) { // authorize payment with the gateway // reserve or deduct items from inventory // confirm and update order status // generate invoice // send confirmation email/notification return new OrderResponse(order, Status.CONFIRMED); } }
77. @RestController class CheckoutController { @Transactional @PostMapping("/api/orders/checkout") public OrderResponse checkout(@RequestBody OrderRequest request) { // authorize payment with the gateway // reserve or deduct items from inventory // confirm and update order status // generate invoice // send confirmation email/notification return new OrderResponse(order, Status.CONFIRMED); } }
78. @RestController class CheckoutController { @Transactional @PostMapping("/api/orders/checkout") public OrderResponse checkout(@RequestBody OrderRequest request) { // authorize payment with the gateway // reserve or deduct items from inventory // confirm and update order status // generate invoice // send confirmation email/notification return new OrderResponse(order, Status.CONFIRMED); } }
79. @RestController class CheckoutController { @Transactional @PostMapping("/api/orders/checkout") public OrderResponse checkout(@RequestBody OrderRequest request) { // authorize payment with the gateway // reserve or deduct items from inventory // confirm and update order status // generate invoice // send confirmation email/notification return new OrderResponse(order, Status.CONFIRMED); } }
80. @RestController class CheckoutController { @Transactional @PostMapping("/api/orders/checkout") public OrderResponse checkout(@RequestBody OrderRequest request) { // authorize payment with the gateway // reserve or deduct items from inventory // confirm and update order status // generate invoice // send confirmation email/notification return new OrderResponse(order, Status.CONFIRMED); } }
81. @RestController class CheckoutController { @Transactional @PostMapping("/api/orders/checkout") public OrderResponse checkout(@RequestBody OrderRequest request) { // authorize payment with the gateway // reserve or deduct items from inventory // confirm and update order status // generate invoice // send confirmation email/notification return new OrderResponse(order, Status.CONFIRMED); } }
82. @RestController class CheckoutController { @Transactional @PostMapping("/api/orders/checkout") public OrderResponse checkout(@RequestBody OrderRequest request) { // authorize payment with the gateway // reserve or deduct items from inventory // confirm and update order status // generate invoice // send confirmation email/notification return new OrderResponse(order, Status.CONFIRMED); } }
83. @RestController class CheckoutController { @Transactional @PostMapping("/api/orders/checkout") public OrderResponse checkout(@RequestBody OrderRequest request) { // authorize payment with the gateway // reserve or deduct items from inventory // confirm and update order status // generate invoice // send confirmation email/notification return new OrderResponse(order, Status.CONFIRMED); } }
84. @RestController class CheckoutController { @Transactional @PostMapping("/api/orders/checkout") public OrderResponse checkout(@RequestBody OrderRequest request) { // authorize payment with the gateway // reserve or deduct items from inventory // confirm and update order status // generate invoice // send confirmation email/notification return new OrderResponse(order, Status.CONFIRMED); } }
85. db browser server
86. db browser server
87. db browser server
88. db browser server
89. db browser server
90. db browser server
91. db browser server
92. db server
93. If execution is expensive, it may become a performance bottleneck.
95. CACHING
96. ASYNC PROCESSING CACHING
97. LOAD BALANCING CACHING ASYNC PROCESSING
98. CACHING LOAD BALANCING ASYNC PROCESSING
99. “That’s easy. We need a queue!
100. When we talk about a queue…
101. 4 3 2 QUEUE
102. 4 3 2 5 QUEUE
103. 4 3 2 5 QUEUE enqueue
104. 4 3 2 5 1 QUEUE enqueue
105. 4 3 2 5 1 enqueue dequeue QUEUE
106. 4 3 2 5 1 FIFO: First-in, First-out QUEUE enqueue dequeue
107. In the enterprise world…
108. QUEUE (enterprise world)
109. QUEUE (enterprise world) 9631 9630
110. (message queue) QUEUE (enterprise world) 9631 9630
111. 9641 (message) (message queue) QUEUE (enterprise world) 9631 9630
112. producer (message queue) QUEUE (enterprise world) 9631 9630
113. producer 9629 (message queue) QUEUE (enterprise world) 9631 9630
114. producer consumer (message queue) QUEUE (enterprise world) 9631 9630
115. producer consumer (message queue) QUEUE (enterprise world) 9631 9630
116. producer consumer (message queue) QUEUE (enterprise world) … 9631 9630
117. So that…
118. db browser server
119. db browser server X
120. X db browser server
121. db queue browser server
122. db browser server queue
123. db browser server queue
124. db browser server queue
125. db browser server queue
126. PostgreSQL
127. Queue
128. Producer Queue
129. Producer Consumer Queue
130. Producer Consumer Queue
134. Producer Consumer Queue
135. @RestController class CheckoutController { @Transactional @PostMapping("/api/orders/checkout") public OrderResponse checkout(@RequestBody OrderRequest request) { // authorize payment with the gateway // reserve or deduct items from inventory // confirm and update order status // generate invoice // send confirmation email/notification return new OrderResponse(order, Status.CONFIRMED); } }
136. @RestController class CheckoutController { @Transactional @PostMapping("/api/orders/checkout") public OrderResponse checkout(@RequestBody OrderRequest request) { // authorize payment with the gateway // reserve or deduct items from inventory // confirm and update order status // generate invoice // send confirmation email/notification return new OrderResponse(order, Status.CONFIRMED); } }
137. @RestController class CheckoutController { private OrderRepository repository; @Transactional @PostMapping("/api/orders/checkout") public OrderResponse checkout(@RequestBody OrderRequest request) { // authorize payment with the gateway // reserve or deduct items from inventory // confirm and update order status // generate invoice // send confirmation email/notification return new OrderResponse(order, Status.CONFIRMED); } }
138. @RestController class CheckoutController { private OrderRepository repository; @Transactional @PostMapping("/api/orders/checkout") public OrderResponse checkout(@RequestBody OrderRequest request) { Order order = request.toModel(); order.setStatus(Status.PENDING); repository.save(order); return new OrderResponse(order, Status.PENDING); } }
139. @RestController class CheckoutController { private OrderRepository repository; @Transactional @PostMapping("/api/orders/checkout") public OrderResponse checkout(@RequestBody OrderRequest request) { Order order = request.toModel(); order.setStatus(Status.PENDING); repository.save(order); return new OrderResponse(order, Status.PENDING); } }
140. @RestController class CheckoutController { private OrderRepository repository; @Transactional @PostMapping("/api/orders/checkout") public OrderResponse checkout(@RequestBody OrderRequest request) { Order order = request.toModel(); order.setStatus(Status.PENDING); repository.save(order); return new OrderResponse(order, Status.PENDING); } }
141. @RestController class CheckoutController { private OrderRepository repository; @Transactional @PostMapping("/api/orders/checkout") public OrderResponse checkout(@RequestBody OrderRequest request) { Order order = request.toModel(); order.setStatus(Status.PENDING); repository.save(order); return new OrderResponse(order, Status.PENDING); } }
142. @RestController class CheckoutController { private OrderRepository repository; @Transactional @PostMapping("/api/orders/checkout") public OrderResponse checkout(@RequestBody OrderRequest request) { Order order = request.toModel(); order.setStatus(Status.PENDING); repository.save(order); return new OrderResponse(order, Status.PENDING); } }
143. @RestController class CheckoutController { private OrderRepository repository; @Transactional @PostMapping("/api/orders/checkout") public OrderResponse checkout(@RequestBody OrderRequest request) { Order order = request.toModel(); order.setStatus(Status.PENDING); repository.save(order); return new OrderResponse(order, Status.PENDING); } } “We are processing your order.”
144. @RestController class CheckoutController { private OrderRepository repository; @Transactional @PostMapping("/api/orders/checkout") public OrderResponse checkout(@RequestBody OrderRequest request) { Order order = request.toModel(); order.setStatus(Status.PENDING); repository.save(order); return new OrderResponse(order, Status.PENDING); } }
145. db browser server
146. db browser server queue
147. db browser server queue
148. db request browser server queue
149. db INSERT browser server queue request
150. db INSERT browser server queue request
151. db INSERT response queue request browser server
152. And eventually a process would consume messages from the queue…
153. Producer Consumer Queue
154. db INSERT queue request browser server
155. db INSERT request browser server queue
156. db INSERT request browser server Who executes it? queue
157. https://twitter.com/rponte/status/1492168535421992960
158. INSERT db request browser server queue
159. INSERT db request browser server queue
160. INSERT db request browser server SELECT queue
161. INSERT db request browser server UPDATE queue
162. The advantage of it..
163. browser INSERT db request server response queue
164. INSERT db request server response queue
165. INSERT db request server response queue
166. INSERT db request server response queue
167. response INSERT db request server queue
168. INSERT queue
169. INSERT SELECT queue
170. INSERT SELECT queue
171. INSERT UPDATE queue
172. But before…
173. How to schedule tasks with Spring Boot?
174. @Component public class OneJob { @Scheduled(fixedDelay = 60_000) public void runQuiteOften() { // lógica do job vai aqui } }
175. @Component public class OneJob { @Scheduled(fixedDelay = 60_000) public void runQuiteOften() { // lógica do job vai aqui } }
176. @Component public class OneJob { @Scheduled(fixedDelay = 60_000) public void runQuiteOften() { // job logic goes here } }
177. @Component public class OneJob { @Scheduled(fixedDelay = 60_000) public void runQuiteOften() { // job logic goes here } }
178. @Component public class OneJob { public void runQuiteOften() { // job logic goes here } }
179. @Component public class OneJob { @Scheduled public void runQuiteOften() { // job logic goes here } }
180. @Component public class OneJob { @Scheduled(fixedDelay = 60_000) public void runQuiteOften() { // job logic goes here } }
181. @Component public class OneJob { @Scheduled(fixedDelay = 60_000) public void runQuiteOften() { // job logic goes here } }
182. Coming back…
183. INSERT fi la db request server response
184. response INSERT db request server
185. @Component public class ProcessPendingOrdersJob { private OrderRepository repository; @Scheduled(fixedDelay = 60_000) public void execute() { List<Order> orders = repository.findAllByStatus(Status.PENDING); orders.forEach(order -> { // authorize payment with the gateway // reserve or deduct items from inventory // confirm and update order status // generate invoice // send confirmation email/notification order.setStatus(Status.CONFIRMED); repository.save(order); }); } }
186. @Component public class ProcessPendingOrdersJob { private OrderRepository repository; @Scheduled(fixedDelay = 60_000) public void execute() { List<Order> orders = repository.findAllByStatus(Status.PENDING); orders.forEach(order -> { // authorize payment with the gateway // reserve or deduct items from inventory // confirm and update order status // generate invoice // send confirmation email/notification order.setStatus(Status.CONFIRMED); repository.save(order); }); } }
187. @Component public class ProcessPendingOrdersJob { private OrderRepository repository; @Scheduled(fixedDelay = 60_000) public void execute() { List<Order> orders = repository.findAllByStatus(Status.PENDING); orders.forEach(order -> { // authorize payment with the gateway // reserve or deduct items from inventory // confirm and update order status // generate invoice // send confirmation email/notification order.setStatus(Status.CONFIRMED); repository.save(order); }); } }
188. @Component public class ProcessPendingOrdersJob { private OrderRepository repository; @Scheduled(fixedDelay = 60_000) public void execute() { Order order; while ((order = repository.findFirstByStatus(PENDING)) != null) { // authorize payment with the gateway // reserve or deduct items from inventory // confirm and update order status // generate invoice // send confirmation email/notification order.setStatus(Status.CONFIRMED); repository.save(order); } } }
189. @Component public class ProcessPendingOrdersJob { private OrderRepository repository; @Scheduled(fixedDelay = 60_000) public void execute() { Order order; while ((order = repository.findFirstByStatus(PENDING)) != null) { // authorize payment with the gateway // reserve or deduct items from inventory // confirm and update order status // generate invoice // send confirmation email/notification order.setStatus(Status.CONFIRMED); repository.save(order); } } }
190. @Component public class ProcessPendingOrdersJob { private OrderRepository repository; @Scheduled(fixedDelay = 60_000) public void execute() { Order order; while ((order = repository.findFirstByStatus(PENDING)) != null) { // authorize payment with the gateway // reserve or deduct items from inventory // confirm and update order status // generate invoice // send confirmation email/notification order.setStatus(Status.CONFIRMED); repository.save(order); } } }
191. @Component public class ProcessPendingOrdersJob { private OrderRepository repository; @Scheduled(fixedDelay = 60_000) public void execute() { Order order; while ((order = repository.findFirstByStatus(PENDING)) != null) { // authorize payment with the gateway // reserve or deduct items from inventory // confirm and update order status // generate invoice // send confirmation email/notification order.setStatus(Status.CONFIRMED); repository.save(order); } } }
192. @Component public class ProcessPendingOrdersJob { private OrderRepository repository; @Scheduled(fixedDelay = 60_000) public void execute() { Order order; while ((order = repository.findFirstByStatus(PENDING)) != null) { // authorize payment with the gateway // reserve or deduct items from inventory // confirm and update order status // generate invoice // send confirmation email/notification order.setStatus(Status.CONFIRMED); repository.save(order); } } }
193. @Repository public interface OrderRepository extends JpaRepository<Order, Long> { public Order findFistByStatus(Status status); }
194. @Repository public interface OrderRepository extends JpaRepository<Order, Long> { public Order findFistByStatus(Status status); }
195. select o.* from order o where o.status = 'PENDING' order by o.created_at asc limit 1
196. select o.* from order o where o.status = 'PENDING' order by o.created_at asc limit 1
197. ID STATUS 1001 PENDING 1002 PENDING 1003 PENDING 1004 PENDING
198. INSERT queue
199. INSERT SELECT queue
200. INSERT UPDATE queue
201. ID STATUS 1001 CONFIRMED 1002 CONFIRMED 1003 CONFIRMED 1004 CONFIRMED
202. Uhuull!
203. What if…
204. …we run the application in a cluster?
208. ID STATUS 1001 PENDING 1002 PENDING 1003 PENDING 1004 PENDING
209. ID STATUS 1001 PENDING 1002 PENDING 1003 PENDING 1004 PENDING
210. ID STATUS 1001 PENDING 1002 PENDING 1003 PENDING 1004 PENDING
211. ID STATUS 1001 CONFIRMED 1002 PENDING 1003 PENDING 1004 PENDING
212. ID STATUS 1001 CONFIRMED 1002 PENDING 1003 PENDING 1004 PENDING
213. ID STATUS 1001 CONFIRMED 1002 CONFIRMED 1003 PENDING 1004 PENDING
214. ID STATUS 1001 CONFIRMED 1002 CONFIRMED 1003 PENDING 1004 PENDING
215. ID STATUS 1001 CONFIRMED 1002 CONFIRMED 1003 CONFIRMED 1004 PENDING
216. ID STATUS 1001 CONFIRMED 1002 CONFIRMED 1003 CONFIRMED 1004 PENDING
217. ID STATUS 1001 CONFIRMED 1002 CONFIRMED 1003 CONFIRMED 1004 CONFIRMED
218. ID STATUS 1001 CONFIRMED 1002 CONFIRMED 1003 CONFIRMED 1004 CONFIRMED
220. ID STATUS 1001 PENDING 1002 PENDING 1003 PENDING 1004 PENDING
221. ID STATUS 1001 PENDING 1002 PENDING 1003 PENDING 1004 PENDING
222. ID STATUS 1001 CONFIRMED 1002 PENDING 1003 PENDING 1004 PENDING
223. ID STATUS 1001 CONFIRMED 1002 PENDING 1003 PENDING 1004 PENDING 2x
224. ID STATUS 1001 CONFIRMED 1002 CONFIRMED 1003 PENDING 1004 PENDING 2x
225. ID STATUS 1001 CONFIRMED 1002 CONFIRMED 1003 PENDING 1004 PENDING 2x 2x
226. ID STATUS 1001 CONFIRMED 1002 CONFIRMED 1003 CONFIRMED 1004 PENDING 2x 2x
227. ID STATUS 1001 CONFIRMED 1002 CONFIRMED 1003 CONFIRMED 1004 PENDING 2x 2x 2x
228. ID STATUS 1001 CONFIRMED 1002 CONFIRMED 1003 CONFIRMED 1004 CONFIRMED 2x 2x 2x
229. ID STATUS 1001 CONFIRMED 1002 CONFIRMED 1003 CONFIRMED 1004 CONFIRMED 2x 2x 2x 2x
230. Oops!
231. Synchronized?
232. @Component public class ProcessPendingOrdersJob { @Scheduled(...) public synchronized void execute() { // executes our business logic } }
237. @Component public class ProcessPendingOrdersJob { @Scheduled(...) public synchronized void execute() { // executes our business logic } } No!
238. Distributed Lock?
239. @Scheduled(...) public void execute() { if (isLeader()) { // executes the logic here } }
240. @Scheduled(...) public void execute() { if (isLeader()) { // executes the logic here } }
252. No!
253. How about…
254. PostgreSQL
255. Give me a lock. Here is your lock.
256. @Repository public interface OrderRepository extends JpaRepository<Order, Long> { public Order findFirstByStatus(Status status); }
257. @Repository public interface OrderRepository extends JpaRepository<Order, Long> { @Lock(LockModeType.PESSIMISTIC_WRITE) public Order findFirstByStatus(Status status); }
258. select o.* from order o where o.status = 'PENDING' order by o.created_at asc limit 1
259. select o.* from order o where o.status = 'PENDING' order by o.created_at asc limit 1 for update
260. ID STATUS 1001 PENDING 1002 PENDING 1003 PENDING ... ...
261. ID STATUS 1001 PENDING 1002 PENDING 1003 PENDING ... ...
262. ID STATUS 1001 PENDING 1002 PENDING 1003 PENDING ... ...
263. ID STATUS 1001 PENDING 1002 PENDING 1003 PENDING ... ...
264. ID STATUS 1001 CONFIRMED 1002 PENDING 1003 PENDING ... ...
265. ID STATUS 1001 CONFIRMED 1002 PENDING 1003 PENDING ... ...
266. ID STATUS 1001 CONFIRMED 1002 PENDING 1003 PENDING ... ...
267. ID STATUS 1001 CONFIRMED 1002 PENDING 1003 PENDING ... ...
268. ID STATUS 1001 CONFIRMED 1002 PENDING 1003 PENDING ... ...
269. ID STATUS 1001 CONFIRMED 1002 PENDING 1003 PENDING ... ...
270. ID STATUS 1001 CONFIRMED 1002 CONFIRMED 1003 PENDING ... ...
271. ID STATUS 1001 CONFIRMED 1002 CONFIRMED 1003 PENDING ... ...
272. ID STATUS 1001 CONFIRMED 1002 CONFIRMED 1003 PENDING ... ...
273. ID STATUS 1001 CONFIRMED 1002 CONFIRMED 1003 PENDING ... ...
274. ID STATUS 1001 CONFIRMED 1002 CONFIRMED 1003 CONFIRMED ... ...
275. Yess!
276. What if…
277. …a single machine is not enough ?
285. To distribute the load…
286. 9630 9631 QUEUE 9629
287. 9630 9631 producer 9629 produces: 1000 items/min QUEUE
288. 9630 9631 producer consumer 9629 consumes: 600 items/min produces: 1000 items/min QUEUE
289. 9630 9631 producer consumer 9629 consumer consumes: 400 items/min consumes: 600 items/min produces: 1000 items/min QUEUE
290. consumes: 400 items/min consumes: 600 items/min produces: 1000 items/min 9630 9631 producer consumer 9629 consumer QUEUE
291. Wait…
293. CACHING LOAD BALANCING ASYNC PROCESSING
294. CACHING ASYNC PROCESSING LOAD BALANCING
296. No!
297. How about…
298. PostgreSQL
299. @Repository public interface OrderRepository extends JpaRepository<Order, Long> { @QueryHints({ @QueryHint( name = "javax.persistence.lock.timeout", value = LockOptions.SKIP_LOCKED) // org.hibernate.LockOptions }) @Lock(LockModeType.PESSIMISTIC_WRITE) public Order findFirstByStatus(Status status); }
300. @Repository public interface OrderRepository extends JpaRepository<Order, Long> { @QueryHints({ @QueryHint( name = "javax.persistence.lock.timeout", value = LockOptions.SKIP_LOCKED) // org.hibernate.LockOptions }) @Lock(LockModeType.PESSIMISTIC_WRITE) public Order findFirstByStatus(Status status); }
301. @Repository public interface OrderRepository extends JpaRepository<Order, Long> { @QueryHints({ @QueryHint( name = "javax.persistence.lock.timeout", value = LockOptions.SKIP_LOCKED) // org.hibernate.LockOptions }) @Lock(LockModeType.PESSIMISTIC_WRITE) public Order findFirstByStatus(Status status); }
302. select o.* from order o where o.status = 'PENDING' order by o.created_at asc limit 1 for update
303. select o.* from order o where o.status = 'PENDING' order by o.created_at asc limit 1 for update skip locked
304. ID STATUS 1001 PENDING 1002 PENDING 1003 PENDING ... ...
305. ID STATUS 1001 PENDING 1002 PENDING 1003 PENDING ... ...
306. ID STATUS 1001 PENDING 1002 PENDING 1003 PENDING ... ...
307. ID STATUS 1001 PENDING 1002 PENDING 1003 PENDING ... ...
308. ID STATUS 1001 PENDING 1002 PENDING 1003 PENDING ... ...
309. ID STATUS 1001 PENDING 1002 PENDING 1003 PENDING ... ...
310. ID STATUS 1001 PENDING 1002 PENDING 1003 PENDING ... ...
311. ID STATUS 1001 CONFIRMED 1002 CONFIRMED 1003 PENDING ... ...
312. ID STATUS 1001 CONFIRMED 1002 CONFIRMED 1003 PENDING ... ...
313. ID STATUS 1001 CONFIRMED 1002 CONFIRMED 1003 PENDING ... ...
314. ID STATUS 1001 CONFIRMED 1002 CONFIRMED 1003 PENDING ... ...
315. ID STATUS 1001 CONFIRMED 1002 CONFIRMED 1003 PENDING ... ...
316. ID STATUS 1001 CONFIRMED 1002 CONFIRMED 1003 PENDING ... ...
317. ID STATUS 1001 CONFIRMED 1002 CONFIRMED 1003 PENDING ... ...
318. ID STATUS 1001 CONFIRMED 1002 CONFIRMED 1003 PENDING ... ...
319. In other words…
320. ID STATUS 1001 PENDING 1002 PENDING 1003 PENDING 1004 PENDING
321. ID STATUS 1001 PENDING 1002 PENDING 1003 PENDING 1004 PENDING
322. Yess!
323. But does it scale?
324. single-threaded works well… https://www.pgcon.org/2016/schedule/track/Applications/929.en.html Thomas Munro
325. single-threaded works well…
326. single-threaded works well… https://www.pgcon.org/2016/schedule/track/Applications/929.en.html
327. multi-threaded with FOR UPDATE…
328. multi-threaded with FOR UPDATE… https://www.pgcon.org/2016/schedule/track/Applications/929.en.html
329. multi-threaded with FOR UPDATE… https://www.pgcon.org/2016/schedule/track/Applications/929.en.html
330. multi-threaded with SKIP LOCKED…
331. multi-threaded with SKIP LOCKED… https://www.pgcon.org/2016/schedule/track/Applications/929.en.html
332. multi-threaded with SKIP LOCKED… https://www.pgcon.org/2016/schedule/track/Applications/929.en.html
333. Relational databases do not scale. They said. 😵💫 “
334. https://www.rudderstack.com/blog/scaling-postgres-queue/
335. https://www.rudderstack.com/blog/scaling-postgres-queue/
336. https://www.rudderstack.com/blog/scaling-postgres-queue/
337. MESSAGING PATTERNS
338. Point-to-Point Pub/Sub Consumers pull messages, process and remove them Publish messages to many subscribers and each subscriber gets a copy of the message to process https://eda-visuals.boyney.io/visuals/queues-vs-streams-vs-pubsub MESSAGING PATTERNS
339. Point-to-Point Consumers pull messages, process and remove them https://eda-visuals.boyney.io/visuals/queues-vs-streams-vs-pubsub Pub/Sub Publish messages to many subscribers and each subscriber gets a copy of the message to process MESSAGING PATTERNS
340. https://eda-visuals.boyney.io/visuals/queues-vs-streams-vs-pubsub Pub/Sub Publish messages to many subscribers and each subscriber gets a copy of the message to process Point-to-Point Consumers pull messages, process and remove them MESSAGING PATTERNS
341. Listen / Notify
342. To send a message…
343. pg_notify() NOTIFY is a utility command for sending noti fi cations to other sessions connected to the same database listening on a channel speci fi ed with the LISTEN command.
344. pg_notify(<topic>, <message>)
345. pg_notify(<topic>, <message>)
346. pg_notify(<topic>, <message>)
347. pg_notify(<topic>, <message>)
348. pg_notify( ‘orders-confirmed-topic’, ‘{“id”: 42, “name”: “iPhone 13”}’ )
349. pg_notify( ‘orders-confirmed-topic’, ‘{“id”: 42, “name”: “iPhone 13”}’ )
350. pg_notify( ‘orders-confirmed-topic’, ‘{“id”: 42, “name”: “iPhone 13”}’ )
351. While to receive messages…
352. LISTEN LISTEN is a utility command which registers the current session as a listener on the named channel.
353. LISTEN <topic>
354. LISTEN <topic>
355. LISTEN ‘orders-confirmed-topic’
356. LISTEN ‘orders-confirmed-topic’
357. Let’s get back to our consumer…
358. @Component public class ProcessPendingOrdersJob { private OrderRepository repository; @Scheduled(fixedDelay = 60_000) public void execute() { Order order; while ((order = repository.findFirstByStatus(PENDING)) != null) { // authorize payment with the gateway // reserve or deduct items from inventory // confirm and update order status // generate invoice // send confirmation email/notification order.setStatus(Status.CONFIRMED); repository.save(order); } } }
359. @Component public class ProcessPendingOrdersJob { private OrderRepository repository; @Scheduled(fixedDelay = 60_000) public void execute() { Order order; while ((order = repository.findFirstByStatus(PENDING)) != null) { // authorize payment with the gateway // reserve or deduct items from inventory // confirm and update order status // generate invoice // send confirmation email/notification order.setStatus(Status.CONFIRMED); repository.save(order); } } }
360. @Component public class ProcessPendingOrdersJob { private OrderRepository repository; @Scheduled(fixedDelay = 60_000) public void execute() { Order order; while ((order = repository.findFirstByStatus(PENDING)) != null) { // authorize payment with the gateway // reserve or deduct items from inventory // confirm and update order status // generate invoice // send confirmation email/notification order.setStatus(Status.CONFIRMED); repository.save(order); } } }
361. @Component public class ProcessPendingOrdersJob { private OrderRepository repository; @Scheduled(fixedDelay = 60_000) public void execute() { Order order; while ((order = repository.findFirstByStatus(PENDING)) != null) { // authorize payment with the gateway // reserve or deduct items from inventory // confirm and update order status // generate invoice // send confirmation email/notification order.setStatus(Status.CONFIRMED); repository.save(order); } } }
363. INSERT PENDING PENDING PENDING
364. INSERT PENDING PENDING PENDING
365. INSERT PENDING PENDING PENDING
366. INSERT PENDING PENDING PENDING
367. INSERT CONFIRMED CONFIRMED PENDING
368. INSERT CONFIRMED CONFIRMED PENDING
369. @Component public class ProcessPendingOrdersJob { private OrderRepository repository; @Scheduled(fixedDelay = 60_000) public void execute() { Order order; while ((order = repository.findFirstByStatus(PENDING)) != null) { // authorize payment with the gateway // reserve or deduct items from inventory // confirm and update order status // generate invoice // send confirmation email/notification order.setStatus(Status.CONFIRMED); repository.save(order); } } }
370. @Component public class ProcessPendingOrdersJob { private OrderRepository repository; @Scheduled(fixedDelay = 60_000) public void execute() { Order order; while ((order = repository.findFirstByStatus(PENDING)) != null) { // authorize payment with the gateway // reserve or deduct items from inventory // confirm and update order status // generate invoice // send confirmation email/notification order.setStatus(Status.CONFIRMED); repository.save(order); } } }
371. @Component public class ProcessPendingOrdersJob { private OrderRepository repository; @Scheduled(fixedDelay = 60_000) public void execute() { Order order; while ((order = repository.findFirstByStatus(PENDING)) != null) { // authorize payment with the gateway // reserve or deduct items from inventory // confirm and update order status repository .publish(“orders-confirmed-topic“, order.toJson()); order.setStatus(Status.CONFIRMED); repository.save(order); } } }
372. @Component public class ProcessPendingOrdersJob { private OrderRepository repository; @Scheduled(fixedDelay = 60_000) public void execute() { Order order; while ((order = repository.findFirstByStatus(PENDING)) != null) { // authorize payment with the gateway // reserve or deduct items from inventory // confirm and update order status repository .publish(“orders-confirmed-topic“, order.toJson()); order.setStatus(Status.CONFIRMED); repository.save(order); } } }
373. @Component public class ProcessPendingOrdersJob { private OrderRepository repository; @Scheduled(fixedDelay = 60_000) public void execute() { Order order; while ((order = repository.findFirstByStatus(PENDING)) != null) { // authorize payment with the gateway // reserve or deduct items from inventory // confirm and update order status repository .publish(“orders-confirmed-topic“, order.toJson()); order.setStatus(Status.CONFIRMED); repository.save(order); } } }
374. @Component public class ProcessPendingOrdersJob { private OrderRepository repository; @Scheduled(fixedDelay = 60_000) public void execute() { Order order; while ((order = repository.findFirstByStatus(PENDING)) != null) { // authorize payment with the gateway // reserve or deduct items from inventory // confirm and update order status repository .publish(“orders-confirmed-topic“, order.toJson()); order.setStatus(Status.CONFIRMED); repository.save(order); } } }
375. @Component public class ProcessPendingOrdersJob { private OrderRepository repository; @Scheduled(fixedDelay = 60_000) public void execute() { Order order; while ((order = repository.findFirstByStatus(PENDING)) != null) { // authorize payment with the gateway // reserve or deduct items from inventory // confirm and update order status repository .publish(“orders-confirmed-topic“, order.toJson()); order.setStatus(Status.CONFIRMED); repository.save(order); } } }
376. @Repository public interface OrderRepository extends JpaRepository<Order, Long> { @Query( nativeQuery = true, value = "SELECT pg_notify(:topic, :message)" ) public void publish(String topic, String message); // other methods }
377. @Repository public interface OrderRepository extends JpaRepository<Order, Long> { @Query( nativeQuery = true, value = "SELECT pg_notify(:topic, :message)" ) public void publish(String topic, String message); // other methods }
378. @Repository public interface OrderRepository extends JpaRepository<Order, Long> { @Query( nativeQuery = true, value = "SELECT pg_notify(:topic, :message)" ) public void publish(String topic, String message); // other methods }
379. @Repository public interface OrderRepository extends JpaRepository<Order, Long> { @Query( nativeQuery = true, value = "SELECT pg_notify(:topic, :message)" ) public void publish(String topic, String message); // other methods }
380. @Repository public interface OrderRepository extends JpaRepository<Order, Long> { @Query( nativeQuery = true, value = "SELECT pg_notify(:topic, :message)" ) public void publish(String topic, String message); // other methods }
381. @Repository public interface OrderRepository extends JpaRepository<Order, Long> { @Query( nativeQuery = true, value = "SELECT pg_notify(:topic, :message)" ) public void publish(String topic, String message); // other methods }
382. INSERT PENDING PENDING PENDING
383. INSERT PENDING PENDING PENDING
384. INSERT PENDING PENDING PENDING
385. INSERT PENDING PENDING PENDING
386. INSERT PENDING PENDING PENDING
387. LISTEN ‘orders-confirmed-topic’ INSERT PENDING LISTEN ‘orders-confirmed-topic PENDING PENDING
388. LISTEN ‘orders-confirmed-topic’ INSERT PENDING LISTEN ‘orders-confirmed-topic PENDING PENDING
389. INSERT PENDING PG_NOTIFY( ‘orders-confirmed-topic’, ‘{“id”: 42, “name”: ... }’ ) PENDING PENDING LISTEN ‘orders-confirmed-topic’ LISTEN ‘orders-confirmed-topic
390. INSERT CONFIRMED CONFIRMED PENDING LISTEN ‘orders-confirmed-topic’ LISTEN ‘orders-confirmed-topic PG_NOTIFY( ‘orders-confirmed-topic’, ‘{“id”: 42, “name”: ... }’ )
391. INSERT CONFIRMED CONFIRMED PENDING LISTEN ‘pedidos-finalizados’ LISTEN ‘pedidos-finalizados’ pg_notify( ‘pedidos-finalizados’, ‘{“id”: 42, “nome”: ... }’ )
392. try (Connection conn = new org.postgresql.Driver().connect(url, properties)) { conn.createStatement().execute(“LISTEN ‘orders-confirmed-topic’"); while (true) { PGNotification[] ns = ((PGConnection) conn).getNotifications(0); if (ns != null) { for (PGNotification n : ns) { System.out.println("pid=%s, event=%s, payload=%s".formatted( n.getPID(), n.getName(), n.getParameter() )); // executes the business logic here } } } }
393. try (Connection conn = new org.postgresql.Driver().connect(url, properties)) { conn.createStatement().execute(“LISTEN ‘orders-confirmed-topic’"); while (true) { PGNotification[] ns = ((PGConnection) conn).getNotifications(0); if (ns != null) { for (PGNotification n : ns) { System.out.println("pid=%s, event=%s, payload=%s".formatted( n.getPID(), n.getName(), n.getParameter() )); // executes the business logic here } } } }
394. try (Connection conn = new org.postgresql.Driver().connect(url, properties)) { conn.createStatement().execute(“LISTEN ‘orders-confirmed-topic’"); while (true) { PGNotification[] ns = ((PGConnection) conn).getNotifications(0); if (ns != null) { for (PGNotification n : ns) { System.out.println("pid=%s, event=%s, payload=%s".formatted( n.getPID(), n.getName(), n.getParameter() )); // executes the business logic here } } } }
395. try (Connection conn = new org.postgresql.Driver().connect(url, properties)) { conn.createStatement().execute(“LISTEN ‘orders-confirmed-topic’”); while (true) { PGNotification[] ns = ((PGConnection) conn).getNotifications(0); if (ns != null) { for (PGNotification n : ns) { System.out.println("pid=%s, event=%s, payload=%s".formatted( n.getPID(), n.getName(), n.getParameter() )); // executes the business logic here } } } } Listen to a topic
396. try (Connection conn = new org.postgresql.Driver().connect(url, properties)) { conn.createStatement().execute(“LISTEN ‘orders-confirmed-topic’"); while (true) { PGNotification[] ns = ((PGConnection) conn).getNotifications(0); if (ns != null) { for (PGNotification n : ns) { System.out.println("pid=%s, event=%s, payload=%s".formatted( n.getPID(), n.getName(), n.getParameter() )); // executes the business logic here } } } }
397. try (Connection conn = new org.postgresql.Driver().connect(url, properties)) { conn.createStatement().execute(“LISTEN ‘orders-confirmed-topic’"); while (true) { PGNotification[] ns = ((PGConnection) conn).getNotifications(0); if (ns != null) { for (PGNotification n : ns) { System.out.println("pid=%s, event=%s, payload=%s".formatted( n.getPID(), n.getName(), n.getParameter() )); // executes the business logic here } } } }
398. try (Connection conn = new org.postgresql.Driver().connect(url, properties)) { conn.createStatement().execute(“LISTEN ‘orders-confirmed-topic’"); while (true) { PGNotification[] ns = ((PGConnection) conn).getNotifications(0); if (ns != null) { for (PGNotification n : ns) { System.out.println("pid=%s, event=%s, payload=%s".formatted( n.getPID(), n.getName(), n.getParameter() )); // executes the business logic here } } } } Blocks FOREVER until new events are received
399. try (Connection conn = new org.postgresql.Driver().connect(url, properties)) { conn.createStatement().execute(“LISTEN ‘orders-confirmed-topic’"); while (true) { PGNotification[] ns = ((PGConnection) conn).getNotifications(0); if (ns != null) { for (PGNotification n : ns) { System.out.println("pid=%s, event=%s, payload=%s".formatted( n.getPID(), n.getName(), n.getParameter() )); // executes the business logic here } } } }
400. try (Connection conn = new org.postgresql.Driver().connect(url, properties)) { conn.createStatement().execute(“LISTEN ‘orders-confirmed-topic’"); while (true) { PGNotification[] ns = ((PGConnection) conn).getNotifications(0); if (ns != null) { for (PGNotification n : ns) { System.out.println("pid=%s, event=%s, payload=%s".formatted( n.getPID(), n.getName(), n.getParameter() )); // executes the business logic here } } } }
401. try (Connection conn = new org.postgresql.Driver().connect(url, properties)) { conn.createStatement().execute(“LISTEN ‘orders-confirmed-topic’"); while (true) { PGNotification[] ns = ((PGConnection) conn).getNotifications(0); if (ns != null) { for (PGNotification n : ns) { System.out.println("pid=%s, event=%s, payload=%s".formatted( n.getPID(), n.getName(), n.getParameter() )); // executes the business logic here } } } }
402. try (Connection conn = new org.postgresql.Driver().connect(url, properties)) { conn.createStatement().execute(“LISTEN ‘orders-confirmed-topic’"); while (true) { PGNotification[] ns = ((PGConnection) conn).getNotifications(0); if (ns != null) { for (PGNotification n : ns) { System.out.println("pid=%s, event=%s, payload=%s".formatted( n.getPID(), n.getName(), n.getParameter() )); // executes the business logic here } } } }
403. try (Connection conn = new org.postgresql.Driver().connect(url, properties)) { conn.createStatement().execute(“LISTEN ‘orders-confirmed-topic’"); while (true) { PGNotification[] ns = ((PGConnection) conn).getNotifications(0); if (ns != null) { for (PGNotification n : ns) { System.out.println("pid=%s, event=%s, payload=%s".formatted( n.getPID(), n.getName(), n.getParameter() )); // executes the business logic here } } } }
404. @Component public class OrdersConfirmedListener { @EventListener(ApplicationReadyEvent.class) public void onApplicationReady() { Thread thread = new Thread(this::listenEvents, "pg-listener-thread"); thread.setDaemon(true); thread.start(); } private void listenEvents() { // executes our listener code here } }
405. A key advantage of this pattern…
406. INSERT CONFIRMED CONFIRMED PENDING LISTEN ‘orders-confirmed-topic’ LISTEN ‘orders-confirmed-topic pg_notify( ‘orders-confirmed-topic’, ‘{“id”: 42, “name”: ... }’ )
407. INSERT CONFIRMED CONFIRMED PENDING LISTEN ‘orders-confirmed-topic’ pg_notify( ‘orders-confirmed-topic’, ‘{“id”: 42, “name”: ... }’ ) LISTEN ‘orders-confirmed-topic
408. INSERT CONFIRMED CONFIRMED PENDING LISTEN ‘orders-confirmed-topic’ pg_notify( ‘orders-confirmed-topic’, ‘{“id”: 42, “name”: ... }’ ) LISTEN ‘orders-confirmed-topic’ LISTEN ‘orders-confirmed-topic
409. INSERT CONFIRMED CONFIRMED PENDING LISTEN ‘orders-confirmed-topic’ pg_notify( ‘orders-confirmed-topic’, ‘{“id”: 42, “name”: ... }’ ) LISTEN ‘orders-confirmed-topic’ LISTEN ‘orders-confirmed-topic
410. INSERT CONFIRMED CONFIRMED PENDING LISTEN ‘orders-confirmed-topic’ LISTEN ‘orders-confirmed-topic’ pg_notify( ‘orders-confirmed-topic’, ‘{“id”: 42, “name”: ... }’ ) LISTEN ‘orders-confirmed-topic
411. Recap…
413. LOAD BALANCING CACHING ASYNC PROCESSING
414. CACHING LOAD BALANCING ASYNC PROCESSING
415. INSERT CONFIRMED CONFIRMED PENDING LISTEN ‘orders-confirmed-topic’ LISTEN ‘orders-confirmed-topic pg_notify( ‘orders-confirmed-topic’, ‘{“id”: 42, “name”: ... }’ )
416. INSERT CONFIRMED CONFIRMED PENDING LISTEN ‘orders-confirmed-topic’ LISTEN ‘orders-confirmed-topic pg_notify( ‘orders-confirmed-topic’, ‘{“id”: 42, “name”: ... }’ )
417. INSERT CONFIRMED CONFIRMED PENDING LISTEN ‘orders-confirmed-topic’ LISTEN ‘orders-confirmed-topic pg_notify( ‘orders-confirmed-topic’, ‘{“id”: 42, “name”: ... }’ )
418. INSERT CONFIRMED CONFIRMED PENDING LISTEN ‘pedidos-finalizados’ LISTEN ‘pedidos-finalizados’ pg_notify( ‘pedidos-finalizados’, ‘{“id”: 42, “nome”: ... }’ ) Queue
419. INSERT CONFIRMED CONFIRMED PENDING Pub/Sub LISTEN ‘orders-confirmed-topic’ LISTEN ‘orders-confirmed-topic PG_NOTIFY( ‘orders-confirmed-topic’, ‘{“id”: 42, “name”: ... }’ )
420. PostgreSQL
421. Concluding
424. No!
429. It also works on all other platforms
430. TASK SCHEDULER LIBRARIES AND FRAMEWORKS
432. Solid Queue
433. Solid Queue
434. Solid Queue
435. Solid Queue
436. Use your database only as a data repository. “
437. Use your database only as a data repository. “ No!
438. Rafael Ponte Thank you!! /rponte