Thread: HOT question - insert/delete
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? Or is vacuum still needed for this scenario? Thnx. Ciao, Gerhard -- http://www.wiesinger.com/
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: > 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 > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
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: > > 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 > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >