Andrew Vogelのプロフィール写真

byAndrew VogelonApril2,2019

先週、私はいくつかのパフォーマンスの最適化を必要とするRailsプロジェクトに飛び乗る 私たちが選んだアプローチは、ActiveRecordの土地から一括検索と更新プロセスを取り出し、すべてをPostgresに移動することでした。 パフォーマンスの向上は、大規模なレコードセットのために巨大でした。 ハイエンドでは、要求時間が>5分からサブ2秒になるのを見ました。

以前はSQLとPostgresを使用していましたが、その完全な機能セットに完全に精通していませんでした。 最初は、Postgresのより深い知識を持っている同僚とペアになりました。 クエリを書いてテストスイートをリファクタリングした数日後、すべてが緑色で、テストのためにステージングにデプロイする準備が整いました。

ここで私が学んだ興味深いSQLとPostgreSQLのことをいくつか紹介します。

CTEの

PostgreSQLに排他的ではありませんが、CTEの、または共通のテーブル式は、データベースシステムで再利用可能なクエリを記述する方法です。

2つのテーブルがあるとしましょう-booksauthors

id タイトル author_id ジャンル
1 デジタル要塞 1 スリラー
2 ダ-ヴィンチ-コード 1 スリラー
3 ハリー-ポッターと秘密の部屋 2 ファンタジー
著者表
id 名前 年齢
1 ダン-ブラウン 54
2 J.K. ローリング 53

私たちは、著者と本を取得するために本当に簡単なCTEを書くことができます:

with authors_and_books as ( SELECT b.id as book_id, b.title, a.name as author_name FROM books b JOIN authors a on b.author_id = a.id; ); 

次に、CTEから選択すると、次のようになります:

SELECT * FROM authors_and_books; 
book_id タイトル 著者名
1 デジタル要塞 ダン-ブラウン
2 ダ-ヴィンチ-コード ダン-ブラウン
3 ハリー-ポッターと秘密の部屋 J.K. ローリング

この例は簡単ですが、複雑なクエリを再利用する必要があるときに、これが本当にどのように便利になるかを見ることができます。

一時テーブル(PostgreSQL)

もう一つの本当に便利なデータベース機能は、一時テーブルを作成する機能です。 私の場合、これはフロントエンドから投稿された”未処理の”データをステージングするのに本当に便利でした。 一時テーブルを作成するには、通常のcreate table ..ステートメントのようにスキーマを定義する必要があります。

CREATE TEMPORARY TABLE temp_isbns ( title varchar(255) NOT NULL, isbn varchar(255) NOT NULL, author_name varchar(255) NOT NULL ) ON COMMIT DROP; 

ここで重要な部分は最後のON COMMITです。 トランザクションブロックの最後に一時テーブルを処理する方法をPostgresに指示する必要があります。 DROPは、トランザクションブロックの最後に一時テーブルを削除するようにPostgresに指示します。 Postgresのドキュメントでは、ON COMMITオプションの詳細について説明しています。

COALESCE

COALESCE関数は、渡された最初のnull以外の値を返します。 この関数は無制限の数の引数を受け入れ、左から右に評価された最初のnull以外の引数を返します。

ここでは、それがどのように見えるかのいくつかの例があります:

合体例1

select coalesce(null, 1); 

合体

合体例2

select coalesce(null, null, 1, null); 

合体

合体例3

select coalesce(2, null, 1); 

合体

Upserts

Upserting、またはupdating-and-insertingは、Postgresの超充実した機能です。 レコードがデータベースに既に存在する場合は、競合解決で挿入を処理できます。

私たちの本のテーブルを覚えていますか?

id タイトル author_id ジャンル
1 デジタル要塞 1 スリラー
2 ダ-ヴィンチ-コード 1 スリラー
3 ハリー-ポッターと秘密の部屋 2 ファンタジー

Upserts例1

ブックにいくつかのレコードを挿入しますが、ブックが存在する場合は何もしないことを選択します:

INSERT INTO books (id, title, author_id, genre) VALUES (3, 'Harry Potter and The Chamber of Secrets', 2, 'fantasy'), (4, 'Harry Potter and The Half Blood Prince', 2, 'fantasy') ON CONFLICT DO NOTHING; 

さて、私たちのテーブルを見てみましょう:

SELECT * FROM books; 
id タイトル author_id ジャンル
1 デジタル要塞 1 スリラー
2 ダ-ヴィンチ-コード 1 スリラー
3 ハリー-ポッターと秘密の部屋 2 ファンタジー
4 ハリー-ポッターと半血の王子様 2 ファンタジー

Upserts例2

その他の一方で、レコードを書籍に挿入して更新しましょう 対応するIDを持つレコード。 Postgres docsによると、挿入のために提案された値を参照するには、EXCLUDEDテーブルを使用する必要があります。

INSERT INTO books (id, title, author_id, genre) VALUES (3, 'Harry Potter and The Goblet of Fire', 2, 'fantasy'), (4, 'Harry Potter and The Half Blood Prince', 2, 'fantasy') ON CONFLICT (id) DO UPDATE SET title = EXCLUDED.title; 

最後にテーブルを見て:

SELECT * FROM books; 
id タイトル author_id ジャンル
1 デジタル要塞 1 スリラー
2 ダ-ヴィンチ-コード 1 スリラー
3 ハリー-ポッターと炎のゴブレット 2 ファンタジー
4 ハリー-ポッターと半血の王子様 2 ファンタジー

このブログ記事と一緒にフォローしてくれてありがとう。 お聞きしたい特定のPostgres機能がある場合は、お気軽にお問い合わせください[email protected]

この投稿は役に立ちましたか? 他の人とそれを共有します。

  • Twitterのロゴツイート
  • Facebookのロゴシェア
  • Linkedinのロゴポスト

コメントを残す

メールアドレスが公開されることはありません。