Date: 2010-05-17 17:38:45 +0200
From: @grobian
To: SQL devs <>
Version: -- development
CC: ashishk, fioravanzos, @hannesmuehleisen, mitja, @mlkersten, @njnes, @skinkie, @yzchang

Blocker for: #3162, #3160
Last updated: 2017-10-31 13:12:17 +0100

Comment 14039

Date: 2010-05-17 17:38:45 +0200
From: @grobian

Many people from both inside as outside really need to be able to shrink their database size badly. The least we should do is implement the VACUUM TABLE and VACUUM DATABASE commands to manually recreate the bats under the hood.

More advanced would be to have a auto_vacuum thing that allows to vacuum all tables at times such that no maintenance is necessary.

Comment 14040

Date: 2010-05-17 19:58:28 +0200
From: @mlkersten

There is a relatively easy way to include a vacuum thread in the system (at least for non-string valued columns), which does not require user involvement. The bottleneck is the time/priority for development and the trickiness of getting it transaction safe.

Comment 14140

Date: 2010-06-23 04:21:31 +0200
From: @skinkie

When the alter table/rename to is implemented, and within the vacuum transaction constraints can be temporary lifted, the only thing that should happen is:

start transaction;
insert into y select * from x order by primary key;
ignore constraints;
drop table x;
alter table y rename to x;
enforce constraints without recheck;
commit;

The rename not being available, and the significant overhead of the constraint recheck is problematic for the people that require the vacuum in the first place. The above sacrifices space, by at most two times the size, but sadly enforces a write lock at the same time.

I wonder if instead of a vacuum thread for auto_vacuum a solution could be in place that maintains (or reuses) the deleted record list, so if this list has entries, new inserts are not appended but in fact updated. This scarifies (primary key) fragmentation over space, which might, (or might not) really bother people. Applying auto_vacuum to the table should trigger the reuse list, and may if the table is not empty, execute the above vacuum method. The former will not solve the delete from x where y <> 1, (having one record remaining), a heuristic (deletelist to become > half table length) falling back to the first presented solution will. Having the delete not to delete records, but exclude them in the select into the new table. (insert into y select * from x where not(delete_where_part) order by primary key;)

Comment 14165

Date: 2010-07-01 16:11:45 +0200
From: @sjoerdmullender

At the GDK level, code similar to fixstrheap in gdk_bbp.mx could be used to vacuum a string BAT in-place.

Comment 14846

Date: 2010-08-30 09:23:21 +0200
From: @sjoerdmullender

The Jun2010-SP2 version has been released.

Comment 15687

Date: 2011-03-28 17:36:33 +0200
From: @sjoerdmullender

The Mar2011 version has been released.

Comment 16040

Date: 2011-07-29 11:00:06 +0200
From: @sjoerdmullender

Apr2011-SP2 has been released.

Comment 16279

Date: 2011-09-16 15:10:40 +0200
From: @sjoerdmullender

The Aug2011 version has been released.

Comment 17682

Date: 2012-08-24 14:55:58 +0200
From: @sjoerdmullender

Jul2012-SP1 has been released.

Comment 18115

Date: 2012-11-27 15:24:37 +0100
From: @yzchang

No test needed for feature request

Comment 19233

Date: 2013-10-03 09:49:41 +0200
From: Ashish Kumar Singh <>

Hi,

Any plans for implementing this feature?

Regards,
Ashish

Comment 24452

Date: 2016-10-04 11:07:07 +0200
From: fioravanzos

Hello, is there any plan to make the VACUUM statement stable?

Regards,
Stefano

Comment 25827

Date: 2017-10-31 13:12:17 +0100
From: @hannesmuehleisen

There is the procedure VACUUM(schema, table) which should do what you described.

sql>create table test (i integer);
sql>insert into test values (1),(2),(3);
sql>delete from test where i > 2;
sql>call vacuum('sys', 'test');