みなさんこんにちは。技術系Q&Aサイトteratail開発チームの出川幾夫です。
PostgreSQLはMySQLと並んで広く使われているオープンソースのRDBMSです。商用のRDMBSと比較しても遜色ない高機能なRDMBSですが、
今回はPostgreSQLの、
PostgreSQLのデータ型・関数
PostgreSQLではさまざまなデータ型が利用可能です。文字列型はchar、
integer型やfloat型などの数値型にはMySQLにあるようなUNSIGNEDはないので注意が必要です。IDなどの連番を表現するにはserial型が便利です。またtimestamp型、
PostgreSQLのデータ型で特徴的なのが配列型です。その名の通り1つのカラムに複数個のデータを格納することができ、
配列型は、tag_
カラムをintegerの1次元配列として定義すると以下のようになります。
CREATE TABLE users (
name varchar(32),
tag_ids integer[]
);
PostgreSQLの配列のインデックスは1始まりです。配列からデータを取り出す際の配列リテラルは{1,2,3}
のように表現でき、
またJSONを保存するJSON型というのもPostgreSQL9.array_
やjson_
などJSONを扱うための組み込み関数も豊富で、
INSERT INTO settings (id, data) VALUES (1, '{ "notifications": {"mail": true, "mobile": true}}');
PostgreSQL9.
複合型というプログラミング言語で言う構造体のような役割を果たす型も存在します。テーブル作成時には同名の複合型が作られます。
これらの非正規化を実現する型はクエリを短縮しパフォーマンスをあげることができますが、
Window関数も便利です。rank()
やrow_
など集約関数に似た動きする関数が用意されています。
SELECT username, rank() OVER (ORDER BY point DESC) AS rank FROM users;
他にもMySQLなど他のRDBMSとは細かな違いがあります。他のDBの癖をそのまま使ってしまわないように基本的な部分は設計時に予め確認しておきましょう。
基礎的な設定項目
PostgreSQLの基本的な設定はpostgresql.
実際に反映されている設定パラメータの値はSHOW ALL
で確認ができます。
=# SHOW ALL;
shared_
は、
log_
はいわゆるスロークエリをログに残すための設定です。ミリ秒単位で記述し、
他にも設定パラメータは多く存在しますので、
postgresql.
- 参考:PostgreSQLでDBバックアップのパフォーマンスチューニングをする方法
- https://
teratail. com/ questions/ 3785 - 参考:Postgresで複雑なSQLの実行計画の制御
- https://
teratail. com/ questions/ 846
VACUUM
PostgreSQLの特徴のひとつに追記型アーキテクチャであることが挙げられます。PostgreSQLではDELETEやUPDATEを実行した際には既存の行は内部的には削除されず、
この不要領域を回収して再利用可能な状態にするのがVACUUMという機能です。テーブルロックを行わないのでサービスが動いていても実行でき、
VACUUMは内部的には不要領域を含むページが記録されているVM
VACUUMは以下のようにテーブル名を指定してそのテーブルに対して実行します。指定しない場合はデータベース内の全テーブルに対してVACUUMを行います。
=# VACUUM some_table;
PostgreSQL 8.autovacuum=on
)
閾値は設定パラーメーターであるautovacuum_
とautovacuum_
を使って以下の式のように表されます。
閾値 = autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * 行数
最後のVACUUMのあとに発生した不要行の数が上のVACUUM閾値を超えるとVACUUMが実行されます。テーブルの更新頻度やデータの件数によってこちらをチューニングしていく必要があります。
データベースごとのVACUUMやANALYZEの実行履歴は、pg_
テーブルから参照できます。
=# SELECT relname, last_vacuum, last_autovacuum, last_analyze, last_autoanalyze, vacuum_count,autovacuum_count, analyze_count,autoanalyze_count FROM pg_stat_user_tables;
VACUUMはPostgreSQLの基本アーキテクチャに関わる部分で非常に重要な機能です。開発環境でVACUUM VERBOSE some_
をして実行の流れを見てみるとどのような流れでVACUUMが行われていくのかがわかりますので、
- 参考:PostgreSQLのvacuum analyzeについて
- https://
teratail. com/ questions/ 13605
全文検索モジュールpg_bigm
PostgreSQLではさまざまなモジュールが利用できます。その中でも最も導入が簡単で効果の大きいものがpg_
pg_LIKE '%word%'
)=%
という演算子を利用することで類似度検索にも対応します。
インストール方法は非常に簡単です。pg_custom_
の行は不要です。
shared_preload_libraries = 'pg_bigm'
# custom_variable_classes = 'pg_bigm' # PostgreSQL 9.1以前
そして拡張を有効化します。
=# CREATE EXTENSION pg_bigm;
これで、
=# CREATE INDEX post_body_gin_index ON posts USING gin(body gin_bigm_ops);
pg_DROP EXTENSION
で無効化します。
=# DROP EXTENSION pg_bigm CASCADE;
pg_
全文検索にはGroongaやElasticSearchのような高機能な全文検索エンジンも効果的ですが、
- 参考:PostgreSQLで、
全文検索を行う為のGINとGiSTインデックスの使い分けについて - https://
teratail. com/ questions/ 1178
最後に
今回は、
- teratail
- https://
teratail. com