Spring Boot + PostgreSQL Read Replica (Primary/Replica) Demo — JPA + AbstractRoutingDataSource

This repository provides a hands-on example of implementing PostgreSQL Read Replicas in a Spring Boot application.

Key highlights:

• Primary / Replica database architecture using PostgreSQL streaming replication
• Read/Write routing with AbstractRoutingDataSource
• Replication lag measurement between Primary and Replica
• Example APIs for data seeding and log retrieval
• React frontend to visualize database behavior

You can clone this repository and run the entire environment locally using Docker to experiment with read/write routing and replication behavior.

---

🏗 Architecture

• Backend: Spring Boot 3.5.0, Java 17, Maven, Spring Data JPA (Hibernate)
• Database: PostgreSQL Primary (Port: 5432) + PostgreSQL Streaming Read Replica (Port: 5433)
• Routing Engine: AbstractRoutingDataSource + LazyConnectionDataSourceProxy + AOP around @Transactional
• Frontend: ReactJS (Vite), TailwindCSS, Axios

🚀 Getting Started

1. Requirements

Ensure you have the following installed on your machine:

• Docker & Docker Compose
• Java 17
• Maven 3.9+
• Node.js (LTS)

2. Start PostgreSQL Databases

We use Docker Compose to spin up a Primary PostgreSQL database and a Streaming Replica.

docker compose up -d

Wait a few moments for the database system and the replica to initialize. The replica is strictly read-only.

3. Run the Backend (Spring Boot)

Open a new terminal window, ensure you are in the root of the project. We will use the JDK provided directly within this repository to build and run the application.

# Make sure you start from the project root!
cd backend

# Temporarily set JAVA_HOME to the bundled JDK in the parent folder
export JAVA_HOME=$(pwd)/../jdk-17.0.10+7/Contents/Home
export PATH=$JAVA_HOME/bin:$PATH

# Build and run the backend
./mvnw clean package -DskipTests
java -jar target/demo-0.0.1-SNAPSHOT.jar

The backend server will start on http://localhost:8080.

4. Run the Frontend (ReactJS)

Open a new terminal window, ensure you are in the root of the project, navigate to the frontend directory, install dependencies, and start the Vite development server.

# Make sure you start from the project root!
cd frontend
npm install
npm run dev

The frontend will start on http://localhost:5173. Open this URL in your browser.

⚙️ How It Works

Read/Write Routing

The application configures two data sources: primaryDataSource and replicaDataSource. It leverages Spring's AbstractRoutingDataSource alongside LazyConnectionDataSourceProxy to determine the database connection dynamically.

An Aspect-Oriented Programming (AOP) class (DataSourceAspect.java) intercepts @Transactional annotations.

• If it sees @Transactional(readOnly = true), it routes the query to the Replica database.
• If it sees @Transactional, it routes the query to the Primary database.

Replication Lag Tracking & Terminology

This project demonstrates two ways to think about "lag":

1. dbTimestampDiffMs (Database Timestamp Difference):

   • This is calculated as replica.create_date - primary.create_date.
   • This will almost always be 0ms. Why? Because we are using PostgreSQL's built-in physical streaming replication. This method copies data at a low level (the WAL files), meaning the create_date timestamp generated on the primary is physically replicated, byte-for-byte, to the replica. The replica does not generate its own timestamp. This metric proves that physical replication is working correctly.

2. visibilityLagMs (Application Visibility Lag):

   • This is calculated as replicaFirstSeenAt - primary.create_date.
   • This is the true measure of replication lag. It measures the real-world time delta between when a row is committed on the primary and when it becomes visible (i.e., SELECT-able) to our application on the replica. This is the metric you should monitor for performance and data consistency.

The API response for /api/replication-lag includes debug information (primaryDbInfo and replicaDbInfo) that provides definitive proof of which database is being queried, including its IP and its read-only status (pg_is_in_recovery).

🔗 Endpoints

Method	Endpoint	Database Route	Description
POST	/api/logs/seed	PRIMARY	Seeds n number of logs into both config and history tables.
DELETE	/api/logs/clear	PRIMARY	Truncates both tables safely and resets lag history.
GET	/api/logs/joined	REPLICA	Fetches a paginated, application-joined view of logs.
GET	/api/replication-lag	REPLICA/PRIMARY	Fetches lag measurements for a specific table.
GET	/api/debug/db	REPLICA	Returns the database IP, Port, and read-only status.
GET	/api/debug/db/write	PRIMARY	Same as above but strictly forces a primary write connection.