Re: limiting hint bit I/O

On Jan 14, 2011, at 7:24 PM, Josh Berkus wrote:
> On 1/14/11 11:51 AM, Tom Lane wrote:
>> The people whose tables are mostly insert-only complain about it, but
>> that's not the majority of our userbase IMO. We just happen to have a
>> couple of particularly vocal ones, like Berkus.
>
> It might or might not be the majority, but it's an extremely common case
> affecting a lot of users. Many, if not most, software applications have
> a "log" table (or two, or three) which just accumulates rows, and when
> that log table gets a vacuum freeze it pretty much halts the database in
> its tracks. Between my client practice and IRC, I run across complaints
> about this issue around 3 times a month.
>
> And data warehousing is a significant portion of our user base, and
> *all* DW users are affected by this. In some cases, vacuum issues are
> sufficient to prevent people from using PostgreSQL for data warehousing.

This also affects us every time we stand up a new londiste replica, and I expect Slony folks would suffer the same thing. When you copy everything over, that's going to happen in a relatively short range of XIDs, so when those XIDs start hitting freeze age suddenly *everything* needs to get frozen.

As for hint bits, you're generally not going to have anyone reading from a slave that's still being built, so you won't see any hint bit setting until you actually open up for users. So for the first X amount of time, performance takes a big hit because you have to write all the hints out. Granted, you can technically VACUUM FREEZE after the slave is built, but that means more time before you can start using the slave and it's something you have to remember to do.
--
Jim C. Nasby, Database Architect jim(at)nasby(dot)net
512.569.9461 (cell) http://jim.nasby.net