Re: When to do a vacuum for highly active table

From: mark@mark.mielke.cc
Subject: Re: When to do a vacuum for highly active table
Date: ,
Msg-id: 20050830220503.GA27740@mark.mielke.cc
(view: Whole thread, Raw)
In response to: Re: When to do a vacuum for highly active table  (Tom Lane)
Responses: Re: When to do a vacuum for highly active table  (Tom Lane)
List: pgsql-performance

Tree view

When to do a vacuum for highly active table  (Markus Benne, )
 Re: When to do a vacuum for highly active table  (Tom Lane, )
  Re: When to do a vacuum for highly active table  (, )
   Re: When to do a vacuum for highly active table  (Tom Lane, )
 Re: When to do a vacuum for highly active table  (Chris Browne, )
 Re: When to do a vacuum for highly active table  ("Rigmor Ukuhe", )
 Re: When to do a vacuum for highly active table  (Chris Browne, )

On Tue, Aug 30, 2005 at 05:29:17PM -0400, Tom Lane wrote:
> Markus Benne <> writes:
> > We have a highly active table that has virtually all
> > entries updated every 5 minutes.  Typical size of the
> > table is 50,000 entries, and entries have grown fat.
> ...
> > We are thinking of splitting the table in two: the
> > part the updates often, and the part the updates
> > infrequently as we suspect that record size impacts
> > vacuum.
> You just said that virtually all rows update constantly --- where's
> the "infrequent" part?

I think he means splitting it vertically, instead of horizontally, and
it sounds like an excellent idea, if a large enough portion of each
record is in fact mostly fixed. Otherwise, PostgreSQL is copying data
multiple times, only to have the data expire as part of a dead row.

I've already started to notice such issues with postgresql - but more
because I'm using low-end hardware, and I'm projecting the effect for
when our database becomes much larger with much higher demand on the
database.

This is the sort of scenario where a database without transactional
integrity would significantly out-perform one designed around it. If
records are fixed sized, and updated in place, these problems would
occur far less often. Is it heresy to suggest MySQL in here? :-)

I switched from MySQL to PostgreSQL several months ago, and haven't
looked back - but they do work differently, and for certain uses, one
can destroy the other. Using a MyISAM table would be the way I would
go with this sort of problem.

Cheers,
mark

--
 /  /      __________________________
.  .  _  ._  . .   .__    .  . ._. .__ .   . . .__  | Neighbourhood Coder
|\/| |_| |_| |/    |_     |\/|  |  |_  |   |/  |_   |
|  | | | | \ | \   |__ .  |  | .|. |__ |__ | \ |__  | Ottawa, Ontario, Canada

  One ring to rule them all, one ring to find them, one ring to bring them all
                       and in the darkness bind them...

                           http://mark.mielke.cc/



pgsql-performance by date:

From: Ron
Date:
Subject: Re: RAID Configuration Sugestion
From: "Mindaugas Riauba"
Date:
Subject: Re: 'Real' auto vacuum?