About the Authors
Consulting
PostgreSQL
PostGIS
Wednesday, October 21. 2009Enable and Disable Vacuum per tableVacuuming and analyzing is the process that removes dead rows and also updates the statistics of a table. As of PostgreSQL 8.3, auto vacuuming (the process that runs around cleaning up tables), is on by default. If you are creating a lot of tables and bulk loading data, the vacuumer sometimes gets in your way. One way to get around that is to disable auto vacuuming on the tables you are currently working on and then reenable afterward. You can also do this from the PgAdmin III management console. Question:
How do you disable and reenable auto vacuuming for a select table? Solution: The SQL way
--disable auto vacuum
ALTER TABLE sometable SET (
autovacuum_enabled = false, toast.autovacuum_enabled = false
);
--enable auto vacuum
ALTER TABLE sometable SET (
autovacuum_enabled = true, toast.autovacuum_enabled = true
);
Solution: The PgAdmin III way
Navigate to the table in PgAdmin III explorer -> Select Table --> Properties and Click to go to Autovacuum tab 
Note the other settings you can set in PgAdmin III. These can be set in SQL as well, and to see the SQL equivalent, simply set the settings, and switch to the PGAdmin III SQL tab. It will show the ALTER TABLE statement to run to make the change. 
Comments
Display comments as
(Linear | Threaded)
Note that the described ALTER TABLE statements works since PostgreSQL 8.4 only. In earlier versions the DBA has to populate a system table called pg_autovacuum itself (or using PgAdmin III and the dialog you've described above). You might also find those settings not to be included in pg_dump before 8.4.
|
QuicksearchCalendar
CategoriesSyndicate This BlogPowered by
Blog AdministrationEntry's Links |
||||||||||||||||||||||||||||||||||||||||||
Tracked: Oct 21, 11:33