Category: PostgreSQL

pg-repackはテーブルの再編成を行えるPostgreSQLの拡張ツール
VACUUM FULLはテーブルの排他ロックがかかり、実行に長い時間がかかる
pg-replackは短い間しか排他ロックをかけないため、リリース戦略としては有効

pg_repackの再編成の種類
– オンラインCLUSTER(テーブルデータを物理的にINDEX順で並び替え)
– 指定カラムでレコードを並び替える
– オンラインVACUUM FULL(レコードの隙間を詰める)
– 指定テーブルのインデックス再構築・再配置

pg_repack

postgres=# \dn
List of schemas
Name | Owner
——–+———-
bank1 | postgres
bank2 | postgres
public | postgres
(3 rows)

スキーマ全体にcluster, vacuum full
postgres=# pg_repack bank1
カラムを指定してレコードを並び替え
pg_repack -o role_id -t user test_schema
pg_repack -o role_id,id -t user test_schema
テーブルを指定してVACUUM FULLを実行
pg_repack -n -t foo test_schema
pg_repack -n -t foo -t bar test_schema
pg_repack -x -t users test_schema

PostgreSQLが追記型アーキテクチャとなっているためファイルサイズが大きくなると肥大化する
auto vaccumがonになっている

VACUUM FULL, REINDEXは気軽に実行できない
VACUUM FULL実行中は排他ロックがかかるのでテーブル参照処理もできなくなる
pg_repackは排他ロックを瞬間的にだけ掛けて、必要最小限だけのロックとなるため、参照・更新処理が実施できる

テーブルを作成したのちにテーブルの主キー制約を変更したいことがある
その時に、ALTER TABLE ~ DROP CONSTRAINT と ALTER TABLE ~ ADD CONSTRAINT を使用する

### テーブル作成
CREATE TABLE test(a int PRIMARY KEY, b int, c int);

### 主キー制約の削除
名前をつけていなければ テーブル名_pkey でOK
ALTER TABLE test DROP CONSTRAINT test_pkey;

not nullの削除の方法
ALTER TABLE test ALTER COLUMN a DROP NOT NULL;

### 主キー制約の追加
ALTER TABLE test ADD CONSTRAINT test_pkey PRIMARY KEY(a, b);

データベースの機能の一つで、あるユーザが参照しているレコードに他のユーザがアクセスできないようにする

FOR UPDATE … 自分が更新する目的で行をロックしておく
FOR SHARE …他からの更新がかからないように行をロック

$ sudo -u postgres psql
psql (14.2 (Ubuntu 14.2-1.pgdg20.04+1))
Type “help” for help.

postgres=# \c testdb;
You are now connected to database “testdb” as user “postgres”.
testdb=#

CREATE TABLE book (
id serial NOT NULL,
name VARCHAR(30) NOT NULL,
author VARCHAR(30) NOT NULL,
age integer,
PRIMARY KEY (id)
);

INSERT INTO book(name, author, age) VALUES (‘SQL入門’, ‘Masumi’, 25);
INSERT INTO book(name, author, age) VALUES (‘Flutter入門’, ‘Masumi’, 25);
INSERT INTO book(name, author) VALUES (‘HTML入門’, ‘Hiro’);
INSERT INTO book(name, author, age) VALUES (‘Github入門2’, ‘Emma’, 22);
INSERT INTO book(name, author, age) VALUES (‘Flutter上級’, ‘Oliva’, 45);

testdb=# select * from book;
id | name | author | age
—-+————-+——–+—–
1 | SQL入門 | Masumi | 25
2 | Flutter入門 | Masumi | 25
3 | HTML入門 | Hiro |
4 | Github入門2 | Emma | 22
5 | Flutter上級 | Oliva | 45
(5 rows)

### BEGIN / COMMITとは？
beginはトランザクションを開始するコマンド。
beginが実行されると、自動コミットモードが解除され、それ以降のSQL文による変更が確定しない状態になる。beginをトランザクションを張ると表現することがある。
commitはトランザクションを終了し、変更を確定する

testdb=# begin;
BEGIN
testdb=*# update book set age=age+10 where author=’Emma’;
UPDATE 1
testdb=*# update book set age=age-10 where author=’Oliva’;
UPDATE 1
testdb=*# commit;
COMMIT
testdb=# select * from book;
id | name | author | age
—-+————-+——–+—–
1 | SQL入門 | Masumi | 25
2 | Flutter入門 | Masumi | 25
3 | HTML入門 | Hiro |
4 | Github入門2 | Emma | 32
5 | Flutter上級 | Oliva | 35
(5 rows)

ROLLBACKはトランザクションを終了し、変更を取り消すコマンド

beginとcommitはセットで見た方が良さそうやね

トランザクションとはデータベースのデータを利用するときに、複数の処理を一連のものとしてまとめる単位のこと
– Aさんの銀行口座から500万円出金
– Bさんの銀行口座に500万円入金

