スポンサーサイト

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

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件の結果が帰ります。
スポンサーサイト

COMMENTS

COMMENT FORM

TRACKBACK


この記事にトラックバックする(FC2ブログユーザー)

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