Ofer Israeli wrote:
> Our system has a couple of tables that hold client generated
> information. The clients communicate every minute with the server
> and thus we perform an update on these two tables every minute. We
> are talking about ~50K clients (and therefore records).
>
> These constant updates have made the table sizes to grow
> drastically and index bloating. So the two solutions that we are
> talking about are:
>
> 1. Configure autovacuum to work more intensively in both time and
> cost parameters.
> Pros:
> Not a major architectural change.
> Cons:
> Autovacuum does not handle index bloating and thus we will need to
> periodically reindex the tables.
Done aggressively enough, autovacuum should prevent index bloat, too.
> Perhaps we will also need to run vacuum full periodically if the
> autovacuum cleaning is not at the required pace and therefore
> defragmentation of the tables is needed?
The other thing that can cause bloat in this situation is a
long-running transaction. To correct occasional bloat due to that on
small frequently-updated tables we run CLUSTER on them daily during
off-peak hours. If you are on version 9.0 or later, VACUUM FULL
instead would be fine. While this locks the table against other
action while it runs, on a small table it is a small enough fraction
of a second that nobody notices.
> 1. Creating a new table every minute and inserting the data into
> this new temporary table (only inserts). This process will happen
> every minute. Note that in this process we will also need to copy
> missing data (clients that didn't communicate) from older table.
> Pros:
> Tables are always compact.
> We will not reach a limit of autovacuum.
> Cons:
> Major architectural change.
I would try the other alternative first.
-Kevin