Re: Auto-clustering?

On 17/12/2010, at 9:20 PM, Pierre C wrote:

>
>> fc=# explain  analyse select collection, period, tariff, sum(bytesSent), sum(bytesReceived), sum(packets),
max(sample),(starttime / 3600) * 3600 as startchunk from sample_20101001 where starttime between 1287493200 and
1290171599 and collection=128    and ip = '10.9.125.207' group by startchunk, tariff, collection, period; 
>
> If CLUSTER locks bother you, and you don't do UPDATEs, you might consider doing something like this :
>
> - accumulate the rows in a "recent" table
> - every hour, INSERT INTO archive SELECT * FROM recent ORDER BY (your cluster fields)
> - DELETE FROM recent the rows you just inserted
> - VACUUM recent
>
> The cluster in your archive table will not be perfect but at least all rows from 1 source in 1 hour will be stored
closetogether. But clustering doesn't need to be perfect either, if you get 100x better locality, that's already good ! 

That's a really decent idea and can slot in perfectly well with how the application already works!  We have existing
DBAOcode that handles monthly tables; it'll happily pop data in to a recent table.... In fact we can probably tolerate
havinga "today" table.  Thanks! 

--Royce