Re: snapshots in analyze

On 2015-10-31 12:52:44 -0400, Tom Lane wrote:
> Andres Freund <andres(at)anarazel(dot)de> writes:
> > Another angle would be trying to reduce the effects of longrunning
> > transaction. Right now holding a snapshot open for 100 seconds results
> > in profiles like this: ...
> > which is pretty extreme. It's not such a seldom thing to hold a snapshot
> > (e.g. pg_dump...) open for a while.
>
> Yeah. I'm afraid blaming ANALYZE for this is just shooting the messenger.
> Even in a mostly-OLTP workload there are going to be some long-running
> queries, for reporting or whatever. I think we need to focus on how we
> can improve that situation in general, rather than inserting kluges into
> ANALYZE (or pg_dump).

I think we might want to do both. It'd obviously would be of great value
to handle longrunning transactions better. But even if we had an
implementation that knew exactly which rows are not visible to anyone, a
big if, such a longrunning transaction would still at least cause bloat
up to a factor of two (with just one longrunning xact).

Sure pg_dump and analytics queries do so as well, but the user actually
has control over these and can schedule them at opportune times, use
base backups and/or hot standby replicas.

Greetings,

Andres Freund