スポンサーサイト

上記の広告は1ヶ月以上更新のないブログに表示されています。
新しい記事を書く事で広告が消せます。

PostgreSQLとRubyを組み合わせてブラックリストチェックの速度を測ってみた。

とある文章がブラックリストに載っているかどうかを判定しようと思っています。ブラックリストが4000レコードもあって、単純に見つかるまで処理すると遅いだろうと思い色々な方法で試して速度を測ってみました。
それぞれ100回回しています。

1. SELECT id FROM dirty_words WHERE 'うんこ' ~* dirty_content
9秒

2. SELECT 'うんこ' ~* string_agg(dirty_content, '|') FROM dirty_words
4秒

3. SELECT string_agg(dirty_content, '|') FROM dirty_words
返ってきた値に対してRubyの正規表現チェック
1秒

4. SELECT dirty_content FROM dirty_words
返ってきた値に対して、Rubyで文字列結合してRubyの正規表現チェック
6秒

5. SELECT dirty_content FROM dirty_words
返ってきた値に対して、それぞれでRubyの正規表現チェック
8秒

ということで、PostgreSQL側で一行にまとめて、Rubyで正規表現チェックするのが早いことがわかりました。
スポンサーサイト

Twitterのcreated_atをPostgreSQLのTIMESTAMP型に変換する

to_timestampではタイムゾーンの解釈ができないけど、Twitterは+0000でしかこないので決め打ちで変換します。
日本時間で管理したい場合は9時間足しておきます。

to_timestamp(
'Fri Feb 26 07:50:35 +0000 2016'
,'Dy Mon DD HH24:MI:SS +0000 YYYY'
)::TIMESTAMPTZ + interval'9 hour'

ストアードプロシージャのノウハウ。例外編

複数行返すようなストアードプロシージャの時エラーを返したい場合に行で返すのが難しい場合があります。
そこで例外を使います。

DROP TYPE IF EXISTS type_test_get_list_data CASCADE;
CREATE TYPE type_test_get_list_data AS (
id BIGINT
);

CREATE OR REPLACE FUNCTION test_get_list_data(
p_id BIGINT DEFAULT NULL
) RETURNS SETOF type_test_get_list_data AS $FUNCTION$
DECLARE
BEGIN
IF p_id = 1 THEN
RAISE SQLSTATE 'U0001' USING MESSAGE = 'error occerd p_id=%', p_id;
END IF;
END;
$FUNCTION$ LANGUAGE plpgsql;


例外を出すにはRAISEを使います。
SQLSTATEには例外のコードを、MESSAGEには例外のメッセージが設定できます。

例外のコードは5桁の文字列です。先頭の2桁が種類を、残り3桁がエラーの詳細を意味しているそうですが
別にコードは何を使ってもかまわないのですし、受け取る側が解釈するだけなので適当でもいいです。
ですがPostgreSQLが内部で返すエラーコードが詳細に定義されているので
それに被らないコードが望ましいです。
先頭にUの文字を使っているエラーコードは事前に定義されていないので
私はU0001からコードを振っています。

メッセージには入力パラメーターの情報など渡すと確認しやすくなります。
%を使うことで、いくつも変数をメッセージに付与できます。

アプリケーションでは、メッセージをログにはいたり、エラーコードに合わせた
ユーザ向きのメッセージに変換して画面に表示したりしています。

PostgreSQLでパフォーマンスに影響しない論理削除

論理削除を削除フラグや削除日時のカラムで実装すると、カーディナリティが低いので検索でのパフォーマンスが良くないです。
そこで、削除用のテーブルを作り移動することにします。

スキーマーを以下の三つにわけます。
source(全てのレコード)
public(生きているレコードのみ)
garbage(削除されているレコードのみ)

sourceスキーマーにテーブルの定義を行い
publicスキーマーとgarbageスキーマーにはテーブル継承したテーブルを置きます。

通常はpublicスキーマーを検索すればよいです。
もし、削除されているものも含めて欲しい場合、sourceスキーマーを検索できます。

スキーマーを作るSQL

CREATE SCHEMA IF NOT EXISTS source;
CREATE SCHEMA IF NOT EXISTS garbage;


テーブルを作るSQL

DROP TABLE IF EXISTS source.projects CASCADE;

CREATE TABLE source.projects (
id BIGSERIAL NOT NULL
,project_nm TEXT NOT NULL DEFAULT ''
,created_id BIGINT NOT NULL DEFAULT 0
,updated_id BIGINT NOT NULL DEFAULT 0
,deleted_id BIGINT NOT NULL DEFAULT 0
,created_at TIMESTAMPTZ NOT NULL
,updated_at TIMESTAMPTZ NOT NULL
,deleted_at TIMESTAMPTZ
,created_pg TEXT NOT NULL DEFAULT ''
,updated_pg TEXT NOT NULL DEFAULT ''
,deleted_pg TEXT NOT NULL DEFAULT ''
,PRIMARY KEY(id)
);

