できれば知らずに済ませたかったAurora DSQL非互換集 – ArkEdge Space Blog

株式会社アークエッジ・スペースの id:koba789 です。

アークエッジ・スペースでは、衛星データを活用するためのアプリケーションを開発しています。人工衛星のカバレッジはグローバルなため、それを活用するアプリケーションもグローバルであるべきでしょう。
グローバルなアプリケーションをコスト効率高く提供するため、私たちはサーバレスなプラットフォームを積極的に活用しています。

それは DBMS についても例外ではなく、昨年の re:Invent で発表された Amazon Aurora DSQL を活用しています。課金モデルが従量課金であるため、固定費を押さえたままスモールスタートできるというのも大きなメリットです。

さて、今回はそんな Aurora DSQL を使うときにハマった PostgreSQL との非互換ポイントを紹介します。Aurora DSQL はプロトコルこそ PostgreSQL 互換ですが、みなさんが直感的に想像する PostgreSQL とはちょいちょい振る舞いが異なります。
まあ、ストレージまわりのアーキテクチャからして別物のようなので、挙動が違うのは当然といえば当然です。MySQL 互換 DBMS を趣味で作っていた身としては同情する部分も多いです。多いですが、困るものは困る！

これから Aurora DSQL を触ってみようという方や、私のような DBMS フリークのみなさんの参考になれば幸いです。

基本的な非互換は Unsupported PostgreSQL features in Aurora DSQL にまとまっていますが、ドキュメントを読んだだけでは分かりづらい “非自明” な点もいくつかあります。以下では、それらを含めて紹介します。

postgres 以外の database は作れない

つまり CREATE DATABASE 禁止です。
これは合理的な仕様だと思います。自分が設計者でもきっとそうするでしょう。
おとなしくデフォルトの postgres database に全部突っ込みましょう。

You can’t create additional databases or rename or drop the postgres database.

Unsupported PostgreSQL features in Aurora DSQL

非 admin ユーザーは public schema に CREATE TABLE できない

前述のとおり、database では分離できないので schema（namespace）で分離することになります。
しかし非 admin ユーザーは public schema に CREATE TABLE できません。また、非 admin ユーザーに対して GRANT CREATE することもできません。
おそらく CVE-2018-1058 への対応だと思われます（推測）。本家 PostgreSQL も v15 から同様の挙動になりました。

Non-admin users can’t create objects in the public schema. Only admin users can create objects in the public schema. The admin user role has permissions to grant read, write, and modify access to these objects to non-admin users, but it cannot grant CREATE permissions to the public schema itself. Non-admin users must use different, user-created schemas for object creation.

Unsupported PostgreSQL features in Aurora DSQL

以下のようなことはできません。また、DML を混ぜることもできません。

begin;
create table ... ;
create table ... ;
commit;

実務上、複数の DDL が atomic かつ isolated じゃないと困ることはまずない（現実的にはそういうふうにアプリを作ったら負けである）ので合理的な仕様だと思います。思いますが、一部のスキーマ管理ツールはそうではないことがあって困るので、お使いのツールの仕様を確認することをおすすめします。

A transaction can include only 1 DDL statement

Unsupported PostgreSQL features in Aurora DSQL

---

普通の CREATE INDEX は使えない

CREATE INDEX ASYNC を使う必要があります。ASYNC キーワードは必須です。
これは DSQL の独自拡張であり、現時点ではほぼすべてのスキーマ管理ツールがこの DDL に未対応です。すげー困る。マジで。

この DDL は名前のとおり非同期にインデックスを構築します。実行完了=インデックス構築完了ではありません。
対象とするテーブルが大きい場合などではインデックスの構築には長い時間がかかることがしばしばありますが、そのためにクライアントを動かし続けるのは結構面倒です。うっかり TCP セッションが切れてしまい、長い時間をかけた create index がロールバックしたら枕を濡らすくらいには悲しくなります。

個人的にはとても良い仕様だと思います。将来的にはすべての DBMS がこの方式になってほしいとすら思っています。ただし現状ではツール側の対応が追いついておらず、普通に困ります。

The CREATE INDEX ASYNC command creates an index on one or more columns of a specified table. This command is an asynchronous DDL operation that doesn’t block other transactions. When you run CREATE INDEX ASYNC, Aurora DSQL immediately returns a job_id.

Asynchronous indexes in Aurora DSQL

