Managing Concurrent Database Writes in a Distributed System with Redis or PostgreSQL
Tags: database, javascript, opensource, programming
Published: 2024-03-23 14:34:42

Scaling applications often leads to distributing workloads across multiple machines, introducing complexities surrounding shared mutable state and concurrent writes. Two prominent choices emerge in this realm: [Redis and PostgreSQL](https://www.suprsend.com/). Both offer distinct strengths and weaknesses depending on specific use cases. Join me as I guide you through navigating these waters successfully.

---


#### Topics Covered

1. [Shared Mutable State](#shared-mutable-state)
2. [Why Not Use SQLite?](#sqlite-limitations)
3. [Comparative Analysis: Redis vs. PostgreSQL](#comparison)
	* Key-value Store vs. Traditional RDBMS
	* Cluster Mode vs. Sharding
	* Eventual Consistency vs. Strong Consistency
4. [Use Case Selection Criteria](#selection-criteria)
5. [Deployment Strategies](#deployment)
6. [Best Practices and Tips](#best-practices)
7. [Potential Drawbacks and Solutions](#drawbacks)
8. [Conclusion](#conclusion)

---

<a name="shared-mutable-state"></a>

### Shared Mutable State

Managing concurrent writes revolves around controlling modifications applied simultaneously to a shared mutable state accessible from multiple locations. Common manifestations include caches, counters, leaderboards, and session storages.

Two primary goals drive successful designs:

* Preserve consistency guarantees
* Mitigate conflicts and contention risks

Meeting these targets demands vigilance and strategic thinking, carefully weighing costs against benefits.

<a name="sqlite-limitations"></a>

### Why Not Use SQLite?

SQLite shines in simple, self-contained projects featuring occasional reads and infrequent writes. Regrettably, scaling beyond humble beginnings exposes severe limitations inherent in its design philosophy:

* Serial writes: Allowing only one writer at a time severely restricts parallelism opportunities
* Single-file format: Impeding horizontal scalability, as filesystem-level atomicity remains insufficient for distributed settings
* Absence of network transport layer: Depriving clients of remote connections essential for geographical dispersion

For these reasons, alternative databases become increasingly attractive as traffic grows heavier and wider dispersed.

<a name="comparison"></a>

### Comparative Analysis: Redis vs. PostgreSQL

Below follows a comprehensive comparison examining vital dimensions affecting selection decisions.

<a name="key-value-store-vs-traditional-rdbms"></a>

#### Key-Value Store vs. Traditional RDBMS

First, contrast Redis as a key-value store against PostgreSQL, a conventional relational database management system:

|   | Redis                               | PostgreSQL              |
|---|---|---|
| Strengths      | High performance, simplicity          | Rich feature suite       |
| Weaknesses     | Feature lags, basic type system     | Lower absolute speed      |
| Primary uses  | Cache, counter, pub/sub             | General-purpose storage |

<a name="cluster-mode-vs-sharding"></a>

#### Cluster Mode vs. Sharding

Second, evaluate cluster mode and sharding practices prevalent in Redis and PostgreSQL communities:

|   | Redis Cluster                           | PostgreSQL Sharding            |
|---|---|---|
| Strengths      | Ease of deployment, automatic failover  | Greater flexibility            |
| Weaknesses     | Slower than standalone instances        | Complex configuration          |
| Primary uses  | Lightweight partitioning               | Fine-grained control           |

<a name="eventual-consistency-vs-strong-consistency"></a>

#### Eventual Consistency vs. Strong Consistency

Lastly, assess consistency models adopted by Redis and PostgreSQL:

|   | Redis (Eventual)                    | PostgreSQL (Strong)                  |
|---|---|---|
| Advantages   | Higher throughput, fewer dependencies   | Instantaneous visibility            |
| Disadvantages | Delayed propagation, risky merge ops   | Reduced scalability                  |

<a name="selection-criteria"></a>

### Use Case Selection Criteria

Deciding between Redis and PostgreSQL depends on answering the following questions accurately:

* What kind of data are you working with?
	+ Ephemeral, fleeting contents lean towards Redis
	+ Persistent, structured payloads suit PostgreSQL
* Are response times paramount?
	+ Yes: Opt for Redis
	+ No: Either works equally well
* Do you require strong consistency guarantees?
	+ Yes: Pick PostgreSQL
	+ No: Choose Redis unless conflict resolution matters
* Will data grow substantially over time?
	+ Yes: Plan for horizontal expansion
	+ No: Vertical scaling suffices

<a name="deployment"></a>

### Deployment Strategies

Irrespective of chosen pathways, adopt industry best practices for deployments:

* Automate infrastructure provisioning
* Isolate services logically and physically
* Monitor health indices constantly
* Trigger auto-scalability heuristics wisely

<a name="best-practices"></a>

### Best Practices and Tips

Follow these guidelines diligently when engaging with Redis or PostgreSQL:

* Benchmark thoroughly before committing
* Apply patches promptly
* Stay abreast of latest releases
* Document configurations comprehensibly

<a name="drawbacks"></a>

### Potential Drawbacks and Solutions

Despite apparent advantages, neither option escapes criticism entirely. Anticipate pitfalls and prepare contingency plans:

* Redis: Memory consumption spikes
	+ Solution: Utilize swap space conservatively
* PostgreSQL: Resource exhaustion
	+ Solution: Restrict max\_connections sensibly

<a name="conclusion"></a>

### Conclusion

Selecting appropriate databases plays a crucial role in constructing dependable, scalable systems. Armed with knowledge presented herein, discern sensible choices aligned with specific contexts, expectations, and aspirations. Remember, no silver bullets exist—compromises come naturally in engineering marvels. Cheers to informed judgments and prosperous endeavors!

---

You can read a detailed post about how we implemented this solution for our actual SAAS product triggering 100 million events.
[How Redis Solved Our Challenges with Dynamic Task Scheduling and Concurrent Execution? [Developer's Guide]](https://www.suprsend.com/post/how-redis-solved-our-challenges-with-dynamic-task-scheduling-and-concurrent-execution-developers-guide)