VACUUM(バキューム)とは

PostgreSQLはデータを消しても実際には消えてはなく、削除フラグがついていて見えなくなっているだけの状態になっています。この削除データは定期的にきれいにする必要があり、この処理をVACUUMといいます。

昔のバージョンでは、このVACUUM処理は手動で行う必要がありましたが、現在、このVACUUM処理を自動で行う機能(AUTOVACUUM)がデフォルトでONになっています。

 

 

VACUUMとVACUUM FULL

VACUUMには通常のVACUUMと、VACUUM FULLの2つがあります。

(加えてVACUUM ANALYZEがありますがそれは後述します)

 

通常のVACUUMは、きれいにした領域をOSに返さない(=サイズは変わらない)かわりに処理は早く、VACUUM FULLでは、きれいにした領域をOSに返しますが処理は遅くなっています

 

また通常のVACUUMの場合、排他的ロックを取得しないため、テーブルへの通常の読み書き操作と並行して実行することができます。一方VACUUM FULLでは、排他的ロックが必要になります。

 

 

VACUUM

VACUUMをするコマンドは次のとおりです。 ※pgAdminのクエリツールからも実行できます。

-- テーブル名を指定してVACUUMする
VACUUM テーブル名;

-- VERBOSEオプションをつけると、実行時間等の詳細情報が表示されます
VACUUM VERBOSE テーブル名;

-- テーブル名を省略すると、すべてのテーブルを対象とします
VACUUM;

 

 

VACUUM FULL

VACUUM FULLのコマンドは次のとおりです。

-- 通常のVACUUM ※テーブル名を省略すると、すべてのテーブルを対象とします
VACUUM テーブル名;

-- FULLオプションあり
VACUUM FULL テーブル名;

 

 

VACUUM ANALYZE

VACUUM ANALYZEは、VACUUM後にANALYZEを実行します。

ANALYZEとは、データベース内のテーブルの内容に関する統計情報を更新するコマンドです。

-- VACUUM+ANALYZEをする ※テーブル名を省略すると、すべてのテーブルを対象とします
VACUUM ANALYZE テーブル名;

-- すべてのテーブル指定では、時間がかかるので注意
VACUUM ANALYZE;

 

あわせて読みたい

※ ANALYZEについてはこちらの記事で説明しています

https://postgresweb.com/post-5390

 

 

 

テーブルの最終VACUUM時間を確認する

次のselect文でvacuumの実行時間を確認できます。

select relname 
, last_vacuum , last_autovacuum
from pg_stat_all_tables order by relname;

データベース一覧の取得(SQL)

データベース一覧を取得するSQLを紹介します。

select 
    datname, 
    pg_encoding_to_char(encoding)  -- DBのエンコーディング
from pg_database;

テーブル情報を取得するにはここを参照してください。

 

 

データベース一覧の取得(コマンド)

データベース一覧を取得するには、次のコマンドを実行します。

※psqlでログイン後に「-l」または「--list」のみを実行すれば一覧が表示されます。

-l
--list

 

 

ビュー一覧の取得

データベースのビューを取得するSQLです。

select * From pg_views
where schemaname = 'public';

 

 

シーケンス一覧の取得

データベースのシーケンスを取得するSQLです。

SELECT relname FROM pg_class 
JOIN pg_namespace ON (pg_class.relnamespace = pg_namespace.oid) 
WHERE relkind = 'S';

 

シーケンスを初期化するには次の文を使います。

select SETVAL('seq_name', 1 , false); -- シーケンス「seq_name」を1に設定

 

 

トリガー一覧の取得

データベースのトリガーを取得するSQLです。

-- トリガー一覧の取得
SELECT * FROM information_schema.triggers;

 

 

インデックス情報の取得

データベースのインデックスを取得するSQLです。

-- 実行後に表示されるidx_scan列は、インデックススキャンを使用した回数です
select * From pg_stat_user_indexes; 

データベースとテーブルの物理容量を取得する

データベースとテーブルの物理容量を取得するSQLです。

-- 1.データベースの物理容量を取得する
SELECT 
    datname,                                      -- データベース名
    pg_size_pretty(pg_database_size(datname))     -- サイズ
