Hi: On Mon, Jan 13, 2014 at 5:26 PM, Tirthankar Barari <tbarari@xxxxxxxxxxx> 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. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
