Create PostgreSQL lab to test PostgreSQL as primary and replica and ProxySQL as proxy

[kkovachki]

Title ideas:

• Building a PostgreSQL HA Lab with ProxySQL + TPC-DS Benchmark

• Primary/Replica PostgreSQL + ProxySQL: Real Performance Test with TPC-DS

• Does ProxySQL Help? PostgreSQL Benchmark with and without Proxy

1. Introduction — Why this lab matters

Explain:

• real-world need:

  • scaling reads

  • separating workloads

  • HA architecture

• problem:

  • does proxy help or add overhead?

2. Architecture Overview

6

Describe:

• Primary (writes)

• Replica (reads)

• ProxySQL layer

• Client behavior

3. Environment Setup (Debian 13)

Short but clear:

• install PostgreSQL

• configure primary

• configure replica

• verify replication

4. ProxySQL Configuration

Explain clearly:

• hostgroups:

  • 10 → primary

  • 20 → replica

• user setup

• monitor user

• query rule:

  • SELECT → replica

5. Validation Tests

Show:

SELECT inet_server_addr(), pg_is_in_recovery();

6. TPC-DS Dataset (Core Value Section)

7

Explain:

• what TPC-DS is

• why it’s perfect:

  • complex joins

  • analytical queries

  • read-heavy workload

Steps:

1. Generate data

2. Load into PostgreSQL

3. Let replication sync

4. Run queries