一連の処理が問題なく行われたときはコミットで終了、途中エラーが発生したときはロールバック
DBのトランザクションはACID特性がある
Atomicity … 完全に実行されるか
Consistency … 実行前後でデータの整合性が担保
Isolation … コミットが確実に保存される

### トランザクションのコマンド
BEGIN
START TRANSACTION
COMMIT
END
ROLLBACK
ABORT

### 分離レベル
READ UNCOMMITED
READ COMMITED
REPEATABLE READ
SERIALIZABLE

なるほど、概念はなんとなく理解した

まずテーブルを作成します。

create table myschema.product (
  name varchar(10), 
  price integer, 
  stock integer
);

続いてデータを挿入します

insert into product values
  ('Orange', 200, 12),
  ('Melon', 450, 6),
  ('Grape', 320, 8),
  ('Apple', 180, 14),
  ('Peach', 380, 5);

testdb=# select * from product;
name | price | stock
——–+——-+——-
Orange | 200 | 12
Melon | 450 | 6
Grape | 320 | 8
Apple | 180 | 14
Peach | 380 | 5
(5 rows)

testdb=# update product set price=340 where name=’Grape’;
UPDATE 1
testdb=# select * from product;
name | price | stock
——–+——-+——-
Orange | 200 | 12
Melon | 450 | 6
Apple | 180 | 14
Peach | 380 | 5
Grape | 340 | 8

testdb=# update product set price=price-50 where stock < 10; UPDATE 3 testdb=# select * from product; name | price | stock --------+-------+------- Orange | 200 | 12 Apple | 180 | 14 Melon | 400 | 6 Peach | 330 | 5 Grape | 290 | 8 (5 rows) 複数更新する場合も条件式でできるのね。

テーブルとテストデータ作成
CREATE TABLE companies(
id bigint primary key,
name varchar(50) NOT NULL,
tel varchar(50)
);

CREATE TABLE employees(
id bigint primary key,
company_id bigint,
name varchar(50) NOT NULL,
age integer,
FOREIGN KEY (company_id) references companies(id)
);

INSERT INTO companies(id, name, tel) VALUES
(1, ‘株式会社ほげ’, ’00-0000-0000′),
(2, ‘株式会社ふが’, ’00-0000-1111′),
(3, ‘株式会社ぴよ’, ’00-0000-2222′);

INSERT INTO employees(id, company_id, name, age) VALUES
(1, 1, ‘テスト太郎’, 25),
(2, 1, ‘テスト次郎’, 20),
(3, 2, ‘テスト三郎’, 30),
(4, null, ‘テスト五郎’, 40);

### 内部結合(inner join)
結合条件に一致するレコードだけ表示
testdb=# SELECT
testdb-# e.id AS employee_id,
testdb-# e.name AS employee_name,
testdb-# c.id AS company_id,
testdb-# c.name AS company_name
testdb-# FROM employees e
testdb-# JOIN companies c ON c.id = e.company_id;
employee_id | employee_name | company_id | company_name
————-+—————+————+————–
1 | テスト太郎 | 1 | 株式会社ほげ
2 | テスト次郎 | 1 | 株式会社ほげ
3 | テスト三郎 | 2 | 株式会社ふが

### LEFT JOIN
左外部結合。左テーブルを全て表示し、結合条件に一致しない右テーブルはNULLとして結合
testdb=# SELECT
testdb-# e.id AS employee_id,
testdb-# e.name AS employee_name,
testdb-# c.id AS company_id,
testdb-# c.name AS company_name
testdb-# FROM employees e
testdb-# LEFT JOIN companies c ON c.id = e.company_id;
employee_id | employee_name | company_id | company_name
————-+—————+————+————–
1 | テスト太郎 | 1 | 株式会社ほげ
2 | テスト次郎 | 1 | 株式会社ほげ
3 | テスト三郎 | 2 | 株式会社ふが
4 | テスト五郎 | |

### LIGHT JOIN
testdb=# SELECT
testdb-# e.id AS employee_id,
testdb-# e.name AS employee_name,
testdb-# c.id AS company_id,
testdb-# c.name AS company_name
testdb-# FROM employees e
testdb-# RIGHT JOIN companies c ON c.id = e.company_id;
employee_id | employee_name | company_id | company_name
————-+—————+————+————–
1 | テスト太郎 | 1 | 株式会社ほげ
2 | テスト次郎 | 1 | 株式会社ほげ
3 | テスト三郎 | 2 | 株式会社ふが
| | 3 | 株式会社ぴよ
(4 rows)

なるほど、これは勉強になる。

create table person (
	id int,
	first_name varchar(50),
	last_name varchar(50),
	gender varchar(7),
	birth_day date
);

