I have an issue with my postgres database, I'm running massive update queries (1000 per second) to a single table (with 3000 entries) and I can see that the size of that table is growing and growing up.

The way that I'm restoring the size is making these queries:


This will return the size of my [TABLE] to its original size.

That's working fine for me, but the problem is that I have to shutdown the application server to run those queries; if I run them with the server alive, the [TABLE] size is reduced, but just a bit.

My problem is reduced to 2 questions:
1. Why this table is growing if I'm just making updates?
2. How could I solve this problem without shutting down my application server?

Thank you very much for your time and your responses.

Update: Postgres versions are from 8.3.x up to 9.x

Update-2: The problem was because an IDLE connection was being kept alive by some application, by killing the connection and performing a vacuum all the problems were solved.

  • What is your Postgres version? What are your autovacuum settings? vacuum full is not such a good idea especially if you are using and older Postgres version.
    – user1822
    Commented Jul 1, 2014 at 22:36
  • You need to make autovacuum run much more aggressively on that table. See the documentation for details. Autovacuum has improved a lot in newer PostgreSQL versions, so if you're on an old version that may also be an issue. Commented Jul 2, 2014 at 0:43

1 Answer 1


The reason that your table is growing while only making updates is that PostgreSQL is that "tuples that are deleted or obsoleted by an update are not physically removed from their table; they remain present until a VACUUM is done."

Of course, while you are updating 1000 rows per second your updates are in contention with VACUUM FULL and perhaps REINDEX.

You should probably not be using the FULLoption. See: http://www.postgresql.org/docs/9.1/static/sql-vacuum.html

"Plain VACUUM (without FULL) simply reclaims space and makes it available for re-use. This form of the command can operate in parallel with normal reading and writing of the table, as an exclusive lock is not obtained."

If that is not enough to resolve your problem, perhaps you could include the VACUUM and REINDEX in your job being driven by your application server. Then periodically switch from updating rows to run a VACUUM.

EDIT: Note that 3 comments have pointed out autovacuum as a process that can run continually. However, dezso mentioned that configuring it for a heavy load may not be straightforward.

  • Thanks for the answer!, I just learned that you are correct, but there is not a way to create the table or configure a setting that would allow us to do this without involving the application itself? Commented Jul 1, 2014 at 20:37
  • If you run VACUUM without FULL it is supposed to run much better during a heavy update period.
    – RLF
    Commented Jul 1, 2014 at 20:39
  • Actually, with any modern PostgreSQL version, the autovacuum daemon can handle this. Tuning it to match the speed of changes and being low-key enough at the same time may not be exactly straightforward, though. Commented Jul 3, 2014 at 15:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.