PostgreSQL �� VACUUM �̓������m�F����
PostgreSQL �� �f�[�^���X�V�����Ƃ��� �폜�����Ƃ��� ���R�[�h�Ɂu�s�v�v�}�[�N��t����Ƃ����������s�Ȃ��܂��B
�u�s�v�v�}�[�N�̕t�������R�[�h�i�ȉ��A�s�v�ȃ��R�[�h�j�� ���̂܂܂ł͍ė��p�ł��Ȃ��̂Ńf�[�^��}�����Ȃ��Ă� �X�V���J��Ԃ������� �e�[�u���̎g�p����X�y�[�X�͍L�����Ă����܂��B
VACUUM �́A�s�v�ɂȂ������R�[�h�̃X�y�[�X�� �ė��p�\�ɂ��܂��B ����́A���̓������m�F���Ă݂����Ǝv���܂��B
�܂��͐V�����e�[�u�� vacuum_test ���쐬���܂��B
db=# CREATE TABLE vacuum_test (f1 VARCHAR(10), f2 INTEGER);
CREATE TABLE
���� pg_stat_all_tables �J�^���O����擾���܂��B
db=# SELECT n_live_tup,n_dead_tup,last_vacuum,last_autovacuum db-# FROM pg_stat_all_tables db-# WHERE relname = 'vacuum_test'; -[ RECORD 1 ]----+------------ n_live_tup | 0 n_dead_tup | 0 last_vacuum | last_autovacuum |
�e�[�u�����쐬�������_�ł͏�̂悤�ɂȂ��Ă��܂��B
VACUUM �Ɋ֘A�����ȗ�̂ݎ擾���Ă��܂��B
���ꂼ��̗�̈Ӗ��͎��̂悤�ɂȂ�܂��B
| �� | ���� |
|---|---|
| n_live_tup | �L���ȃ��R�[�h�� |
| n_dead_tup | �s�v�ɂȂ������R�[�h�� |
| last_vacuum | �Ō�� VACUUM �����s�������� |
| last_autovacuum | �Ō�� ���� VACUUM �����s���ꂽ���� |
���R�[�h��}�����܂��B
INSERT INTO vacuum_test VALUES ('00001', 10);
INSERT INTO vacuum_test VALUES ('00002', 20);
INSERT INTO vacuum_test VALUES ('00003', 30);
INSERT INTO vacuum_test VALUES ('00004', 40);
INSERT INTO vacuum_test VALUES ('00005', 50);
��Ԃ��m�F���܂��B
db=# SELECT n_live_tup,n_dead_tup,last_vacuum,last_autovacuum db-# FROM pg_stat_all_tables db-# WHERE relname = 'vacuum_test'; -[ RECORD 1 ]----+------------ n_live_tup | 5 n_dead_tup | 0 last_vacuum | last_autovacuum |
5 ���R�[�h�}�������̂ŁA�L���ȃ��R�[�h���� 5 �ɂȂ�܂����B
���� 1 ���R�[�h�폜���܂��B
db=# DELETE FROM vacuum_test WHERE f1 = '00002';
DELETE 1
��Ԃ��m�F���܂��B
db=# SELECT n_live_tup,n_dead_tup,last_vacuum,last_autovacuum db-# FROM pg_stat_all_tables db-# WHERE relname = 'vacuum_test'; -[ RECORD 1 ]----+------------ n_live_tup | 4 n_dead_tup | 1 last_vacuum | last_autovacuum |
�L���ȃ��R�[�h���� 4 �ɂȂ� �s�v�ȃ��R�[�h���� 1 �ɂȂ�܂����B
���łɍX�V�����܂��B
db=# UPDATE vacuum_test SET f2 = 15 WHERE f1 = '00001';
UPDATE 1
��������Ԃ��m�F���܂��B
db=# SELECT n_live_tup,n_dead_tup,last_vacuum,last_autovacuum db-# FROM pg_stat_all_tables db-# WHERE relname = 'vacuum_test'; -[ RECORD 1 ]----+------------ n_live_tup | 4 n_dead_tup | 2 last_vacuum | last_autovacuum |
�s�v�ȃ��R�[�h���� 1 �����܂����B
�����܂ł͏����ł��B �悤�₭ VACUUM �����s���܂��B
db=# VACUUM vacuum_test;
VACUUM
��Ԃ��m�F���܂��B
db=# SELECT n_live_tup,n_dead_tup,last_vacuum,last_autovacuum db-# FROM pg_stat_all_tables db-# WHERE relname = 'vacuum_test'; -[ RECORD 1 ]----+------------------------------ n_live_tup | 4 n_dead_tup | 0 last_vacuum | 2010-04-20 22:20:20.560647+09 last_autovacuum |
�s�v�ȃ��R�[�h���� 0 �ɂȂ�܂����B
last_vacuum �� VACUUM �����s���������������Ă��܂��B
���̂悤�� VACUUM �ŁA�s�v�ɂȂ������R�[�h�� �X�y�[�X���ė��p�\�ɂ��邱�Ƃ��ł��܂��B