FROM pg_database; 


-- 2.テーブルの物理容量を取得する
SELECT 
    relname,                                         -- テーブル名
    reltuples as rows,                               -- データ行数
    (relpages * 8192) as bytes,                      -- サイズ(バイト単位)
    ((relpages * 8192) / (1024 * 1024)) AS mbytes    -- サイズ(MB単位)
FROM pg_class order by mbytes desc;

テーブル一覧を取得する(SQL)

テーブル一覧を取得するSQL文を紹介します。

※ここで紹介するSQLは、そのまま実行できるので試してみて下さい。

--テーブル一覧をテーブル名順に取得するSQL
select tablename from pg_tables
where schemaname not in('pg_catalog','information_schema')
order by tablename;

 

 

テーブル一覧を取得する(psql)

単にテーブルの一覧を取得するだけなら、psqlでこのコマンドで可能です。※最初の記号は「¥」です。

\dt

 

実行結果はこんな感じです。

 

 

テーブル一覧とそのデータ件数を表示する

テーブルごとのデータ数を一覧表示するSQL文を紹介します。

--テーブルごとのデータ数を表示する
SELECT 
pg_class.relname , pg_class.reltuples
FROM pg_stat_user_tables
INNER JOIN pg_class ON pg_stat_user_tables.relname = pg_class.relname
order by cast(pg_class.reltuples as numeric) desc;

 

ちなみにreltuples(レコード数)が大きいと「1e+06」のように表示されることがあります。

この例では、1e+06=10の6乗)=1,000,000になります。

「1e+06」のように表示したくない時は、(pg_class.reltuples / 1)と1で割ると

数値で表示されるようになると思います。(それでもかなり大きいと1eで表示されますが)

--テーブルごとのデータ数を表示する(1e+XX表示させないパターン)
SELECT 
pg_class.relname , (pg_class.reltuples / 1)
FROM pg_stat_user_tables
INNER JOIN pg_class ON pg_stat_user_tables.relname = pg_class.relname
order by cast(pg_class.reltuples as numeric) desc;

 

 

Delete文を生成するSQL

テーブル一覧を取得するSQLを使い、delete文を作成するSQLです。

このSQLを投げると「delete from テーブル名;」の文が結果に出力されます。

※テーブル名で指定する場合は、最後の行のコメントアウトを外してください。

--テーブル一覧からdelete文を作成する
select 
    'delete from ' || tablename || ';' as del_sql from pg_tables  -- delete文
where schemaname not in('pg_catalog','information_schema')
--and tablename like '%テーブル名を指定%';

 

 

関連記事

https://postgresweb.com/get-columns-in-table

https://postgresweb.com/post-5371

 

SQL(SELECT文)の結果をループする方法

select結果をループするサンプルを紹介します。

大雑把にいうと1.でSQLの結果を格納する変数をrecord型で宣言し、

2.でselect結果が変数に格納されるので、それを使って処理をします。

CREATE OR REPLACE FUNCTION loop_test1()
RETURNS boolean LANGUAGE 'plpgsql'
COST 100 VOLATILE 
AS $BODY$
declare
    --1.レコード型で変数宣言
    school_rec record; 
begin
    --2.for文(select結果をschool_recに格納してループする)
    FOR school_rec IN
        --select文の最後の;は不要
        select * from m_school
    LOOP
        --ここにループ中の処理を書く
        --動作確認用:raise infoで変数の中身を表示する
        raise info '%', school_rec.school_code;
    END LOOP;

    return true;
	
end;
$BODY$;

※サンプルですので、そのまま実行してもテーブルがためエラーになります。

 

あわせて読みたい

1行の結果を取得して扱う方法はこちら

https://postgresweb.com/post-5305

 

SQLの結果を変数に格納する

関数をつくる際、select結果を変数に代入し、その後の処理に使うケースが結構あると思います。

その場合の書き方は、次のようになります。

--「列名」の値を変数へ代入する
select 列名 into 変数名 from テーブル名 where 条件文;

--実例:m_schoolのschool_nameを変数sch_nmに入れる
select school_name into sch_nm from m_school where school_code = 'a';

 

