【PostgreSQL】DELETEしてもディスク容量が減らない挙動を検証してみた
最近 PostgreSQL を触っていて、テーブルのレコードを DELETE しても
テーブルのディスクサイズ自体は変化しないということを知りました。
このサイズが変化しない挙動が気になったので、今回は実際に手を動かして確認してみます。
以下の流れで検証します。
- 10 万件レコードを持ったテーブルを準備
- 5 万件を DELETE する
- VACUUM を実行する
- VACUUM FULL を実行する
それぞれの操作後にディスクサイズとタプル（行）の件数を確認し、何が起きているのかを見ていきます。
いきなりまとめ
後述する検証で確認した結果です。
DELETE を実行してもディスクサイズは変わらず、
ディスク領域が解放されるのは VACUUM FULL を実行したときでした。
| ディスクサイズ | live tuple | dead tuple | ||
|---|---|---|---|---|
| DELETE前 | 6280 kB | 100,000 | 0 | |
| DELETE後 | 6280 kB | 50,000 | 50,000 | <- サイズ変わらず、dead tuple が増加 |
| VACUUM後 | 6280 kB | 50,000 | 0 | <- dead tuple は消えたが、サイズは変わらず |
| VACUUM FULL後 | 3152 kB | 50,000 | 0 | <- サイズが約半分に減少 |
実行環境
検証のための実行環境は EC2（AL2023）にインストールした PostgreSQL を使用しました。
EC2 に PostgreSQL をインストールする手順としては下記をご参照ください。
PostgreSQL 17.8 を利用し、検証していきます。
[ec2-user@ip-10-0-13-178 ~]$ sudo -u postgres psql
psql (17.7)
Type "help" for help.
ostgres=# select * from version();
version
--------------------------------------------------------------------------------------------------------------
PostgreSQL 17.8 on x86_64-amazon-linux-gnu, compiled by gcc (GCC) 11.5.0 20240719 (Red Hat 11.5.0-5), 64-bit
(1 row)
サンプルデータ準備
検証用の DB と 10 万件のレコードを持つテーブルを準備します。
まず、DB とテーブルを作成します。
-- 検証用 DB の作成
postgres=# CREATE DATABASE blog_sample_db;
CREATE DATABASE
-- 作成した DB へ移動
postgres=# \c blog_sample_db
You are now connected to database "blog_sample_db" as user "postgres".
-- テーブル作成
blog_sample_db=# CREATE TABLE employees (
id SERIAL PRIMARY KEY,
name TEXT,
department TEXT,
salary INTEGER
);
CREATE TABLE
PostgreSQL には autovacuum という自動でVACUUMを実行してくれる機能がありますが、
今回は「手動でVACUUMを実行したときに何が起きるか」を確認したいので自動実行をオフにします。
-- autovacuum の無効化
blog_sample_db=# ALTER TABLE employees SET (autovacuum_enabled = false);
ALTER TABLE
テーブルに 10 万件のデータを投入します。
blog_sample_db=# INSERT INTO employees (name, department, salary)
SELECT
'employee_' || i,
(ARRAY['HR', 'Engineering', 'Sales', 'Marketing'])[((i % 4) + 1)],
(random() * 90000 + 10000)::INTEGER
FROM generate_series(1, 100000) AS i;
INSERT 0 100000
-- 10 万件入っているか確認
blog_sample_db=# SELECT COUNT(*) FROM employees;
count
--------
100000
(1 row)
-- 実際にテーブルに入っているレコード（最初の 5 行を一例として表示）
blog_sample_db=# SELECT * FROM employees LIMIT 5;
id | name | department | salary
----+------------+-------------+--------
1 | employee_1 | Engineering | 28026
2 | employee_2 | Sales | 21013
3 | employee_3 | Marketing | 92650
4 | employee_4 | HR | 73950
5 | employee_5 | Engineering | 90082
(5 rows)
以上で、サンプルデータ準備は完了です。
DELETE 前のテーブルの状態を確認
テーブル容量の確認
実際に DELETE クエリを実行する前に、現時点での employees テーブルのディスクサイズを確認します。
blog_sample_db=# SELECT pg_size_pretty(pg_relation_size('employees'));
pg_size_pretty
----------------
6280 kB
(1 row)
結果より、現在の employees テーブルが占めるディスクサイズは 6280 kB です。
なお上記クエリで使用している pg_relation_size は引数に指定したテーブルのディスク容量を計算する関数、pg_size_pretty は表記を見やすくする関数です。
pg_relation_size
指定したリレーションの一つの「fork」で使用されているディスクスペースを計算します。...
引数1つではリレーションの主データフォークのサイズを返します。2番目の引数で対象となるのがどのフォークであるかを指定できます。9.28. システム管理関数
https://www.postgresql.jp/document/17/html/functions-admin.html#FUNCTIONS-ADMIN-DBSIZE
pg_size_pretty
バイトサイズを、サイズ単位(バイト、kB、MB、GB、TB、PBのうちの適切なもの)を使った、より人間が読みやすい形式に変換します。9.28. システム管理関数
https://www.postgresql.jp/document/17/html/functions-admin.html#FUNCTIONS-ADMIN-DBOBJECT
上記 pg_relation_size 関数のドキュメントの説明にあるリレーションとはテーブルのことです。
リレーションは基本的にはテーブルを表す数学用語です。
2.2. 概念
https://www.postgresql.jp/document/17/html/tutorial-concepts.html
また PostgreSQL はテーブルのデータを複数のファイルに分けて格納しています。
この「ファイルの種類」のことをフォークと呼びます。
各テーブルおよびインデックスは別個のファイルに格納されます。
主ファイル（いわゆる主フォーク）に加え、それぞれのテーブルとインデックスは
リレーションに利用できる空き領域についての情報を格納する空き領域マップを持ちます。65.1. データベースファイルのレイアウト
https://www.postgresql.jp/document/17/html/storage-file-layout.html
Physical files (present in the PGDATA directory) are called Forks and PostgreSQL splits the data into multiple forks to manage and optimize different aspects of data storage and retrieval.
（日本語訳：PGDATA ディレクトリに存在する物理ファイルはフォーク（Fork）と呼ばれ、PostgreSQL はデータストレージと取得のさまざまな側面を管理・最適化するために、データを複数のフォークに分割します。）
フォークには main / fsm / vm / init の種類があり、実際のレコードが格納されているのは main フォークです。
今回の pg_relation_size('employees') は引数1つなので、この main フォークのサイズを返しています。
次項では DELETE クエリを実行して、main フォークのサイズがどう変化するかを確認していきます。
タプル件数の確認
現在の employees テーブルのタプル件数も確認しておきます。
タプル件数は pg_stat_user_tables ビュー から確認できます。
blog_sample_db=# SELECT relname, n_live_tup, n_dead_tup
FROM pg_stat_user_tables
WHERE relname = 'employees';
relname | n_live_tup | n_dead_tup
-----------+------------+------------
employees | 100000 | 0
(1 row)
INSERT した10万件が全て live tuple として確認できました。dead tuple は 0 件です。
なお、タプルとはテーブルの 1 行のことです。
以下公式ドキュメントより、タプルは「属性を一定の順序で集めたもの」とありますが、本ブログにおいては、「タプル = 行」という理解で問題ありません。
Tuple【タプル】
属性を一定の順序で集めたもの。 この順序はタプルが含まれるテーブル（または他のリレーション）によって定義されます。その場合タプルは、しばしば行と呼ばれます。 また結果セットの構造によって定義される場合もあります。その場合、タプルはレコードと呼ばれることがあります。
また、タプルには以下の2種類があります。
- live tuple：有効な（生きている）行。SELECT で取得できる。
- dead tuple：DELETE や UPDATE で無効になった（死んでいる）行。SELECT では見えないが、ディスクには残っている。
n_live_tup 有効な行数の推定値です。
n_dead_tup 無効な行数の推定値です。
次項で DELETE することによりタプルの件数がどう変化するかも見ていきます。
DELETE してみる
employees テーブル（10 万件レコード格納）から
5 万件のレコードを削除します。
blog_sample_db=# DELETE FROM employees WHERE id <= 50000;
DELETE 50000
その結果、dead tuple が 5 万件となりました。
blog_sample_db=# SELECT relname, n_live_tup, n_dead_tup
FROM pg_stat_user_tables
WHERE relname = 'employees';
relname | n_live_tup | n_dead_tup
-----------+------------+------------
employees | 50000 | 50000
(1 row)
一方で employees テーブルが保有するディスクサイズは 6280 kb と DELETE前と比較し変化がありません。
blog_sample_db=# SELECT pg_size_pretty(pg_relation_size('employees'));
pg_size_pretty
----------------
6280 kB
(1 row)
続いて、VACUUM を実行してみます。
-- VACUUM の実行
blog_sample_db=# VACUUM employees;
VACUUM
-- ディスクサイズの確認
blog_sample_db=# SELECT pg_size_pretty(pg_relation_size('employees'));
pg_size_pretty
----------------
6280 kB
(1 row)
-- タプル件数の確認
blog_sample_db=# SELECT relname, n_live_tup, n_dead_tup
FROM pg_stat_user_tables
WHERE relname = 'employees';
relname | n_live_tup | n_dead_tup
-----------+------------+------------
employees | 50000 | 0
(1 row)
上記より、VACUUM を行うと、dead tuple は削除されますが、
実際にディスクサイズは変化していないことがわかります。
これは VACUUM の仕様によるものです。
VACUUM は、無効タプルが使用する領域を回収します。 PostgreSQLの通常動作では、削除されたタプルや更新によって不要となったタプルは、テーブルから物理的には削除されません。 これらのタプルはVACUUMが完了するまで存在し続けます。 そのため、特に更新頻度が多いテーブルでは、VACUUMを定期的に実行する必要があります。
...
（FULLが指定されていない）通常のVACUUMは、単に領域を回収し、そこを再利用可能な状態に変更します。 この形式のコマンドでは排他的ロックを取得しないため、テーブルへの通常の読み書き操作と並行して実行することができます。 しかし余った領域はオペレーティングシステムには(ほとんどの場合)返されません。 同じテーブル内で再利用できるように保持されるだけです。
VACUUM は dead tuple を削除しますが、その余った領域を OS に返すのではなく
テーブル内の空き領域として再利用できる状態にするだけです。
そのため、ディスクサイズは変化しません。
続いて、VACUUM FULL を行います。
blog_sample_db=# VACUUM FULL employees;
VACUUM
blog_sample_db=# SELECT pg_size_pretty(pg_relation_size('employees'));
pg_size_pretty
----------------
3152 kB
(1 row)
blog_sample_db=# SELECT relname, n_live_tup, n_dead_tup
FROM pg_stat_user_tables
WHERE relname = 'employees';
relname | n_live_tup | n_dead_tup
-----------+------------+------------
employees | 50000 | 0
(1 row)
VACUUM FULL を実行すると、ディスクサイズが 6280 kB から 3152 kB と約半分になりました。
なるほど、VACUUM FULL をした時点でディスク容量が解放されるんですね。勉強になりました。
なお、この VACUUM FULL によるディスク解放も VACUUM コマンドの仕様によるものです。
VACUUM FULLでは、テーブルの内容全体を新しいディスクファイルに領域を余すことなく書き換えるため、オペレーティングシステムに未使用の領域を返すことができます。 この形式では、実行速度がかなり低速になります。また、処理中のテーブルに対するACCESS EXCLUSIVEロックが必要になります。
結果をまとめると以下の通りです。これで実験は終了です。
| ディスクサイズ | live tuple | dead tuple | ||
|---|---|---|---|---|
| DELETE前 | 6280 kB | 100,000 | 0 | |
| DELETE後 | 6280 kB | 50,000 | 50,000 | ← サイズ変わらず、dead tuple が増加 |
| VACUUM後 | 6280 kB | 50,000 | 0 | ← dead tuple は消えたが、サイズは変わらず |
| VACUUM FULL後 | 3152 kB | 50,000 | 0 | ← サイズが約半分に減少 |
<補足：なぜ DELETE にこんなに手間がかかるのか>
今回検証していて、なぜ DELETE するのにこんなに手間がかかるんだと思いましたが、
これは PostgreSQL の MVCC（多版型同時実行制御）という仕組みによるものです。
例えば、以下 2 つの処理が並行していた場合、
- トランザクション A：id=1 のデータを SELECT 中
- トランザクション B：id=1 のデータを DELETE 実行
このとき id=1 のデータを即座に消してしまうと、トランザクション A が読めなくなってしまうためです。
こちらについては、また改めて検証できたらと思います。
終わりに
今回は DELETE した際の挙動を検証してみました。
DELETE をしてもディスク容量はすぐには解放されず、
VACUUM FULL を実行して初めてディスク容量が解放されることが確認できました。
正直、DELETE するだけでデータが消えると思っていたので今回の検証は勉強になりました。
MVCC についてもいずれ検証してみたいと思います。
本記事がどなたかのお役に立てば幸いです。
参考情報
