Re: autovacuum not prioritising for-wraparound tables

On 1/30/13 4:37 PM, Christopher Browne wrote:
>> From a more practical standpoint, I think it would be extremely useful to
>> >have a metric that showed how quickly a table churned. Something like dead
>> >tuples per time period. Comparing that to the non-bloated table size should
>> >give a very strong indication of how critical frequent vacuums on that table
>> >are.
> That's a nice idea, though I'm pretty sure that this requires capturing and
> storing a lot more statistical data, which presumably worsens things
> (presuming the data gets stored in tables), making the database bigger,
> adding an extra set of data that needs to get collected, queried, and
> trimmed, and chewing through still more XIDs.

We don't necessarily need added storage (or at least nothing serious).
It could potentially be as simple as remembering when we last took a
measurement and using that to also store number of blahs per second.
That's only 2 added data points (time and the rate). Of course, there
are pitfalls to something that simple, though some of those pitfals have
simple solutions (such as using estimated weighted means).

In this case I think we would be better served by trying to define a
good metric first, and then worrying about how to actually capture and
store it.

>> >I don't have a good metric in mind for freeze right now, but I do want to
>> >mention a use case that I don't think has come up before. When building a
>> >londiste slave (and presumably all the other trigger replication systems
>> >suffer from this), each table is copied over in a single transaction, and
>> >then updates start flowing in for that table. That can easily result in a
>> >scenario where you have an enormous volume of tuples that will all need
>> >freezing at almost exactly the same time. It would be nice if we could
>> >detect such a condition and freeze those tuples over time, instead of trying
>> >to freeze all of them in one shot.
> I wonder if these are perhaps good candidates for being frozen immediately;
> COPY FREEZE was added in not so long ago; it doesn't perfectly cover
> this, but if I squint at it a bit...
>
> http://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=8de72b66a2edcf12c812de0a73bd50b6b7d81d62
>
> I don't see a lot of value to having the data COPYed over to a subscriber
> NOT getting frozen immediately.

Yeah, COPY FREEZE would probably be highly valuable in this case.