CREATE TABLE public.projects (
LIKE source.projects INCLUDING ALL
) INHERITS (source.projects);

CREATE TABLE garbage.projects (
LIKE source.projects INCLUDING ALL
) INHERITS (source.projects)


データを削除スキーマーに移動するストアードプロシージャ

DROP TYPE IF EXISTS type_dyn_move_gabage CASCADE;
CREATE TYPE type_dyn_move_gabage AS (
id BIGINT
);

-- データを削除スキーマーに移動する
-- 引数
-- p_table_name : テーブル名
-- p_id : IDの値
-- 戻り値
-- ID
CREATE OR REPLACE FUNCTION dyn_move_gabage(
p_table_name TEXT DEFAULT NULL
,p_id BIGINT DEFAULT NULL
,p_deleted_id BIGINT DEFAULT NULL
,p_deleted_at TIMESTAMPTZ DEFAULT NULL
,p_deleted_pg TEXT DEFAULT NULL
) RETURNS SETOF type_dyn_move_gabage AS $FUNCTION$
DECLARE
w_deleted_id BIGINT := COALESCE(p_deleted_id, 0);
w_deleted_at TIMESTAMPTZ := COALESCE(p_deleted_at, NOW());
w_deleted_pg TEXT := COALESCE(p_deleted_pg, 'dyn_move_gabage');
BEGIN
-- 移動先データ登録
EXECUTE $$ INSERT INTO garbage.$$ || p_table_name || $$
SELECT
*
FROM
public.$$ || p_table_name || $$
WHERE
id = $1
$$
USING
p_id
;

-- 移動先データ更新
EXECUTE $$ UPDATE garbage.$$ || p_table_name || $$ SET
deleted_id = $2
,deleted_at = $3
,deleted_pg = $4
WHERE
id = $1
$$
USING
p_id
,w_deleted_id
,w_deleted_at
,w_deleted_pg
;

-- データ元削除
EXECUTE $$ DELETE FROM public.$$ || p_table_name || $$
WHERE
id = $1
$$
USING
p_id
;

-- 戻り値
RETURN QUERY SELECT
p_id
;
END;
$FUNCTION$ LANGUAGE plpgsql;


実験

INSERT INTO projects (
project_nm
,created_at
,updated_at
) VALUES (
'aaa'
,NOW()
,NOW()
);

SELECT * FROM projects;
SELECT * FROM source.projects;
SELECT * FROM gabage.projects;

SELECT * FROM dyn_move_gabage('projects', 1);

SELECT * FROM projects;
SELECT * FROM source.projects;
SELECT * FROM gabage.projects;


削除される前はsourceとpublicのprojectsで1件の結果が帰ります。
削除後はsourceとgarbageで1件の結果が帰ります。

ストアードプロシージャのノウハウ。リターン編

PostgreSQLのストアードプロシージャではRETURNが3種類あります。
RETURN
RETURN QUERY
RETURN NEXT
です。3種類を使ったサンプルが以下です。

DROP TYPE IF EXISTS type_sp_test CASCADE;
CREATE TYPE type_sp_test AS (
id BIGINT
,name TEXT
);
CREATE OR REPLACE FUNCTION sp_test(p_param1 int, p_param2, text)
RETURNS SETOF type_sp_test AS $FUNCTION$
DECLARE
w_row type_sp_test;
BEGIN
RETURN QUERY SELECT
1::BIGINT
,'abc'
;
RETURN QUERY SELECT
2::BIGINT
,'efg'
;
w_row.id = 3;
w_row.name = 'hij';
RETURN NEXT w_row;
RETURN;
END;
$FUNCTION$ LANGUAGE plpgsql;


SELECT * FROM sp_test(1, 'a');

1, 'abc'
2, 'efg'
3, 'hij'

ストアードプロシージャ名を型に持つ関数です。
SETOFの指定があるので複数行返せます。
DECLAREとBEGINの間で定義しているのはローカル変数で、この関数が返す行を型にしています。

RETURN QUERYはSELECTやRUTURNINGを使ったINSERT、UPDATEの値を戻せます。
例では1行づつ2回呼んでますが、1回で複数行返すこともできます。
そして2回呼んでいることからわかると思いますが、RETURN QUERYは関数が終了しません。
関数の終了はRETURNが単独で呼ばれた時だけです。

RETURN NEXTは行を型にもつ変数を返す時に使用できます。
行の各列の値を、色々な方法でセットしてから返したい場合などに便利です。

RETURNは関数を終了させます。省略した場合、関数の最後に到達した時に関数が終了します。
上記広告は1ヶ月以上更新のないブログに表示されています。新しい記事を書くことで広告を消せます。