第10回 PostgreSQLアンカンファレンス@東京 に参加した

勉強会

pgunconf.connpass.com

pl/sqlをpl/pgsqlにした話　を同人誌にした話

資料

https://www.slideshare.net/TakashiMeguro/plsqlplpgsql

oracleもpl/sqlも触ったことなくてpl/pgsqlは少しだけ、という感じだったのでいまいちイメージできない話ではあった。データベースの移行というのは大変なんだな

闇RDBアンチパターン

フラグの闇

  • delete_flag が色々ある(1,2,0,9,99,null)。それぞれに意味がある。
  • そいつらを組み合わせて状態を作るのであれば最初から状態をもつ方が良い
  • 事実に基づいてデータベースを設計していく

論理id

  • idの中の桁とかで意味を持たせる。ゼロから始まるidはこういう属性、みたいなやつ。
  • パースする必要が出てくる
  • 別でカラム持って、とりあえずトリガーかけよう

転んだ後のバックアップ

  • 納品時は動いたけどエラーになる日がくる
  • mysqlのdumpはエラーになったら標準出力にだす
  • 知らないうちにエラーになる
  • db2は0を返す
  • バックアップをテストする
  • 5年前のバックアップは多分リストアできない
  • リストアまで考えよう

なんかgithubでもリストアに失敗したみたいな話あった気がする

タイムゾーンの闇

  • timestamp with timezone
  • RDSのデフォルトのタイムゾーンはUTC
  • mysqlはjstでpostgresがutcだと?
    • postgres側がよしなに変換してくれる
    • mysqlは再起動するとjstがutcになる
  • 失敗から学ぶRDBの正しい歩き方という本がでる

pg2arrowとarrow_fdwに向けて

  • pg-stromを作ってる(stormだと思ってた、、)
  • ssdからgpuにデータ転送してgpu上でsql実行する
    • さらに高速化するならcpuの下にssdとgpuの組みをpciで複数ぶら下げる
  • 今の時代
    • IoTとかDBの外でデータが発生している
    • 集計解析する為にimportするのが遅い RDSもimportが遅い
  • fdwで外のファイルをそのまま扱う
  • apache arrowという形式
    • 負荷の大半はpostgresの内部形式に変換するところ
    • そのままgpuに送ってしまってgpu側もその形式を認識できるようにする
    • gpuは並んだデータへのアクセスが得意
    • 列志向の形式が有利
    • postgresは内部構造的に苦手
    • apache arrowは可変長データの持ち方が賢い
    • postgresはバイナリの先頭に4byteでながさが入ってる。それを毎度みる必要がある
    • apache arrowの勉強がてら、postgresのデータをarrow形式に変換するpg2arrowを作った

テーブル更新ログを取得するextensionを作った

  • 特定インスタンスへの差分更新を後からまとめて別インスタンスへ適用したい
  • ダウンタイム少なく行こうできる
  • tablelogというモジュールが昔あった
    • すでにメンテされてない
    • Cの関数だとDBaaSにはいれられない
  • 新しいtablelogモジュール
    • pl/v8で書いた
  • ログの蓄積方法
    • トリガーを仕込む
    • 更新情報を別テーブルに貯める
    • 好きに加工して突っ込む
  • なぜplv8?

postgresをコンパイルする時の話

  • pgconf asia 2017の資料
  • gccのコンパイルオプションを色々工夫して早くする
  • コードを自動生成していると余計なコードが結構発生する
  • 使っていない変数の削除
  • 関数のインライン化
  • 他にも色々最適化のオプションは本当に色々ある
  • 数パーセントの高速化になった
  • インライン化が重要
  • ファイルが別れているとだめな場合があるのでリンク時の最適化をかけるといい
  • コンパイラがコンパイル時にcpuのプロファイルをとって最適化かける
  • pgoというやつ
  • python3で使える。お試しした時に10%くらい早くなった
  • pythonの場合は400くらいプロファイルとってる
  • コンパイル時間はかなり長くなる

拡張統計とjoinの話

  • pg_hint_plan
    • 辛い
    • 本質的な問題から目を背けている。使わない方がいい
    • データが変わった時に追従できない
  • 拡張統計情報create statistics
  • 関数従属なカラムをfilter(where)に使ってるとプランをうまく作れない
    • 統計情報作ってやる
  • 結合に対応してないという情報があって試してみた
    • だめだった
  • 結合方法より前に拡張統計情報が見られていない

詳しい話はこちら kyabatalian.hatenablog.com

postgresqlのいけてるテク7選

  • timestampの範囲
    • キャンペーンの範囲みたいなの
    • start_at is null or start_at<now()
    • timestampでinfinityが使える
    • coalesce(start_at, '-infinity')<now()
    • tstzrange で範囲をそのまま扱える
    • 半開区間をそのままかける
    • gistも貼れる
  • with句で書きやすい
  • valuesはtableが入る場所にいくらでもかける
    • 定数とか書きやすい
  • filter節
  • 統計処理
  • ltree型
    • ツリーのデータ構造を簡単に扱える
    • カテゴリ構造に使えそう

postgres11 のパラレルクエリの動作

  • 実際に起動されるworkerがパラメータで決定される
  • worker数が増えるごとの性能をプロファイルをとった
    • 11が途中でさちる
  • 統計情報がおかしかった
    • 11になるとだいたい半分になった
    • pg_class.relpages
    • 実際のファイルサイズは正しい
    • もともと正確でないと言われているとはいえおかしいn
  • 元のテーブルは100G
  • 会社だと10G
  • 家の3Gテーブルだと再現しない