From: | Scott Marlowe <smarlowe(at)g2switchworks(dot)com> |
---|---|
To: | Bohdan Linda <bohdan(dot)linda(at)seznam(dot)cz> |
Cc: | Ben-Nes Yonatan <da(at)canaan(dot)co(dot)il>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: detection of VACUUM in progress |
Date: | 2005-08-31 15:35:12 |
Message-ID: | 1125502511.28179.135.camel@state.g2switchworks.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Wed, 2005-08-31 at 07:18, Bohdan Linda wrote:
> >
> > From the postgresql manual
> > http://www.postgresql.org/docs/8.0/interactive/maintenance.html :
> > " The standard form of VACUUM is best used with the goal of maintaining
> > a fairly level steady-state usage of disk space. If you need to return
> > disk space to the operating system you can use VACUUM FULL ? but what's
> > the point of releasing disk space that will only have to be allocated
> > again soon? Moderately frequent standard VACUUM runs are a better
> > approach than infrequent VACUUM FULL runs for maintaining
> > heavily-updated tables."
> >
> > From this I conclude that an ordinary VACUUM is sufficent to your
> > purpose cause you insert/delete almost the same amount of data daily.
> >
> > But then again I can be mistaken so if anyone can back me up here or
> > throw the manual on me will be nice ;P
>
>
> If I vacuum only the table, the records may be used by new lines, that is
> fine. Problem is, that when creating select on such table, it takes more
> pages to be read from the IO (it will read laso deleted rows) thus the
> select will last a bit longer.
It really depends on what percentage of rows you're updating.
If you are updating 240,000 rows a day, and have a database with 100M
rows, then that's not too bad. Regular vacuums once a day would be
plenty.
If you're updating 240,000 rows a day, spread out over the day, and you
have a table that has 10,000 rows, then you will need to run vacuum far
more often to keep the table at a steady state of 10,000 to 20,000 rows.
If you're doing the 240,000 updates all at once on a small table, then
you might well be a candidate for a vacuum full.
So, again, it's about percentages really. If 240k rows represents 1% of
your table, then daily, regular vacuums will do fine.
Personally, I just install pg_autovacuum and check on it once a week or
so to make sure it's doing its job.
From | Date | Subject | |
---|---|---|---|
Next Message | vishal saberwal | 2005-08-31 15:41:04 | Re: PQConnectdb SSL (sslmode): Is this a bug |
Previous Message | Joost Kraaijeveld | 2005-08-31 15:14:08 | Re: [SQL] How do I copy part of table from db1 to db2 |