Re: truncate a table instead of vaccum full when count(*) is 0

On Tue, 8 May 2007, Heikki Linnakangas wrote:

> Pomarede Nicolas wrote:
>> There's not too much simultaneous transaction on the database, most of the
>> time it shouldn't exceed one minute (worst case). Except, as I need to run
>> a vacuum analyze on the whole database every day, it now takes 8 hours to
>> do the vacuum (I changed vacuum values to be a little slower instead of
>> taking too much i/o and making the base unusable, because with default
>> vacuum values it takes 3-4 hours of high i/o usage (total base is 20 GB) ).
>>
>> So, at this time, the complete vacuum is running, and vacuuming only the
>> spool table gives all dead rows are currently not removable (which is
>> normal).
>
> Oh, I see. I know you don't want to upgrade, but that was changed in 8.2.
> Vacuum now ignores concurrent vacuums in the oldest xid calculation, so the
> long-running vacuum won't stop the vacuum on the spool table from removing
> dead rows.

Well, this concurrent vacuum is very interesting, I didn't notice this in
8.2, but it would really help here to vacuum frequently this spool table
and have dead rows removed while the 'big' vacuum is running.
Seems, I will have to consider migrating to 8.2 then :)

Anyway, now my vacuum is over, I can vacuum the spool table and see the
results :

before : 6422 pages for the data and 1700 pages for the indexes.

after vacuum analyze : 6422 data pages / 1700 index pages

here's the log for vacuum :

fbxtv=# vacuum analyze verbose mysql_spool ;
INFO: vacuuming "public.mysql_spool"
INFO: index "pk_mysql_spool" now contains 21 row versions in 1700 pages
DETAIL: 7759 index row versions were removed.
1696 index pages have been deleted, 1667 are currently reusable.
CPU 0.01s/0.00u sec elapsed 1.78 sec.
INFO: "mysql_spool": removed 7759 row versions in 1521 pages
DETAIL: CPU 0.00s/0.00u sec elapsed 4.88 sec.
INFO: "mysql_spool": found 7759 removable, 21 nonremovable row versions
in 6422 pages
DETAIL: 20 dead row versions cannot be removed yet.
There were 261028 unused item pointers.
0 pages are entirely empty.
CPU 0.01s/0.01u sec elapsed 25.90 sec.
INFO: vacuuming "pg_toast.pg_toast_386146338"
INFO: index "pg_toast_386146338_index" now contains 0 row versions in 1
pages
DETAIL: 0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.01 sec.
INFO: "pg_toast_386146338": found 0 removable, 0 nonremovable row
versions in 0 pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.01 sec.
INFO: analyzing "public.mysql_spool"
INFO: "mysql_spool": scanned 3000 of 6422 pages, containing 0 live rows
and 14 dead rows; 0 rows in sample, 0 estimated total rows
VACUUM

So far, so good, nearly all rows are marked as dead and removable. But
then, if I do 'select ctid,* from mysql_spool', I can see ctid values in
the range 5934, 5935, 6062, ...

Isn't it possible for postgres to start using pages 0,1,2, ... after the
vacuum, which would mean that after a few minutes, all high pages number
would now be completly free and could be truncated when the next vacuum is
run ?

Actually, if I run another vacuum, some more dead rows are added to the
list of removable rows, but I can never reach the point where data is
stored in the low pages number (in my case a few pages would be enough)
and all other pages get truncated at the end.
Well at least, the number of pages doesn't increase past 6422 in this
case, but I'd like to reclaim space sometimes.

Is this one of the feature that is planned for 8.3 : reusing low pages
number in piority after a vacuum to help subsequent vacuums truncating the
end of the table once data are located at the beginning of the table ?

Thanks to all for all your very interesting answers.

Nicolas