On 01/10/2014 07:06 AM, Francisco Olarte wrote:
Hi:
On Thu, Jan 9, 2014 at 7:50 PM, Tirthankar Barari <tbarari@xxxxxxxxxxx> wrote:
We have a table where we insert about 10 million rows everyday. We keep 14
day's worth of entries (i.e. 140 mil). A scheduled task wakes up every day
and deletes all entries past the 14 day window (i.e. deletes entries from
the 15th day in the past).
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.
Also, if the pattern is insert-only all the time, delete whole day, it
may be easier to exclude the table from autovacuum and make the
scheduled task vacuum the table after deleting.
Francisco Olarte.
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.
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