selectで取得した1行丸ごと変数に入れたいときも、書き方は変わりません。

が、結果を格納する型に注意する必要があります(後ほど説明)。

--「select *」で取得した結果を変数に入れる
select * into 変数名 from テーブル名 where 条件文;

--もちろん「select *」ではなく、列名指定でも同じ 
--一列ごとintoではなく、まとめてinto 変数名で変数に結果を格納する
select 列名 , 列名 , ・・・ into 変数名 from テーブル名 where 条件文;

--実例:m_schoolの行を変数rec_schoolに入れる
--下の方にある「関数で使う例(1行を取得して変数に格納する)」がわかりやすいと思います
select * into rec_school from m_school where school_code = 'a';

 

あわせて読みたい

この記事は1行取得した結果を変数に入れる方法です。

複数行の結果を取得して扱う方法はこちらで紹介しています。

https://postgresweb.com/post-5331

 

 

 

関数で使う例(1列を取得して変数に格納する)

上で紹介した構文を使った、実際の関数での使用例です。

CREATE OR REPLACE FUNCTION test()
RETURNS boolean 
LANGUAGE 'plpgsql' COST 100 VOLATILE AS $BODY$
declare
     
   --selectの結果を格納する変数を宣言
   sch_nm   character(10);

begin

   --m_schoolのschool_nameを変数sch_nmに入れる
   select school_name into sch_nm from m_school where school_code = 'a';

   --動作確認用(変数の中身を表示する)
   raise info '%' , sch_nm;

  -- (略)

end;
$BODY$;
ALTER FUNCTION public.test()
    OWNER TO postgres;

 

あわせて読みたい

関数の終わりにあるraiseは、変数の中を途中で確認するために使っています。

詳しくはこちら。

https://postgresweb.com/post-2852

 

 

 

 

関数で使う例(1行を取得して変数に格納する)

上で紹介した構文を使った、実際の関数での使用例です。

取得した行丸ごと、変数に入れるにはrecord型で変数宣言すると便利です。

CREATE OR REPLACE FUNCTION test2()
RETURNS boolean 
LANGUAGE 'plpgsql' COST 100 VOLATILE AS $BODY$
declare
     
   --selectの結果を格納する変数を宣言(record型で宣言)
   rec_school   record;

begin

   --実例:m_schoolの行を変数rec_schoolに入れる
   select * into rec_school from m_school where school_code = 'a';

   --動作確認用(変数の中身を表示する)
   raise info '%' , rec_school.school_name;

   -- (略)

end;
$BODY$;
ALTER FUNCTION public.test2()
    OWNER TO postgres;

IF文の書き方

PostgreSQLでのif文の書き方を紹介します。

-- 1.単純なif文
if 条件文 then
   --条件を満たすときの処理を書く
end if;

-- 2.if文とそれ以外(else)の書き方
if 条件文 then
   --条件を満たすときの処理を書く
else
   --それ以外の場合の処理を書く(else thenではない点に注意)
end if;

-- 3. AND(AかつBの時)の書き方
if (条件文1) and (条件文2) then
   --条件を満たすときの処理を書く
end if;

-- 4. OR(AまたはBの時)の書き方
if (条件文1) or (条件文2) then
   --条件を満たすときの処理を書く
end if;

-- 5.複数の条件の書き方
if 条件文1 then
   --条件1を満たすときの処理を書く
elseif 条件文2 then
   --条件2を満たすときの処理を書く
else
   --それ以外の場合の処理を書く
end if;

 

ちなみにif文は関数(FUNCTION)で使用可能ですが、SELECT文では使えません。

SELECT文ではCASE文を使います。CASE文の使い方はこちら。

 

あわせて読みたい

https://postgresweb.com/post-4229

 

 

 

IF文の書き方(実例)

if文の実例です。

-- 1.単純なif文
if num1 = 1 then
   return 0;  --変数num1が1だったら0でリターンする
end if;

-- 否定形には「!=」を使う
if num1 != 1 then
   return 0;  --変数num1が1でなければ0でリターンする
end if;

