Success report (WAS: postgresql 'eats' my whole data partition)

On Sep-26 2003, Fri, 16:28 -0400
Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Okay, so a pkey index entry will take 32 bytes counting overhead ...
> you've got about 10:1 bloat on the stats_min indexes and 2:1 in stats_hr.
> Definitely bad :-(
>
> I expect the bloat is coming from the fact that the interesting range of
> "start" changes over time. 7.4 should be able to recycle index space
> in that situation, but 7.3 and before can't.

This is how things look with 7.4beta3:

INFO: vacuuming "public.stats_min"
INFO: index "stats_min_start" now contains 1963394 tuples in 12086 pages
DETAIL: 736088 index tuples were removed.
5029 index pages have been deleted, 2411 are currently reusable.
CPU 1.07s/2.13u sec elapsed 8.15 sec.
INFO: index "stats_min_pkey" now contains 1963520 tuples in 27945 pages
DETAIL: 736088 index tuples were removed.
11722 index pages have been deleted, 5603 are currently reusable.
CPU 4.18s/5.55u sec elapsed 113.04 sec.
INFO: "stats_min": removed 736088 tuples in 9483 pages
DETAIL: CPU 1.00s/1.12u sec elapsed 13.68 sec.
INFO: "stats_min": found 736088 removable, 1963394 nonremovable tuples in 23890 pages
DETAIL: 0 dead tuples cannot be removed yet.
There were 0 unused item pointers.
0 pages are entirely empty.
CPU 7.20s/9.16u sec elapsed 146.28 sec.

...

INFO: vacuuming "public.stats_hr"
INFO: index "stats_hr_start" now contains 7850557 tuples in 28059 pages
DETAIL: 728706 index tuples were removed.
4337 index pages have been deleted, 2035 are currently reusable.
CPU 2.66s/4.72u sec elapsed 41.18 sec.
INFO: index "stats_hr_pkey" now contains 7852826 tuples in 57657 pages
DETAIL: 728706 index tuples were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 5.26s/5.66u sec elapsed 123.91 sec.
INFO: "stats_hr": removed 728706 tuples in 16033 pages
DETAIL: CPU 1.51s/1.68u sec elapsed 25.43 sec.
INFO: "stats_hr": found 728706 removable, 7848770 nonremovable tuples in 161840 pages
DETAIL: 0 dead tuples cannot be removed yet.
There were 0 unused item pointers.
0 pages are entirely empty.
CPU 15.71s/12.88u sec elapsed 213.28 sec.

Definitely a tremendous improvement. The "working base" on-disk
db size seems to have stabilised at around 2.4 GiB and is only
increasing by ~100 MiB a day, which, by my calculations, is roughly
proportional to the frequency of data added. (There is still
some minor overhead all right, but it's far from serious.)

So again it's a big thank-you to the whole postgres development
group for yet another nice step forward, especially to you Tom
for the "allow btree index compaction and empty page reuse"
changeset.

Best regards,
--
Tomas Szepe <szepe(at)pinerecords(dot)com>