ユーザ用ツール

サイト用ツール


it技術:データベース:postgresql

差分

このページの2つのバージョン間の差分を表示します。

この比較画面へのリンク

両方とも前のリビジョン前のリビジョン
次のリビジョン
前のリビジョン
it技術:データベース:postgresql [2020/05/21 15:08] – [注意点] yajuadminit技術:データベース:postgresql [2024/02/23 21:20] (現在) – [PostgreSQL15情報] yajuadmin
行 54: 行 54:
 |11.0|2018/10/18|[[it技術:データベース:postgresql#postgresql11情報|postgresql11情報]] | |11.0|2018/10/18|[[it技術:データベース:postgresql#postgresql11情報|postgresql11情報]] |
 |12.0|2019/10/03|[[it技術:データベース:postgresql#postgresql12情報|postgresql12情報]] | |12.0|2019/10/03|[[it技術:データベース:postgresql#postgresql12情報|postgresql12情報]] |
-==== Postgres10情報 ====+|13.0|2020/09/24|[[it技術:データベース:postgresql#postgresql13情報|postgresql13情報]] | 
 +|14.0|2021/09/30|[[it技術:データベース:postgresql#postgresql14情報|postgresql14情報]] | 
 +|15.0|2022/10/06|[[it技術:データベース:postgresql#postgresql15情報|postgresql15情報]] | 
 +|16.0|2023/09/14|[[it技術:データベース:postgresql#postgresql16情報|postgresql16情報]] | 
 +==== サポート終了期限(EOL=End Of Life) ==== 
 +https://www.postgresql.org/support/versioning/ 
 + 
 +^バージョン^初期リリース日^サポート終了期限^ 
 +|16|2023年09月14日|2028年11月9日| 
 +|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を保存するカラム、一意かつ自動でカウントアップされます。
-  - ネイティブのパーティショニング機能 \\ ひとつのテーブルの実体を分割して扱える+  - ネイティブのパーティショニング機能 \\ ひとつのテーブルの実体を分割して扱える \\ ※[[ https://tech-lab.sios.jp/archives/11693|UPDATE時にパーティションキーが更新されるとエラーになる]]
   - 複数列統計 \\ [[http://www.oracle.com/webfolder/technetwork/jp/obe/11gr1_db/perform/multistats/multicolstats.html|Oracle11gで採用された複数列統計]]相当なもの   - 複数列統計 \\ [[http://www.oracle.com/webfolder/technetwork/jp/obe/11gr1_db/perform/multistats/multicolstats.html|Oracle11gで採用された複数列統計]]相当なもの
   - 並列性の強化 \\ パラレルクエリの改善   - 並列性の強化 \\ パラレルクエリの改善
   - 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: 行 113:
  
   * JITコンパイルの追加 [性能]   * JITコンパイルの追加 [性能]
-  * パーティショニングの様々な改善[機能+性能]+  * パーティショニングの様々な改善[機能+性能]\\ ※[[ https://tech-lab.sios.jp/archives/11693|UPDATE時にパーティションキーが更新されると適切な子テーブルに移動される]]
   * 並列実行の様々な改善[性能]   * 並列実行の様々な改善[性能]
   * プロシージャ内でのトランザクション制御をサポート [機能]   * プロシージャ内でのトランザクション制御をサポート [機能]
行 90: 行 124:
  
 === 主な新機能 === === 主な新機能 ===
 +[[https://h50146.www5.hpe.com/products/software/oe/linux/mainstream/support/lcc/pdf/PostgreSQL_12_GA_New_Features_ja_20191011-1.pdf|PostgreSQL 12 新機能検証結果 (GA) - pdf]]\\
 [[https://www.postgresql.org/about/press/presskit12/ja/|PostgreSQL 12 プレスキット]]\\ [[https://www.postgresql.org/about/press/presskit12/ja/|PostgreSQL 12 プレスキット]]\\
 [[https://mag.osdn.jp/19/10/04/163000|「PostgreSQL 12」リリース、性能面でさまざまな強化が行われる]]\\ [[https://mag.osdn.jp/19/10/04/163000|「PostgreSQL 12」リリース、性能面でさまざまな強化が行われる]]\\
-[[https://www.publickey1.jp/blog/19/postgresql_12bjson.html|PostgreSQL 12が正式リリース。Bツリー周りの改善による性能向上、JSONパスによる抽出可能など]]+[[https://www.publickey1.jp/blog/19/postgresql_12bjson.html|PostgreSQL 12が正式リリース。Bツリー周りの改善による性能向上、JSONパスによる抽出可能など]]\\ 
 +[[https://www.slideshare.net/nttdata-tech/postgresql12-performance-improvement-pluggable-storage-engine-ntt-sawada|PostgreSQL 12は ここがスゴイ! ~性能改善やpluggable storage engineなどの新機能を徹底解説~ - SlideShare]]\\ 
 +[[https://www.fujitsu.com/jp/products/software/resources/feature-stories/postgres/partitioning-performance/|技術者に聞く:パーティショニングにおける性能改善への取り組み PostgreSQLインサイド]]\\ 
 +[[https://aws.amazon.com/jp/blogs/news/postgresql-12-a-deep-dive-into-some-new-functionality/|PostgreSQL 12 – いくつかの新機能のご紹介]]
  
  
行 100: 行 138:
   * パーティショニングの機能追加、性能改善   * パーティショニングの機能追加、性能改善
   * テーブルアクセスメソッドに対応   * テーブルアクセスメソッドに対応
 +  * REINDEX CONCURRENTLYが追加、重いロックなしでインデックスを再構築可能
 +  * WITH句の共通テーブル式(CTE)で同じCTEが2回以上ある場合に[[https://www.sraoss.co.jp/tech-blog/pgsql/pg12-cte/|高速化]]
 +
 +=== 注意 ===
 +  * WITH OIDが使えなくなった。WITH OIDSがあるテーブルがあるとpg_upgradeが実行できない
 +  * recovery.confはなくなりpostgresql.confに統合
 +
 +=== 対処方法 ===
 +[[https://www.slideshare.net/nttdata-tech/postgresql12-performance-improvement-pluggable-storage-engine-ntt-sawada|PostgreSQL 12は ここがスゴイ! ~性能改善やpluggable storage engineなどの新機能を徹底解説~ - SlideShare]]
 +
 +  * WITH OIDSを使わなくても良い設計を検討する
 +  * ALTER TABLE ... SET WITHOUT OIDSでOIDSを取り除く
 +  * recovery.confに書いていたパラメータをpostgresql.confに書くようにする
 +  * リカバリ時はrecovery.signal、スタンバイ時はstandby.signal を置くようにする
 +  * recovery_target_XXXは設定ファイルに必ず一つになるようにすること
 +==== PostgreSQL13情報 ====
 +2020-09-24にリリース
 +
 +=== 主な新機能 ===
 +[[https://www.postgresql.org/about/press/presskit13/ja/|PostgreSQL 13 プレスキット]]\\
 +[[https://www.sraoss.co.jp/tech-blog/pgsql/pg13report/|PostgreSQL 13 検証報告]]\\
 +[[https://news.mynavi.jp/article/20200525-1040900/|PostgreSQL 13で登場する新機能まとめ]]\\
 +[[https://qiita.com/nuko_yokohama/items/e253dd2619c639558a23|PostgreSQL 13がやってくる!(6) - psqlの改善あれこれ]]\\
 +[[https://www.slideshare.net/nttdata-tech/postgresql13-pg-stat-statements-nttdata-fujii|PostgreSQL 13でのpg_stat_statementsの改善について - SlideShare]]\\
 +[[https://www.slideshare.net/nttdata-tech/postgresql13-pg-stat-statements-nttdata-fujii|PostgreSQL 13でのpg_basebackupの改善について - SlideShare]]\\
 +[[https://www.slideshare.net/nttdata-tech/postgresql13-replication-nttdata-fujii|PostgreSQL 13でのレプリケーション関連の改善について - SlideShare]]
 +
 +  * B-Treeインデックスの性能向上
 +  * 新しい実行プラン
 +  * パーティションテーブル機能の拡張
 +  * パラレルVACUUM
 +  * 進捗レポートビューの拡張
 +  * pgbenchの拡張
 +  * pg_rewindの拡張
 +  * SQL機能の追加
 +
 +==== PostgreSQL14情報 ====
 +2021-09-30にリリース
 +
 +=== 主な新機能 ===
 +[[https://www.postgresql.org/about/press/presskit14/ja/|PostgreSQL 14 プレスキット]]\\
 +[[https://www.sraoss.co.jp/tech-blog/pgsql/pg14report/|PostgreSQL 14 検証報告]]\\
 +[[https://www.sraoss.co.jp/tech-blog/wp-content/uploads/2021/08/pg14_report_20210818.pdf|PostgreSQL14 検証レポート- pdf]]
 +
 +  * Btreeインデックスの肥大化防止
 +  * 式に対する拡張統計
 +  * LZ4による列のTOAST圧縮
 +  * postgres_fdwの拡張
 +  * ロジカルレプリケーションの改善
 +  * マルチ範囲型
 +  * 再帰CTE問い合わせにおけるSEARCH/CYCLE句のサポート
 +  * アイドルセッションのタイムアウト機能
 +  * 新システムロールpg_read_all_data/pg_write_all_data
 +  * COPY進捗表示、WAL書き出しやロジカルレプリケーションの統計表示
 +  * pg_amcheckコマンド
 +  * ANALYZEコマンドのパフォーマンス改善により大幅に高速化
 +
 +==== PostgreSQL15情報 ====
 +2022-10-06にリリース
 +
 +=== 主な新機能 ===
 +[[https://www.sraoss.co.jp/tech-blog/pgsql/pg15report/|PostgreSQL 15検証報告]]\\
 +[[https://www.sraoss.co.jp/tech-blog/wp-content/uploads/2022/09/pg15_report_20220906_rev2.pdf|PostgreSQL15 検証レポート - pdf]]\\
 +[[https://speakerdeck.com/takahashiikki/postgresql15-new-information|アプリケーションエンジニアから見たPostgreSQL15 の新機能]]
 +
 +  * Merge文のサポート
 +  * 正規表現関数の追加
 +  * NOT IN句の改善
 +  * ソートの性能改善
 +  * ウィンドウ関数の性能改善
 +  * psql \copyの性能改善
 +  * zstandard圧縮サポート
 +  * 先読みによるリカバリ性能改善
 +  * SQL/JSON対応の拡張
 +  * 多重範囲型への集約
 +  * ロジカルレプリケーションの機能拡張
 +  * 呼び出し元権限で実行されるビュー
 +  * モジュールによるWALアーカイブ
 +  * JSONLOG形式
 +  * モニタリングビューの追加
 +  * ロジカルレプリケーションの機能拡張
 +  * パラレルクエリの強化
 +  * バージョン非互換対応(新機能ではないけど)
 +    * PublicスキーマのCreate権限がデフォルトからなくなる 
 +
 +==== PostgreSQL16情報 ====
 +2023-09-14にリリース
 +
 +=== 主な新機能 ===
 +[[https://www.sraoss.co.jp/tech-blog/pgsql/pg16report/|PostgreSQL 16検証報告]]\\
 +[[https://community.hpe.com/t5/hpe-blog-japan/%E7%AF%A0%E7%94%B0%E3%81%AE%E8%99%8E%E3%81%AE%E5%B7%BB-postgresql-16-beta-1-%E6%96%B0%E6%A9%9F%E8%83%BD%E6%A4%9C%E8%A8%BC%E7%B5%90%E6%9E%9C-%E5%85%AC%E9%96%8B/ba-p/7188972?profile.language=ja|篠田の虎の巻「PostgreSQL 16 Beta 1 新機能検証結果」公開!]]
  
 +  * 性能向上
 +    * パラレルクエリ対応追加
 +    * ウィンドウ関数の最適化
 +    * 同時データ投入の性能向上
 +    * SIMD CPUアクセラレータ対応
 +  * SQL機能
 +    * SQL/JSON 対応
 +    * 整数リテラル表現の追加
 +    * ICU照合順序の拡張
 +    * ロジカルレプリケーション機能追加
 +    * スタンバイ上でのパブリケーション
 +    * トランザクションの並列適用
 +    * 双方向ロジカルレプリケーション
 +  * クライアント機能
 +    * libpqロードバランス
 +    * psqlで拡張プロトコル対応
 +    * pg_dump圧縮オプション追加
 +  * 運用管理
 +    * 新たなモニタリング項目
 +    * 新たな定義済みロール
 +    * ページ凍結の改善
 +    * VACUUMリングバッファ指定
 +    * Mesonビルド
 ===== 設定について ===== ===== 設定について =====
 postgresql.confの設定値は、下記SQLで context の値により設定反映のタイミングが違う。 postgresql.confの設定値は、下記SQLで context の値により設定反映のタイミングが違う。
行 212: 行 364:
 [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
 </code> </code>
 +
 +=== 実行されたクエリを記録 ===
 +調査の時に行うくらい。実行するSQLが多いので運用時はコメントアウトにしている。\\
 +<wrap em>但し、バインド変数の値までは取れない。またSQL文そのものに文法エラーなどがあり実行されなかった場合も出力されない。</wrap>
 +
 +<code .conf postgresql.conf>
 +log_statement = ‘all’
 +</code>
 +
 +[[https://www.casleyconsulting.co.jp/blog/engineer/4470/|PostgreSQLで実行されたクエリからバグを探してみた(Windows編)]]
 ==== 接続子の優先設定変更 ==== ==== 接続子の優先設定変更 ====
 postgresql.confの下記設定を変更することによって、スキーマ名未設定でSQL実行時に選択されるスキーマの優先度を変更できる。 postgresql.confの下記設定を変更することによって、スキーマ名未設定でSQL実行時に選択されるスキーマの優先度を変更できる。
行 223: 行 385:
 search_path = 'test,"$user", public' # schema names search_path = 'test,"$user", public' # schema names
 </code> </code>
 +==== psqlの使い方 ====
 +psqlの機能やの使い方を紹介しているスライド資料\\
 +[[https://event.ospn.jp/slides/OSC2022_OnlineKyoto/OSC-kyoto-psql.pdf|psqlの使い方 - pdf]]
 +
 ==== psqlのパスワード入力省略 ==== ==== psqlのパスワード入力省略 ====
 パスワード入力なしでpsqlを実行するには、下記フォルダの「pgpass.conf」にパスワードを設定しておく。 パスワード入力なしでpsqlを実行するには、下記フォルダの「pgpass.conf」にパスワードを設定しておく。
行 325: 行 491:
 ==== 条件指定 ==== ==== 条件指定 ====
 COPYコマンドを使用する。\\ COPYコマンドを使用する。\\
-TOは、絶対パスのみで相対パスは「relative path not allowed for COPY to file」エラーになる。+TOは、絶対パスのみで相対パスは「relative path not allowed for COPY to file」エラーになる。\\ 
 +dumpファイルは、テキスト形式でタブ区切りでNULL文字が「\N」で出力される。
  
 <code> <code>
-COPY  (SELECT * FROM sample_table WHERE id > 10000) TO 'E:/work/tmp/sample_dump';+COPY  (SELECT * FROM sample_table WHERE id > 10000) TO 'E:/work/sample_table.dump';
 </code> </code>
  
行 336: 行 503:
   * [[https://qiita.com/cyborg__ninja/items/99efcb5b62a4cef2f156|PostgreSQLのCSV出力(Export)方法]]   * [[https://qiita.com/cyborg__ninja/items/99efcb5b62a4cef2f156|PostgreSQLのCSV出力(Export)方法]]
  
 +=== writing: Permission denied === 
 +PostgreSQLのユーザーで実行されるため、出力先がPostgreSQLのユーザーで書き込めるようにする。\\ 
 +フォルダにNETWORK SERVICEユーザーを追加して「フル コントロール」にする。 
 +{{:it技術:データベース:psql使用フォルダ権限.png|}}
 ===== インポート ===== ===== インポート =====
 PostgreSQLのCSVインポートする場合、COPY コマンドで取り込む。\\ PostgreSQLのCSVインポートする場合、COPY コマンドで取り込む。\\
行 377: 行 547:
  
 <code sql> <code sql>
-\COPY mstc000040(aa,ba,bb,bc,ca,cb,cc,cd) FROM 'C:/work/test.csv' WITH(FORMAT CSV,HEADER ON,FORCE_NULL(aa,ba,bb,bc,ca,cb,cc,cd));+\COPY tablename(col1,col3,col4,col2) FROM pathName WITH encoding 'UTF8' CSV HEADER NULL AS ''; 
 +                                                     ↓ 
 +\COPY tablename(col1,col3,col4,col2) FROM pathName WITH(ENCODING 'UTF8',FORMAT CSV,HEADER ON,NULL '',FORCE_NULL(col1,col3,col4,col2));
 </code> </code>
 === COPYで読み込みエラー === === COPYで読み込みエラー ===
行 454: 行 626:
        CONNECTION LIMIT = -1;        CONNECTION LIMIT = -1;
 </code> </code>
 +
 +==== 複合インデックス ====
 +複合インデックスは、キーの先頭から途中までのカラムが指定されていればインデックスが使われる。
 +
 +PostgreSQLの場合、インデックスが使われる為には、先頭のキーが必ず必要になる。\\
 +[[https://sgyatto.hatenablog.com/entry/2018/11/24/225938|複合インデックスは、構成列のどの列を指定すれば選択候補に挙がるのか。]]
 +
 +あるテーブルのカラム(C1, C2, C3) に対して複合インデックスを貼っている状況を考えます。\\
 +C1, C2, C3 の順で順序付けされています。
 +
 +^C1^C2^C3^複合インデックス利用可不可^
 +|○|○|○|可|
 +|○|○|×|可|
 +|○|×|×|可|
 +|○|×|○|可 ※C1のみ、C3は無視|
 +|×|○|○|可 ※table scanが選択される可能性高い|
 +|×|○|×|可 ※table scanが選択される可能性高い|
 +|×|×|○|可 ※table scanが選択される可能性高い|
 +|×|×|×|不可|
 +
 +主キーが複数の場合のインデックスにはデフォルトのb-treeインデックスが使用されるが、弱点として先頭のキーが外れるとインデックスが使われなくなる。その場合に複数列インデックスには、bloomインデックスを使用することで、先頭のキーが条件が外れていてもインデックスが使われる。\\
 +[[https://qiita.com/nuko_yokohama/items/4a7fe2a731a479189034|bloomインデックス]]
 +==== 実行計画の取得 ====
 +SQLの前に「EXPLAIN」か「EXPLAIN ANALYZE」を付ける。\\
 +EXPLAIN は、実行計画を取得できます。\\
 +「ANALYZE」オプションを使用することで、予想コストに加え、実際にかかった処理時間等の情報も確認できます。\\
 +(※ ANALYZEオプションを付与すると、実際にクエリが実行されてしまうため、INSERT文や、DELETE文などを行う際は注意が必要です。)
 +
 +  * [[https://www.casleyconsulting.co.jp/blog/engineer/259/|PostgreSQLの実行計画について調べてみた]]
 +  * [[https://www.slideshare.net/MikiShimogai/postgre-sql-explain|PostgreSQLクエリ実行の基礎知識 ~Explainを読み解こう~ - slideshare]]
 +
 +^主な演算子一覧^^^
 +^分類^演算子^処理^
 +|テーブルスキャン|Seq scan|インデックスを使用せず、全件を検索|
 +|  |Index scan|インデックスを使用してスキャン|
 +|  |Bitmap scan|ビットマップを使用してスキャン|
 +|  |Index only scan|問い合わせがインデックスに含まれるカラムのみで完結する場合のスキャン|
 +|  |Tid scan|検索条件がタプルID(ctid)のスキャン|
 +|その他のスキャン|Function scan|関数がデータをgatherした結果をスキャン|
 +|テーブルの結合|Nested Loop|ネステッド・ループ結合を行う|
 +|  |Merge Join|ソート・マージ結合を行う|
 +|  |Hash Join|ハッシュ結合を行う|
  
 ==== 照合順序 ==== ==== 照合順序 ====
行 536: 行 750:
 COPY (SELECT * FROM fuga WHERE id = :id) TO :inputpath; COPY (SELECT * FROM fuga WHERE id = :id) TO :inputpath;
 </code> </code>
 +==== パフォーマンス向上 ====
 +  * [[https://techracho.bpsinc.jp/hachi8833/2020_04_16/79219|PostgreSQL: 「OR」を避けてパフォーマンスを向上させよう]]
 +  * [[https://blog.honjala.net/entry/2016/11/19/021057|PostgreSQLでpg_bigmを使って中間一致like検索を高速化する]]
 +
 ==== その他 ==== ==== その他 ====
   * postgresql.confのlc_messagesを「Japanese_Japan.932」から「C」にすることでエラーメッセージが文字化けしなくなる。   * postgresql.confのlc_messagesを「Japanese_Japan.932」から「C」にすることでエラーメッセージが文字化けしなくなる。
it技術/データベース/postgresql.1590041335.txt.gz · 最終更新: 2020/05/21 15:08 by yajuadmin