Thanks Kevin for the ideas. Now that you have corrected our misconception regarding the autovacuum not handling index bloating, we are looking into running autovacuum frequently enough to make sure we don't have significant increase in table size or index size. We intend to keep our transactions short enough not to reach the situation where vacuum full or CLUSTER is needed. Thanks, Ofer -----Original Message----- From: Kevin Grittner [mailto:Kevin.Grittner@xxxxxxxxxxxx] Sent: Tuesday, February 07, 2012 2:28 PM To: Ofer Israeli; pgsql-performance@xxxxxxxxxxxxxx Cc: Netta Kabala; Olga Vingurt Subject: Re: Inserts or Updates 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 Scanned by Check Point Total Security Gateway. -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
