Re: VARCHAR -vs- CHAR: huge performance difference?

On Thu, 2004-06-17 at 09:57, C. Bensend wrote:
> > The problem here isn't pg_autovacuum, but too small of settings for
> > fsm.  I've run multi-day tests where autovacuum kept the size of the
> > database pretty much the same with 200+ updates a second going on.
>
> Hi Scott,
>
>    Could you explain the fsm a little more?  I have done _no_ tuning on
> my database simply because I don't know how, but I would be interested
> to hear your recommendations for the fsm settings.

OK, Here's the basics of what fsm does.  As you probably already know,
PostgreSQL uses an in store multiple versioning system, where the first
version of a tuple points to next newest version, and so on, until we
get to the last version.  So, if you update the same row four times,
you'll have five copies of it in the data storage files, the original,
plus four copies.

Each transaction that needs to access said tuple will grab the first
one, check the date stamp on it, and go on the the next one, until it
finds the last valid one for it, based on the rules of visibility.  We
won't get into that right now, because what's important is that we have
5 versions of that tuple in memory.

Now, after all the currently processing transactions finish, all the new
transactions are going to be seeing the fifth version of the tuple from
then on.  But they'll all start by looking at the first one and working
forward one at a time.

Along comes vacuum and vacuum full.  Vacuum full marks all these dead
tuples, then actually compresses the data file back down to save the
actual lost space.  Plain (or lazy) vacuums simply mark the rows as
free, without actually reclaiming them.  The free space map is what
vacuum uses to mark those tuples as free, and what the storage manager
checks to find free space to place new tuples in the tables.

So, the free space map needs to be big enough to hold a reference to
every single freed row from vacuuming, or the vacuumed tuple space will
not get reused, and the storage manager will simply append new tuples
onto the end of the data file.

You can find a bit more on this subject at:

http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html