はじめに

OSS-DB Exam Silver 合格しました🎉

ということで合格記事と、Postgres について知ったことのいくつかを記事にしたいと思います。試験の参考や、Postgres について興味をもっていただくきっかけになれば幸いです。

受験者のスペック

フロントエンドエンジニア（そっちばっかりやっているので）
けれどサーバーサイドもやらなくはない
SQL は人並みには書ける
エンジニア歴は5年
触ったことがある DB は Oracle、Postgres、MySQL、SQLite

という感じです。経験年数5年くらいなら、Oracle Master の資格を持っている人も多いかと思います。

結果

84点 / 100点 (合格基準64点)

セクション	正解率（％）
一般知識	100
運用管理	73
開発/SQL	93

運用管理はもうちょっと？という感じですが、アプリケーションエンジニアとしては SQL は9割取れているからいいかな...という具合の点数ですね。

勉強方法

Ping-t をやり込む

合格点を超えるなら、まずは Ping-t をやり込むと良いでしょう。すべての問題を金にして、模擬試験モードで3回連続85%を超える正答率なら、まず試験では問題ないかと思います。ただし実機に Postgres を導入したほうがいいかと思います（理由は後述）。

公式のサンプル問題をやり込む

公式ページにもサンプル問題が掲載してありますので、こちらも解いておくと良いでしょう。Ping-t とはまた違ったテイストの問題があり、Ping-t には出題されていない内容のものも掲載されております。

勉強時間が足りず公式サンプルは半分しか手をつけておりませんが、それでも十分に知識を試せる問題が揃っております。

実機でコマンドを試す

頭の良い人ならば上記で事足りますが、実際にコマンドを打ち込んでデータベースの構築と、作成される設定ファイルの内容、およびディレクトリ構成は確認しておくと非常に勉強になることが多いです。

# yum -y install postgresql11-server postgresql11-contrib

等で Postgres をインストールした後は、 postgres ユーザでログインして、 データベースクラスタが配置されているディレクトリごと削除。

１から pg_ctl init をして、どのように postgresql.conf はどのディレクトリに配置されるか、 wal はどこに吐き出されるか。

そういえば pg_hba.conf は特定のホストからのアクセスは拒否しておきたいので設定を変更しておこう。

といった文章は、読んでいても右から左に流れるだけでしょう。Ping-t の解答を参考にしながら、コマンドを叩きちゃんと動くか確かめていくことが重要です。一度手を動かしておくことで非常に身につくかと思います。是非試していただきたいです。pg_ctl init くらいはやっておくべきかと思っています。

勉強した中での学び

psql コマンド

とても便利なコマンド。いろいろできるので、細かなオプションを覚えておくとほんと実務で助かります。

$ psql -U postgres -d mydb -c "select * from t where id > 3"

とかとか、わざわざ Postgres のコマンドモードに遷移せずともコマンドを実行できたりします。そもそも psql コマンドってなんやねん、どうやってコマンドモードに入ればええのん...？っていう不明点も解消されます。

ベタなところだけ解説

-U ユーザーを指定します。例えば taro なら -U taro など
-d データベースを指定します。例えば mydb なら -d mydb など

実務ベースだとあまり使うことはないかもしれませんが、オプションの省略は可能ですので知っていて損はないでしょう。

pg_ctl コマンド

Postgres を停止したり、再起動したり、設定ファイルを読み直したりしたい。そういうときには大抵 systemctl とか使ったりするんですが、Postgres にもそれ相当のコマンドがあります。それが pg_ctl コマンドで、例えば、stop、restart、reload というオプションを追加することで実現できます。


# 起動
$ pg_ctl start
# 設定ファイルの再読み込み
$ pg_ctl reload
# 再起動
$ pg_ctl restart
# 停止
$ pg_ctl stop

ローカルにDB構築したけれど、停止したあとの postgres の再起動の方法がわからないと困ってしまいますよね。そんなときには環境変数 PGDATA も設定しつつ、pg_ctl コマンドを使っていきましょう。

vaccum

「ばきゅーむ」、「おーとばきゅーむ」。平仮名で書くと可愛いのですが、英単語としては吸い出すという意味です。では何を吸い出すのでしょうか。

VACUUM は、不要タプルが使用する領域を回収します。 PostgreSQLの通常動作では、削除されたタプルや更新によって不要となったタプルは、テーブルから物理的には削除されません。 これらのタプルはVACUUMが完了するまで存在し続けます。 そのため、特に更新頻度が多いテーブルでは、VACUUMを定期的に実行する必要があります。

不要タプル、他には不要領域と呼ばれたりするこれを回収するのがこのコマンドです。ようはきれいな部屋になるようにゴミを掃除機で吸っちゃおう！っていうのが vacuum のやっていることです。そうすると、欲しい物が見つかりやすいですよね。

お掃除を人の手でやるのではなく、ルンバを用いてやるのが auto vacuum です。その名の通り、ある一定のところでルンバが検知してお掃除してくれます、非常に助かりますね。

っていうのが平易な説明として、もっとガッツリわかりたいっていう人には、公式サイトのこのセミナー資料が良かったんで見てみてください。

パーテーション

例えばとっても大きいログテーブルがあったとき、どうしますか？保守していないデータなら消したいと思うはずです。例えばこんなコマンドを打ってみたくなるかと思います。

