HOT question - insert/delete

On Wed, May 20, 2009 at 3:11 PM, Gerhard Wiesinger <lists@wiesinger.com> wrote:

Hello!

Is HOT in 8.3 used in the following scenario:
INSERT ...
DELETE ...
at the same ratio.

So for example current data is added every minute and all old data older
than 2 years are deleted.

Would the heap be bloated or is this avoided by the HOT feature of 8.3 and
nearly kept constant?

HOT doesn't help here...it only helps with updates and then only if
you are updating fields that are not indexed. if your table has a
rolling set of data, for example a log file...you probably want to
look at table partitioning (either manual or built in).

merlin

On Wed, May 20, 2009 at 9:01 PM, Merlin Moncure <mmoncure@gmail.com> wrote:

On Wed, May 20, 2009 at 3:11 PM, Gerhard Wiesinger <lists@wiesinger.com> wrote:

Hello!

Is HOT in 8.3 used in the following scenario:
INSERT ...
DELETE ...
at the same ratio.

So for example current data is added every minute and all old data older
than 2 years are deleted.

Would the heap be bloated or is this avoided by the HOT feature of 8.3 and
nearly kept constant?

HOT doesn't help here...it only helps with updates and then only if
you are updating fields that are not indexed.  if your table has a
rolling set of data, for example a log file...you probably want to
look at table partitioning (either manual or built in).

on that note, does HOT works in case I have TOASTed columns ? bytea,
varchar(), etc ?

--
GJ

On Wed, 2009-05-20 at 16:01 -0400, Merlin Moncure wrote:

On Wed, May 20, 2009 at 3:11 PM, Gerhard Wiesinger <lists@wiesinger.com> wrote:

Hello!

Is HOT in 8.3 used in the following scenario:
INSERT ...
DELETE ...
at the same ratio.

So for example current data is added every minute and all old data older
than 2 years are deleted.

Would the heap be bloated or is this avoided by the HOT feature of 8.3 and
nearly kept constant?

HOT doesn't help here...it only helps with updates and then only if
you are updating fields that are not indexed.

Partial vacuum, in 8.4, will deal with this situation, though
partitioning does sound best for such clearly historical data.

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support

Are there any plans to support this kind of scenario?

Ciao,
Gerhard

--
http://www.wiesinger.com/

On Wed, 20 May 2009, Merlin Moncure wrote:

The data isn't really historical, but some data have to be for e.g. some
regulations after a period of time. But all the available data should be
available for e.g. reporting. So partitioning doesn't make any sense
in this case, right?

Ciao,
Gerhard

--
http://www.wiesinger.com/

On Thu, 21 May 2009, Simon Riggs wrote: