A Middleware to Synchronize Multiple
Instances in a PostgreSQL Cluster
SRA OSS, Inc. JapanTa k e s h i M I S H I M A

https://2021.postgresconf.cn

About me• I had been researching database systems at NTT and Keio
University in Japan.
• My representative papers were accepted by VLDB2009 andSIGMOD 2015.
• I received Ph.D. degree from The University of Tokyo.
• Now, I work for SRA OSS, Inc. Japan.

1. What is Pangea?

CONTENT

2. The disadvantage of streamingreplication
3. The algorithms of Pangea

4. Summary

PART 1
What is Pangea?

What is Pangea?
• Proposed Pangea and accepted by VLDB2009.
A synchronization middleware for multiple Postgres instances without
modifying the source codes of Postgres.
• Pangea is a just prototype but the algorithms have been implemented into
the latest version of Pgpool-II with the snapshot isolation mode.
• In today’s my presentation, I will explain Pangea, and enjoy the fruit by using
Pgpool-II if you are interested in Pangea !

The advantages of Pangea• The advantages(1) not only it increases availability but also,(2) any instance returns the latest consistent data.
cf. Streaming replication provides (1) but does not (2).
Next, I will explain the detail of it.

PART 2The disadvantage of streaming replication

The disadvantage of streaming replication• Standby instance cannot return the latest data.
ex.
T0:
BEGIN;UPDATE test_table;
SELECT * FROM test_table;
COMMIT;

Middleware

WALs
PrimaryStandbyThe disadvantage of streaming replication• Standby instance cannot return the latest data.
ex.
T0:
BEGIN;UPDATE test_table;
SELECT * FROM test_table;
COMMIT;

Middleware

UPDATE

PrimaryStandby

The disadvantage of streaming replication• Standby instance cannot return the latest data.
ex.
T0:
BEGIN;UPDATE test_table;
SELECT * FROM test_table;
COMMIT;

Middleware

SELECT

PrimaryStandby

The disadvantage of streaming replication• Standby instance cannot return the latest data.
ex.
T0:
BEGIN;UPDATE test_table;
SELECT * FROM test_table;
COMMIT;

Middleware

Old data!

PrimaryStandby

The disadvantage of streaming replication• Even if we have a lot of standby instances, all queries must be sent to only
the primary to ensure consistency.
Middleware
UPDATE

Primary

SELECT

Standby Standby Standby Standby

The goal of Pangea• Even if Pangea sends a SELECT query to any instance, it must return thelatest consistent data.
PangeaSELECT
SELECT

SELECT

SELECT

SELECT

SELECT

The goal of Pangea• Even if Pangea sends a SELECT query to any instance, it must return thelatest consistent data.
PangeaSELECT
SELECT

Load-balancing without
violating consistency!

SELECT

SELECT

SELECT

SELECTPART 3Pangea’s algorithms to address the disadvantage

Assumption for Pangea• Snapshot isolation (REPEATABLE READ isolation level)• A query does not have any non-deterministic functions such as RANDOMand NOW.
• We call one replica the leader and the others the followers.
• We call the first DML query a snapshot query.

LeaderFollower

Follower

The replication method of Pangea• Instead of using streaming replication, Pangea sends an UPDATE query toall instances.
PangeaUPDATEUPDATEUPDATE

LeaderFollower

FollowerRecap: snapshot isolation
Recap1A snapshot is taken just before a snapshot query (the first DML query) isexecuted.

SnapshotOriginal databaseRecap: snapshot isolation
Recap2
All queries are executed on the snapshot.
query1
query0 query2

SnapshotOriginal databaseRecap: snapshot isolation
Recap2Update queries are also executed on the snapshot.
UPDATE DELETE

SnapshotOriginal databaseRecap: snapshot isolation
Recap3The updates are applied to the original database when the transaction commits.

SnapshotOriginal database

Pangea’s algorithm1• Pangea propagates snapshot and commit queries exclusively.
• That is, Pangea propagates commit queries only when all instances do not
execute snapshot queries.
• Conversely, Pangea propagates snapshot queries only when all instances donot execute commit queries.

Pangea’s algorithm1• When Pangea sends commit queries to all instances, it stops sending
snapshot ones.

S0S1 Pangea
C1
C0C1
C0LeaderFollower

C0C1

FollowerPangea’s algorithm1• When Pangea sends snapshot queries to all instances, it stops sending
commit ones.

C0C1 Pangea
S1
S0

S1
S0LeaderFollower

S0S1

FollowerAlgorithm1This algorithm introduces that all instances must provide the same snapshot data.
ver.0

ver.0

ver.0

ver.0

ver.0

ver.0

S0

S1

S2

S0

S1

S2

ver.0
Instance0

ver.0
Instance1

Algorithm1
This algorithm introduces that all instances must provide the same snapshot data.

C0

C0

ver.1
Instance0

ver.1
Instance1

Algorithm1
This algorithm introduces that all instances must provide the same snapshot data.
ver.1

ver.1

ver.1

ver.1

ver.1

ver.1

S3

S4

S5

S3

S4

S5

ver.1
Instance0

ver.1
Instance1

Algorithm1
This algorithm introduces that all instances must provide the same snapshot data.

C1C1ver.2
Instance0ver.2
Instance1

Algorithm1
This algorithm introduces that all instances must provide the same snapshot data.
ver.2

ver.2

ver.2

ver.2

ver.2ver.2

S6S7S8S6S7S8ver.2
Instance0ver.2
Instance1

Algorithm1
The exclusive execution of snapshot and commit queries makes all instances
create the same snapshot!

Pangea’s algorithm2• First, Pangea propagates all UPDATE queries to only the leader.

Pangea
U2 U1
U0LeaderFollower

FollowerU0, U1
U2

Ack of U0

U1

U0U2

Ack of U2

Pangea’s algorithm2• First, Pangea propagates all UPDATE queries only to the leader.

PangeaAck of U2
Ack of U0

LeaderFollower

FollowerPangea’s algorithm2• Second, if Pangea receives the answer, it sends the query to all thefollowers.

PangeaLeaderU2
U0U0U2

Follower

FollowerPangea’s algorithm2• This protocol introduces that write/write conflicting queries are serialized by
the leader,• and non-conflicting queries are executed on all instances simultaneously.

In a nutshell• Algorithm1 makes all instances create the same snapshot, and• Algorithm2 makes the order of only conflicting queries serialized by theleader.

• Pangea’s algorithms keep consistency between the leader and followers loosely.
• Any instance can return the latest consistent data.
• Surprisingly, Pangea’s algorithms need not modify the source codes of Postgres!

PART 4
SummarySummary• We proposed the algorithms to address the disadvantage of streaming
replication.
• Formally, we gave the full proof of the correctness in our VLDB2009 paper,please see it for detail.
• Our algorithms have been implemented into the latest version of Pgpool-II,
and therefore, enjoy the fruit of Pangea by using Pgpool-II !

THANK YOU
mishima@sraoss.co.jp