再編成

• Oracle

alter table tab1 move;
alter index ind1 rebuild online;

※move実行によりインデックス無効となるため、リビルド必要
※move onlineであればインデックスは無効にならない模様

alter table tab1 enable row movement;
alter table tab1 shrink space;

• MySQL

OPTIMIZE table City;

※Mysql 5.6.17 より前は並列 DML (INSERT、UPDATE、DELETE) が許可されません。

• PostgreSQL

create table tab2(col1 int);
create index ind21 on tab2(col1);

insert into tab2 values(1),(2),(3);

cluster verbose tab2 using ind21;

vacuum verbose tab1;
vacuum full verbose tab1;
vacuum FREEZE verbose tab1;
vacuum analyze verbose tab1;

reindex index ind21;
reindex table tab1;

--pg_repack
https://yum.postgresql.org/9.4/redhat/rhel-7-x86_64/
https://github.com/reorg/pg_repack/blob/master/doc/pg_repack_jp.rst#id2
https://qiita.com/bwtakacy/items/3be7a3e1540ccef7c9e3

wget https://yum.postgresql.org/9.4/redhat/rhel-7-x86_64/pg_repack94-1.4.4-1.rhel7.x86_64.rpm
rpm -ivh pg_repack94-1.4.4-1.rhel7.x86_64.rpm

CREATE EXTENSION pg_repack;

create table tab2(col1 int not null primary key,col2 text);

select count(*) from tab2;
select pg_size_pretty(pg_total_relation_size('tab2'));

INSERT INTO tab2 VALUES(generate_series(1,10000), 'initial');
select pg_size_pretty(pg_total_relation_size('tab2'));

UPDATE tab2 SET col2 = 'updated-001';
select pg_size_pretty(pg_total_relation_size('tab2'));

UPDATE tab2 SET col2 = 'updated-001';
select pg_size_pretty(pg_total_relation_size('tab2'));
繰り返し実行

VACUUM tab2;
select pg_size_pretty(pg_total_relation_size('tab2'));

/usr/pgsql-9.4/bin/pg_repack -d test -t tab2

select pg_size_pretty(pg_total_relation_size('tab2'));

--自動バキュームの対象から除外
alter table tab1 set(autovacuum_enabled= off);

--pg_squeeze

https://github.com/cybertec-postgresql/pg_squeeze

(9.6)

tar xvzf pg_squeeze-REL1_0_2_PG_9_6.tar.gz
cd pg_squeeze-REL1_0_2_PG_9_6/

export PG_CONFIG=/usr/pgsql-9.6/bin/pg_config

make
su
make install

exit

vim postgresql.conf

wal_level = logical
max_replication_slots = 3
shared_preload_libraries = 'pg_squeeze'

sudo systemctl restart postgresql-9.6

CREATE EXTENSION pg_squeeze;

create table tab1(col1 int primary key);

INSERT INTO squeeze.tables (tabschema, tabname, first_check, task_interval, min_size)
VALUES ('public','tab1',now(),'1 min', 0.1);

--有効化
SELECT squeeze.start_worker();

--無効化
SELECT squeeze.stop_worker();

• SQL Server

USE test
GO

ALTER table tab2
REBUILD WITH (ONLINE = ON);
GO