Re: Inserts or Updates - Mailing list pgsql-performance

From Kevin Grittner
Subject Re: Inserts or Updates
Date
Msg-id 4F30C4550200002500044EC9@gw.wicourts.gov
Whole thread Raw
In response to Inserts or Updates  (Ofer Israeli <oferi@checkpoint.com>)
Responses Re: Inserts or Updates  (Ofer Israeli <oferi@checkpoint.com>)
List pgsql-performance
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

pgsql-performance by date:

Previous
From: Ofer Israeli
Date:
Subject: Inserts or Updates
Next
From: Andy Colson
Date:
Subject: Re: Inserts or Updates