-- 大なりイコールは「>=」を使う
if num1 >= 0 then
   return true;
end if;

-- 小なりイコールは「<=」を使う
if num1 <= 0 then
   return false;
end if;

-- 2.if文とそれ以外(else)の書き方
if num1 = 1 then
   return true;   --変数num1が1だったらtrueでリターンする
else
   return false;  --変数num1が1以外ならばfalseでリターンする
end if;

-- 3. AND(AかつBの時)の書き方
if (bool1 = true) and (moji = 'abc') then
   return true;
end if;

-- 4. OR(AまたはBの時)の書き方
if (bool1 = true) and (moji = 'abc') then
   return true;
end if;

-- 5.複数の条件の書き方
if num1 = 1 then
   return 0;    --変数num1が1だったら0でリターンする
elseif num1 = 2 then
   return 1;    --変数num1が2だったら1でリターンする
else
   return -1;   --変数num1が1,2以外だったら-1でリターンする
end if;

 

変数の中身を確認する方法

if文とはほぼ関係ないですが、関数で実行中に変数の中身を確認したい場合があると思います。

それにはraiseを使う方法があり、こちらの記事で紹介しています。是非ご覧ください。

 

あわせて読みたい

https://postgresweb.com/post-2852

 

変数を宣言する方法

変数は関数で使用することができ、冒頭(declareとbeginの間)で宣言をします。

(通常のSQL問い合わせでは使用できません。)

CREATE OR REPLACE FUNCTION var_test()
RETURNS boolean 
LANGUAGE 'plpgsql' COST 100 VOLATILE AS $BODY$
declare
   --関数宣言はdeclareからbeginの間に書きます。
   --宣言は変数名+半角スペース+型です。
   variable1   integer;  
begin

 

あわせて読みたい

実行して試したい方に、関数を手っ取り早く作成する関数テンプレをこちらで紹介しています

https://postgresweb.com/template-create-function

 

 

あわせて読みたい

SQLの結果を変数に格納する方法はこちらで紹介しています

https://postgresweb.com/post-5305

 

 

 

よく使う変数

比較的よく使う変数を紹介します。

--変数名+スペース+型で宣言する
variable1   character(10);             --文字
variable2   character varying(10);     --文字
variable3   text;                      --文字
variable4   integer;                   --数値
variable5   numeric;                   --数値
variable6   boolean;                   --true/false
variable7   RECORD;                    --レコード
variable8   テーブル名%ROWTYPE;         --存在するテーブルの行
variable9   テーブル名.カラム名%TYPE;   --存在するテーブルの列
arr1        character varying[];       --配列
arr2        integer[];                 --配列

 

あわせて読みたい

関数を作っている時やデバッグ時に、変数の中身を確認したい時があると思います。

こちらの記事で、raise関数を使った変数の中身の表示方法を紹介しています。

https://postgresweb.com/post-2852

 

 

 

 

 

変数宣言と同時に代入する方法

変数宣言と同時に代入をする方法です。

-- 型の後に、コロン+イコールで値を代入します
変数名 型:=値

var1   character(10):='abc';                          --文字
var2   character varying(10):='CDE';                  --文字
var3   integer:=1;                                    --数値
var4   numeric:=2.3;                                  --数値
var5   boolean:=true;                                 --真偽値
arr1   character varying[]:=array['A','B','C','D'];   --配列
arr2   integer[]:=array[1,2,3];                       --配列

 

 

 

定数を宣言する方法

変数の次に、定数の宣言方法です。

-- 変数名の後に「constant」をつけ、型の後にコロン+イコールで定数宣言
変数名 constant 型:=値

var1 constant character varying(10) := 'abcdefghij';
var2 constant numeric := 1.08;

マテリアライズドビュー(マテビュー)とは

マテリアライズドビューとは、実際にデータがテーブルに格納されるビューです。

格納されたデータを取得するだけのため、ビューよりはデータ取得が高速な反面、

データは更新しないと古いままのデータになってしまうため、注意が必要です。

 

マテリアライズドビュー(マテビュー)を作成する

マテビューを作成する構文です。

※VIEWを作る構文に「MATERIALIZED」がつく形になります。

 --マテビューを作成する構文
