A global cybersecurity company serves thousands of organizations across North America, Europe, and Asia. As its platform expanded, the company needed a PostgreSQL design that could keep regional user experiences responsive without creating separate data silos in each geography. The goal was a global database architecture with nodes close to users while keeping the underlying data sufficiently current across all three regions.
The team initially evaluated OCI Database with PostgreSQL Replication with Warm Standby. The feature offered a straightforward way to establish a remote read-only copy of the primary database, and it appeared to be a natural starting point for regional resilience and local reads. However, the team’s validation showed that a change written in one region could take up to 60 seconds to appear in the remote regions. For an application in which users expect to see current data wherever they connect, that delay was too long.
Figure 1. OCI Console configuration for PostgreSQL Replication with Warm Standby.
Understanding the replication behavior
Replication with Warm Standby uses PostgreSQL write-ahead logs, or WAL, to move changes from the primary database node to remote nodes, where PostgreSQL replays the changes. When a database generates a steady stream of writes, it also generates WAL continuously. That pattern keeps replication moving with little visible delay.
The company’s user database did not follow that pattern. It had long periods of limited activity, so WAL segments often did not fill quickly enough to ship immediately. The service waited for the configured archive timeout before switching and shipping the segment. In practice, that behavior produced replication latency of roughly 60 seconds during many low-traffic periods.
The team could reduce archive_timeout to force more frequent WAL segment switches. However, each forced switch creates another archived WAL file. The files are typically 16 MB, even when they contain little data. A shorter timeout would reduce latency but increase archive activity and storage use. That tradeoff did not meet the architecture’s requirements.
Users in remote regions could read data that was up to 60 seconds old, which prevented the team from using the replicas for latency-sensitive application reads.
Evaluating alternatives
The cybersecurity team and I then evaluated two alternatives: PostgreSQL-native pglogical and OCI GoldenGate. Both options can replicate changes without waiting for a full archived WAL segment or an archive timeout, which made them better candidates for a low-traffic database that still required near-real-time regional visibility.
We compared the options across the following decision areas.
| Decision area | pglogical | OCI GoldenGate | Guidance |
|---|---|---|---|
| Best fit | PostgreSQL-to-PostgreSQL replication with a lower cost and smaller footprint. | Managed enterprise change data capture, transformation, and heterogeneous replication. | Choose pglogical for a PostgreSQL-only design. Choose OCI GoldenGate for broader enterprise needs. |
| Architecture complexity | Requires extension support, replication slots, schema discipline, and monitoring. | Requires replication pipelines, security configuration, and monitoring. | pglogical has fewer components. OCI GoldenGate provides a managed replication platform. |
| Data transformation | Best when source and target schemas closely align. | Supports mapping, filtering, routing, and transformation. | Choose OCI GoldenGate when data must be reshaped or routed. |
| Database support | PostgreSQL-centric. | Supports heterogeneous sources and targets. | Choose OCI GoldenGate for a multi-vendor architecture. |
| Active-active writes | Requires careful conflict handling and application design. | Also requires conflict avoidance and resolution rules. | Neither option creates a strongly consistent write-anywhere PostgreSQL cluster. |
| Conflict management | Supports conflict modes, but the application must preserve business correctness. | Provides configurable conflict rules, but the application must preserve business correctness. | Partition write ownership by tenant, customer, region, or key range where possible. |
| Read scaling | Supports regional reads when the application accepts eventual consistency. | Supports regional reads when the application accepts eventual consistency. | Define an acceptable stale-read window before you choose a design. |
| Operational tooling | Uses a PostgreSQL-native extension model. | Provides managed monitoring, orchestration, and integration capabilities. | Choose OCI GoldenGate when governance and observability are priorities. |
| Cost | Usually has lower product overhead. | Has a higher service cost but can reduce operational effort. | Balance product cost against operational capabilities. |
Selecting pglogical
A interesting fact is that the name pglogical is very descriptive. pglogical uses logical replication, as opposed to Warm Standby which uses physical replication. These are two high level replication technologies used by many database systems, including PostgreSQL.
Physical replication creates a full copy of the primary database by sending low-level records for an entire database to a standby, which replays them in the same order. Logical replication, including pglogical, turns those records into data changes such as inserts, updates, and deletes, so you can choose which tables and changes to send to another database.
The company chose to test pglogical. Its PostgreSQL-native model, lower operational footprint, and near-real-time replication potential made it the best initial fit.
Figure 2. A global PostgreSQL design replicates table updates to all three tables.
After configuring pglogical, the team tested row updates from a PostgreSQL node across the three continents. The team observed replication latency of approximately one to two seconds. The result gave the company a practical foundation for cross-regional updates and reads that remain close to current, even when the source database is relatively quiet.
Match replication design to your workload
Replication design depends on more than database size or the number of regions. Your write pattern, acceptable replication latency, read-consistency needs, and operational model all affect the right choice. A design that works for a high-throughput database might not meet the required replication latency target for a low-traffic workload.
If you are designing a global PostgreSQL architecture, start by defining the replication latency your users can tolerate, the reads that require current data, and the write-conflict model your application can support. For more information, review the OCI Database with PostgreSQL documentation, the cross-region pglogical tutorial, and the Oracle GoldenGate PostgreSQL documentation.