it技術:データベース:postgresql
差分
このページの2つのバージョン間の差分を表示します。
両方とも前のリビジョン前のリビジョン次のリビジョン | 前のリビジョン次のリビジョン両方とも次のリビジョン | ||
it技術:データベース:postgresql [2020/05/21 15:14] – [注意点] yajuadmin | it技術:データベース:postgresql [2023/12/16 09:21] – [複合インデックス] yajuadmin | ||
---|---|---|---|
行 54: | 行 54: | ||
|11.0|2018/ | |11.0|2018/ | ||
|12.0|2019/ | |12.0|2019/ | ||
- | ==== Postgres10情報 ==== | + | |13.0|2020/ |
+ | |14.0|2021/ | ||
+ | |15.0|2022/ | ||
+ | ==== サポート終了期限(EOL=End Of Life) ==== | ||
+ | https:// | ||
+ | |||
+ | ^バージョン^初期リリース日^サポート終了期限^ | ||
+ | |15|2022年10月13日|2027年11月11日| | ||
+ | |14|2021年09月30日|2026年11月12日| | ||
+ | |13|2020年09月24日|2025年11月13日| | ||
+ | |12|2019年10月3日|2024年11月14日| | ||
+ | |11|2018年10月18日|2023年11月9日| | ||
+ | |10|2017年10月5日|2022年11月10日| | ||
+ | |9.6|2016年09月29日|2021年11月11日| | ||
+ | |||
+ | ==== PostgreSQL10情報 ==== | ||
2017-10-05にリリース | 2017-10-05にリリース | ||
=== 主な新機能 === | === 主な新機能 === | ||
- IDカラム \\ 行のIDを保存するカラム、一意かつ自動でカウントアップされます。 | - IDカラム \\ 行のIDを保存するカラム、一意かつ自動でカウントアップされます。 | ||
- | - ネイティブのパーティショニング機能 \\ ひとつのテーブルの実体を分割して扱える | + | - ネイティブのパーティショニング機能 \\ ひとつのテーブルの実体を分割して扱える |
- 複数列統計 \\ [[http:// | - 複数列統計 \\ [[http:// | ||
- 並列性の強化 \\ パラレルクエリの改善 | - 並列性の強化 \\ パラレルクエリの改善 | ||
- JSONとJSONBの全文検索 \\ JSON型やJSONB型のカラムで全文検索をサポート | - JSONとJSONBの全文検索 \\ JSON型やJSONB型のカラムで全文検索をサポート | ||
- 論理レプリケーション \\ 特定のテーブルの情報だけをレプリケーションする | - 論理レプリケーション \\ 特定のテーブルの情報だけをレプリケーションする | ||
+ | |||
+ | === 名称変更 === | ||
+ | PostgreSQL10ではいくつかのディレクトリ名や関数名などが変更されています。メンテナンス系のシェルスクリプトや監視ツールで下記ディレクトリ名や関数名などをハードコードされている場合は、PostgreSQL10以降の名称に修正する必要があります。 | ||
+ | |||
+ | * ログファイル出力先のディレクトリ名がpg_logからlogへ変更 | ||
+ | * WALに関連するディレクトリ/関数/コマンドなどでxlogがwal、locationがlsnに変更 | ||
+ | * コミットログの出力先ディレクトリ名がpg_clogからpg_xactへ変更 | ||
+ | |||
+ | ^PostgreSQL10以降で変更されたディレクトリ/関数名などの例^^ | ||
+ | ^PostgreSQL9.6以前の名称^PostgreSQL10以降の名称^ | ||
+ | |pg_log|log| | ||
+ | |pg_xlog|pg_wal| | ||
+ | |pg_clog|pg_xact| | ||
+ | |pg_current_xlog_location|pg_current_wal_lsn| | ||
+ | |pg_xlogdump|pg_waldump| | ||
+ | |pg_receivexlog|pg_receivewal| | ||
+ | |||
=== 参照 === | === 参照 === | ||
行 79: | 行 111: | ||
* JITコンパイルの追加 [性能] | * JITコンパイルの追加 [性能] | ||
- | * パーティショニングの様々な改善[機能+性能] | + | * パーティショニングの様々な改善[機能+性能]\\ ※[[ https:// |
* 並列実行の様々な改善[性能] | * 並列実行の様々な改善[性能] | ||
* プロシージャ内でのトランザクション制御をサポート [機能] | * プロシージャ内でのトランザクション制御をサポート [機能] | ||
行 90: | 行 122: | ||
=== 主な新機能 === | === 主な新機能 === | ||
+ | [[https:// | ||
[[https:// | [[https:// | ||
[[https:// | [[https:// | ||
- | [[https:// | + | [[https:// |
+ | [[https:// | ||
+ | [[https:// | ||
+ | [[https:// | ||
行 100: | 行 136: | ||
* パーティショニングの機能追加、性能改善 | * パーティショニングの機能追加、性能改善 | ||
* テーブルアクセスメソッドに対応 | * テーブルアクセスメソッドに対応 | ||
+ | * REINDEX CONCURRENTLYが追加、重いロックなしでインデックスを再構築可能 | ||
+ | * WITH句の共通テーブル式(CTE)で同じCTEが2回以上ある場合に[[https:// | ||
+ | |||
+ | === 注意 === | ||
+ | * WITH OIDが使えなくなった。WITH OIDSがあるテーブルがあるとpg_upgradeが実行できない | ||
+ | * recovery.confはなくなりpostgresql.confに統合 | ||
+ | |||
+ | === 対処方法 === | ||
+ | [[https:// | ||
+ | |||
+ | * WITH OIDSを使わなくても良い設計を検討する | ||
+ | * ALTER TABLE ... SET WITHOUT OIDSでOIDSを取り除く | ||
+ | * recovery.confに書いていたパラメータをpostgresql.confに書くようにする | ||
+ | * リカバリ時はrecovery.signal、スタンバイ時はstandby.signal を置くようにする | ||
+ | * recovery_target_XXXは設定ファイルに必ず一つになるようにすること | ||
+ | ==== PostgreSQL13情報 ==== | ||
+ | 2020-09-24にリリース | ||
+ | |||
+ | === 主な新機能 === | ||
+ | [[https:// | ||
+ | [[https:// | ||
+ | [[https:// | ||
+ | [[https:// | ||
+ | [[https:// | ||
+ | [[https:// | ||
+ | [[https:// | ||
+ | |||
+ | * B-Treeインデックスの性能向上 | ||
+ | * 新しい実行プラン | ||
+ | * パーティションテーブル機能の拡張 | ||
+ | * パラレルVACUUM | ||
+ | * 進捗レポートビューの拡張 | ||
+ | * pgbenchの拡張 | ||
+ | * pg_rewindの拡張 | ||
+ | * SQL機能の追加 | ||
+ | |||
+ | ==== PostgreSQL14情報 ==== | ||
+ | 2021-09-30にリリース | ||
+ | |||
+ | === 主な新機能 === | ||
+ | [[https:// | ||
+ | [[https:// | ||
+ | [[https:// | ||
+ | |||
+ | * Btreeインデックスの肥大化防止 | ||
+ | * 式に対する拡張統計 | ||
+ | * LZ4による列のTOAST圧縮 | ||
+ | * postgres_fdwの拡張 | ||
+ | * ロジカルレプリケーションの改善 | ||
+ | * マルチ範囲型 | ||
+ | * 再帰CTE問い合わせにおけるSEARCH/CYCLE句のサポート | ||
+ | * アイドルセッションのタイムアウト機能 | ||
+ | * 新システムロールpg_read_all_data/ | ||
+ | * COPY進捗表示、WAL書き出しやロジカルレプリケーションの統計表示 | ||
+ | * pg_amcheckコマンド | ||
+ | * ANALYZEコマンドのパフォーマンス改善により大幅に高速化 | ||
+ | |||
+ | ==== PostgreSQL15情報 ==== | ||
+ | 2022-10-06にリリース | ||
+ | |||
+ | === 主な新機能 === | ||
+ | [[https:// | ||
+ | [[https:// | ||
+ | [[https:// | ||
+ | * Merge文のサポート | ||
+ | * 正規表現関数の追加 | ||
+ | * NOT IN句の改善 | ||
+ | * ソートの性能改善 | ||
+ | * ウィンドウ関数の性能改善 | ||
+ | * psql \copyの性能改善 | ||
+ | * zstandard圧縮サポート | ||
+ | * 先読みによるリカバリ性能改善 | ||
+ | * SQL/ | ||
+ | * 多重範囲型への集約 | ||
+ | * ロジカルレプリケーションの機能拡張 | ||
+ | * 呼び出し元権限で実行されるビュー | ||
+ | * モジュールによるWALアーカイブ | ||
+ | * JSONLOG形式 | ||
+ | * モニタリングビューの追加 | ||
+ | * ロジカルレプリケーションの機能拡張 | ||
+ | * パラレルクエリの強化 | ||
+ | * バージョン非互換対応(新機能ではないけど) | ||
+ | * PublicスキーマのCreate権限がデフォルトからなくなる | ||
===== 設定について ===== | ===== 設定について ===== | ||
postgresql.confの設定値は、下記SQLで context の値により設定反映のタイミングが違う。 | postgresql.confの設定値は、下記SQLで context の値により設定反映のタイミングが違う。 | ||
行 212: | 行 331: | ||
[2020-03-28 15:45:06 JST] postgres postgres 8512[2] LOG: connection authorized: user=postgres database=postgres | [2020-03-28 15:45:06 JST] postgres postgres 8512[2] LOG: connection authorized: user=postgres database=postgres | ||
</ | </ | ||
+ | |||
+ | === 実行されたクエリを記録 === | ||
+ | 調査の時に行うくらい。実行するSQLが多いので運用時はコメントアウトにしている。\\ | ||
+ | <wrap em> | ||
+ | |||
+ | <code .conf postgresql.conf> | ||
+ | log_statement = ‘all’ | ||
+ | </ | ||
+ | |||
+ | [[https:// | ||
==== 接続子の優先設定変更 ==== | ==== 接続子の優先設定変更 ==== | ||
postgresql.confの下記設定を変更することによって、スキーマ名未設定でSQL実行時に選択されるスキーマの優先度を変更できる。 | postgresql.confの下記設定を変更することによって、スキーマ名未設定でSQL実行時に選択されるスキーマの優先度を変更できる。 | ||
行 223: | 行 352: | ||
search_path = ' | search_path = ' | ||
</ | </ | ||
+ | ==== psqlの使い方 ==== | ||
+ | psqlの機能やの使い方を紹介しているスライド資料\\ | ||
+ | [[https:// | ||
+ | |||
==== psqlのパスワード入力省略 ==== | ==== psqlのパスワード入力省略 ==== | ||
パスワード入力なしでpsqlを実行するには、下記フォルダの「pgpass.conf」にパスワードを設定しておく。 | パスワード入力なしでpsqlを実行するには、下記フォルダの「pgpass.conf」にパスワードを設定しておく。 | ||
行 325: | 行 458: | ||
==== 条件指定 ==== | ==== 条件指定 ==== | ||
COPYコマンドを使用する。\\ | COPYコマンドを使用する。\\ | ||
- | TOは、絶対パスのみで相対パスは「relative path not allowed for COPY to file」エラーになる。 | + | TOは、絶対パスのみで相対パスは「relative path not allowed for COPY to file」エラーになる。\\ |
+ | dumpファイルは、テキスト形式でタブ区切りでNULL文字が「\N」で出力される。 | ||
< | < | ||
- | COPY (SELECT * FROM sample_table WHERE id > 10000) TO ' | + | COPY (SELECT * FROM sample_table WHERE id > 10000) TO ' |
</ | </ | ||
行 336: | 行 470: | ||
* [[https:// | * [[https:// | ||
+ | === writing: Permission denied === | ||
+ | PostgreSQLのユーザーで実行されるため、出力先がPostgreSQLのユーザーで書き込めるようにする。\\ | ||
+ | フォルダにNETWORK SERVICEユーザーを追加して「フル コントロール」にする。 | ||
+ | {{: | ||
===== インポート ===== | ===== インポート ===== | ||
PostgreSQLのCSVインポートする場合、COPY コマンドで取り込む。\\ | PostgreSQLのCSVインポートする場合、COPY コマンドで取り込む。\\ | ||
行 377: | 行 514: | ||
<code sql> | <code sql> | ||
- | \copy tablename(col1, | + | \COPY tablename(col1, |
↓ | ↓ | ||
- | \copy tablename(col1, | + | \COPY tablename(col1, |
</ | </ | ||
=== COPYで読み込みエラー === | === COPYで読み込みエラー === | ||
行 456: | 行 593: | ||
| | ||
</ | </ | ||
+ | |||
+ | ==== 複合インデックス ==== | ||
+ | 複合インデックスは、キーの先頭から途中までのカラムが指定されていればインデックスが使われる。 | ||
+ | |||
+ | PostgreSQLの場合、インデックスが使われる為には、先頭のキーが必ず必要になる。\\ | ||
+ | [[https:// | ||
+ | |||
+ | あるテーブルのカラム(C1, | ||
+ | C1, C2, C3 の順で順序付けされています。 | ||
+ | |||
+ | ^C1^C2^C3^複合インデックス利用可不可^ | ||
+ | |○|○|○|可| | ||
+ | |○|○|×|可| | ||
+ | |○|×|×|可| | ||
+ | |○|×|○|可 ※C1のみ、C3は無視| | ||
+ | |×|○|○|可 ※table scanが選択される可能性高い| | ||
+ | |×|○|×|可 ※table scanが選択される可能性高い| | ||
+ | |×|×|○|可 ※table scanが選択される可能性高い| | ||
+ | |×|×|×|不可| | ||
+ | |||
+ | 複数列インデックスには、bloomインデックスを使用することで、先頭のキーが条件が外れていてもインデックスが使われる。\\ | ||
+ | [[https:// | ||
+ | ==== 実行計画の取得 ==== | ||
+ | SQLの前に「EXPLAIN」か「EXPLAIN ANALYZE」を付ける。\\ | ||
+ | EXPLAIN は、実行計画を取得できます。\\ | ||
+ | 「ANALYZE」オプションを使用することで、予想コストに加え、実際にかかった処理時間等の情報も確認できます。\\ | ||
+ | (※ ANALYZEオプションを付与すると、実際にクエリが実行されてしまうため、INSERT文や、DELETE文などを行う際は注意が必要です。) | ||
+ | |||
+ | * [[https:// | ||
+ | * [[https:// | ||
+ | |||
+ | ^主な演算子一覧^^^ | ||
+ | ^分類^演算子^処理^ | ||
+ | |テーブルスキャン|Seq scan|インデックスを使用せず、全件を検索| | ||
+ | | |Index scan|インデックスを使用してスキャン| | ||
+ | | |Bitmap scan|ビットマップを使用してスキャン| | ||
+ | | |Index only scan|問い合わせがインデックスに含まれるカラムのみで完結する場合のスキャン| | ||
+ | | |Tid scan|検索条件がタプルID(ctid)のスキャン| | ||
+ | |その他のスキャン|Function scan|関数がデータをgatherした結果をスキャン| | ||
+ | |テーブルの結合|Nested Loop|ネステッド・ループ結合を行う| | ||
+ | | |Merge Join|ソート・マージ結合を行う| | ||
+ | | |Hash Join|ハッシュ結合を行う| | ||
==== 照合順序 ==== | ==== 照合順序 ==== | ||
行 538: | 行 717: | ||
COPY (SELECT * FROM fuga WHERE id = :id) TO :inputpath; | COPY (SELECT * FROM fuga WHERE id = :id) TO :inputpath; | ||
</ | </ | ||
+ | ==== パフォーマンス向上 ==== | ||
+ | * [[https:// | ||
+ | * [[https:// | ||
+ | |||
==== その他 ==== | ==== その他 ==== | ||
* postgresql.confのlc_messagesを「Japanese_Japan.932」から「C」にすることでエラーメッセージが文字化けしなくなる。 | * postgresql.confのlc_messagesを「Japanese_Japan.932」から「C」にすることでエラーメッセージが文字化けしなくなる。 |
it技術/データベース/postgresql.txt · 最終更新: 2024/04/24 16:37 by yajuadmin