Database Administrators Stack Exchange
Database Administrators Stack Exchange

Questions tagged [vacuum]

The vacuum command in postgres releases unused space. Be sure to also include the [postgres] tag, and version tag such as [postgresql-9.6]. Include an operating system tag,

The vacuum command in postgres releases unused space created by updating and deleting rows. It can optionally analyse tables.

Documentation

172 questions
66
votes
2 answers

How to view the current settings of Autovacuum in Postgres?

I've found a million examples of how to set Autovacuum's configuration settings, but can't seem to find how to list the current configuration. Postgres 9.1 is the version I'm most interested in.
Peter Groves
  • 1,165
  • 2
  • 9
  • 7
51
votes
5 answers

Aggressive Autovacuum on PostgreSQL

I'm trying to get PostgreSQL to aggressively auto vacuum my database. I've currently configured auto vacuum as follows: autovacuum_vacuum_cost_delay = 0 #Turn off cost based vacuum autovacuum_vacuum_cost_limit = 10000 #Max…
CadentOrange
  • 783
  • 1
  • 8
  • 10
47
votes
5 answers

I need to run VACUUM FULL with no available disk space

I have one table that is taking up close to 90% of hd space on our server. I have decided to drop a few columns to free up space. But I need to return the space to the OS. The problem, though, is that I'm not sure what will happen if I run VACUUM…
Justin Rhyne
  • 573
  • 1
  • 4
  • 5
39
votes
3 answers

Are regular VACUUM ANALYZE still recommended under 9.1?

I'm using PostgreSQL 9.1 on Ubuntu. Are scheduled VACUUM ANALYZE still recommended, or is autovacuum enough to take care of all needs? If the answer is "it depends", then: I have a largish database (30 GiB compressed dump size, 200 GiB data…
François Beausoleil
  • 1,463
  • 3
  • 15
  • 24
37
votes
1 answer

VACUUM returning disk space to operating system

VACUUM usually does not return disk space to operating system, except in some special cases. From the docs: The standard form of VACUUM removes dead row versions in tables and indexes and marks the space available for future reuse. However, it will…
Vadim Samokhin
  • 635
  • 1
  • 8
  • 13
34
votes
4 answers

How much time will a vacuum/autovacuum operation take?

I manage a big (some hundreds of gigs) database containing tables with various roles, some of them holding millions of records. Some tables only receive large number of inserts and deletes, some other few inserts and large number of…
zaadeh
  • 485
  • 1
  • 5
  • 9
30
votes
3 answers

VACUUM FREEZE vs. VACUUM FULL

Can someone explain the difference between these types of VACUUM in PostgreSQL? I read the doc but it just says that FULL locks the tables and FREEZE "freezes" the tuples. I think that's the same. Am I wrong?
Christian Maíz
  • 403
  • 1
  • 4
  • 5
23
votes
1 answer

Does cancelling an (AUTO)VACUUM process in PostgreSQL make all the work done useless?

In some occasions, and after making a massive update, insert or delete from a table, I have started a VACUUM FULL ANALYZE to make sure the DB was not getting too bloated. Doing it in a production database has let me discover that this was not a good…
joanolo
  • 13,657
  • 8
  • 39
  • 67
22
votes
4 answers

Should I manually VACUUM my PostgreSQL database if autovacuum is turned on?

I use software which makes a big PostgreSQL database (there is a table with a million rows in it) and the developers says I should VACUUM and ANALYZE periodically. But the PostgreSQL database default is autovacuum turned on. Should I vacuum/analyze…
kissgyorgy
  • 345
  • 1
  • 3
  • 8
22
votes
1 answer

PostgeSQL 9.3: Is it safe to stop VACUUM FULL?

I'm using PostgreSQL 9.3 on RDS. Once in a while, I run a VACUUM FULL operation on the database. However, such operation can take quite a while and it blocks other tables, so the need to stop the operation might arise. Is it safe to stop a VACUUM…
Adam Matan
  • 12,079
  • 30
  • 82
  • 96
20
votes
2 answers

PostgreSQL difference between VACUUM FULL and CLUSTER

I have a table with 200 GB of size occupied by data and 180 GB of size by the 6 indexes on it. It is 30% bloated, so I want to reclaim unwanted space occupied by it. It is clustered on job_id_idx index. So to reclaim the space do I need to use…
Arun P
  • 201
  • 1
  • 2
  • 3
20
votes
1 answer

Is it worth it to run VACUUM on a table that only receives INSERTs?

In a 2015 re:Invent talk, AWS mentioned that vacuum should be run not only after updates or deletes but also after inserts. Here is the relevant part of the talk: http://www.youtube.com/watch?v=tZXp19q8RFo&t=16m2s Supposedly there is some cleanup…
foobar0100
  • 641
  • 7
  • 15
14
votes
3 answers

Is it necessary to ANALYZE a table after an index has been created?

In my transaction, I am creating a temporary table: create temporary table x on commit drop as select ... I also add an index on that table: create index on x(some_column); Is it now necessary to run analyze on that table? Or do I only need to…
Kollp
  • 143
  • 1
  • 1
  • 4
13
votes
1 answer

Disk file effects of delete and vacuum

I have a very frequently updated table with 240 million rows (and growing). Every three hours 1.5 million rows are inserted and 1.5 million are deleted. When I moved the cluster to a SSD this bulk insert (using copy) time was cut from 22 minutes to…
Clodoaldo
  • 1,145
  • 2
  • 8
  • 22
13
votes
1 answer

Does VACUUM FULL need ANALYZE

I'd be grateful for some insight into the VACUUM functionality within PostgreSQL 9.3. I read through the documentation and searched around a bit but could not find a definitive answer to this. I'm setting up a weekly DB maintenance job for a 9.3…
Eoin O'Hehir
  • 133
  • 1
  • 5
1
2 3
…
11 12