Re: Catching up Production from Warm Standby aftermaintenance - Please help

On Tue, Jul 7, 2009 at 1:40 PM, Jennifer Spencer
<jenniferm411(at)hotmail(dot)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(at)postgresql(dot)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>
>