postgreSQLで大量のレコードをロードする際に気をつけたこと

仕事で数億のレコードをテーブルにロードする機会がありました。初めての経験で戸惑うことも多かった為、忘備録として残します。

環境

Windows10
PostgreSQL13

気にしなければならないこと

①大量のデータをロードすると、どうしても時間がかかってしまいます。なので、その時間をいかに短縮するか。
②レコード数が多いと、何かしらの不備でレコードのロードミスが発生する可能性もある。確認が必要。
③Cドライブの容量不足にならないか確認。

①の解決方法。

少しでもロードを早くする為に、インデックスをつける際にはデータをロードした後に追加すると良い。
ここでいうインデックスは、プリマリーキーも含まれる。

②の解決方法

ロードが終わった際に、総レコード数の件数（SELECT　COUNT)と、NULLがないかのコマンドを使用する。

③の解決方法。

テーブルスペースを使用して、他の場所にデータをロードする。
参照：　https://qiita.com/taxi-btn/items/32a890f08d0944a09d17

データベースに接続

postgres-# \c db201225
データベース"db201225"にユーザ"postgres"として接続しました。
db201225-#

テーブルの作成

CREATE TABLE testtable (
  red bigint not null,
  blue varchar(10000) not null,
  yellow varchar(10000) not null,
  green varchar(10000) not null,
  pink numeric not null,
  black integer not null,
  white varchar(10000) not null
);

ここでは、テーブルとカラムを作成するが、インデックスはつけない。上記で書いたように、先にインデックスをつけてしまうとロード時間が長くなってします。PKも同様。

あとは、not nullも後付けの方が良いのかなあなんて思っています。万が一レコードにNULLが発生したらエラーになってしまうので。

データをロードする

COPY testtable FROM 'D:\color.csv' with csv header;

ここでは、Dドライブに入っているcolor.csvのレコードをロードするコマンドを書いています。

インデックスをつける

CREATE INDEX ON testtable (red);
CREATE INDEX ON testtable (blue);
CREATE INDEX ON testtable (yellow);

インデックスの種類を指定しないのであれば、デフォルトでB-treeになる。
選ぶ基準が難しいので、B-treeでいいんじゃなかと思ってしまいます。わかりやすい記事などあれば教えて欲しいです。

レコードの件数を数える

SELECT COUNT(*) FROM testtable;

• ←このワイルドカードで全てを表します。

NULLがないか確認する。

SELECT COUNT(red)FROM testtable;

testtableテーブルのredカラムのカウント数を数えることで、レコード数と見比べて数が間違っていないか調べることができる。

エラーが出ていないかログで確認する為、ログファイルの設定（confファイルをいじるのは自己責任で！）

※順番が前後してしまいますが、ロード前に修正してください。

設定ファイル　C:\Program Files\PostgreSQL\13\data 直下にあるpostgresql.confファイルを使用。

ファイルが開かなければ、7-zipとsublimeの併用で開く。

①修正１：

420行目あたりにある、REPORTING AND LOGGING項目で

log_destination = 'stderr'にする。

②修正２：

420行目あたりにある、

logging_collector = onにする。（ファイル出力の設定）

③修正３：

558行目あたりにある、

log_statement = "all"にする。（どのSQL文をログに記録するかを制御する）。

ログの確認方法

C:\Program Files\PostgreSQL\13\data\log\対象のファイルをクリック。
中にログが記載されている。

文字ばけしている可能性有。

もし文字化けしていたら、

postgresql.conf内で、以下のように変更をする。

【変更前】
lc_messages = 'Japanese_Japan.932'

【変更後】
lc_messages = 'en_US'
(Ubuntuの場合は'en_US.UTF8'のようです）

※変更はPostgreSQLを再起動すると反映されます。

logを確認することで、正常にロードができているか確認することができる。