今日は、データ型の話をしたいと思います。
現在、勤め先でお守りしてるDBにPostgreSQL7.4(以下、Postgres)があります。
このPostgresは総レコード数200万件でDBサイズが4GBありますが、前任者不在の状態で引き継いだため不思議仕様が結構あります。
例えば、データ型の定義の仕方。
一例を挙げると、文字データ型が全部character(一部text)だったりします。
ちなみにcharacterで定義してあるので、取得後にTrimして使ってます(．_．)。
Postgresの公開文書を見ると、character、varcharに性能の違いはないと記載されています。
(参考URL:http://www.postgresql.jp/document/current/html/datatype-character.html)
また、非常に参考になったのが"Let's Postgres"の「文字列型の使い分け」のページです。
(参考URL:http://lets.postgresql.jp/documents/technical/text-processing/1/)
このページでは、データ型ごとに消費するバイト数が異なるので、それぞれ適したものを使用しましょうと記載してあります。
当たり前のことですが、わかりやすいです。
でも考えてもピンとこなかったので、テスト環境で単純にcharacterとvarcharのサイズの違いを比べてみました。
今回テストに使用したテーブルの概要です。
・レコード数：35万件
・テーブルサイズ：315MB
・全カラム数：49
・文字データ型のカラム数：35
・文字データ型の文字数：599文字(Postgresはバイト数ではなく文字数で定義します)
※ちなみにほとんどの項目が定義した文字数の半分も消費しません
別名でテーブル(characterをvarchar(character varying)に定義して、ダンプしたデータを流し込んでみると、
・テーブルサイズが、315MB→143MBへ減少
・キーとインデックスのサイズが増加(キー：15MB→21MB、インデックス：13MB→15MB)
となりました(どちらもvacuum full、reindex実施)。
テスト結果と参考URLの記事を元に考察すると、
・セットする文字数が変動するものは、varcharの方がサイズを小さくできる(可能性有り)
・固定長のデータをvarcharで格納するとデータサイズが大きくなる可能性有り(キー、インデックスは共に固定長の項目)
非常に驚いたのは、テーブルサイズの減少です。
半分以下です。
これはパフォーマンスの向上を期待できます。
(データ量が減れば、共有メモリ(Shared buffers)を占有する容量が減るので、キャッシュヒット率が上がります)。
また、"Let's Postgres"にはcharacterの型変換による性能劣化の一例が上がっています。
ここら辺も調査して、実際にデータ型の変更を行いたいと思います。
現在、勤め先でお守りしてるDBにPostgreSQL7.4(以下、Postgres)があります。
このPostgresは総レコード数200万件でDBサイズが4GBありますが、前任者不在の状態で引き継いだため不思議仕様が結構あります。
例えば、データ型の定義の仕方。
一例を挙げると、文字データ型が全部character(一部text)だったりします。
ちなみにcharacterで定義してあるので、取得後にTrimして使ってます(．_．)。
Postgresの公開文書を見ると、character、varcharに性能の違いはないと記載されています。
(参考URL:http://www.postgresql.jp/document/current/html/datatype-character.html)
また、非常に参考になったのが"Let's Postgres"の「文字列型の使い分け」のページです。
(参考URL:http://lets.postgresql.jp/documents/technical/text-processing/1/)
このページでは、データ型ごとに消費するバイト数が異なるので、それぞれ適したものを使用しましょうと記載してあります。
当たり前のことですが、わかりやすいです。
でも考えてもピンとこなかったので、テスト環境で単純にcharacterとvarcharのサイズの違いを比べてみました。
今回テストに使用したテーブルの概要です。
・レコード数：35万件
・テーブルサイズ：315MB
・全カラム数：49
・文字データ型のカラム数：35
・文字データ型の文字数：599文字(Postgresはバイト数ではなく文字数で定義します)
※ちなみにほとんどの項目が定義した文字数の半分も消費しません
別名でテーブル(characterをvarchar(character varying)に定義して、ダンプしたデータを流し込んでみると、
・テーブルサイズが、315MB→143MBへ減少
・キーとインデックスのサイズが増加(キー：15MB→21MB、インデックス：13MB→15MB)
となりました(どちらもvacuum full、reindex実施)。
テスト結果と参考URLの記事を元に考察すると、
・セットする文字数が変動するものは、varcharの方がサイズを小さくできる(可能性有り)
・固定長のデータをvarcharで格納するとデータサイズが大きくなる可能性有り(キー、インデックスは共に固定長の項目)
非常に驚いたのは、テーブルサイズの減少です。
半分以下です。
これはパフォーマンスの向上を期待できます。
(データ量が減れば、共有メモリ(Shared buffers)を占有する容量が減るので、キャッシュヒット率が上がります)。
また、"Let's Postgres"にはcharacterの型変換による性能劣化の一例が上がっています。
ここら辺も調査して、実際にデータ型の変更を行いたいと思います。