Unlocking Scalability Magic: Run Postgres Read-Write Nodes on Docker and Implement it with Python’s SQLAlchemy

Scaling your PostgreSQL database for high-performance read and write operations is crucial for modern applications. When you use a managed Relational Database Service like Aurora RDS (PostgreSQL) from AWS, you get one primary node and a choice of multiple reader nodes.

To have better High Availability, AWS ensures any failure in the primary node automatically failover to any other reader nodes and makes them a primary one.

Efforts to give a too-basic visualization about Auroras Read and Primary endpoints

AWS Aurora Cluster offers two endpoints in FQDN form. That DNS has the entries of the nodes that the cluster contains with shallow TTL values(5 seconds TTL). Internally, it handles

• DNS entries for added and removed reader nodes.
• DNS change for failover of primary nodes to reader nodes.
• DNS entries of reader nodes will follow a round-robin algorithm.

Agenda

• How to create a mock Aurora cluster using docker-compose?
• How to split the read-write queries in SQLalchemy?

Database Architecture

I will be creating

• Read-Write Node: This primary server handles all write operations and…