delete from SUGOI_DEKAI_LOG where to_char(created_at, 'YYYY-MM-DD') between '2000-01-01' and '2000-12-31';

ちょっと待った。もしこの SUGOI_DEKAI_LOG テーブルの 2000年のレコードの件数が、億単位の場合はどのようになるでしょうか。他にもモリモリ処理が動いている中で、この SQL を流す自信は本当にありますか？そんなときに役に立つのがパーテーションです。

create table SUGOI_DEKAI_LOG (
    id         serial,
    user_id    int,
    created_at timestamp
) partition by range (created_at);


-- 2000年用のパーテーション
create table SUGOI_DEKAI_2000_LOG partition of SUGOI_DEKAI_LOG
    for values from ('2000-01-01') to ('2000-12-31');

-- 2001年用のパーテーション
create table SUGOI_DEKAI_2001_LOG partition of SUGOI_DEKAI_LOG
    for values from ('2001-01-01') to ('2001-12-31');

こういう感じにしてパーテーションを区切っておけば、そこからパーテーション単位で drop table コマンドを叩くこともできます。とても便利だと思いました。もちろんこの SUGOI_DEKAI_LOG パーテーションは事前に作成しておく必要がありますので、何かしらこういうことが必要かも...と思い立ったときには使うといいと思います。Blockchain のトランザクションデータみたいなものとかをコールドデータ化したい、といったときにこういうパーテーションに一時保存させる、みたいな使い方ができるかもしれませんね。

※親テーブル SUGOI_DEKAI_LOG には排他ロックが必要かどうかの判断にこまる場合もあります。お近くの DBA と相談しながら実施することをオススメします。

コマンド

何はなくとも、postgres のコマンドモードに入り、 \? を実行します。すると...

一般
  \copyright             PostgreSQL の使い方と配布条件を表示します。
  \crosstabview [列数]   問い合わせを実行し、結果をクロスタブに表示します。
  \errverbose            最後に発生したエラーメッセージを冗長性最大で表示します。
  \g [ファイル] または ; 問い合わせを実行（し、結果をファイルまたは |パイプ へ出力）します。
  \gexec                 問い合わせを実行し、結果の中の個々の値を実行します。
  \gset [PREFIX]         問い合わせを実行して結果を psql 変数に格納します。
  \gx [ファイル名]       \g と同じですが、拡張出力モードで実行します。
  \q                     psql を終了します。
  \watch [秒数]          指定した秒数ごとに問い合わせを実行します。

ヘルプ
  \? [コマンド]          バックスラッシュコマンドのヘルプを表示します。
  \? オプション          psql のコマンドライン・オプションのヘルプを表示します。
  \? 変数名              特殊変数のヘルプを表示します。
  \h [名前]              SQL コマンドの文法ヘルプの表示。* で全コマンドを表示します。

...

っていっぱい出てきます。ヘルプコマンドは必須で覚えておくべきかと思います。出てきたやつは全部覚えよう...とは言いませんが、一通り目を通しておくことをおすすめします。

あたりは、運用担当になったらよく使いそうな気がします。

受験の感想

運用管理編

WAL（先行書き込みログ）の初期化についてのコマンドについて問われました。そもそもそういうコマンドは知らなかったので、後から見返して勉強になりました。

ping-t で正しく理解しておけば、ちゃんと正答できるであろう問題が多いように思います。

SQL編

SQLでそこまで難しいと思う部分はなかったのですが、この1問だけは解答に迷ったので載せておきたいと思います。

以下SQLを実行後
#=> create table t1 (id integer);  
#=> insert into t1 values (1),(1),(2),(2),(3);  

次のSQLは何行返されるか？
#=> select id from t1 group by id having count(*) = 2;

こちらの解き方を解説します。
まず select * from t1 とすると次のように出力されます。

id
1
1
2
2
3

次に、 select count(*), id from t1 group by id を実行します。どのように出力されるか確認しましょう。

count	id
1	3
2	2
2	1

すると、 count 列が取るのは、id をグループ化した件数を取ります。これで結果が分かるでしょう。having 句で count が 2 となっている部分のみを取り出すことになるので、結果は 2 行となります。実行結果を見ておきましょう。

count
2
2

最後に

資格を取ると転職に有利だとか、昇給があるだとか、そういった面があることは否定しません。しかし資格の取得を通して勉強し、合格することで一定の能力が保証されていることの証明となります。いくら実務で培ってきた技術だとしても、そこには当然に扱わなかった内容もあるはずです。例えばトリガーを知らず、特定の時間に select insert を行う SQL をバッチ処理を実行していたせいでマシンが重くなって業務影響が発生した、パーテーションを知らずにログテーブルが肥大化したから delete 文で削除していてロックされて事故った、ということもあるかもしれません。

技術を身につける方法はたくさんありますが、プログラムを書くだけではなく、こういった試験を通して能力を証明しながら正しい知識を身に着けていくことも重要です。資格を持っている人は、資格を持っていない人に比べて、総じて知識では勝っているのです。知識を持つことで選択肢が増えるからこそ、採用する技術の幅も増やせ、そしてさまざまな分野を任されるようになり、実践で培った知識はさらに洗練されていくことになるでしょう。

OSS-DB Exam Silver 合格体験記