シャーディングしている環境でpostgres-fdwを使ってテーブルをJOINする

Posted at 2024-03-10

導入

既存のアプリケーションでDBの負荷がボトルネックになってきたとき、負荷分散の手段の一つとしてシャーディングがあります。シャーディングを実施しようと思った場合に問題となるのが、別々のデータベースに分かれたテーブルのJOINができなくなることです。
単純なテーブル構造だったらアプリケーションに記述されたSQLを分割し、それぞれのデータベースから必要なデータを取ってきて、アプリケーションで結合するようにコードを修正していきます。これは大変な手間です。また、複雑なクエリではとても性能を維持することができないかもしれません。
しかし、postgres_fdwという拡張モジュールを利用すればアプリケーションのSQLに手を加えることなく、データベースが別サーバーに存在するテーブルとのJOINが実現できます。
この記事ではdockerを使ってシャーディングされたDBの環境を構築し、postgres_fdwを導入してJOINできるまでを試します。

テーブルの構成とやりたいこと

この記事で利用する環境のテーブル構成は以下になります。

users
- id: ユーザーのID
- name: ユーザー名
post_types
- id: 投稿種別のID
- name: 投稿種別の名前
- active: アクティブかどうか
posts
- id: 投稿のID
- user_id: 投稿したユーザーのID
- post_type_id: 投稿種別のID
- contents: 投稿した内容

実際のコードはありませんが、想定するアプリケーションの構成を説明します。

posts

ユーザーがログインするとそのユーザーの投稿が見られるものとします。そのため、基本的にWHERE条件にuser_idを指定します。他のユーザーの投稿をみることはありません。そのため、シャーディングするにあたってuser_idをキーにします。ユーザーのIDが奇数だったらshard1に、偶数だったらshard2に配置します。

post_types

投稿するにあたっては、投稿種別を指定します。投稿種別にはすでにアクティブではないものも含まれます。アクティブではない投稿種別の投稿は表示しないものとします。
投稿種別はユーザーごとに設定できるものではなくアプリケーションで同じものを使います。そのため、シャーディングしないデータベースに格納します。

users

ログイン処理でユーザーを特定する必要があるためユーザーもシャーディングしないデータベースに格納します。

シャーディング前の環境を構築する

まずはシャーディング前の環境を構築します。

postgresを1台立ち上げます。

cat << EOF > docker-compose.yml
version: "3.9"
services:
  postgres-non-shard:
    image: postgres
    volumes:
      - .:/tmp
    environment:
      - POSTGRES_PASSWORD=postgres
EOF
docker compose up -d

testデータベースを作成し、データを投入します。

cat << EOF > setup.sql
  DROP TABLE IF EXISTS posts;
  DROP TABLE IF EXISTS post_types;
  DROP TABLE IF EXISTS users;
  CREATE TABLE users(id serial PRIMARY KEY, name text not null);
  CREATE TABLE post_types(id serial PRIMARY KEY, name text not null, active boolean not null);
  CREATE TABLE posts(id serial PRIMARY KEY, user_id integer REFERENCES users(id) not null, post_type_id integer REFERENCES post_types(id), contents text);
  INSERT INTO users values
    (1,'田中'),
    (2,'鈴木');
  INSERT INTO post_types values
    (1,'通常', true),
    (2,'キャンペーン', false);
  INSERT INTO posts values
    (1,1,1,'田中の通常投稿です'),
    (2,1,2,'田中のキャンペーン投稿です'),
    (3,2,1,'鈴木の通常投稿です'),
    (4,2,2,'鈴木のキャンペーン投稿です');
EOF
docker compose exec postgres-non-shard createdb -U postgres test
docker compose exec postgres-non-shard psql -U postgres -f /tmp/setup.sql test

正常にデータが登録されたかを確認しましょう。

$ docker compose exec postgres-non-shard psql test -U postgres -c "SELECT * FROM posts p LEFT JOIN post_types pt ON pt.id = p.post_type_id LEFT JOIN users u ON u.id = p.user_id;"

 id | user_id | post_type_id |          contents          | id |     name     | active | id | name
