Postgresql – Postgres table growing on massive updates

indexpostgresqlvacuum

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.

Best Answer

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.

Related Question