On Tue, Jul 7, 2009 at 1:40 PM, Jennifer Spencer
<jenniferm...@hotmail.com>wrote:
> You are sure that the XID wraparound is gone? That's good news. No other
> reasons for vacuum full on the entire database.
>
I think we're talking apples and gorillas on the use of the word 'full'.
There is a command:
VACUUM FULL;
When you do that, you lock a table and much hatred reigns upon you from
user-land.
When you:
vacuum all databases in a cluster (notice, no 'FULL' here), (could be
through autovacuum)
You are doing a vacuum that is capable of operating alongside
transactions.
XID wrap-around is still an issue, but if you have autovacuum on and you
pay attention to your logs, you'll be okay.
>
> We could do it a table at a time if we absolutely have to do it, and that
> would minimize down time on the rest of the system.
>
No need, see above.
Also, a quick note about your growth pattern (~ 1 TB / year)
If you're going to be growing that much, index builds on a TB of data
really stink. You may want to look into constraint_exclusion partitioning
(maybe by quarter or something easy to chunk up ) would be worth-while.
When you do need to do index rebuilds, you can:
A) Do them concurrently
Rebuilding an index becomes
CREATE INDEX CONCURRENTLY my_index_1 ....
DROP INDEX my_index_0;
ANALYZE table;
That way, you aren't blocking during that rebuild.
B) Your indexes will be a subset of your data, in my example, you'd only be
building one quarter's worth of indexes.
C) It's possible that after a quarter is over, you won't even need to
reindex because maybe you won't need data from a previous quarter.
Good - glad to be wrong about that! We do mostly inserts, no updates and
> very few deletes. We drop entire tables but don't delete often. We have
> very long rows, though. Do you think the above is a situation likely to
> create extreme bloat?
I wonder, what do you use to decide when to reindex? Under this
situation, it's very possible that you don't need to do it all that often.
Are you just flying 'willy-nilly' about reindexing things, or is there some
indicator you use?
--Scott
>
> -Jennifer
>
> > > I _think_ autovacuum, somewhere around early 8.x resolves the
> transaction
> > > wrap-around issues, but someone else should verify that.
> >
> > Ayup.
> >
> > --
> > Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> > To make changes to your subscription:
> > http://www.postgresql.org/mailpref/pgsql-admin
>
> ------------------------------
> Lauren found her dream laptop. Find the PC that’s right for
> you.<http://www.microsoft.com/windows/choosepc/?ocid=ftp_val_wl_290>
>