アクセスメソッドが非互換

そもそも DSQL は B-Tree 以外のアクセスメソッド（ひらたく言えばインデックスアルゴリズムのこと）に対応していません。で、この B-Tree の実装は内部的に PostgreSQL オリジナルの btree とは別物ということになっているらしく、btree_index という名前のアクセスメソッドになっています。これがまたしてもスキーマ管理ツールを惑わせる！

そのため、CREATE INDEX の USING 句でも USING btree_index と書く必要があります。USING btree ではエラーになります。pg_am テーブルには btree はありますが、使えないらしいです。

PostgreSQL を知っている人が正しく拡張した結果、一般人には意味不明な仕様になっているという気配を感じます。PostgreSQL はこういう拡張に耐えられるメタな仕組みがたくさんあるんですよねぇ。

primary key が clustered index

ヒープテーブルなんてだっせーよなという意見には同意しますし、現代の OLTP ワークロードの大半は primary key による lookup なのだから clustred index にするのが合理的なんであって vacuum で頭を悩ますのはナンセンスでしょう。InnoDB もそう言うとる。

しかし、これを正直にカタログテーブル上で表現するのでまたしてもスキーマ管理ツールが暴れる！

たとえば users テーブルの pkey を pg_indexes で見ると、id 以外のすべてのカラムを INCLUDE しているように見えます。ちゃんと clustered index です。

create unique index users_pkey on users using btree_index (id) include (name, email, email_verified, image, created_at, updated_at)

実態を反映していて誠に結構ではあるのですが、普通の PostgreSQL を前提にしたツールは「こんなインデックス知らん！」といって勝手に pkey を DROP INDEX しようとしたりします。気をつけましょう。

おまけ：drizzle-orm 用 monkey patch

drizzle-orm も例に漏れず、Aurora DSQL との相性が悪い部分があります。
Pull Request も出していますが、十分な対応が入るにはまだ時間がかかりそうです。

そこで、アークエッジ・スペースでは暫定対応として drizzle-orm に monkey patch を当てて運用しています。以下、その内容を紹介します。

import { Connection } from "pg";
import { defineConfig } from "drizzle-kit";




function patchPgForDsql() {
  
  function rewriteSQL(sql: string): string {
    
    if (sql.includes("pg_stat_user_indexes")) {
      const [tableName, tableSchema] = sql.match(/'[^']+'/g)!;

      return `SELECT
          i.relname AS index_name,
          t.relname AS table_name,
          ns.nspname AS schemaname,
          CASE WHEN con.oid IS NOT NULL THEN 1 ELSE 0 END AS generated_by_constraint
        FROM
          pg_class t
        LEFT JOIN
          pg_namespace ns ON ns.oid = t.relnamespace
        LEFT JOIN
          pg_index x ON x.indrelid  = t.oid
        LEFT JOIN
          pg_class i ON i.oid = x.indexrelid
        LEFT JOIN
          pg_constraint con ON con.conindid = i.oid
        WHERE t.relname = ${tableName} AND ns.nspname = ${tableSchema}
        GROUP BY i.relname, t.relname, ns.nspname, generated_by_constraint;`;
    }

    
    return sql.replace(
      /(\bCREATE\s+(?:UNIQUE\s+)?INDEX\b[\s\S]*?)(?=;|$)/gi,
      (stmt) => {
        
        if (!/\bASYNC\b/i.test(stmt)) {
          
          stmt = stmt.replace(
            /\bCREATE\s+(?:UNIQUE\s+)?INDEX\b/i,
            (m) => `${m} ASYNC`,
          );
        }
        return stmt;
      },
    );
  }

  const origQuery = Connection.prototype.query;
  Connection.prototype.query = function query(sql: string) {
    const rewritten = rewriteSQL(sql);
    origQuery.call(this, rewritten);
  };
}

const awsRegionInHostname = /\.dsql\.(.+)\.on\.aws$/.exec(hostname)?.[1];
const isDsql = typeof awsRegionInHostname === "string";
if (isDsql) {
  patchPgForDsql();
}

export default defineConfig({
  dialect: "postgresql",
  schema: "./schema.ts",
  out: "./drizzle",
  schemaFilter: "",
  dbCredentials: {
    host: hostname,
    port,
    database,
    user,
    ssl: { rejectUnauthorized: true },
    password,
  },
});