MySQL / MariaDB vs PostgreSQLメモ

本記事について

アプリケーションを開発するためにどの DB を使った方がよいか考える機会は何度かあると思います。
実際に使ってみて初めて問題や課題が見つけられることが多いと思いますが、次回の検討するタイミングで思い出せるようメモとして残すためのものです。

メリット・デメリットが見付かった場合は追記していく予定です。
但し対象は恐らく MySQL/MariaDB と PostgreSQL 以外に対象は増やさないと思います。

レコード数は最大でも数千程度を扱った経験に基づき、数十万レコードといった大規模の性能比較は出来ておりませんし、実施する必要に迫られていないため行う予定はありません。
主に利便性や機能面での比較となります。

尚、あくまで個人の経験に基づく内容なので解決策があるのかもしれませんのでご了承ください。

MySQL / MariaDB vs PostgreSQL

PostgreSQL のメリット

local 接続の Ident 認証(Peer認証)が利用できる

同一ホスト上に DB とその DB を使うアプリケーションがあった場合に、DB 接続時のアカウントを OS のアカウントと一緒に管理することが出来ます。(参考)

インデックスが作成できる配列がデータ型として使える (テーブルを使わずにタグのような概念が実装できる)

※ MySQL 5.7 からは JSON 型のデータが扱えるようになったので同様のことは可能

タグのようなあるデータに対して付加情報をつけたいケースがあります。
例えば、wiki のようにページを示すデータがあった時に、ページごとに MySQL, MariaDB, PostgreSQL のようなタグをつけられるようにしたいといったケースです。

その場合、タグをテーブルとして実装する必要がありますが、PostgreSQL では配列が使えるためページを指すテーブルのカラムとしてタグをつけることが出来ます。参考/PostgreSQL9.4.5

pagesテーブルにtagsカラムを定義する例

CREATE TABLE pages (
    title  text,
    tags   text[]
);

PostgreSQL のデメリット

• (TBD)

MySQL / MariaDB のメリット

• (TBD)

MySQL / MariaDB のデメリット

INSERT ... ON DUPLICATE KEY UPDATE により複数レコードを更新する際に、挿入したレコードの ID が調べられない

複数のテーブルが外部キーを使って関連している場合に、挿入した ID は知りたいものです。
しかし挿入したレコードの ID が調べられないため、改めて SELECT クエリを使う等の手段を使って調べる必要があります。

AUTO_INCREMENT 属性が設定されている場合は、LAST_INSERT_ID() により挿入した ID を知ることが出来るので、AUTO_INCREMENT 属性を使っており、レコードを 1 つずつ挿入・更新する場合は特に問題とならないでしょう。

また、Bulk insert により複数のレコードを挿入する場合は、AUTO_INCREMENT 属性が指定されていれば、LAST_INSERT_ID() は挿入した最初の行の ID を返します。(参考/MySQL 5.6)

挿入処理が全て成功していれば、挿入したレコードの ID は LAST_INSERT_ID() から挿入したレコード数分加えた ID の間になっていると言えます。
但し、これは InnoDB を使っている場合には複数のレコードが挿入されるまでロックする場合のみ
挿入順序は Bulk insert で指定した順序が担保されるため使える手段です。(参考)

そのため Bulk insert により複数レコードを挿入する場合も AUTO_INCREMENT 属性が指定されていれば特に問題とならないでしょう。

問題となるのは Bulk update (INSERT ... ON DUPLICATE KEY UPDATE) により複数レコードを更新しようとして、一部レコードが挿入される場合です。
全て挿入されるとは限らないため、どの ID が挿入されたレコードであるか LAST_INSERT_ID() から特定できません。

まとめると次のとおりです。

• AUTO_INCREMENT 属性が設定されている場合、
  • レコードを 1 つずつ挿入・更新する場合は LAST_INSERT_ID() で ID を取得できる
  • Bulk insert により複数レコードを挿入する場合は LAST_INSERT_ID() ～ LAST_INSERT_ID()+挿入レコード数 から ID を推測できる
• AUTO_INCREMENT 属性が設定されている場合であっても、
  • Bulk update (INSERT ... ON DUPLICATE KEY UPDATE) により複数レコードを更新する場合は挿入したレコードの ID が取得できない

尚、挿入したレコードの ID を一時的な table に保存することで調べる方法があるようです。
https://jumble-note.blogspot.com/2013/06/mysql-insertid.html
どうしても MySQL / MariaDB を使う必要があり、ID を調べる必要がある場合は参考にしてみて下さい。