初めてのPostgreSQLメモ

はじめに

普段MySQLを使っている人間が初めてPostgreSQLを触ってみたので、調べたことを整理して書いておきます。インストールしてテーブルを作るという本当に基本のところから、開発運用しているとどうしても気になってくるALTER TABLE周りまで。

インストー

Macを使っているのでhomebrewで入れました。

$ brew install postgresql

バージョンは9.6.3でした。

$ postgres --version
postgres (PostgreSQL) 9.6.3

データベースクラスタの作成

PostgreSQLにはデータベースクラスタというのがあって、その中にデータベースを作るという感じになっている。データベースクラスタを作るにはinitdbを使う。またはpg_ctlを使う。brew install postgresqlした時にすでに実行されていた。

==> /usr/local/Cellar/postgresql/9.6.3/bin/initdb /usr/local/var/postgres

ここで作成されたディレクトリを使ってサーバープロセスを起動することになる。

サーバーの起動

フォアグラウンドで起動

$ postgres -D /usr/local/var/postgres

バックグラウンドで起動

$ pg_ctl start -D /usr/local/var/postgres

launchdを使って起動。この場合はMacを再起動した後も自動的に起動してくれる。

$ brew services start postgresql

サーバープロセスはデフォルトで5432ポートをLISTENしている。

データベースの作成

createdbを使う。これはCREATE DATABASEというSQLを実行するのと一緒。

$ createdb test

データベースへの接続

psqlを使う。

$ psql test

psqlはデータベースに接続してSQLを実行するためのアプリケーションだけど、メタコマンドというのがたくさんあって面白そう。

テーブルの作成

これは普通。serialというのは連番型でMySQLのAUTO_INCREMENTみたいなもの。

$ cat create_users.sql
create table users(
    id serial not null,
    name varchar(255) not null,
    primary key (id)
);
$ cat create_users.sql | psql test
CREATE TABLE

psqlのメタコマンド

メタコマンドをいくつか試してみる。

テーブル一覧を表示 \dt

test=# \dt
         List of relations
 Schema | Name  | Type  |   Owner
--------+-------+-------+-----------
 public | users | table | takatoshi
(1 row)

+をつけるとSizeとDescriptionが表示される。

test=# \dt+
                     List of relations
 Schema | Name  | Type  |   Owner   |  Size   | Description
--------+-------+-------+-----------+---------+-------------
 public | users | table | takatoshi | 0 bytes |
(1 row)

\dなんとかという\d系のコマンドはたくさんあって何かを一覧表示するためのコマンドになっている様子。また単に\dと打った場合は\dtvsEと同じ意味になってテーブル、ビュー、シーケンス、外部テーブルの一覧が表示される。

test=# \d
              List of relations
 Schema |     Name     |   Type   |   Owner
--------+--------------+----------+-----------
 public | users        | table    | takatoshi
 public | users_id_seq | sequence | takatoshi
(2 rows)

SQLにかかる時間を表示 \timing

MySQLだと何もしなくても出るやつ。

test=# \timing
Timing is on.
test=# select * from users;
 id | name
----+------
  1 | 太郎
  2 | 花子
  3 | 二郎
  4 | カズ
  5 | Mark
(5 rows)

Time: 0.226 ms

結果の表示形式を変える \pset

MySQLみたいにする。デフォルトはborder 1

test=# \pset border 2
Border style is 2.
test=# select * from users;
+----+------+
| id | name |
+----+------+
|  1 | 太郎 |
|  2 | 花子 |
|  3 | 二郎 |
|  4 | カズ |
|  5 | Mark |
+----+------+
(5 rows)

Time: 0.196 ms

formatをHTMLにする。デフォルトはformat align

test=# \pset format html
Output format is html.
test=# select * from users;
<table border="2">
  <tr>
    <th align="center">id</th>
    <th align="center">name</th>
  </tr>
  <tr valign="top">
    <td align="right">1</td>
    <td align="left">太郎</td>
  </tr>
  <tr valign="top">
    <td align="right">2</td>
    <td align="left">花子</td>
  </tr>
  <tr valign="top">
    <td align="right">3</td>
    <td align="left">二郎</td>
  </tr>
  <tr valign="top">
    <td align="right">4</td>
    <td align="left">カズ</td>
  </tr>
  <tr valign="top">
    <td align="right">5</td>
    <td align="left">Mark</td>
  </tr>
</table>
<p>(5 rows)<br />
</p>
Time: 0.208 ms

MySQL\Gみたいに左に列名、右にデータという2つの列で出力(拡張モードを有効にする)

test=# \pset x
Expanded display is on.
test=# select * from users limit 3;
-[ RECORD 1 ]
id   | 1
name | 太郎
-[ RECORD 2 ]
id   | 2
name | 花子
-[ RECORD 3 ]
id   | 3
name | 二郎

Time: 0.251 ms

ALTER TABLE

ALTER TABLE - PostgreSQL 9.6.3文書

ALTER TABLEは基本的にはACCESS EXCLUSIVEロックを取得する。これは読み込みもブロックする。ただし操作によっては異なる場合もある。

カラム追加

DEFAULT句がない場合はメタデータの変更のみとなり、テーブルのデータ変更が必要ないので、ロックかからない。

カラム削除

これは短時間で終了する。列を物理的には削除せず不可視にするだけ。あとで更新がかかった時に領域が回収されるらしい。