----+---------+--------------+----------------------------+----+--------------+--------+----+------
  1 |       1 |            1 | 田中の通常投稿です            |  1 | 通常          | t      |  1 | 田中
  2 |       1 |            2 | 田中のキャンペーン投稿です       |  2 | キャンペーン    | f      |  1 | 田中
  3 |       2 |            1 | 鈴木の通常投稿です            |  1 | 通常          | t      |  2 | 鈴木
  4 |       2 |            2 | 鈴木のキャンペーン投稿です       |  2 | キャンペーン    | f      |  2 | 鈴木
(4 rows)

シャーディングされた環境への移行

シャーディングされた環境へ移行していきます。
以下3つのステップで移行を実施します。

データのバックアップ
DBの立ち上げとレストア
不要なデータの削除

データのバックアップ

稼働中のデータベースから物理バックアップを取得します。本来は停止してから行いますがdocker環境ではpostgresを停止するとコンテナも停止してしまうので立ち上げたままにします。

mkdir data-for-shard1
mkdir data-for-shard2
docker compose cp postgres-non-shard:/var/lib/postgresql/data/ ./data-for-shard1/
cp -r ./data-for-shard1/data ./data-for-shard2/

DBの立ち上げとレストア

取得したバックアップをdockerのvolumeを使って新しいサーバにマウントし、立ち上げます

# 既存の環境は一旦停止する
docker compose stop
cat << EOF > docker-compose.yml
version: "3.9"
services:
  postgres-non-shard:
    image: postgres
    volumes:
      - .:/tmp
    environment:
      - POSTGRES_PASSWORD=postgres
  postgres-shard1:
    image: postgres
    environment:
      - POSTGRES_PASSWORD=postgres
    volumes:
      - ./data-for-shard1/data:/var/lib/postgresql/data
      - .:/tmp
  postgres-shard2:
    image: postgres
    environment:
      - POSTGRES_PASSWORD=postgres
    volumes:
      - ./data-for-shard2/data:/var/lib/postgresql/data
      - .:/tmp
EOF
docker compose up -d

正常にレストアされたかを確認します。

$ docker compose exec postgres-shard1 psql test -U postgres -c "SELECT * FROM posts p LEFT JOIN post_types pt ON pt.id = p.post_type_id LEFT JOIN users u ON u.id = p.user_id WHERE pt.active = true;"
 id | user_id | post_type_id |      contents      | id | name | active | id | name
----+---------+--------------+--------------------+----+------+--------+----+------
  1 |       1 |            1 | 田中の通常投稿です    |  1 | 通常  | t      |  1 | 田中
(1 row)

$ docker compose exec postgres-shard2 psql test -U postgres -c "SELECT * FROM posts p LEFT JOIN post_types pt ON pt.id = p.post_type_id LEFT JOIN users u ON u.id = p.user_id WHERE pt.active = true;"
 id | user_id | post_type_id |      contents      | id | name | active | id | name
----+---------+--------------+--------------------+----+------+--------+----+------
  3 |       2 |            1 | 鈴木の通常投稿です    |  1 | 通常  | t      |  2 | 鈴木
(1 row)

不要なデータの削除

現状ではデータがコピーされただけなので、不要なテーブルやレコードを削除します。これによりDBがスリム化し、クエリの負荷が軽減されることが期待されます。

# non-shardにはusersとpost_typesのみ残す
docker compose exec postgres-non-shard psql -U postgres test -c "DROP TABLE posts;"

# shard1には奇数のidのユーザーのpostsのみ残す
docker compose exec postgres-shard1 psql -U postgres test -c "ALTER TABLE posts DROP CONSTRAINT posts_user_id_fkey"
docker compose exec postgres-shard1 psql -U postgres test -c "ALTER TABLE posts DROP CONSTRAINT posts_post_type_id_fkey"
docker compose exec postgres-shard1 psql -U postgres test -c "DELETE FROM posts WHERE user_id % 2 <> 1"
docker compose exec postgres-shard1 psql -U postgres test -c "DROP TABLE users;"
docker compose exec postgres-shard1 psql -U postgres test -c "DROP TABLE post_types;"

