Re: Index space growing even after cleanup via autovacuum in Postgres 9.2

On 01/13/2014 01:38 PM, Francisco Olarte wrote:
> Hi:
>
> On Mon, Jan 13, 2014 at 5:26 PM, Tirthankar Barari <tbarari@verizon.com> wrote:
>> On 01/10/2014 07:06 AM, Francisco Olarte wrote:
>>> Not related to your vacuum problem, but if your pattern is something
>>> like deleting everything inserted 15 days ago you may want to think of
>>> using partitioning or simple inheritance. Make the scheduled task
>>> create  a new partition/child table, redirect insertions to it, drop
>>> the oldest partition.
> ...
>> Thanks for your input. The rentention window was supposed to be variable and
>> dynamically changeable. So, partitioning is our last resort. Will try the
>> vacuum after delete instead of autovacuum.
> The ability to use partition/inheritance does not depend on an static
> window, but on wether your deletion pattern is as described. Supose
> you do it daily. You can name your partitions / child_tables as
> child_YYYYMMDD. Then to delete data that is >N days old you just build
> the cutoff date, select from the system catalog relations whos name is
> like child_\d\d\d\d\d\d\d\d , whose name is greater than
> child_12345678 ( substitute the curoff date ) and  whose parent is the
> appropiate table and drop all of them. If the retention window just
> grew ( say from 15 to 20 ), the first 5 days you'll find no child
> table ( same as when starting, this looks like the window grew from 0
> to N ). If it shrank from 15 to 10 the first day you'll drop 10
> tables. Depending on how you change the retention window you can also
> delete just the appropiate partition, ignoring error in case it does
> not exists ( to acomodate window growing cases, you can even use just
> a drop if exists ) and when the window shrinks you can zap extra
> tables manually or on the procedure which shrinks the window. The
> advantage of this is avoiding system catalog query, but I personally
> would use first alternative. The logic is much the same as a deleting,
> just using partition drops.
>
> Regards.
>
> Francisco Olarte.
>
>
My tables are:

table test_metric (
         id varchar(255) not null, // just auto generated uuid from app
         timestamp timestamp not null,
         version int4,
         entity_id varchar(255) not null,
         primary key (id, timestamp)
     );
Indexes:
     "test_metric_pkey1" PRIMARY KEY, btree (id)
     "test_metric_entity_id_timestamp_key" UNIQUE CONSTRAINT, btree (entity_id, "timestamp")

AND

table test_metric_metrics (
         metric_id varchar(255) not null,
         metrics float8,
         metrics_key varchar(255) not null,
         primary key (metric_id, metrics_key)
     );
Indexes:
     "test_metric_metrics_pkey" PRIMARY KEY, btree (metric_id, metrics_key)
Foreign-key constraints:
     "fk3b8e13abb63406d5" FOREIGN KEY (metric_id) REFERENCES test_metric(id)

Basically, test_metric holds the timestamp and some metadata and test_metric_metrics holds the set of key/value pairs
forthe give entity and timestamp in the parent table. 

Is it possible to partition the second table by timestamp field from first table?

We are using postgres 9.2.2

Thanks,

- Tirthankar