Transaction Write Routing for Application Continuity

Problem Being Solved

Core Problem Statement

Applications on a replicated database have to send every write to the primary, while reads could be spread over standbys. Without a router, each service keeps two connection pools or two connection strings, decides statement by statement where each one goes, and has to learn about a new primary after a role change. A write that lands on a read-only standby fails, and a read sent to a standby right after a write can miss that write.

Root Cause Analysis

Who Suffers Most

1. Application teams: maintain read/write splitting and retry code in every service that talks to the HeliosDB Lite pair.

2. Teams scaling read traffic: want standbys to serve lists and reports without writes landing on the wrong node or stale reads right after a write.

3. Operators: need one place to change where writes go when the primary is replaced, instead of a configuration change in every service.

HeliosDB Lite Solution

Architecture Overview

In this illustrative deployment the routing layer is HeliosProxy, a separate open-source product that speaks the PostgreSQL wire protocol, in front of a HeliosDB Lite primary and its warm standby. The application keeps one connection string. HeliosProxy runs a read it can show to be side-effect-free, outside a transaction, on a healthy standby chosen by read_strategy; every other statement, and everything inside a transaction, runs on the node configured with role = "primary". After a session writes, its reads stay on the primary for read_your_writes_ms, so it reads back what it wrote. While the primary is unavailable, writes wait for it for up to write_timeout_secs, and Transaction Replay keeps open sessions connected.

          Inventory service (one connection string)
                        |
                        v
   +-----------------------------------------------+
   | HeliosProxy                                   |
   |  writes and transactions: role = "primary"    |
   |  side-effect-free reads: a healthy standby    |
   +-----------+---------------------+-------------+
               |                     |
               v                     v
   +-------------------+  WAL   +-------------------+
   | HeliosDB Lite     | -----> | HeliosDB Lite     |
   | primary           |        | warm standby      |
   +-------------------+        +-------------------+

HeliosDB Lite replication is an opt-in build: the heliosdb-lite binary must be built from the licensed source distribution with the ha-tier1 feature, and a binary built without it refuses to start with any --replication-role other than standalone. The standby applies the primary's write-ahead log (WAL) and serves reads.

A HeliosDB Lite standby can also forward writes itself. A standby started with --sync-mode sync or semi-sync forwards INSERT, UPDATE, DELETE, CREATE, DROP, ALTER and TRUNCATE to the primary and returns the primary's result, while SELECT runs on the standby; an async standby rejects writes with SQLSTATE 25006. Forwarding covers statements sent with the simple query protocol, such as those typed in psql. Statements sent with the extended query protocol, which most drivers use for parameterized queries, are not forwarded, REST calls to a standby are not forwarded, and the standby connects to the primary without a password as the heliosdb role, so forwarding works only while the primary accepts trust authentication. Applications therefore send their writes through HeliosProxy.

Key Capabilities

Concrete Examples with Code, Config & Architecture

The examples follow an illustrative inventory service. Host names, ports and credentials are placeholders.

Example 1: Primary and Warm Standby

Build the server with the replication feature and start both nodes; every node needs HELIOSDB_JWT_SECRET. The standby runs in the default async sync mode, so a write sent to it directly is rejected with 25006 rather than forwarded, and the nodes use SCRAM authentication: all application writes go through HeliosProxy.

# In the unpacked source distribution: replication is the ha-tier1 build feature
cargo build --release --features ha-tier1
export HELIOSDB_BIN=./target/release/heliosdb-lite
export HELIOSDB_JWT_SECRET="$(cat /etc/heliosdb/jwt.secret)"

# Primary (db-primary.example.com)
$HELIOSDB_BIN start \
  --data-dir /var/lib/heliosdb \
  --listen 0.0.0.0 --port 5432 \
  --auth scram-sha-256 --password "$HELIOSDB_ADMIN_PASSWORD" \
  --replication-role primary \
  --replication-port 5433 \
  --standby-hosts db-standby.example.com:5433 \
  --sync-mode semi-sync

# Warm standby (db-standby.example.com): applies the primary's WAL and serves reads
$HELIOSDB_BIN start \
  --data-dir /var/lib/heliosdb \
  --listen 0.0.0.0 --port 5432 \
  --auth scram-sha-256 --password "$HELIOSDB_ADMIN_PASSWORD" \
  --replication-role standby \
  --primary-host db-primary.example.com:5433

Example 2: HeliosProxy Read/Write Routing

read_write_split and standby_reads decide where a read runs, and read_your_writes_ms keeps a session's reads on the primary just after its own write. Another session's recent write can still be missing on an async standby. Sessions that use temporary tables, cursors or SQL PREPARE keep their reads on the primary. Standbys that ask for a password need [auth] mode = "scram" with an auth_file; otherwise those reads fall back to the primary. Keep session pooling (no [pool_mode] section) in front of HeliosDB Lite, and the default static topology, since Lite does not implement pg_is_in_recovery().

listen_address = "0.0.0.0:6432"
admin_address  = "127.0.0.1:9090"
tr_enabled = true
tr_mode = "session"
write_timeout_secs = 30

# Standby connections need a credential when the standby asks for a password
[auth]
mode = "scram"
auth_file = "/etc/heliosproxy/userlist.txt"

[pool]
min_connections = 2
max_connections = 50
idle_timeout_secs = 300
max_lifetime_secs = 1800
acquire_timeout_secs = 30
test_on_acquire = true

[load_balancer]
read_strategy = "least_connections"
read_write_split = true
standby_reads = "async"        # async | sync | primary
read_your_writes_ms = 1000
latency_threshold_ms = 100

# SQL-comment hints such as /*helios:route=primary*/ (routing-hints feature)
[routing_hints]
enabled = true