# shard2には偶数のidのユーザーのpostsのみ残す
docker compose exec postgres-shard2 psql -U postgres test -c "ALTER TABLE posts DROP CONSTRAINT posts_user_id_fkey"
docker compose exec postgres-shard2 psql -U postgres test -c "ALTER TABLE posts DROP CONSTRAINT posts_post_type_id_fkey"
docker compose exec postgres-shard2 psql -U postgres test -c "DELETE FROM posts WHERE user_id % 2 <> 0"
docker compose exec postgres-shard2 psql -U postgres test -c "DROP TABLE users"
docker compose exec postgres-shard2 psql -U postgres test -c "DROP TABLE post_types;"

これでシャーディングの環境が整いました。
テーブルが別のデータベースに配置されているためテーブルのJOINができず、SQLが失敗することを確認してみましょう。

$ docker compose exec postgres-non-shard psql test -U postgres -c "SELECT * FROM posts p LEFT JOIN post_types pt ON pt.id = p.post_type_id WHERE pt.active = true and p.user_id = 1;"

ERROR:  relation "posts" does not exist
LINE 1: SELECT * FROM posts p LEFT JOIN post_types pt ON pt.id = p.p...
                      ^
$ docker compose exec postgres-shard1 psql test -U postgres -c "SELECT * FROM posts p LEFT JOIN post_types pt ON pt.id = p.post_type_id WHERE pt.active = true and p.user_id = 1;"

ERROR:  relation "post_types" does not exist
LINE 1: SELECT * FROM posts p LEFT JOIN post_types pt ON pt.id = p.p...
                                        ^

postgres-fdwの導入

postgres-fdwを導入していきます。詳細は公式ドキュメントを参考にしてください。
導入するデータベースは外部サーバに接続する必要があるデータベースになります。この記事の場合、postgres-shard1とpostgres-shard2に導入します。

大まかな流れは以下になります。

拡張のインストール
サーバの定義（CREATE SERVER）。接続したいサーバのhost, port, dbnameなどを指定します
ユーザーマッピングの定義(CREATE USER MAPPING)。サーバに接続するためのユーザーの定義をします。user, passwordを指定します
外部テーブルの定義(CREATE FOREIGN TABLE)。このデータベース内に外部テーブルと呼ばれるオブジェクトを作成し、どのサーバから実際のデータを取得するかを定義します。この記事ではシャードしないデータベースに存在するusersとpost_typesテーブルの定義をしました

cat << EOF > postgres-fdw.sql
  CREATE EXTENSION postgres_fdw;
  CREATE SERVER non_tenant FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'postgres-non-shard', dbname 'test', port '5432');
  CREATE USER MAPPING FOR postgres SERVER non_tenant OPTIONS (user 'postgres', password 'postgres');
  CREATE FOREIGN TABLE users(id integer not null, name text not null) SERVER non_tenant;
  CREATE FOREIGN TABLE post_types(id integer not null, name text not null, active boolean not null) SERVER non_tenant;
EOF
docker compose exec postgres-shard1 psql test -U postgres -f /tmp/postgres-fdw.sql
docker compose exec postgres-shard2 psql test -U postgres -f /tmp/postgres-fdw.sql

JOINを試す

postgres-fdwを導入したことで各シャードにおいてもテーブルがJOINできるようになりました。試してみましょう。

$ docker compose exec postgres-shard1 psql test -U postgres -c "SELECT * FROM posts p LEFT JOIN post_types pt ON pt.id = p.post_type_id WHERE pt.active = true and p.user_id = 1;"

 id | user_id | post_type_id |      contents      | id | name | active
----+---------+--------------+--------------------+----+------+--------
  1 |       1 |            1 | 田中の通常投稿です    |  1 | 通常  | t
(1 row)

$ docker compose exec postgres-shard2 psql test -U postgres -c "SELECT * FROM posts p LEFT JOIN post_types pt ON pt.id = p.post_type_id WHERE pt.active = true and p.user_id = 2;"

 id | user_id | post_type_id |      contents      | id | name | active
----+---------+--------------+--------------------+----+------+--------
  3 |       2 |            1 | 鈴木の通常投稿です    |  1 | 通常  | t
(1 row)

以上になります。

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up