Search Postgresql Archives

HouseKeeping and vacuum Questions

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



I'm starting to perform some basic housekeeping to try to trim some big
tables (~200 million rows - ~50GB+indexes) into separate partitions (via
inheritance).

The current issue which prompted me to do such housekeeping is due to
long database wide vacuum time. (it went from 2 hours to 4 hours to 7
hours)

My current strategy is to keep only 1 month of data in the base table
and make a job to automatically insert/delete 1 days worth of data into
the child partition tables.

Currently, I've moved 3 such large tables into separate partitions and
my vacuum time is still slow. (I suspect it's because I bulk
insert/deleted the tuples from the main_tables in 1 go and then the
vacuum had to deal with vacuum off the MVCC for the past few months's
data)

I'm already batching my housekeep into 6 hours timeframes (eg: insert
into foo_child  select * from fooo from hour1 to hour6)

So, my question is now

1. Should I perform a vacuum after each 6 hour batch? or
2. perform a vacuum after 1 day batch? (4x 6hour sessions)

and what should I do with the few tables which I've not started to
partition? There's 4 months worth of data which is still sitting in the
respective main tables.

Appreciate pointers etc.

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux