🚥
【Postgres】行指向のテーブルと列指向のテーブルを比較する。
はじめに
最近業務で、RDB にも関わらず、一つのテーブルに一つのカラムを管理しているデータベースを触る機会がありました。
そこで、本来行として持つべきテーブルを、列で持つとどれくらいパフォーマンスに影響を及ぼすのか気になり調べてみました。
データの履歴(バージョン)管理のために、列でデータを持つ必要になる方にも参考になると思います。
環境
% brew info postgresql
postgresql: stable 13.4 (bottled), HEAD
今回実行した SQL
こちらの gitに記載している。
パフォーマンステスト: 行指向テーブル
| 処理※1 | テーブル作成 | 全件取得 | 検索 | 作成 | 更新 | 削除 |
|---|---|---|---|---|---|---|
| 時間(ms)※2 | 5.7 | 6.6 | 1.3 | 1481.1 | 7736.5 | 5502.1 |
| 凡その時間(秒) | 1 秒以下 | 1 秒以下 | 1 秒以下 | 1 秒 | 8 秒 | 6 秒 |
※1 行数は約 10000 行
※2 小数第二位を四捨五入
パフォーマンステスト: 列指向テーブル
| 処理※1 | テーブル作成 | 全件取得 | 検索 | 作成 | 更新 | 削除 |
|---|---|---|---|---|---|---|
| 時間(ms)※2 | 22.4 | 92.6 | 2.1 | 15878.8 | 108352.1 | 53893.3 |
| 凡その時間(秒) | 1 秒以下 | 1 秒以下 | 1 秒以下 | 15 秒 | 108 秒 | 53 秒 |
※1 行数は約 10000 行
※2 小数第二位を四捨五入
終わりに
結果的に、列で管理すると相当のパフォーマンス低下に繋がることがわかりました。
注意事項
- UML のディレクトリでは、ER 図と書いているが、リレーションは記載せず、どのようなテーブルがあるかだけを記載した。
- SQL のクエリの書き方で大きくパフォーマンスが変わると思われる。
おまけ 1: ログファイルの出力方法
- ログファイルの設定場所
postgres=# SHOW config_file;
config_file
-----------------------------------------
/usr/local/var/postgres/postgresql.conf
(1 row)
Time: 0.398 ms
- ログファイルの場所
/usr/local/var/log
- ログに時間を設定する方法
Can I log query execution time in PostgreSQL 8.4?
- ログに出力させたものを grep を使って、外部のログファイルに出力する方法
$ grep "2021-xx-xx 11:50:" postgres.log >> /Users/mac/Psql/ColumnOrientedDatabase/Sql/Insert/Performance/Performance.log
おまけ 2: ログファイルから時間だけを取得するモジュール
下記ディレクトリ(/Users/mac/Psql/ExtractTimeFromPerformanceLog)に、ログファイルから時間だけを取り出すモジュールを作成した。ファイル実行時に、第一引数として該当のログファイルを指定すると、そのログファイルに記載された時間を合計して、出力するようにした。
- ファイル実行例
$ python3 /Users/mac/Psql/ExtractTimeFromPerformanceLog/ExtractTimeFromPerformanceLog.py /Users/mac/Psql/RowOrientedDatabase/Sql/Delete/Performance/Performance.log
5502.076999999987ms
おまけ 3: ディレクトリ構成
- ColumnOrientedDatabase と RowOrientedDatabase の sql ディレクトリに、今回のパフォーマンステストで用いた SQL ファイル、SQL を作成するための python のファイル、SQL の実行時間を記載したログファイルを置いている。
- ExtractTimeFromPerformanceLog.py がおまけ 2 の内容。
$ tree
.
├── ColumnOrientedDatabase
│ ├── Csv
│ │ ├── Output
│ │ │ └── 10000Rows.csv
│ │ └── Process
│ │ └── CreateCsv.py
│ ├── Sql
│ │ ├── CreateTable
│ │ │ ├── Output
│ │ │ │ └── CreateTable.sql
│ │ │ └── Performance
│ │ │ └── Performance.log
│ │ ├── Delete
│ │ │ ├── Output
│ │ │ │ └── Delete.sql
│ │ │ ├── Performance
│ │ │ │ └── Performance.log
│ │ │ └── Process
│ │ │ └── Delete.py
│ │ ├── Insert
│ │ │ ├── Output
│ │ │ │ └── Insert.sql
│ │ │ ├── Performance
│ │ │ │ └── Performance.log
│ │ │ └── Process
│ │ │ └── Insert.py
│ │ ├── Search
│ │ │ ├── Output
│ │ │ │ └── Search.sql
│ │ │ └── Performance
│ │ │ └── Performance.log
│ │ ├── Select
│ │ │ ├── Output
│ │ │ │ └── Select.sql
│ │ │ └── Performance
│ │ │ └── Performance.log
│ │ └── Update
│ │ ├── Output
│ │ │ └── Update.sql
│ │ ├── Performance
│ │ │ └── Performance.log
│ │ └── Process
│ │ └── Update.py
│ └── Uml
│ ├── Er.pu
│ └── Erd.png
├── ExtractTimeFromPerformanceLog
│ └── ExtractTimeFromPerformanceLog.py
├── README.md
└── RowOrientedDatabase
├── Csv
│ ├── Output
│ │ └── Matrix.csv
│ └── Process
│ └── CreateCsvMatrix.py
├── Sql
│ ├── CreateTable
│ │ ├── Output
│ │ │ └── CreateTable.sql
│ │ └── Performance
│ │ └── Performance.log
│ ├── Delete
│ │ ├── Output
│ │ │ └── Delete.sql
│ │ ├── Performance
│ │ │ └── Performance.log
│ │ └── Process
│ │ └── Delete.py
│ ├── Insert
│ │ ├── Output
│ │ │ └── Insert.sql
│ │ ├── Performance
│ │ │ └── Performance.log
│ │ └── Process
│ │ └── Insert.py
│ ├── Search
│ │ ├── Output
│ │ │ └── Search.sql
│ │ └── Performance
│ │ └── Performance.log
│ ├── Select
│ │ ├── Output
│ │ │ └── Select.sql
│ │ └── Performance
│ │ └── Performance.log
│ └── Update
│ ├── Output
│ │ └── Update.sql
│ ├── Performance
│ │ └── Performance.log
│ └── Process
│ └── Update.py
└── Uml
├── Er.pu
└── Erd.png
55 directories, 40 files
Discussion