Member-only story
Cache and Locking with PostgreSQL
Unlogged Table & Advisory Lock for Golang HTTP Handler
With the continued evolution of PostgreSQL, it’s no longer just a database for storing and querying data. It has become a strong candidate for implementing application-level logic, traditionally achieved with tools like Redis.
In a Kubernetes environment, we can easily spin up a fully functional PostgreSQL cluster with just a plain YAML file using operators. This functionality makes PostgreSQL a natural choice for cloud-native applications.
In this paper, I will explore the use of unlogged tables and advisory locks to achieve caching and distributed locks in a web service.
The Problem
I have a system that manages incidents, each composed of multiple events. When a new event is added to an incident, the system must re-evaluate the incident to update its priority and process additional logic.
This logic is implemented as a Golang HTTP handler. However, if multiple events arrive simultaneously, the handler may be triggered concurrently, potentially leading to race conditions where the logic executes multiple times for the same incident update.
To prevent this, we need a synchronization mechanism, such as a lock or a queue, to ensure the…
