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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
