1


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:

VACUUM FULL [TABLE];
REINDEX TABLE [TABLE];

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. – a_horse_with_no_name Jul 1 '14 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. – Craig Ringer Jul 2 '14 at 0:43
2

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? – Timoteo Ponce Jul 1 '14 at 20:37
  • If you run VACUUM without FULL it is supposed to run much better during a heavy update period. – RLF Jul 1 '14 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. – dezso Jul 3 '14 at 15:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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