Skip to content

14. Database Test Application & Benchmarking

Goal: Verify connection pooling, test failover resilience, insert validation markers for disaster recovery, and benchmark the read-only replication offloading.

The test application (test_db.py) is deployed as a Kubernetes Deployment running inside the cluster, connected to PostgreSQL via PgBouncer.

How it works inside the cluster

The application is deployed using a ConfigMap to inject the Python script and a Deployment using the python:3.11-slim base image. It connects to the two PgBouncer pooler services: - Primary writes & reads (RW): pg-pooler-rw.production.svc.cluster.local - Replica reads (RO): pg-pooler-ro.production.svc.cluster.local

flowchart TD
    subgraph Cluster
        App[db-test-app pod]
        App -->|writes & reads| PoolRW[pg-pooler-rw]
        App -->|benchmarking reads| PoolRO[pg-pooler-ro]
        PoolRW --> Primary[Primary Pod]
        PoolRO --> Standby1[Standby Pod 1]
        PoolRO --> Standby2[Standby Pod 2]
    end

Running the Verification Commands

1. Failover Loop Test

To watch connection recovery in real time during a failover (unplanned restart or planned switchover), run the write loop:

kubectl exec -it deployment/db-test-app -n production -- python /app/test_db.py --mode loop

While this runs, trigger a switchover or delete the primary pod. The application will catch connection errors and resume writes automatically when the new primary is promoted.

2. Disaster Recovery Validation Marker

Insert a unique marker into the running database before a backup or restore test:

kubectl exec -it deployment/db-test-app -n production -- python /app/test_db.py --mode insert --marker "my-recovery-test-01"

After restoring the backup to a new cluster, point this application (or a debug pod) to the restored cluster and verify the marker exists:

kubectl exec -it deployment/db-test-app -n production -- python /app/test_db.py --mode verify --marker "my-recovery-test-01"

Benchmarking Read Replicas (RO vs RW)

To prove that scaling read replicas increases total query throughput and offloads work from the primary pod, run the benchmark mode:

kubectl exec -it deployment/db-test-app -n production -- python /app/test_db.py --mode benchmark --workers 10 --queries 100

What this does:

  1. Ensures the database has test rows.
  2. Spawns 10 concurrent worker threads querying the Read-Write Pooler (RW), running 100 queries each.
  3. Spawns 10 concurrent worker threads querying the Read-Only Pooler (RO), running 100 queries each.
  4. Measures total time, calculates Queries Per Second (QPS), and outputs the performance improvement.

Under heavy query loads, the RO throughput should scale with the number of standby instances, leaving the primary pod free for write traffic.