CREATE MATERIALIZED VIEW マテビュー名 AS
SELECT xxx , xxx, … FROM テーブル名;

 

マテビューにINDEXを作成する

マテビューにもテーブルと同じく、INDEXを定義することができます。

--マテビューにINDEXを定義する
CREATE UNIQUE INDEX ON マテビュー名(列1 , 列2 , ・・・);

 

マテビューを更新する

マテビューのデータを更新(=リフレッシュ)する方法です。

マテビューはリフレッシュ中にロックがされるため、リフレッシュが終わるまで待たされる

という欠点があります。(リフレッシュが短い場合は特に気にしなくてもいいですが)

 

解決するには「CONCURRENTLY」オプションをつけることで、

テーブル全体をロックすることなくマテリアライズドビューの更新が行えます。

(オプションをつけるには、INDEXか主キーが作成されている必要があります。)

--通常のリフレッシュ文
REFRESH MATERIALIZED VIEW マテビュー名;

--CONCURRENTLYをつけた方式、ただしこれにはINDEX作成が必要
REFRESH MATERIALIZED VIEW CONCURRENTLY マテビュー名;

 

構文まとめを書いています。ぜひ一度見てみて下さい

「基本構文・こんな時どう書くんだっけ?」をまとめました

なんとなくわかるWindow関数とは

この記事ではWINDOW関数を、サンプルデータを使って

なんとなーくイメージをつかんでもらおうとする記事です。

(なんとなくなので構文の説明はほかのサイトに任せることとします。)

 

そもそもWindow関数とは

Window関数とはデータをWindowという領域で区切り、

その中で合計値を取得したり、ランク付けしたりできるようになります。

 

GROUP BYは行が集約されて結果が取得できますが、WINDOW関数では、

行がまとめられることはなく、それぞれが取得できるのが大きく違います。

 

サンプルデータで実行してみる

生徒とそのテスト結果というデータを用意してみました。

student_name(生徒名)、subject_name(教科)、score(点数)とします。

 

WINDOWS関数の例①

教科でWINDOWを区切った後、合計値、ランク付けをするSQLの例です。

※「PARTITION BY subject_name」で「教科ごとに区切る」を表しています

SELECT 
	--教科
	subject_name, 
	
	--教科でWindowを区切る(PARTITION BY subject_name)
	--教科ごとに、点数の高い順(order by score desc)に生徒を表示する
	rank() OVER(PARTITION BY subject_name order by score desc) AS ranking
	
	--生徒,          点数
	, student_name , score
	
	--教科ごとに合計点を取得する
	, sum(score) OVER (PARTITION BY subject_name) 
FROM test_score;

 

結果はこんな感じ。教科ごとに点数の高い順にデータが並ぶ形になります。

英語の点数が高い生徒、国語の点数が高い生徒、…という形。

(赤い線はわかりやすいように後から引いています)

 

WINDOWS関数の例②

上のSQLは

『PARTITION BY subject_name教科に対しての順位付け)』でしたが、

『PARTITION BY student_name生徒に対しての順位付け)』に変更してみます。

SELECT 
	--生徒名
	student_name, 
	
	--生徒でWindowで区切る(PARTITION BY student_name)
	--生徒ごとに、点数の高い順(order by score desc)に教科を表示する
	rank() OVER(PARTITION BY student_name order by score desc) AS ranking
	
	--教科,           点数
	, subject_name , score
	
	--またその生徒ごとに合計点を取得する
	, sum(score) OVER (PARTITION BY student_name) 
FROM test_score;

 

すると結果はこんな感じ。

生徒ごとに教科の点数が高い順に並べた結果になります。

 

なんとなくまとめ

サンプルを2つ提示しましたが、ざっくりとイメージはつかめたでしょうか。

まとめるとWINDOW関数とは…

・WINDOWという区切りを作り、その中で集計やランク付けができる

・GROUP BYは結果が集計されるが、WINDOW関数は集計されない

というものでした。

 

構文まとめを書いています。ぜひ一度見てみて下さい

「基本構文・こんな時どう書くんだっけ?」をまとめまし