今日は、データ型の話をしたいと思います。
現在、勤め先でお守りしてる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の型変換による性能劣化の一例が上がっています。
ここら辺も調査して、実際にデータ型の変更を行いたいと思います。
“My”アフィリエイト スカウト事務局の松嶋と申します。
私はNTTコミュニケーションズが運営する“My”アフィリエイトの広告プログラムを優良サイト様へご紹介する活動をしております。
貴サイトを拝見し、日常生活が楽しくなるサイトだと感じました。
インテリアや家電など、今人気の広告バナー掲載にぜひご協力いただけないでしょうか。
詳細をご覧いただいてからご判断いただいて構いません。
特別報酬もありますので、ご興味がございましたら以下のURLから詳細をご確認いただければ幸いです。
http://www.affiliate.ntt.com/topics/scout/
※申込フォームには下記の情報をご入力ください。
エントリーコード『176』 、サイトURL『http://blog.goo.ne.jp/self-style』
※2/18までに広告掲載にご協力いただいた方が特別報酬の対象となります
この度の連絡で不快な思いをされた方には大変申し訳ございません。お詫び申し上げます。