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

Hi:

On Mon, Jan 13, 2014 at 5:26 PM, Tirthankar Barari <tbarari(at)verizon(dot)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.