PostgreSQLに簡単にラージサイズのテーブルを作る方法

現在取り組んでいる仕事で、かなり大きいサイズのデータベーステーブルを作る必要がありました。正直言ってそれを簡単に作る方法はいくつかあるのですが、テーブルのフィールド（表計算ソフトで言うところのセル）に乱数が入っていたり、aとかbとかcとかいう適当な文字列が入っていたり...といったようなデータは用意できるのですが、それをSELECTしてもなんか面白くありませんし、そのデータを使って集計処理とか色々するにもちょっと都合が悪いので、PostgreSQLに簡単にラージサイズのテーブルを作るものを作ってみました。

データベースとテーブルの作成

データベースを作成して、それに切り替えます。

=# CREATE DATABASE testdb;
=# \c testdb

次に、テーブルを作成します。複数のテーブルを使って集計処理をするなら、同じ感じでテーブル名だけ違うものを複数用意しておきます。

=# CREATE TABLE uriage
(
  number SERIAL,
  name VARCHAR(128) NOT NULL,
  okashi VARCHAR(128) NOT NULL,
  amount_sold INTEGER NOT NULL
);

テーブルにデータの投入

次のようなコードで、テーブルにデータを投入します。arrayでデータを複数定義しておいて、その後の[ceil(random() * 4)]で適当に選択した文字列がGENERATE_SERIESで指定したカラム分だけ生成されます。

=# INSERT INTO
  uriage (name,okashi,amount_sold)  
SELECT
  (array['Alice', 'Jane', 'Ted', 'Bob'])[ceil(random() * 4)] AS name,
  (array['Candy', 'Cookie', 'Chocolate', 'Icecream'])[ceil(random() * 4)] AS okashi,     
  ceil(random() * 100) *10 amount_sold
FROM
  GENERATE_SERIES(1, 10);

あとはこの数を無限に増やしていけば、そのカラムの分だけサイズの大きいテーブルを作成できるというわけです。上のやつをテーブル名と追加するカラム数を変えて実行していけば、その分だけ増えていきます。

例えばこんな感じですね。

testdb=# SELECT COUNT(*) FROM  uriage;
   count   
-----------
 130000000
(1 行)

testdb=# \dt+
                              リレーション一覧
 スキーマ |     名前     |  タイプ  |  所有者  | 永続性 |   サイズ   | 説明 
----------+--------------+----------+----------+--------+------------+------
 public   | uriage       | テーブル | postgres | 永続   | 6471 MB    | 
 public   | uriage2      | テーブル | postgres | 永続   | 1493 MB    | 
 public   | uriage3      | テーブル | postgres | 永続   | 1493 MB    | 
(4 行)

データは次のような感じで利用できます。

testdb=# SELECT * FROM  uriage LIMIT 5;
  number   | name  |  okashi   | amount_sold 
-----------+-------+-----------+-------------
 129978417 | Ted   | Chocolate |         580
 129978418 | Bob   | Candy     |         140
 129978419 | Jane  | Chocolate |         900
 129978420 | Bob   | Icecream  |         930
 129978421 | Alice | Cookie    |         410
(5 行)

時間: 0.474 ミリ秒

testdb=# SELECT sum(uriage.amount_sold + uriage2.amount_sold + uriage3.amount_sold) FROM uriage,uriage2,uriage3 WHERE uriage.number = uriage2.number AND uriage2.number = uriage3.number;
     sum     
-------------
 45448135670
(1 行)

時間: 15198.594 ミリ秒(00:15.199)

SQLファイルを使った自動化

ここではCLIでデータベース、テーブル、データの投入までを行っていますが、普段はSQLファイルに書いてリモートから実行しています。

• sql1.sql

CREATE TABLESPACE nvme LOCATION '/opt/nvme'; 
CREATE DATABASE testdb TABLESPACE nvme;

• sql2.sql

CREATE TABLE uriage
(
  number SERIAL,
  name VARCHAR(128) NOT NULL,
  okashi VARCHAR(128) NOT NULL,
  amount_sold INTEGER NOT NULL
);

CREATE TABLE uriage2
(
  number SERIAL,
  name VARCHAR(128) NOT NULL,
  okashi VARCHAR(128) NOT NULL,
  amount_sold INTEGER NOT NULL
);

CREATE TABLE uriage3
(
  number SERIAL,
  name VARCHAR(128) NOT NULL,
  okashi VARCHAR(128) NOT NULL,
  amount_sold INTEGER NOT NULL
);

INSERT INTO
  uriage (name,okashi,amount_sold)
SELECT
  (array['Alice', 'Jane', 'Ted', 'Bob'])[ceil(random() * 4)] AS name,
  (array['Candy', 'Cookie', 'Chocolate', 'Icecream'])[ceil(random() * 4)] AS okashi,
  ceil(random() * 100) *10 amount_sold
FROM
  GENERATE_SERIES(1, 30000000);

INSERT INTO
  uriage2 (name,okashi,amount_sold)
SELECT
  (array['Alice', 'Jane', 'Ted', 'Bob'])[ceil(random() * 4)] AS name,
  (array['Candy', 'Cookie', 'Chocolate', 'Icecream'])[ceil(random() * 4)] AS okashi,
  ceil(random() * 100) *10 amount_sold
FROM
  GENERATE_SERIES(1, 30000000);

INSERT INTO
  uriage3 (name,okashi,amount_sold)
SELECT
  (array['Alice', 'Jane', 'Ted', 'Bob'])[ceil(random() * 4)] AS name,
  (array['Candy', 'Cookie', 'Chocolate', 'Icecream'])[ceil(random() * 4)] AS okashi,
  ceil(random() * 100) *10 amount_sold
FROM
  GENERATE_SERIES(1, 30000000);

• クエリの実行など psqlコマンドツールを使ったDBへのアクセスはpg_hba.confへの接続許可のための設定が必要です。

//DBの作成
psql -h 172.17.28.66 -U postgres -d testdb -f  ~/test/sql1.sql

//テーブル作成とデータ投入
psql -h 172.17.28.66 -U postgres -d testdb -f  ~/test/sql2.sql

今回はPostgreSQLに簡単にラージサイズのテーブルを作る方法のご紹介でした。