PostgreSQL Advent Calendar 2018 の 8日目を務めさせていただきます。
TL;DR
- Amazon RDS for PostgreSQL を使って運用しているサービスの、データ参照用途にレプリケーションDBが欲しかった。
- 単にレプリカを参照するのだと、センシティブ情報の可視性に関して問題があった。
- Foreign Data Wrapper をつかって、一段かませることにした。（参照用インスタンス）
- PostgreSQL11 を 参照用インスタンスに使い始めたら捗っている。
事の発端
もともとは、参照専用のデータベースノードを作りたかったのです。
要件というか、やりたいこととしては以下のような感じ。
- サービス利用状況の分析や、問い合わせ対応などのため、サービスデータベースと同等の内容を安全に参照するためのデータベース（通称：分析用DB）が欲しい
- このデータベースを利用することで、運用DBに影響を与えてはいけない（性能,データ）
- できる限り運用DBとの差分は小さくしたい。（リアルタイム性）
- 運用DBにある、センシティブな情報を保護しつつ、多くの関係者（開発・マーケetc）に使って欲しい
どうやるか
リアルタイム性と、運用からの隔離性という意味では、レプリケーションがいいかな、と考えていました。
PostgreSQL10以降のロジカルレプリケーションがあれば、レプリカ側でVIEWを作ったりセンシティブ情報を加工したり……と、柔軟に対応できそうです。
制限
- 運用DBはRDS for PostgreSQL
- 運用DBはPostgreSQL 9.4（相当）
ロジカルレプリケーションはありません。
RDS for PostgreSQL10のレプリケーションがどうなのかまでは確かめられていませんが、そもそもPostgreSQL10にバージョンアップがすぐにできそうならしています。
互換性検証の時間を取らないと危険だ、というところまでは調査済みで、検証の期間と更新のタイミングをいつにするかは別途上長に相談中というステータスです。
天の声
「自前のPostgreSQL on EC2 を立てて、FDWでリードレプリカを参照することにしたらどう？RDSじゃなくてPostgreSQLを立てることでだいぶ柔軟に運用できるでしょ」
私 ： 「せっかくそういうことにするのなら、FDWもバージョンを経る毎にパワーアップしているし、パラレルクエリなんかも使いどころがあるかもしれない。リリースになったばかりだけれどPostgreSQL11使ってみましょうか。実サービスに影響がないところで運用知見も溜められるし」
ということで、以下の様な構成で参照用のDBを構成することになりました。
外部テーブルでは性能が出ない部分などは、マテビューにするとか、単純なレプリケーションインスタンスではないので集計テーブルを用意したりとかが柔軟にできます。
（多段にしなくてもロジカルレプリケーションならできますというのは…… 
）
PostgreSQL11のすぐ効く恩恵
外部テーブルへのJOIN送出（PostgereSQL9.6〜）
普段、データが欲しいときには参照用インスタンス（PostgreSQL11）に対してクエリを投げます。
先に述べたような構成なので、参照用インスタンスにあるテーブルは全部外部テーブルです。外部サーバーはPostgreSQL9.4ですが、送出する側であるところの参照用インスタンスのバージョンが新しければJOINの送出はしてくれます。
=# EXPLAIN ANALYZE SELECT t1.id, t2.id, t2.status FROM table1 t1 JOIN table2 t2 ON t1.table2_id = t2.id limit 20;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------
Limit (cost=100.00..110.46 rows=20 width=22) (actual time=22.667..22.693 rows=20 loops=1)
-> Foreign Scan (cost=100.00..11818.89 rows=22416 width=22) (actual time=22.665..22.674 rows=20 loops=1)
Relations: (public.table1 t1) INNER JOIN (public.table2 t2)
Planning Time: 0.192 ms
Execution Time: 23.403 ms
(5 rows)
比較のために、PostgreSQL9.4同士の環境でFDWしているところへの外部テーブルのJOINの実行計画も取得してみました。
=> EXPLAIN ANALYZE SELECT * FROM table1 t1 JOIN table2 t2 ON t1.t2id = t2.id limit 10;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=215.52..216.54 rows=10 width=786) (actual time=2333.348..2334.767 rows=10 loops=1)
-> Hash Join (cost=215.52..241.34 rows=255 width=786) (actual time=2333.346..2334.759 rows=10 loops=1)
Hash Cond: (t2.id = t1.t2id)
-> Foreign Scan on table2 t2 (cost=100.00..121.79 rows=393 width=184) (actual time=3.501..4.789 rows=152 loops=1)
-> Hash (cost=113.90..113.90 rows=130 width=602) (actual time=2329.624..2329.624 rows=424067 loops=1)
Buckets: 1024 Batches: 16 (originally 1) Memory Usage: 4097kB
-> Foreign Scan on table1 t1 (cost=100.00..113.90 rows=130 width=602) (actual time=1.239..2107.886 rows=424067 loops=1)
Planning time: 0.697 ms
Execution time: 2387.932 ms
(9 rows)
PostgreSQL9.4のForeignDataWrapperの仕様通り、外部サーバー上でのJOINはされず、全件をローカルサーバーにもってきてからJOINをしていますね。
さいごに
互換性検証ですぐには動かせない運用DBを抱えて、最新版の運用経験を積みながら、最新版の御利益も会社にもたらす、参照用インスタンスの構築と運用に関するご紹介でした。
あしたは noborus さんです。