On Tue, Jul 7, 2009 at 1:40 PM, Jennifer Spencer
<jenniferm411@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.
>