Re: Postgresql 'eats' all mi data partition

> [tgl(at)sss(dot)pgh(dot)pa(dot)us]
>
> > indexes:
> > stats_min_pkey primary key btree (ip, "start")
> > stats_min_start btree ("start")
> > stats_hr_pkey primary key btree (ip, "start")
> > stats_hr_start btree ("start")
>
> > ip is of type "inet" in all tables.
> > start is of type "timestamp without time zone" in all tables.
>
> 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 :-(

The only difference between the way stats_min and stats_hr are updated
stems from the fact that stats_min only holds records for the last 1440
minutes (because of its killer time granularity), whereas stats_hr
holds its data until we decide some of it is obsolete enough and
issue a "delete from" by hand.

Thus, the updates look like (pardon the pseudosql please):

stats_min:
begin;
delete from stats_min where \
[data is current minute but yesterday's, or even older,
or from the future];
[repeat for all ips]
insert into stats_min [data for the current minute];
[end repeat]
commit;

stats_hr:
begin;
[repeat for all ips]
update stats_hr set [data += increment] where \
[data is for current hour];
[if no_of_rows_updated < 1]
insert into stats_hr [current hour's first increment];
[endif]
[end repeat]
commit;

> 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.

OK, I'll definitely try 7.4 once I'm confident with it.

Thanks for your time,
--
Tomas Szepe <szepe(at)pinerecords(dot)com>