On Thu, May 10, 2012 at 9:42 AM, Guy Helmer <guy.hel...@palisadesystems.com> wrote: > On May 10, 2012, at 4:31 AM, Horaci Macias wrote: > >> Hi everybody, >> >> I'm running postgres 9.1 and having disk space problems. >> My application captures information 24x7 and stores it into the database. >> This includes several bytea and can be ~5M entries a day, so the size can be >> an issue after several days. >> My application also cleans up entries older than 10 days; it does this every >> night and the delete operations are happening successfully. I cannot >> truncate the tables as they contain both stale and active data. >> The database is able to store all the entries for ~15 days without problems, >> but for some reason the deletion of old entries is not freeing up the space >> (or the insertion of new entries is not reusing the space used by old >> entries) because after running the application for ~20days I run out of >> space on disk. >> I've been reading on this forum and the postgres documentation; vacuum full >> is not recommended and apparently vacuum should be all I need. I'm using >> autovacuum but this doesn't seem to be solving the problem (perhaps because >> while vacuum is running the application keeps inserting entries 24x7?) >> >> Just to clarify, I don't really care if the disk space is returned to the >> OS; what I need though is to be sure that I can keep a window of 10 days of >> records (assuming of course my HD is big enough for those 10 days, which >> seems to be the case). >> >> Some questions: >> * Although not being generally recommended, I've read that vacuum full is >> sometimes the only choice when large deletions are in place in order to >> maintain the database. Is this the case here? >> * Should I try to have a "maintenance window" and stop all inserts/writes >> while vacuum is running? If so, is there any way to configure at what time >> vacuum will be executed by autovacuum or should I rely on cron-type jobs for >> this? and is there any way to prevent external connections at certain times >> of day to make sure inserts/writes don't happen while vacuum is going, or >> again I should use cron-type jobs for this? >> * Any other suggestions/ideas to troubleshoot this or any pointers to >> further documentation? > > I would expect a plain VACUUM to make unused space available for re-use -- > not sure why it would not be helping. > > Since Postgresql can have tables that are children of tables, a neat trick in > this situation is to create per-day child tables and insert the new data > directly into the appropriate per-day table; with this approach, deleting old > data is accomplished by simply dropping outdated tables and thereby avoiding > VACUUM completely. With constraints on the child tables, Postgresql can > optimize a query on the parent table by knowing what child table has data > from what day and will only check child tables that would have data for a > given query.
+1 on this approach. it's a best practice for rotating logging tables. merlin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
