初めての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)
- 作者: 勝俣智成,佐伯昌樹,原田登志
- 出版社/メーカー: 技術評論社
- 発売日: 2014/09/04
- メディア: 単行本(ソフトカバー)
- この商品を含むブログ (2件) を見る