> ## Content Index
> Fetch the complete content index at: https://corti.com/llms.txt
> Use this file to discover other available public pages before exploring further.

# Performance Testing PostgreSQL Replication under Load using Apache JMeter
- URL: https://corti.com/performance-testing-postgresql-replication-under-load/
- Published: 2025-04-14T11:20:59.000Z
- Updated: 2025-04-14T11:23:14.000Z
- Author: Sascha Corti

I recently came along the requirement to test the performance of the database replication of PostgreSQL running on Azure Database for PostgreSQL Flexible Server under load.

Quote from: [https://learn.microsoft.com/en-us/azure/postgresql/flexible-server/concepts-read-replicas](https://learn.microsoft.com/en-us/azure/postgresql/flexible-server/concepts-read-replicas?ref=corti.com)

*Read replicas are primarily designed for scenarios where offloading queries is beneficial, and a slight lag is manageable. They're optimized to provide near real time updates from the primary for most workloads, making them an excellent solution for read-heavy scenarios. However, it's important to note that they aren't intended for synchronous replication scenarios requiring up-to-the-minute data accuracy. While the data on the replica eventually becomes consistent with the primary, there might be a delay, which typically ranges from a few seconds to minutes, and in some heavy workload or high-latency scenarios, this delay could extend to hours.*

But what is "heavy load"? Will 100 threads running 1000 updates in parallel overwhelm the database?

I found Apache JMeter, a simple but versatile tool to do exactly that. In the following, I outline my experiment and the findings.

## Outline

This test was conducted on Azure Database for Postgres Flexible Server with replication in place to see how fast replication works under load.

It will spawn 100 threads that will in parallel run 1000 insert statements.

## Database Setup

I created a main PostgreSQL Flexible Server in Azure with the following configuration.

![](https://corti.com/content/images/2025/04/postgresq-server-config.png)

PostgreSQL Server Configuration

Note that the Memory Optimized compute tier is required for automatic replication to work.

Any table will work for the test, but I used two tables with the following setup for a realistic feel:

```python
class Product(Base):
    __tablename__ = "products"

    id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    name = Column(String(100), nullable=False)
    category = Column(String(50), nullable=False)
    price = Column(Float(precision=10, decimal_return_scale=2), nullable=False)
    in_stock = Column(Boolean, nullable=False, default=True)
    created_at = Column(DateTime, server_default=func.now())

    # Relationship with Order model
    orders = relationship("Order", back_populates="product")

    def __repr__(self) -> str:
        return (f"<Product(id={self.id}, name='{self.name}', "
                f"category='{self.category}', price={self.price})>")

class Order(Base):
    __tablename__ = "orders"

    id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    product_id = Column(UUID(as_uuid=True), ForeignKey("products.id"))
    quantity = Column(Integer, nullable=False)
    order_date = Column(DateTime, server_default=func.now())

    # Relationship with Product model
    product = relationship("Product", back_populates="orders")

    def __repr__(self) -> str:
        return f"<Order(id={self.id}, product_id={self.product_id}, \
quantity={self.quantity})>"

```

This sample code is based on [SQL Alchemy](https://www.sqlalchemy.org/?ref=corti.com) and can be found on [GitHub](https://github.com/TechPreacher/azure%5Fpostgres%5Fapp/blob/main/database%5Fsetup.py?ref=corti.com).

I used the `Replication` tab in the Azure Portal to create a replica into a different PostgreSQL server in the same Azure subscription.

![](https://corti.com/content/images/2025/04/postgresql-replication-lag-0-1.png)

PostgreSQL Flexible Server Replication Settings

## Test Setup

This test uses the Apache JMeter load test tool to measure performance.  
Download JMeter from [https://jmeter.apache.org/download\_jmeter.cgi](https://jmeter.apache.org/download%5Fjmeter.cgi?ref=corti.com).  
You will need the PostgreSQL JDBC driver which can be downloaded from [https://jdbc.postgresql.org/download/](https://jdbc.postgresql.org/download/?ref=corti.com)

### Install JMeter

Extract the JMeter tgz archive to a folder of your choice.  
Add the PostgreSQL driver to the JMeter `lib/` subfolder.  
Start JMeter by executing the script file associated with your platform.

## Configure JMeter

Start by naming your empty test plan.

![](https://corti.com/content/images/2025/04/jmeter-1.png)

Next, add a **JDBC Connection Configuration** to the test plan. Give it a name and and set the **variable name for created pool**. Next, configure the **Max Number of Connections**, **Max Visits (ms)** and the **Time Between Eviction Runs (ms)**.

Provide the **Database URL** in the format of `jdbc:postgresql://servername.postgres.database.azure.com:5432/databasename`

Specify the **username** and **password** as well as the **JDBC Driver Class**: `org.postgresql.Driver` which should be selectable from the drop-down box if the JDBC driver is present in the `lib/` folder for JMeter.

![](https://corti.com/content/images/2025/04/jmeter-2.png)

Now create a **Thread Group** and specify the **Number of Threads (users)**, **Ramp-Up Period (ms)** and **Loop Count**.

![](https://corti.com/content/images/2025/04/jmeter-3.png)

Next, add a **JDBC Request** in which you specify the **Variable Name of Pool declared in JDBC Connection Configuration**. This is the value you set in the **JDBC Connection Configuration**.

Set the **Query Type** to `Update Statement` and add the query to insert data into the table. In case of the sample app linked above, this is:

```sql
INSERT INTO products (id, name, category, price, in_stock, created_at)  
VALUES ('${__UUID()}', 'test', 'test', 100.0, 'true', '${__RandomDate(,,2050-07-08,,)}');

```

![](https://corti.com/content/images/2025/04/jmeter-4.png)

Finally, add a **Summary Report** and link it to a `.csv` file on your disk so output gets saved.

![](https://corti.com/content/images/2025/04/jmeter-5.png)

Start the test with the green ▶️ button in the toolbar and wait for it to finish.

The output window should show the following initialization:

```plain
2025-04-14 09:27:27,214 INFO o.a.j.e.StandardJMeterEngine: Running the test!  
2025-04-14 09:27:27,215 INFO o.a.j.s.SampleEvent: List of sample_variables: []  
2025-04-14 09:27:27,227 INFO o.a.j.g.u.JMeterMenuBar: setRunning(true, *local*)  
2025-04-14 09:27:27,485 INFO o.a.j.e.StandardJMeterEngine: Starting ThreadGroup: 1 : Thread Group PostgreSQL Test  
2025-04-14 09:27:27,485 INFO o.a.j.e.StandardJMeterEngine: Starting 100 threads for group Thread Group PostgreSQL Test.  
2025-04-14 09:27:27,485 INFO o.a.j.e.StandardJMeterEngine: Thread will continue on error  
2025-04-14 09:27:27,485 INFO o.a.j.t.ThreadGroup: Starting thread group... number=1 threads=100 ramp-up=10 delayedStart=false  
2025-04-14 09:27:27,485 INFO o.a.j.t.JMeterThread: Thread started: Thread Group PostgreSQL Test 1-1  
2025-04-14 09:27:27,495 INFO o.a.j.t.ThreadGroup: Started thread group number 1
2025-04-14 09:27:27,495 INFO o.a.j.e.StandardJMeterEngine: All thread groups have been started  
2025-04-14 09:27:27,600 INFO o.a.j.t.JMeterThread: Thread started: Thread Group PostgreSQL Test 1-2
... repeat for each worker ...
2025-04-14 09:28:53,791 INFO o.a.j.t.JMeterThread: Thread is done: Thread Group PostgreSQL Test 1-1
2025-04-14 09:28:53,791 INFO o.a.j.t.JMeterThread: Thread finished: Thread Group PostgreSQL Test 1-2
... repeat for each worker ...
2025-04-14 09:28:54,309 INFO o.a.j.e.StandardJMeterEngine: Notifying test listeners of end of test  
2025-04-14 09:28:58,860 INFO o.a.j.g.u.JMeterMenuBar: setRunning(false, *local*)

```

The Summary Report should show you the performance of the test:

```plain
Label,# Samples,Average,Min,Max,Std. Dev.,Error %,Throughput,Received KB/sec,Sent KB/sec,Avg. Bytes
WRITE to PostgreSQL,100000,81,32,39248,1087.05,0.002%,1151.70222,10.12,0.00,9.0
TOTAL,100000,81,32,39248,1087.05,0.002%,1151.70222,10.12,0.00,9.0

```

Detailed performance output for each individual insert statement can be found in the CSV file you specified in the Summary Report step.

## Replication Lag

If you are using **Azure Database for PostgreSQL Flexible Server**, you can see the replication lag over time by selecting the replica and clicking on the **Read Replica Log** column. The average lag over time stays approximately the same even when the database is under load.

![](https://corti.com/content/images/2025/04/postgresql-replication-lag-0.png)

In this case, the maximum lag was 7 seconds.

![](https://corti.com/content/images/2025/04/postgresql-replication-lag-1.png)

Other metrics available uner **Metrics** are **Max Logical Replication Lag**, **Max Physical Replication Lag** and **Read Replica Lag**.

![](https://corti.com/content/images/2025/04/postgresql-replication-lag-2.png)