testdb=# \d person
Table “public.person”
Column | Type | Collation | Nullable | Default
————+———————–+———–+———-+———
id | integer | | |
first_name | character varying(50) | | |
last_name | character varying(50) | | |
gender | character varying(7) | | |
birth_day | date | | |

### テーブルに制約をつける

create table person (
	id BIGSERIAL not null primary key,
	first_name varchar(50) not null,
	last_name varchar(50) not null,
	gender varchar(7) not null,
	birth_day date not null,
	email varchar(150)
);

bigserial 整数を自動決定
primary key キーとして扱う
not null 空白を許可しない

### テーブルにデータを挿入

insert into person (
	first_name,
	last_name,
	gender,
	birth_day
) values ('Anne', 'Smith', 'Femail', DATE '1988-01-09');

insert into person (
	first_name,
	last_name,
	gender,
	birth_day,
	email
) values ('Jake', 'Jone', 'Male', DATE '1990-01-10', 'jake@gmail.com');

testdb=# select * from person;
id | first_name | last_name | gender | birth_day | email
—-+————+———–+——–+————+—————-
1 | Anne | Smith | Femail | 1988-01-09 |
2 | Jake | Jone | Male | 1990-01-10 | jake@gmail.com
(2 rows)

データの自動生成
https://mockaroo.com/

create table person (
	id BIGSERIAL not null primary key,
	first_name varchar(50) not null,
	last_name varchar(50) not null,
	gender varchar(7) not null,
	email varchar(150),
	date_of_birth date not null,
	country varchar(50) not null
);

$ \i /home/vagrant/dev/app/person.sql
$ select * from person;

create table person (
	id BIGSERIAL not null primary key,
	first_name varchar(50) not null,
	last_name varchar(50) not null,
	gender varchar(7) not null,
	email varchar(150),
	date_of_birth date not null,
	country varchar(50) not null,
	car_id BIGINT REFERENCES car (id),
	unique(car_id)
);

create table car (
	id BIGSERIAL not null primary key,
	make varchar(50) not null,
	model varchar(50) not null,
	price varchar(50) not null
);

BIGINT REFERENCESで関連づけるのか。なるほど、勉強になるね。

テーブルを作成
$ CREATE TABLE test(image bytea);

bytea型はバイナリデータを保存。1Gまで可能
https://www.postgresql.jp/document/9.6/html/datatype-binary.html

画像データの登録

$dsn = "pgsql:dbname=testdb host=localhost port=5432";
$user = "user1";
$password = "password";

try {
	$dbh = new PDO($dsn, $user, $password);
	print('connection success<br>');

	$stmt = $dbh->prepare("insert into test (image) values (:image)");

	$fp = fopen("images.jpeg", "rb");
	$stmt->bindParam(':image', $fp, PDO::PARAM_LOB);
	$stmt->execute();

} catch(PDOException $e){
	print("Error:".$e->getMessage());
	die();
}

画像の表示

$dsn = "pgsql:dbname=testdb host=localhost port=5432";
$user = "user1";
$password = "password";

try {
	$dbh = new PDO($dsn, $user, $password);

	$stmt = $dbh->prepare("select image from test");
	$stmt->execute();
	$stmt->bindColumn("image", $image, PDO::PARAM_LOB);
	$stmt->fetch();
	header("Content-Type: image/jpeg");
	fpassthru($image);

	unset($db);
} catch(PDOException $e){
	print("Error:".$e->getMessage());
	die();
}

なるほど、DBに画像保存も可能なのね。
データサイズが気になるが、軽量な画像であれば良さそう

PostgreSQLは削除フラグがついて見えなくなっているだけのため、削除データを定期的にきれいにする必要があり、この処理をVACUUMという。

$ psql –version
psql (PostgreSQL) 14.2 (Ubuntu 14.2-1.pgdg20.04+1)
$ sudo -u postgres psql
psql (14.2 (Ubuntu 14.2-1.pgdg20.04+1))
Type “help” for help.

postgres=# \l
postgres=# \c testdb;
testdb=# \d department;
testdb=# select * from department;

testdb=# delete from department where department_code = ‘c’;
testdb=# vacuum verbose department;
INFO: vacuuming “public.department”
INFO: scanned index “pk_department” to remove 1 row versions
DETAIL: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
INFO: table “department”: removed 1 dead item identifiers in 1 pages
DETAIL: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
INFO: index “pk_department” now contains 5 row versions in 2 pages
DETAIL: 1 index row versions were removed.
0 index pages were newly deleted.
0 index pages are currently deleted, of which 0 are currently reusable.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
INFO: table “department”: found 1 removable, 5 nonremovable row versions in 1 out of 1 pages
DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 747
Skipped 0 pages due to buffer pins, 0 frozen pages.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
VACUUM

vacuum ${table_name} でテーブルをvacuumできる。
なるほど。

autovacuumの設定はpostgres.confで設定されている。