https://spin.atomicobject.com/2021/02/04/redis-postgresql/ Atomic Object Show Nav Close Menu < Blog Home C-19 Update Atomic Object Services Portfolio Team Culture Contact Atomic Object Atomic Object Services Portfolio Team Culture C-19 Update Blog Contact Atomic Object Services Portfolio Team Culture C-19 Update Blog Contact Atomic Spin Atomic Object's blog on everything we find fascinating. Close < back to Blog Home Chris Farber By: Chris Farber Software Consultant & Developer at Atomic Object Grand Rapids. Read Bio - Posted In * Web Apps About This Author February 4, 2021 by: Chris Farber 1 Comment Do You Really Need Redis? How to Get Away with Just PostgreSQL There's a tried-and-true architecture that I've seen many times for supporting your web services and applications: * PostgreSQL for data storage * Redis for coordinating background job queues (and some limited atomic operations) Redis is fantastic, but what if I told you that its most common use cases for this stack could actually be achieved using only PostgreSQL? Use Case 1: Job Queuing Perhaps the most common use of Redis I've seen is to coordinate dispatching of jobs from your web service to a pool of background workers. The concept is that you'd like to record the desire for some background job to be performed (perhaps with some input data) and to ensure that only one of your many background workers will pick it up. Redis helps with this because it provides a rich set of atomic operations for its data structures. But since the introduction of version 9.5, PostgreSQL has a SKIP LOCKED option for the SELECT ... FOR ... statement (here's the documentation). When this option is specified, PostgreSQL will just ignore any rows that would require waiting for a lock to be released. Consider this example from the perspective of a background worker: BEGIN; WITH job AS ( SELECT id FROM jobs WHERE status = 'pending' LIMIT 1 FOR UPDATE SKIP LOCKED ) UPDATE jobs SET status = 'running' WHERE jobs.id = job.id RETURNING jobs.*; COMMIT; By specifying FOR UPDATE SKIP LOCKED, a row-level lock is implicitly acquired for any rows returned from the SELECT. Further, because you specified SKIP LOCKED, there's no chance of this statement blocking on another transaction. If there's another job ready to be processed, it will be returned. There's no concern about multiple workers running this command receiving the same row because of the row-level lock. The biggest caveat for this technique is that, if you have a large number of workers trying to pull off this queue and a large number of jobs feeding them, they may spend some time stepping through jobs and trying to acquire a lock. In practice, most of the apps I've worked on have fewer than a dozen background workers, and the cost is not likely to be significant. Use Case 2: Application Locks Let's imagine that you have a synchronization routine with a third-party service, and you only want one instance of it running for any given user across all server processes. This is another common application I've seen for Redis: distributed locking. PostgreSQL can achieve this as well using its advisory locks. Advisory locks allow you to leverage the same locking engine PostgreSQL uses internally for your own application-defined purposes. Use Case 3: Pub/Sub I saved the coolest example for last: pushing events to your active clients. For example, say you need to notify a user that they have a new message available to read. Or perhaps you'd like to stream data to the client as it becomes available. Typically, web sockets are the transport layer for these events while Redis serves as the Pub/Sub engine. However, since version 9, PostgreSQL also provides this functionality via the LISTEN and NOTIFY statements. Any PostgreSQL client can subscribe (LISTEN) to a particular message channel, which is just an arbitrary string. When any other client sends a message (NOTIFY) on that channel, all other subscribed clients will be notified. Optionally, a small message can be attached. If you happen to be using Rails and ActionCable, using PostgreSQL is even supported out of the box. Taking Full Advantage of PostgreSQL Redis fundamentally fills a different niche than PostgreSQL and excels at things PostgreSQL doesn't aspire to. Examples include caching data with TTLs and storing and manipulating ephemeral data. However, PostgreSQL has a lot more capabilities than you may expect when you approach it from the perspective of just another SQL database or some mysterious entity that lives behind your ORM. There's a good chance that the things you're using Redis for may actually be good tasks for PostgreSQL as well. It may be a worthy tradeoff to skip Redis and save on the operational costs and development complexity of relying on multiple data services. Share this article: * Hackernews * Twitter * Facebook * Googleplus * Linkedin Tagged: database postgreSQLPosted on February 4, 2021 by Chris Farber in Web Apps Related Posts Embedding Google Forms in a Static Site by Brian May How to Make Writing Performance Tests Easy With Cypress by Molly Alger 5 Tips & Tricks that Make p5 Even Better by Jarek Wojciechowski By commenting below, you agree to the terms and conditions outlined in our (linked) Privacy Policy 1 Comment 1. Nitin Sareen says: May 12, 2021 Well explained Chris, thanks for the insights. Reply Join the conversation Cancel reply Your email address will not be published. Required fields are marked * [ ] [ ] [ ] [ ] [ ] [ ] [ ] Comment [ ] Name * [ ] Email * [ ] Website [ ] [ ] Save my name, email, and website in this browser for the next time I comment. [Replies to my comments] Notify me of followup comments via e-mail. You can also subscribe without commenting. By commenting, you agree to the terms and conditions outlined in our Privacy Policy. [Submit Comment] [ ] [ ] [ ] [ ] [ ] [ ] [ ] [ ] Tell Us About Your Project We'd love to talk with you about your next great software project. Fill out this form and we'll get back to you within two business days. Share Your Project We're Hiring in Ann Arbor, Chicago, and Grand Rapidsopen positions > x Want to see what Atomic can do? Check out some of our work Atomic's Portfolio [ao-wordmark-white] Atomic is a software design + development consultancy. (c) 2021 Atomic Object LLC Explore * Careers * Diversity * Resources * Atomic Blog Offices * Grand Rapids * Ann Arbor * Chicago Details * Contact * Media * Privacy Policy * [circle-twi] * [circle-fac] * [circle-git] * [circle-lin] * [circle-ins] Certified B Corporation