Almost every SaaS application reaches the same wall: one PostgreSQL server that handles both the transactional writes and the analytical reads, until the reads grow enough to slow the writes. The answer is replicas, and the interesting question is how to get them without rewriting the application. This post follows a developer doing it on MassiveGRID PaaS, which is built on Virtuozzo Application Platform, formerly Jelastic, with the PostgreSQL Primary-Secondary Cluster package.
The application is a Django-based subscription billing SaaS we will call Ledgerline, with about 900 business customers whose finance teams run heavy reports every morning while the same database processes payments. The PostgreSQL Primary-Secondary Cluster package deploys a primary and one or more hot-standby secondaries kept in sync by streaming replication, with an optional Pgpool-II layer in front.
What streaming replication gives you, and what it does not
PostgreSQL's primary-secondary replication ships the write-ahead log (WAL) from the primary to each standby as it is written, and the standby replays it. The standbys are hot: they accept read-only queries while replaying. Replication is asynchronous by default, which means a standby is typically a few milliseconds behind under normal load and can fall further behind during a bulk write. For Ledgerline's morning reports, a report that is a few hundred milliseconds stale is indistinguishable from a current one. For the payments path, every write and every read that must see its own write goes to the primary.
The package installs all of this: the primary in archiving mode with WAL senders configured, the standbys in recovery mode with their replication settings, and the authentication between them. Installation is a dialog: PostgreSQL version, whether to add Pgpool-II, region, and go.
Why Pgpool-II, and why it lets the app stay unchanged
Without a proxy, the application must know two connection strings and decide per query which to use. Django can do this with database routers, and many teams do, but every ORM query then needs a routing decision and every developer needs to remember it. Pgpool-II sits in front of the cluster as a single entry point, and its load balancing mode parses each statement: writes and anything inside a transaction go to the primary; plain SELECTs can be spread across the standbys. Pgpool-II also pools connections, which matters for Django's per-request connection pattern, and monitors the nodes so a failed standby is dropped from rotation.
Ledgerline's developer enables Pgpool-II at install and changes exactly one thing in the application: the database host, from the old single node to a Pgpool-II node. The package can deploy Pgpool-II as a highly available pair, so that host is not a new single point of failure. The platform's built-in pgpoolAdmin panel on the Pgpool node is where she tunes load balancing per database, pool sizes and the health-check interval.
Making sure the right queries go to the right place
Pgpool-II's statement-level routing is good but not psychic, and three cases needed attention.
- Read-your-own-write. A payment is inserted, then the receipt page reads it. Pgpool-II routes the read to the primary if it is in the same transaction; Django's default autocommit means it is not. The developer wraps the write-then-read paths in
transaction.atomic(), which is good practice anyway and pins them to the primary. - Functions with side effects. A
SELECTthat calls a function that writes must go to the primary. Pgpool-II has a list of such functions (its write function list) for exactly this; Ledgerline had one, for sequence allocation, and it went on the list. - Long reports. The morning reports are the point of the exercise and should only hit standbys. Pgpool-II lets you set the load-balance weight per backend, so the developer gives the primary weight zero for read balancing: reads go to standbys unless none is available.
Adding a second standby without a maintenance window
Two months in, the first standby is busy every morning. Adding another is a platform operation: scale the PostgreSQL standby layer by one node from the dashboard. The package's configuration for new standbys is applied automatically; the new node takes a base backup from the primary, catches up through streaming replication and registers with Pgpool-II, which starts routing reads to it once its health check passes. The primary keeps serving throughout. The developer watches the standby's replication lag in the platform's Web SSH with pg_stat_replication on the primary until it reads zero.
| Query type | Routed to | Mechanism |
|---|---|---|
| INSERT, UPDATE, DELETE | Primary | Pgpool-II statement parsing |
| SELECT inside a transaction | Primary | Transaction pinning |
| SELECT calling a writing function | Primary | Write function list |
| Plain SELECT (reports, dashboards) | Standby 1 or 2 | Load balancing, primary weight 0 |
| Any query while a standby is down | Remaining nodes | Health check drops the failed backend |
Encryption, backups and the compliance sheet
Ledgerline processes payment data, so the developer installs the PostgreSQL SSL/TLS add-on on both the PostgreSQL and the Pgpool-II layers. It generates certificates, sets ssl = on with the certificate paths, switches password authentication to SCRAM-SHA-256 and provides client certificates for the application. Backups come from the Database Backup/Restore add-on, scheduled nightly, and the developer points it at a standby rather than the primary so a long dump never competes with payments. The whole cluster sits in an environment group in the Frankfurt region, which is the sentence the compliance sheet needed.
What it costs
The old single node ran at a fixed size all day. The cluster is a primary, two standbys and two Pgpool-II nodes, each scaling vertically by the hour in cloudlets (128 MiB of RAM plus 400 MHz of CPU). The standbys are busy for two hours each morning and quiet otherwise; the primary is steadier. Across a month the five nodes average about 70 cloudlets an hour, roughly $170 before discounts at MassiveGRID's published $0.003372 per cloudlet-hour. Ledgerline's previous single node, sized for the morning peak, had cost about two-thirds of that and slowed payments every day. The difference bought two standbys, a highly available entry point and mornings without a support queue.
Frequently Asked Questions
Can I make replication synchronous for zero data loss?
Yes. PostgreSQL supports synchronous replication by naming standbys in synchronous_standby_names on the primary, which you can edit through the platform's config editor. The primary then waits for the named standby to confirm each commit, which adds latency to every write. Most SaaS applications keep asynchronous replication and accept a few milliseconds of potential loss in a crash.
Does Pgpool-II add latency?
A small amount per statement for parsing and forwarding, typically well under a millisecond on the internal network. Connection pooling usually saves more than that for applications that open many short connections. If a specific hot path is latency-critical, connect it directly to the primary.
Is failover automatic if the primary fails?
Pgpool-II detects the failed primary and stops routing to it, and it can be configured through pgpoolAdmin to run a failover command that promotes a standby. Without Pgpool-II the promotion is a manual pg_ctl promote on the standby, or a script the platform docs describe. In either case, test the procedure on a cloned environment first.
Reads on the standbys, writes on the primary, one connection string
The PostgreSQL Primary-Secondary Cluster package deploys streaming replication with an optional Pgpool-II layer on MassiveGRID PaaS. Add standbys from the dashboard, encrypt with the TLS add-on, pay per cloudlet-hour. Free 14-day trial.
PostgreSQL Cluster on MassiveGRID PaaS