Re: Postgresql 'eats' all mi data partition

fjcarlos(at)correo(dot)insp(dot)mx (Javier Carlos) writes:
> *** When I make a 'DROP DATABASE' to the database where that table belongs to,
> mi /var partition returns to its original size (in this example to 9%).

VACUUM ANALYZE is your friend, here.

PostgreSQL uses MVCC, wherein each update results in the creation of a
new tuple which coexists with the old one in the database. If you
update a particular row in the database 10 times, then there will be
11 copies, 10 of them being obsolete.

If you VACUUM the table or the database, the rows that are no longer
accessible will be cleared out, thus diminishing the amount of space
in use.

Things are somewhat more complicated than that; VACUUM looks at the
data page-by-page, and won't _always_ reclaim all free space. The
more often you VACUUM, the more likely that such reclaimation will be
possible.

There is a program called pg_autovacuum that will vacuum automatically
when specific tables look like they need it.

ANALYZE does something different but related; it updates the
statistics used by the query optimizer. When you make major changes
to the table, it may be necessary to ANALYZE it in order for queries
to be efficient.
--
"cbbrowne","@","libertyrms.info"
<http://dev6.int.libertyrms.com/>
Christopher Browne
(416) 646 3304 x124 (land)