[health]
check_interval_secs = 5
check_timeout_secs = 3
failure_threshold = 3
success_threshold = 2
check_query = "SELECT 1"

[[nodes]]
name = "lite-primary"
host = "db-primary.example.com"
port = 5432
role = "primary"
weight = 100
enabled = true

[[nodes]]
name = "lite-standby"
host = "db-standby.example.com"
port = 5432
role = "standby"
weight = 100
enabled = true

Routing hints need a HeliosProxy build with the routing-hints feature; the HeliosProxy container image includes every optional feature, and cargo install heliosdb-proxy --features routing-hints adds it to a crates.io build.

Example 3: Application Code (Node.js)

The inventory service uses one pg pool pointed at HeliosProxy. Its parameterized queries use the extended query protocol, which standby forwarding does not cover and HeliosProxy routes like any other statement.

const { Pool } = require('pg');

// One pool for reads and writes: HeliosProxy routes each statement
const pool = new Pool({
  host: 'proxy.example.com',
  port: 6432,
  database: 'heliosdb',
  user: 'inventory_app',
  password: process.env.INVENTORY_APP_PASSWORD,
});

// A write: runs on the primary
async function reserveStock(sku, qty) {
  const res = await pool.query(
    'UPDATE stock SET on_hand = on_hand - $1 WHERE sku = $2 AND on_hand >= $1',
    [qty, sku],
  );
  return res.rowCount === 1;
}

// A transaction: every statement in it runs on the primary
async function moveStock(fromSku, toSku, qty) {
  const client = await pool.connect();
  try {
    await client.query('BEGIN');
    await client.query('UPDATE stock SET on_hand = on_hand - $1 WHERE sku = $2', [qty, fromSku]);
    await client.query('UPDATE stock SET on_hand = on_hand + $1 WHERE sku = $2', [qty, toSku]);
    await client.query('COMMIT');
  } catch (err) {
    await client.query('ROLLBACK');
    throw err;
  } finally {
    client.release();
  }
}

// A side-effect-free read outside a transaction: served by a healthy standby
async function lowStock(threshold) {
  const res = await pool.query(
    'SELECT sku, on_hand FROM stock WHERE on_hand < $1 ORDER BY on_hand',
    [threshold],
  );
  return res.rows;
}

Example 4: Check the Routing

pg_replication_status reports the role of the node that answers, which shows where a read ran. The admin API reports the primary and the healthy nodes.

PROXY="host=proxy.example.com port=6432 dbname=heliosdb user=inventory_app"

# Which node answers a read through HeliosProxy (each call opens a new session)
for i in 1 2 3 4; do
  psql "$PROXY" -X -t -A -c "SELECT role FROM pg_replication_status"
done

# The same read pinned to the primary with a routing hint
psql "$PROXY" -X -t -A -c "SELECT /*helios:route=primary*/ role FROM pg_replication_status"

# A write through HeliosProxy reaches the primary
psql "$PROXY" -X -c "UPDATE stock SET on_hand = on_hand + 5 WHERE sku = 'A-100'"

# The primary and the healthy nodes as HeliosProxy sees them
curl -s http://127.0.0.1:9090/topology
curl -s http://127.0.0.1:9090/nodes

Technical Advantages

Design Properties

• One endpoint: each service keeps one connection string and one pool; HeliosProxy decides per statement, whatever the driver or wire-protocol mode.
• Writes reach the primary: every statement that is not a side-effect-free read outside a transaction runs on the node configured as primary.
• Own writes stay visible: a session's reads stay on the primary for read_your_writes_ms after its write.
• Forwarding for operators: psql sessions and scripts connected straight to a sync or semi-sync standby can still write, on a private network where the primary accepts trust authentication.
• Planned items: automatic failover and controlled switchover (SWITCHOVER TO) are planned for HeliosDB Lite; promoting the standby is a manual or external step today.

Adoption Strategy

Phase 1: Routing Check

Objective: Confirm that HeliosProxy sends writes to the HeliosDB Lite primary and reads to the standby.

Actions:

1. Build HeliosDB Lite with ha-tier1 and start a primary and a warm standby (Example 1)
2. Start HeliosProxy with the routing configuration (Example 2)
3. Run the routing checks (Example 4)

Success Criteria: Reads through HeliosProxy are answered by the standby, writes and transactions by the primary.

Phase 2: Pilot Service

Objective: Replace one service's own read/write split with a single pool on HeliosProxy.

Actions:

1. Point the service's single pool at HeliosProxy (Example 3)
2. Remove the service's own routing code
3. Choose standby_reads and read_your_writes_ms for the service's consistency needs
4. Watch /nodes and /pools on the admin API

Success Criteria: The service runs with one connection string, and its reads right after its own writes return the written rows.

Phase 3: Production Rollout

Objective: Move the remaining services and document the role-change procedure.

Actions:

1. Move the remaining services to HeliosProxy
2. Document the promotion: restart the standby with --replication-role primary, change the [[nodes]] roles and reload HeliosProxy
3. Alert on lag_ms in pg_replication_standbys on the primary

Success Criteria: No service keeps its own read/write routing, and a role change is one documented procedure.

Conclusion

HeliosProxy gives applications on a HeliosDB Lite primary and warm standby one endpoint: writes and transactions run on the primary, side-effect-free reads outside a transaction run on healthy standbys, and a session reads back its own writes from the primary. HeliosDB Lite contributes WAL streaming replication from a binary built with the ha-tier1 feature, plus write forwarding from sync and semi-sync standbys for simple-protocol statements on a trust-authenticated network. Automatic failover for HeliosDB Lite is planned; promoting the standby is a manual or external step today.