CREATE INDEX

CREATE INDEX - PostgreSQL 9.6.3文書

SHAREロックを取得する。これは書き込みをブロックする。CONCURRENTLYオプションを使うとロックがかからないが、余計な処理をするので余計な負荷がかかる。

Railsのコードを見たらCONCURRENTLYがついていた。

ロック

ACCESS EXCLUSIVE、SHAREなどのロックについては、ここに書いてある。

13.3. 明示的ロック - PostgreSQL 9.6.3文書

ALTER TABLEとCREATE INDEXの実験

やっぱり実際に試してみた方がいいので実験してみる。まずは適当に800万レコードくらい作っておく。

$ echo 'truncate table users' | psql test
TRUNCATE TABLE
$ echo 'select * from users' | psql test
 id | name | created_at
----+------+------------
(0 rows)

$ cat insert_users.sql
insert into users(name,created_at) values('あああいいい',now());
insert into users(name,created_at) select name,created_at from users;
insert into users(name,created_at) select name,created_at from users;
insert into users(name,created_at) select name,created_at from users;
insert into users(name,created_at) select name,created_at from users;
insert into users(name,created_at) select name,created_at from users;
insert into users(name,created_at) select name,created_at from users;
insert into users(name,created_at) select name,created_at from users;
insert into users(name,created_at) select name,created_at from users;
insert into users(name,created_at) select name,created_at from users;
insert into users(name,created_at) select name,created_at from users;
insert into users(name,created_at) select name,created_at from users;
insert into users(name,created_at) select name,created_at from users;
insert into users(name,created_at) select name,created_at from users;
insert into users(name,created_at) select name,created_at from users;
insert into users(name,created_at) select name,created_at from users;
insert into users(name,created_at) select name,created_at from users;
insert into users(name,created_at) select name,created_at from users;
insert into users(name,created_at) select name,created_at from users;
insert into users(name,created_at) select name,created_at from users;
insert into users(name,created_at) select name,created_at from users;
insert into users(name,created_at) select name,created_at from users;
insert into users(name,created_at) select name,created_at from users;
insert into users(name,created_at) select name,created_at from users;
$ cat insert_users.sql | psql test
INSERT 0 1
INSERT 0 1
INSERT 0 2
INSERT 0 4
INSERT 0 8
INSERT 0 16
INSERT 0 32
INSERT 0 64
INSERT 0 128
INSERT 0 256
INSERT 0 512
INSERT 0 1024
INSERT 0 2048
INSERT 0 4096
INSERT 0 8192
INSERT 0 16384
INSERT 0 32768
INSERT 0 65536
INSERT 0 131072
INSERT 0 262144
INSERT 0 524288
INSERT 0 1048576
INSERT 0 2097152
INSERT 0 4194304
$ echo 'select count(*) from users' | psql test
  count
---------
 8388608
(1 row)

$ echo 'select * from users limit 3' | psql test
 id |     name     |         created_at
----+--------------+----------------------------
  6 | あああいいい | 2017-06-30 23:36:08.271341
  7 | あああいいい | 2017-06-30 23:36:08.271341
  8 | あああいいい | 2017-06-30 23:36:08.271341
(3 rows)

カラム追加

まずはDEFAULT句なしのケースから。これは早いはず。

test=# alter table users add column email varchar(255);
ALTER TABLE
Time: 20.960 ms

次にDEFAULT句ありのケース。これはデータ変更を伴うので遅いはず。そしてロックするので、alter tableを発行してすぐに別コンソールからselectして実験。

test=# alter table users add column status int not null default 0;
ALTER TABLE
Time: 8921.286 ms

この時selectは

test=# select * from users limit 1;
 id |     name     |         created_at         | email | status
----+--------------+----------------------------+-------+--------
  6 | あああいいい | 2017-06-30 23:36:08.271341 |       |      0
(1 row)

Time: 8303.237 ms

ブロックされていた。

カラム削除

早い。

test=# alter table users drop column status;
ALTER TABLE
Time: 2.729 ms

インデックス追加

まずはCONCURRENTLYなしでinsertがブロックされることを確認したい。create indexを発行してすぐ、別のコンソールでinsertを発行する。

test=# create index on users (name);
CREATE INDEX
Time: 5124.442 ms

insertはブロックされていた。

test=# insert into users(name) values('テスト');
INSERT 0 1
Time: 4490.800 ms

作ったインデックスは消しておく。

test=# drop index users_name_idx;
DROP INDEX
Time: 20.238 ms

次はCONCURRENTLYオプションをつけて同じことをする。

test=# create index concurrently on users (name);
CREATE INDEX
Time: 6854.765 ms

少し実行時間が長い気がする。そしてinsertはブロックされなかった。

test=# insert into users(name) values('テスト');
INSERT 0 1
Time: 1.817 ms

まとめ

ALTER TABLEについてはMySQL 5.6以降のオンラインDDL機能の方が優れているけど、一方でPostgreSQLの方が優れている部分もあるだろうので、引き続き勉強していく。違いを意識しながら知識を得るの楽しい。

PostgreSQL 9.3がベースと少し古いですが、ひとまず以下の書籍を買ったので読んでみます。

内部構造から学ぶPostgreSQL 設計・運用計画の鉄則 (Software Design plus)

内部構造から学ぶPostgreSQL 設計・運用計画の鉄則 (Software Design plus)