[pgpool-hackers: 2430] Amazon Aurora

[Tatsuo Ishii]

Hi,

I had chance to evaluate "Amazon Aurora with PostgreSQL Compatibility"
preview version. Basically the result was pretty positive: I found
that Aurora is roughly 3 times faster than RDS for PostgreSQL using
similar VM spec while running standard pgbench benchmarks (TCP-B like
transactions).

So I start to think about how Pgpool-II deals with it.

I think most of functionaries in streaming replication mode can also
work with Aurora.

The immediate problem is, there's no "primary" or "standby" servers in
Aurora. Instead there is single "writer" and multiple "read
replicas". The former acts like primary node in streaming
replication. The latter acts like standby servers. We cannot use
pg_is_in_recovery() function to find out the primary node. Writer and
read replicas are assigned fixed host names. If fail over occurs, AWS
magically swap the host name and IP address binding for the writer and
read replicas.

My idea is, add a new flag to backend_flagN something like
"AURORA_WRITER" and "AURORA_READ_REPLICA". If Pgpool-II finds the
flag, it will not issue pg_is_in_recovery() in find_primary_node(). It
just returns the node id which has AURORA_WRITER flags is on.

Another problem is, we cannot call pg_current_xlog_location() or
pg_last_xlog_replay_location() against Aurora. Thus, replication delay
function will not work. There's no workaround for now. However,
replication delay is pretty small in Aurora since the storage for DB
(including for WAL) are on a same shared storage. I observed that
replication delay using cloudWatch is less than 20 ms. So probably
users don't care about replication delay anyway.

What do you think?

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp