Hello PostgreSQL!! & Keep in touch MySQL!!
ご挨拶
エンジニアチームの杉山です。あと1ヵ月程で今期も終了し、新しい期に向けて準備を進めています。来期は、更に多くの方にLCLのサービスをより快適に利用して頂く為に、エンジニアからも積極的にアイデアや技術を活用したソリューションを提案し、サービスに実装していければ良いかなと思っています。
現在、バス比較なびでは、全国37,000件以上のバス料金を掲載し 月間400万人超の方々に利用して頂いてますが、サービスの規模が年々大きくなってきている為、 アプリケーションやインフラもビジネス側のニーズとバランスを取りながら、日々整備していく必要がある状態です。優秀でやる気のある若手エンジニアメンバーが課題意識を持って、 日々自発的に取り組んでくれているので、自分の方は引き続き経営陣と調整しながらサービスの成長に努めて行こうと思ってます。が、やるべき事は多々あるので、5割弱はエンジニアとしても貢献出来ればと思い、経験を積んで来たデータベースの知見を活かそうと、DeepにMySQL中心の生活を送っていた自分が、LCLのスタンダードデータベースである、PostgreSQLを少しずつ勉強しています。
これまで、ネットサービスとベンダーの間で半々位で仕事をしてきましたが、ネット関連の会社では9割以上の業務をMySQL中心に仕事をさせて頂いてきました。 自分が初めてMySQLを触ったのは、2005年なのでいまから15年前(MySQL4.1.x)でしたが、それまでDELL等のデータベースチームでOracleデータベースやMS SQLを管理してきていた自分からすると, 「とてもシンプルなデータベースだな～」と当時思った印象があります。縁あって、MySQL5.6～MySQL8.0までは、OracleのMySQLチームにてMySQLのコンサルとして仕事をさせて頂いていたので、 MySQLは自分にとって最も付き合いが長く、深く知見があるデータベースです。そんな自分がPostgreSQLと向き合うようになって、日々戸惑いながら勉強しているので簡単ですがまとめてみました。 基本的には、「MySQLの場合はこうやって対応するけど、PostgreSQLではどうなんだろう？」といった感じで頭の中で常に比較しながら確認し対応しています。
MySQLは、今から25年前の1995年に、スウェーデンのMySQL ABにて開発されたデータベースで、2008年にSun Micro Systemsに買収され、現在は2010年から、Oracleデータベースを販売する、Oracle社の傘下で開発を継続しているオープンソースのデータベースです。現状、MySQL8.0になって更に進化して、CTE, Windows Function, 空間情報、JSON、Hash JOIN等々様々に機能が5.7に続き、改良されている状況です。PostgreSQLと同じくオープンソースですが、様々な用途に活用出来る洗練されたデータベースになってきたなという印象です。グローバルでは、Oracleデータベースに続き、2番目に利用されているデータベースにまで成長しています。
Worklogを見ると、MySQLの開発状況を確認する事が出来ます。 dev.mysql.com
DB Engines db-engines.com
細かな部分は違いますが、Oracle、MS SQL, MySQLもPostgreSQLもACID, MVCCに対応したリレーショナルデータベースなので、 大きな違和感は感じていませんが、やはりPostgreSQLの管理方法はOracleに近いなという印象を受けています。
データベースとスキーマ
データベースとスキーマがデータベースシステム間で異なることは、概念としては理解していましたが、実際に機能による違いを活用して工夫した構成を組むところまでイメージ出来ていません。 その為、基本的な違いを少し整理してみました。
- MySQLではスキーマはデータベースと同義。所有者の概念がありません。同じデータベースに同じ名前のオブジェクトを作成出来ない。
- PostgreSQLでは、データベースとスキーマは異なり、データベースには複数の名前付きスキーマが含まれ、異なるスキーマには同じデータベースでも同一名のオブジェクトを作成可能。
- PostgreSQLで同じデータベースに同じ名前でオブジェクトを作成し処理しても競合が発生しない。（異なるスキーマにオブジェクトを作成した場合） 以下の例では、同じデータベースに同じオブジェクト名でオブジェクトを作成して異なったユーザーで処理しています。また、ユーザー毎にDefaultスキーマを変更しています。
-bash-4.2$ psql -d users -U user_a -c "ALTER ROLE user_a SET search_path TO user_a_schema, pg_catalog"; ユーザ user_a のパスワード: ALTER ROLE -bash-4.2$ -bash-4.2$ psql -d users -U user_b -c "ALTER ROLE user_b SET search_path TO user_b_schema, pg_catalog"; ユーザ user_b のパスワード: ALTER ROLE -bash-4.2$
- ここでは、別々のユーザーで接続し、同じデータベースの同じテーブル名に対してDMLを実行していますが、それぞれ異なったオブジェクトで処理している事が確認出来ます。 ユーザーが異なれば、データベース名、テーブル名でも異なったデータベースオブジェクトに対して処理する事が出来ます。MSQLの場合は少なくともデータベースを変更しないと同じオブジェクト名は作成出来ないので少し戸惑う所です。
| ユーザー | データベース | テーブル | スキーマ |
|---|---|---|---|
| user_a | users | users_table | user_a_schema |
-bash-4.2$ psql -d users -U user_a -c "insert into users_table(id,name) values(nextval('user_id'),'on user_a and schema_a');" ユーザ user_a のパスワード: INSERT 0 1 -bash-4.2$ psql -d users -U user_a -c "insert into users_table(id,name) values(nextval('user_id'),'on user_a and schema_a');" ユーザ user_a のパスワード: INSERT 0 1 -bash-4.2$ psql -d users -U user_a -c "select id,name,to_char(now(), 'YYYY/MM/DD HH24:MI:SS'),current_database() from users_table;" ユーザ user_a のパスワード: id | name | to_char | current_database ----+------------------------+---------------------+------------------ 1 | on user_a and schema_a | 2020/02/15 11:11:58 | users 2 | on user_a and schema_a | 2020/02/15 11:11:58 | users (2 行)
| ユーザー | データベース | テーブル | スキーマ |
|---|---|---|---|
| user_b | users | users_table | user_b_schema |
-bash-4.2$ psql -d users -U user_b -c "insert into users_table(id,name) values(nextval('user_id'),'on user_b and schema_b');" ユーザ user_b のパスワード: INSERT 0 1 -bash-4.2$ psql -d users -U user_b -c "insert into users_table(id,name) values(nextval('user_id'),'on user_b and schema_b');" ユーザ user_b のパスワード: INSERT 0 1 -bash-4.2$ psql -d users -U user_b -c "select id,name,to_char(now(), 'YYYY/MM/DD HH24:MI:SS'),current_database() from users_table;" ユーザ user_b のパスワード: id | name | to_char | current_database ----+------------------------+---------------------+------------------ 1 | on user_b and schema_b | 2020/02/15 11:11:47 | users 2 | on user_b and schema_b | 2020/02/15 11:11:47 | users (2 行) -bash-4.2$
データベースパラメータについて
MySQLもPostgreSQLもバージョンによってパラメータが変わり、知らないでアップグレードや設定してしまうと痛い目に合うので、 不明なパラメータは適宜確認して対応していく必要があるので主要なパラメータを確認しつつ対応していきたいと思います。 MySQLもパラメータが500以上ありますが、殆どをDefaultで利用してきたので変更した事があるのは1/10程度でした。 PostgreSQL12でも300以上のパラメータがありました。これも使い切る事は不可能に近いでしょう。 とりあえず、メモリーやI/O処理等の主要なパラメータだけ抑えておきたいと思います。
pgsql> select name,setting,unit,context,category,short_desc from pg_settings;
mysql> show global variables;
パラメータに関しては、ここら辺の便利なサイトを利用すると短時間で効率良く確認する事が出来ます。
- POSTGRESQLCO.NF https://postgresqlco.nf/ja/doc/param/
- MySQLユーザー会の会長のとみたさんが公開されているパラメータ比較シート https://mysql-params.tmtms.net/mysqld/?vers=5.7.21,8.0.19&diff=true
MySQLとPostgreSQLの基本パラメータ比較
アカウント管理
MySQLでは、データベース内のmysql.userテーブルにてアカウントを管理するので、基本的にはSQLコマンドを実行すればユーザーが作成出来るので外部ファイルの編集に関しては気にしてませんでしたが、 PostgreSQLは、SQLコマンドでロール(ユーザー）を作成すると同時に、pg_hba.confやpostgresql.confなどで認証方法やアクセス出来るホストを設定するので最初に若干戸惑いました。 ただ、どちらもアクセス出来るユーザー、ホスト、IP、サブネット、認証方法等を設定するのでやる事は同じですね。
- MySQLのアカウント
root@localhost [mysql]> select user,host,left(authentication_string,20) from mysql.user; +------------------+-----------+--------------------------------+ | user | host | left(authentication_string,20) | +------------------+-----------+--------------------------------+ | application | % | *500286BAD5E7F51CFD0 | | mysql.infoschema | localhost | *THISISNOTAVALIDPASS | | mysql.session | localhost | *THISISNOTAVALIDPASS | | mysql.sys | localhost | $A$005$THISISACOMBIN | | root | localhost | *A41ECFBE1191DDE4713 | +------------------+-----------+--------------------------------+ 5 rows in set (0.00 sec)
- PostgreSQLのアカウント
postgres=# \du
ロール一覧
-[ RECORD 1 ]+-------------------------------------------------------------------------
ロール名 | admin
属性 | スーパユーザ
所属グループ | {pg_monitor,pg_signal_backend}
-[ RECORD 2 ]+-------------------------------------------------------------------------
ロール名 | application_role
属性 | スーパユーザ
所属グループ | {}
-[ RECORD 3 ]+-------------------------------------------------------------------------
ロール名 | postgres
属性 | スーパユーザ, ロール作成可, DB作成可, レプリケーション可, RLS のバイパス
所属グループ | {}
-[ RECORD 4 ]+-------------------------------------------------------------------------
ロール名 | replication_user
属性 | レプリケーション可
所属グループ | {}
-[ RECORD 5 ]+-------------------------------------------------------------------------
ロール名 | user_a
属性 | スーパユーザ
所属グループ | {}
-[ RECORD 6 ]+-------------------------------------------------------------------------
ロール名 | user_b
属性 | スーパユーザ
所属グループ | {}
postgres=#
-bash-4.2$ cat /var/lib/pgsql/12/data/pg_hba.conf | grep -i "users"
local users all md5
-bash-4.2$
MySQLとPostgreSQLの基本的なアカウント管理比較
バックアップ
どんなシステムを運用していても、ユーザー操作ミスやエンジニアの操作ミスで意図しない障害が発生しデータを損失が発生してしまう可能性があるので、 バックアップ無しではサービス運用する事は出来ないし、安心出来ないかと思います。どんな、データベースを管理するにしても、バックアップ・リカバリー方法の理解と検証は必要ですね。
MySQLとPostgreSQLの基本バックアップ方法比較
レプリケーション
ネットでサービスを提供していると、レプリケーションは必須な機能だと思っていますし、実際にこれまでレプリケーションを運用しない環境で仕事をした事はありませんでした。 MySQLでは、自分が利用し始めたMySQL4.xの時代には既にレプリケーションが実装されていたので、MySQLが広く普及した要因の一つだと思っています。 とても便利な機能ですが、きちんと仕様を理解していないと思わぬところで痛い目に合うのでPostgreSQLでのレプリケーションも経験を積んでいきたいと思ってます。 PostgreSQLにレプリケーションが実装されたのは、9.xの時代からだと認識しています。MySQLよりも遅いですが、細かな設定も出来ると同時になかなか安定していて良いなと言った印象です。 ただ、MySQLはメジャーバージョンが1つ異なっても、レプリケーションをオフィシャルにサポートしていますが、PostgreSQLは若干厳しいという印象です。メジャーバージョン違いをサポートしないと、 段階的にアップグレードが厳しいといった印象を受けているので、タイミングが合えば、最適なアップグレード方法をPostgreSQLのプロフェッショナルな方々に聞いてみようと思っています。
MySQLとPostgreSQLの基本的なレプリケーション比較
以上、簡単ですがMySQLとPostgreSQLの違いの一部を少しですがまとめました。今後も、少しずつまとめていこうと思っています。 ただ、基本的な業務はマネージメントなので、常にビジネスにフォーカスを置きつつ、様々な技術とユーザーのニーズをマッチさせていく事に時間を割いていくのであまり時間取れませんが、合間合間で少しずつ対応して行きたいと考えてます。
今後とも、宜しくお願い致します。
採用情報
LCLではエンジニアのアイデアを生かして、更にサービスを盛り上げて行こうと思ってます!! 引き続き、生産性向上に励む仲間を絶賛募集中です。
