Thread: Where to find information on the new HOT tables?

Where to find information on the new HOT tables?

From
"Chris Hoover"
Date:
I have been searching through the 8.3 documentation trying to find information on the new HOT tables that 8.3 provides and can not seem to find any.  Can someone please point me towards some documentation on this new feature so I can better understand it, and see if I can implement it into our current application when we upgrade in a few months.

thanks,

Chris

--
Come see how to SAVE money on fuel, decrease harmful emissions, and even make MONEY.  Visit http://colafuelguy.mybpi.com and join the revolution!

Re: Where to find information on the new HOT tables?

From
Bruce Momjian
Date:
Chris Hoover wrote:
> I have been searching through the 8.3 documentation trying to find
> information on the new HOT tables that 8.3 provides and can not seem to find
> any.  Can someone please point me towards some documentation on this new
> feature so I can better understand it, and see if I can implement it into
> our current application when we upgrade in a few months.

HOT is automatic.  All tables are HOT-enabled.

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://postgres.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

Re: Where to find information on the new HOT tables?

From
"Chris Hoover"
Date:


On Feb 5, 2008 2:53 PM, Bruce Momjian <bruce@momjian.us> wrote:
Chris Hoover wrote:
> I have been searching through the 8.3 documentation trying to find
> information on the new HOT tables that 8.3 provides and can not seem to find
> any.  Can someone please point me towards some documentation on this new
> feature so I can better understand it, and see if I can implement it into
> our current application when we upgrade in a few months.

HOT is automatic.  All tables are HOT-enabled.

Ok that's awesome.  However, is there any documentation out there explaining this feature in detail, and how it works, what to expect, etc. ?

Thanks,

Chris


--
Come see how to SAVE money on fuel, decrease harmful emissions, and even make MONEY.  Visit http://colafuelguy.mybpi.com and join the revolution!

Re: Where to find information on the new HOT tables?

From
"Joshua D. Drake"
Date:
On Tue, 5 Feb 2008 15:11:49 -0500
"Chris Hoover" <revoohc@gmail.com> wrote:

> > Ok that's awesome.  However, is there any documentation out there
> explaining this feature in detail, and how it works, what to expect,
> etc. ?

As I understand it, the key to hot is that it is only useful if you are
updating non-indexed columns. This is great when dealing with things
like session tables where you are doing this:

UPDATE sessions SET last_active = current_date WHERE sid = <session_id>.

last_active may not indexed.

Sincerely,

Joshua D. Drake



--
The PostgreSQL Company since 1997: http://www.commandprompt.com/
PostgreSQL Community Conference: http://www.postgresqlconference.org/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL SPI Liaison | SPI Director |  PostgreSQL political pundit


Attachment

Re: Where to find information on the new HOT tables?

From
Alvaro Herrera
Date:
Chris Hoover escribió:

> Ok that's awesome.  However, is there any documentation out there
> explaining this feature in detail, and how it works, what to expect, etc. ?

Implementation documentation:

http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/access/heap/README.HOT?rev=1.2

Perhaps there is a user-level document somewhere.

--
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Re: Where to find information on the new HOT tables?

From
Dimitri Fontaine
Date:
Hi,

Le Tuesday 05 February 2008 21:28:31 Alvaro Herrera, vous avez écrit :
> Perhaps there is a user-level document somewhere.

I don't think such a document already exists, even rtfm_please doesn't know
about any as of now. So I'll try to begin something here, and depending on
the comments I'll publish a short user oriented HOT introduction article.
Here we go...

PostgreSQL implements HOT (Heap Only Tuples), a way for the server to limit
the work it has to make when updating tuples. That's what we call an
optimization :)

PostgreSQL MVCC implementation choice means that updating a tuple create a
entire new version of it and mark the old one as no longer valid (as of the
updating transaction id). Then VACUUM will have to clean out the old
reference as soon as possible.
Let's not forget that the indexes pointing the the old tuples need to point to
the new version of it as of transaction id. PostgreSQL currently does not
save visibility information into the index, though, reducing the janitoring
here. But still, for the index, the operation of updating a tuple is
equivalent to a delete and an insert.
That's before HOT.

Starting with PostgreSQL 8.3, when a tuple is updated and if the update only
concerns non-indexed columns, the RDBMS is smart enough for the existing
indexes not to need any update at all.

This is done by creating a new tuple if possible on the same page as the old
one, and maintaining a chain of updated tuples linking a new version to the
old one. An HOT tuple is in fact one that can't be reached from any index.
VACUUM will now only have to prune the tuple versions of the chain that are
no more visible, and as no index were updated (there was no need to), there's
no VACUUM work to get done on the indexes.
Of course, for HOT to work properly, PostgreSQL has now to follow each HOT
chain when SELECT'ing tuples and using an index, but the same amount of
tuples version was to be read before HOT too. The difference is that with HOT
the new versions of the HOT-updated tuples are no more reachable via the
index directly, so PostgreSQL has to follow the chain when reading the heap.

Please comment and correct me if my understanding is wrong (which wouldn't be
a surprise), if this article over simplified, or not really written in
English :)
I'd like to publish a correct version of this for us to point asking users to,
or maybe it could even end up as official documentation/FAQ material?

Regards,
--
dim

Attachment

Re: Where to find information on the new HOT tables?

From
Tom Lane
Date:
Dimitri Fontaine <dfontaine@hi-media.com> writes:
> Le Tuesday 05 February 2008 21:28:31 Alvaro Herrera, vous avez �crit�:
>> Perhaps there is a user-level document somewhere.

> I don't think such a document already exists, even rtfm_please doesn't know
> about any as of now.

The reason there isn't one is it doesn't need one; it's an
implementation detail not a user-visible feature.

            regards, tom lane

Re: Where to find information on the new HOT tables?

From
Dimitri Fontaine
Date:
Le Tuesday 05 February 2008 22:35:11 Tom Lane, vous avez écrit :
> The reason there isn't one is it doesn't need one; it's an
> implementation detail not a user-visible feature.

So how do you want us to handle the users questions about how HOT is good for
them and who'd like to have some insights of its inner working, but don't
want to properly learn the internals of PostgreSQL implementation?

Such users exist, are (in my POV) sanely curious about this open source
product they're using, and HOT has received full marketing lights now. I
think we should provide some user-level material about it, and that the fact
it is not a user-visible feature is not really relevant here. Of course
that's only a curious user opinion :)

Regards,
--
dim

Attachment

Re: Where to find information on the new HOT tables?

From
"Joshua D. Drake"
Date:
On Tue, 5 Feb 2008 23:13:59 +0100
Dimitri Fontaine <dfontaine@hi-media.com> wrote:

> Le Tuesday 05 February 2008 22:35:11 Tom Lane, vous avez écrit :
> > The reason there isn't one is it doesn't need one; it's an
> > implementation detail not a user-visible feature. ,

Although it is an implementation detail it is certainly a very
user-visible detail. It can and will affect how people design their
databases to take advantage of the implementation detail.

>
> So how do you want us to handle the users questions about how HOT is
> good for them and who'd like to have some insights of its inner
> working, but don't want to properly learn the internals of PostgreSQL
> implementation?

We need to write a one page mini-HOWTO on taking advantage of HOT and
HOTs limitations.

Sincerely,

Joshua D. Drake

--
The PostgreSQL Company since 1997: http://www.commandprompt.com/
PostgreSQL Community Conference: http://www.postgresqlconference.org/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL SPI Liaison | SPI Director |  PostgreSQL political pundit


Attachment

Re: Where to find information on the new HOT tables?

From
Tom Lane
Date:
"Joshua D. Drake" <jd@commandprompt.com> writes:
> Le Tuesday 05 February 2008 22:35:11 Tom Lane, vous avez écrit :
>>> The reason there isn't one is it doesn't need one; it's an
>>> implementation detail not a user-visible feature. ,

> Although it is an implementation detail it is certainly a very
> user-visible detail. It can and will affect how people design their
> databases to take advantage of the implementation detail.

We have no user-facing documentation on TOAST, either, which would seem
to me to be at least as much of a candidate for people to custom-design
their databases around.  In practice I don't think anyone does.

            regards, tom lane

Re: Where to find information on the new HOT tables?

From
Decibel!
Date:
On Tue, Feb 05, 2008 at 05:57:31PM -0500, Tom Lane wrote:
> "Joshua D. Drake" <jd@commandprompt.com> writes:
> > Le Tuesday 05 February 2008 22:35:11 Tom Lane, vous avez écrit :
> >>> The reason there isn't one is it doesn't need one; it's an
> >>> implementation detail not a user-visible feature. ,
>
> > Although it is an implementation detail it is certainly a very
> > user-visible detail. It can and will affect how people design their
> > databases to take advantage of the implementation detail.
>
> We have no user-facing documentation on TOAST, either, which would seem
> to me to be at least as much of a candidate for people to custom-design
> their databases around.  In practice I don't think anyone does.

Uh... http://www.postgresql.org/docs/8.3/interactive/storage-toast.html
--
Decibel!, aka Jim C. Nasby, Database Architect  decibel@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828

Attachment

Re: Where to find information on the new HOT tables?

From
Tom Lane
Date:
Decibel! <decibel@decibel.org> writes:
> On Tue, Feb 05, 2008 at 05:57:31PM -0500, Tom Lane wrote:
>> We have no user-facing documentation on TOAST, either, which would seem
>> to me to be at least as much of a candidate for people to custom-design
>> their databases around.  In practice I don't think anyone does.

> Uh... http://www.postgresql.org/docs/8.3/interactive/storage-toast.html

That's developer documentation, buried in the "internals" volume.

            regards, tom lane

Re: Where to find information on the new HOT tables?

From
Decibel!
Date:
On Tue, Feb 05, 2008 at 10:11:31PM -0500, Tom Lane wrote:
> Decibel! <decibel@decibel.org> writes:
> > On Tue, Feb 05, 2008 at 05:57:31PM -0500, Tom Lane wrote:
> >> We have no user-facing documentation on TOAST, either, which would seem
> >> to me to be at least as much of a candidate for people to custom-design
> >> their databases around.  In practice I don't think anyone does.
>
> > Uh... http://www.postgresql.org/docs/8.3/interactive/storage-toast.html
>
> That's developer documentation, buried in the "internals" volume.

Which any serious user will find and read.

One of the things that drew me to Postgres years ago was that I could
actually read about how it works in a clear, concise manner.
--
Decibel!, aka Jim C. Nasby, Database Architect  decibel@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828

Attachment

Re: Where to find information on the new HOT tables?

From
"Jonah H. Harris"
Date:
On Feb 5, 2008 11:54 PM, Decibel! <decibel@decibel.org> wrote:
> One of the things that drew me to Postgres years ago was that I could
> actually read about how it works in a clear, concise manner.

Agreed.

--
Jonah H. Harris, Sr. Software Architect | phone: 732.331.1324
EnterpriseDB Corporation                | fax: 732.331.1301
499 Thornall Street, 2nd Floor          | jonah.harris@enterprisedb.com
Edison, NJ 08837                        | http://www.enterprisedb.com/

Re: Where to find information on the new HOT tables?

From
Tom Lane
Date:
"Jonah H. Harris" <jonah.harris@gmail.com> writes:
> On Feb 5, 2008 11:54 PM, Decibel! <decibel@decibel.org> wrote:
>> One of the things that drew me to Postgres years ago was that I could
>> actually read about how it works in a clear, concise manner.

> Agreed.

Not sure how you could argue "concise" as a benefit here.

There are literally thousands of aspects of the PG codebase that could
impact performance in user-visible ways.  Most of them are not
documented in the SGML docs.  If we tried to expose all that, the docs
would become completely unreadable.  (I just got done reading some
slashdot griping about how our docs are already too long and too
complicated for novices, so I'm not feeling particularly charitable
about proposals to dump even more seldom-useful details into them.)

I'm really not seeing the case for user-level documentation of HOT,
when for instance most of the planner's optimization behavior is not
so documented.

            regards, tom lane

Re: Where to find information on the new HOT tables?

From
"Markus Bertheau"
Date:
2008/2/6, Tom Lane <tgl@sss.pgh.pa.us>:

> I'm really not seeing the case for user-level documentation of HOT,
> when for instance most of the planner's optimization behavior is not
> so documented.

Actually that would be very useful information. Questions like how is
cost calculated, how are joins ordered, which kinds of subselects are
pulled up to the outer query and so on are quite essential when
optimizing queries and understanding query plans. Also more detailed
information on how to tune the various performance parameters for
bg_writer, autovacuum, checkpointing and so on would be useful in my
opinion.

I find the current manual very useful and the fact that it's 2000
pages doesn't make it any less useful.

Markus

Re: Where to find information on the new HOT tables?

From
Dimitri Fontaine
Date:
Le mercredi 06 février 2008, Tom Lane a écrit :
> I'm really not seeing the case for user-level documentation of HOT,
> when for instance most of the planner's optimization behavior is not
> so documented.

In my POV, the case for HOT to appear in the documentation as been made by the
PR and Presskit. They both announce HOT to be a major performance addition in
8.3 that users will want to benefit from. Now we have to tell some more to
the users, I think we want to answer those two basic questions:
 - How do I check that I'm using HOT? (you don't, transparent feature, blah)
 - What's HOT is so good about compared to how PostgreSQL used to work?

My proposal was about answering this without requiring the user to be capable
of understanding internals and 'developer topics'.

If you want me to talk about another documentation missing entry, I'll be
happy to request a user targetted presentation of Executor Nodes, their input
and output, relative costs (simplified algorithms?) and the reason behind
their choice by the planner. I'm slowly becoming familiar with their names, a
little more each time I Explain [Analyze] a query, but not enough so to yet
be able to sketch an article about this...

I'd also like to add that people judging documentation effectiveness by its
length are certainly not real users of it. I've yet to meet a PostgreSQL user
who doesn't praise the product documentation, even if newcomers often need
some time to be able to understand where to find what. The Tsearch enabled
website has been a huge improvement here.
To ease PostgreSQL newbies manual grasping, I certainly don't think it will
need shortening the documentation.

Once more, all of this is only a enthusiastic user POV, the kind who think
he's helping when writing this mail ;)
--
dim

Attachment

Re: Where to find information on the new HOT tables?

From
Guillaume Lelarge
Date:
Tom Lane wrote:
> "Jonah H. Harris" <jonah.harris@gmail.com> writes:
>> On Feb 5, 2008 11:54 PM, Decibel! <decibel@decibel.org> wrote:
>>> One of the things that drew me to Postgres years ago was that I could
>>> actually read about how it works in a clear, concise manner.
>
>> Agreed.
>
> Not sure how you could argue "concise" as a benefit here.
>
> There are literally thousands of aspects of the PG codebase that could
> impact performance in user-visible ways.  Most of them are not
> documented in the SGML docs.  If we tried to expose all that, the docs
> would become completely unreadable.  (I just got done reading some
> slashdot griping about how our docs are already too long and too
> complicated for novices, so I'm not feeling particularly charitable
> about proposals to dump even more seldom-useful details into them.)
>

That's something we hear on booths too. Someone, at Solutions Linux
2008, told me the manual is great but not that easy to begin with. He
reads Sébastien Lardière's french book on PostgreSQL and finds it more
convenient for him. And now he needs something more advanced but still
not the manual... problem is, he's not able to explain why he doesn't
like our current manual.

> I'm really not seeing the case for user-level documentation of HOT,
> when for instance most of the planner's optimization behavior is not
> so documented.
>

A user level, no. But an advanced/developper level documentation would
be greatly appreciated.

Really, I found our manual great. I've read it many times, I've
translated it, I think I know it a bit. But I think a 1500 pages manual
is a bit too much for a beginner. It kind of afraids them. And it may
not be enough for advanced users. Drawing the line between not enough
and too much is not that easy. But I'm sure there's work to do on the
documentation front.

Regards.


--
Guillaume.
  http://www.postgresqlfr.org
  http://dalibo.com

Re: Where to find information on the new HOT tables?

From
Tino Schwarze
Date:
Hi there,

On Wed, Feb 06, 2008 at 10:11:09AM +0100, Dimitri Fontaine wrote:

> > I'm really not seeing the case for user-level documentation of HOT,
> > when for instance most of the planner's optimization behavior is not
> > so documented.
>
> In my POV, the case for HOT to appear in the documentation as been made by the
> PR and Presskit. They both announce HOT to be a major performance addition in
> 8.3 that users will want to benefit from. Now we have to tell some more to
> the users, I think we want to answer those two basic questions:
>  - How do I check that I'm using HOT? (you don't, transparent feature, blah)
>  - What's HOT is so good about compared to how PostgreSQL used to work?
>
> My proposal was about answering this without requiring the user to be capable
> of understanding internals and 'developer topics'.

Which might be pretty difficult, if it's an internal feature. But a
short HOT-FAQ would be great, something like this:

- What is HOT?
...
- How do I exploit it?
  Make sure that ...
- When will HOT kick in?
  If ...
  and ...
  and ...


> I'd also like to add that people judging documentation effectiveness by its
> length are certainly not real users of it. I've yet to meet a PostgreSQL user
> who doesn't praise the product documentation, even if newcomers often need
> some time to be able to understand where to find what. The Tsearch enabled
> website has been a huge improvement here.

Yes, I praise it, too! Even though it needs some reading and thinking
and reading and thinking to understand, e.g. the internals of VACUUM or
freeze_max_age etc., the documentation is very helpful to understand
what's going on.

> To ease PostgreSQL newbies manual grasping, I certainly don't think it will
> need shortening the documentation.

Maybe there could be quickstart chapters or HOWTOs.

Bye,

Tino.

--
www.craniosacralzentrum.de
www.spiritualdesign-chemnitz.de

Tino Schwarze * Lortzingstraße 21 * 09119 Chemnitz

Re: Where to find information on the new HOT tables?

From
Adam Tauno Williams
Date:
> I'd also like to add that people judging documentation effectiveness by its
> length are certainly not real users of it. I've yet to meet a PostgreSQL user
> who doesn't praise the product documentation, even if newcomers often need
> some time to be able to understand where to find what. The Tsearch enabled
> website has been a huge improvement here.
> To ease PostgreSQL newbies manual grasping, I certainly don't think it will
> need shortening the documentation.

Agree, 125%.  There are quick start guides for newbies, and a
quadrillion goofy little howtos (although I've come to have some
contempt for the whole "howto" category).   I chose PostgreSQL over
that-other-database because it had useful documentation and smelled like
a "real" database.  I'm also an Informix/DB2 DBA and I think the
documentation is very good,  there can't be too much documentation so
anyone judging on length just needs to be dope-slapped.  [And I've
personally used the TOAST section to tune a database that uses lots of
large values].  The current manual is divided into sections, including a
tutorial so I just don't see any grounds for a complexity complaint at
all.  A new user should, of course, wade into the "System
Administration" section with some sense of trepidation.

My experience [now extensive] with users of that-other-database is that
they will *ALWAYS* claim *ANYTHING* else is too complicated [most of
them have never used anything else] before starting to explain why they
have two storage backends and the merits between MyISAM and InnoDb;
Sheeesh!

It is important to distinguish between informed criticism and the
triumvirate of laziness, fanboy FUD, and outright whining.

OTOH,  I think a paragraph explaining how HOT causes performance to
differ from previous versions would be sufficient.  It isn't really a
user, or even an admin, tunable AFAIK.  It is just an inherent good in
the current version - but since it was mentioned allot in release
announcements it merits some mention in the docs.

Adam Tauno Williams, Network & Systems Administrator
Consultant - http://www.whitemiceconsulting.com
Developer - http://www.opengroupware.org


Re: Where to find information on the new HOT tables?

From
Alvaro Herrera
Date:
Tom Lane escribió:
> Dimitri Fontaine <dfontaine@hi-media.com> writes:
> > Le Tuesday 05 February 2008 21:28:31 Alvaro Herrera, vous avez �crit�:
> >> Perhaps there is a user-level document somewhere.
>
> > I don't think such a document already exists, even rtfm_please doesn't know
> > about any as of now.
>
> The reason there isn't one is it doesn't need one; it's an
> implementation detail not a user-visible feature.

One reason to think it needs to be explained is that it affects how you
plan for VACUUM.  In tables where HOT is in charge of a large part of
cleanup, you need to do a lot less vacuuming than you were for 8.2.  In
that sense it is very much user-visible.

--
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

Re: Where to find information on the new HOT tables?

From
Adam Tauno Williams
Date:
> > > I don't think such a document already exists, even rtfm_please doesn't know
> > > about any as of now.
> > The reason there isn't one is it doesn't need one; it's an
> > implementation detail not a user-visible feature.
> One reason to think it needs to be explained is that it affects how you
> plan for VACUUM.  In tables where HOT is in charge of a large part of
> cleanup, you need to do a lot less vacuuming than you were for 8.2.  In
> that sense it is very much user-visible.

Wouldn't the thresholds in the auto-vaccuming make that adjustment
transparent?


Re: Where to find information on the new HOT tables?

From
Alvaro Herrera
Date:
Adam Tauno Williams escribió:
> > > > I don't think such a document already exists, even rtfm_please doesn't know
> > > > about any as of now.
> > > The reason there isn't one is it doesn't need one; it's an
> > > implementation detail not a user-visible feature.
> > One reason to think it needs to be explained is that it affects how you
> > plan for VACUUM.  In tables where HOT is in charge of a large part of
> > cleanup, you need to do a lot less vacuuming than you were for 8.2.  In
> > that sense it is very much user-visible.
>
> Wouldn't the thresholds in the auto-vaccuming make that adjustment
> transparent?

For people that use autovacuum, yes, it would, but not everybody does.

--
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Re: Where to find information on the new HOT tables?

From
"Hans Guijt"
Date:
On the subject of documentation I'd like to say this: I find
PostgreSQL's documentation to be truly excellent (both in accessibility
and depth), and it is one of the main reasons why we use PostgreSQL in
our projects (the quality of the software itself is of course the other
reason).

I certainly wouldn't mind explanations of 'internal' features on a user
level; as others have stated, this may be helpful in understanding the
output of EXPLAIN, as a means of building better databases, or even just
because we are interested but not quite so much that we want to read
source code. And I agree with earlier posters that, now that HOT has
been pushed as a great new feature, it should be properly explained.

People who complain about the PostgreSQL documentation need to wake up:
the documentation of PostgreSQL is one of the best, if not the best,
I've ever seen for an open source project, and other projects should
take it as a shining example of how things should be done.


Hans Guijt


Re: Where to find information on the new HOT tables?

From
Michael Monnerie
Date:
I let Hans' full e-mail quoted here because it's absolutely true: It's
the best documentation available, and that's why we always preferred
PostgreSQL over that other - ehrm - "database" MySQL. And of course
because it's really a DB ;-)

And yes, I'd love even more docu for EXPLAIN, and howto optimize the DB
in general. Can there ever be enough detail? Those who don't understand
it don't have to read it.

mfg zmi

On Donnerstag, 7. Februar 2008 Hans Guijt wrote:
> On the subject of documentation I'd like to say this: I find
> PostgreSQL's documentation to be truly excellent (both in
> accessibility and depth), and it is one of the main reasons why we
> use PostgreSQL in our projects (the quality of the software itself is
> of course the other reason).
>
> I certainly wouldn't mind explanations of 'internal' features on a
> user level; as others have stated, this may be helpful in
> understanding the output of EXPLAIN, as a means of building better
> databases, or even just because we are interested but not quite so
> much that we want to read source code. And I agree with earlier
> posters that, now that HOT has been pushed as a great new feature, it
> should be properly explained.
>
> People who complain about the PostgreSQL documentation need to wake
> up: the documentation of PostgreSQL is one of the best, if not the
> best, I've ever seen for an open source project, and other projects
> should take it as a shining example of how things should be done.



--
// Michael Monnerie, Ing.BSc    -----      http://it-management.at
// Tel: 0676/846 914 666                      .network.your.ideas.
// PGP Key:         "curl -s http://zmi.at/zmi.asc | gpg --import"
// Fingerprint: AC19 F9D5 36ED CD8A EF38  500E CE14 91F7 1C12 09B4
// Keyserver: www.keyserver.net                   Key-ID: 1C1209B4

Attachment

Re: Where to find information on the new HOT tables?

From
Dimitri Fontaine
Date:
Hi,

Le Tuesday 05 February 2008 22:13:37 Dimitri Fontaine, vous avez écrit :
> the comments I'll publish a short user oriented HOT introduction article.
> Here we go...

I don't think there was anythink like a consensus about the opportunity to
publish a user-level presentation of HOT in the FAQ or any other official
documentation, and I still think we'll keep getting users questions about it,
with good reason for them to ask, so:
  http://pgsql.tapoueh.org/site/html/misc/hot.html

> Please comment and correct me if my understanding is wrong (which wouldn't
> be a surprise), if this article over simplified, or not really written in
> English :)

Regards,
--
dim

Attachment

Re: Where to find information on the new HOT tables?

From
Bruce Momjian
Date:
Dimitri Fontaine wrote:
-- Start of PGP signed section.
> Hi,
>
> Le Tuesday 05 February 2008 22:13:37 Dimitri Fontaine, vous avez ?crit?:
> > the comments I'll publish a short user oriented HOT introduction article.
> > Here we go...
>
> I don't think there was anythink like a consensus about the opportunity to
> publish a user-level presentation of HOT in the FAQ or any other official
> documentation, and I still think we'll keep getting users questions about it,
> with good reason for them to ask, so:
>   http://pgsql.tapoueh.org/site/html/misc/hot.html

Does the release note item on HOT need additions?

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://postgres.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

Re: Where to find information on the new HOT tables?

From
Dimitri Fontaine
Date:
Le Thursday 07 February 2008 20:43:57 Bruce Momjian, vous avez écrit :
> Does the release note item on HOT need additions?

If I put the release not item on HOT here:

-=-=-=-
Heap-Only Tuples (HOT) accelerate space reuse for most UPDATEs and DELETEs
(Pavan Deolasee, with ideas from many others)

UPDATEs and DELETEs leave dead tuples behind, as do failed INSERTs. Previously
only VACUUM could reclaim space taken by dead tuples. With HOT dead tuple
space can be reclaimed at the time of INSERT or UPDATE if no changes are made
to indexed columns. This allows for more consistent performance. Also, HOT
avoids adding duplicate index entries.
-=-=-=-

I'd say it only misses the transparent & automatic precision, telling the
users they don't have anything to do about HOT: PostgreSQL is able on its own
to decide when HOT is or is not safe to use.
And I have to get it back that documentation is not verbose enough about how
HOT is working, just that you have to know where to look...

What about Alvaro's concern about manual vacuuming policies editing wrt to
HOT?

Regards,
--
dim

Attachment

Re: Where to find information on the new HOT tables?

From
Bruce Momjian
Date:
Dimitri Fontaine wrote:
-- Start of PGP signed section.
> Le Thursday 07 February 2008 20:43:57 Bruce Momjian, vous avez ?crit?:
> > Does the release note item on HOT need additions?
>
> If I put the release not item on HOT here:
>
> -=-=-=-
> Heap-Only Tuples (HOT) accelerate space reuse for most UPDATEs and DELETEs
> (Pavan Deolasee, with ideas from many others)
>
> UPDATEs and DELETEs leave dead tuples behind, as do failed INSERTs. Previously
> only VACUUM could reclaim space taken by dead tuples. With HOT dead tuple
> space can be reclaimed     at the time of INSERT or UPDATE if no changes are made

                         ^^^

> to indexed columns. This allows for more consistent performance. Also, HOT
> avoids adding duplicate index entries.
> -=-=-=-

OK, what I did was to add the word "automatically" at the location
indicated.  Hopefully that will help.

> I'd say it only misses the transparent & automatic precision, telling the
> users they don't have anything to do about HOT: PostgreSQL is able on its own
> to decide when HOT is or is not safe to use.
> And I have to get it back that documentation is not verbose enough about how
> HOT is working, just that you have to know where to look...
>
> What about Alvaro's concern about manual vacuuming policies editing wrt to
> HOT?

I think most people will be using autovacuum, and those who aren't will
have to realize that HOT makes vacuuming less needed.

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://postgres.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

Re: Where to find information on the new HOT tables?

From
Alvaro Herrera
Date:
Bruce Momjian escribió:
> Dimitri Fontaine wrote:

> > What about Alvaro's concern about manual vacuuming policies editing wrt to
> > HOT?
>
> I think most people will be using autovacuum, and those who aren't will
> have to realize that HOT makes vacuuming less needed.

Hmm, and how will they realize it, if they don't understand how HOT
comes into the equation?

--
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

Re: Where to find information on the new HOT tables?

From
"Chris Hoover"
Date:


On Feb 7, 2008 6:48 PM, Alvaro Herrera <alvherre@commandprompt.com> wrote:
Bruce Momjian escribió:
> Dimitri Fontaine wrote:

> > What about Alvaro's concern about manual vacuuming policies editing wrt to
> > HOT?
>
> I think most people will be using autovacuum, and those who aren't will
> have to realize that HOT makes vacuuming less needed.

Hmm, and how will they realize it, if they don't understand how HOT
comes into the equation?

How about just adding a "for more information look here link" pointing to http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/access/heap/README.HOT?rev=1.2

That is the link Alvaro sent me in the first part of the thread.  It was a very good read explaining how HOT works along with the caveats.

Chris


--
Come see how to SAVE money on fuel, decrease harmful emissions, and even make MONEY.  Visit http://colafuelguy.mybpi.com and join the revolution!

Re: Where to find information on the new HOT tables?

From
Bruce Momjian
Date:
Alvaro Herrera wrote:
> Bruce Momjian escribi?:
> > Dimitri Fontaine wrote:
>
> > > What about Alvaro's concern about manual vacuuming policies editing wrt to
> > > HOT?
> >
> > I think most people will be using autovacuum, and those who aren't will
> > have to realize that HOT makes vacuuming less needed.
>
> Hmm, and how will they realize it, if they don't understand how HOT
> comes into the equation?

Well, since this is a change in the behavior of this release, and not
something that will change over time, perhaps we need a sentence in
there about vacuuming changes.

The release note item says we can now reclaim without vacuum:

       "UPDATE"s and "DELETE"s leave dead tuples behind, as do failed
       "INSERT"s. Previously only "VACUUM" could reclaim
       space taken by dead tuples. With HOT dead tuple space can be
       automatically reclaimed at the time of "INSERT" or ...

so it seems logical that the need to vacuum would change.  We can add
an explicit sentence about that to the release notes.

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://postgres.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

Re: Where to find information on the new HOT tables?

From
Bruce Momjian
Date:
Chris Hoover wrote:
> On Feb 7, 2008 6:48 PM, Alvaro Herrera <alvherre@commandprompt.com> wrote:
>
> > Bruce Momjian escribi?:
> > > Dimitri Fontaine wrote:
> >
> > > > What about Alvaro's concern about manual vacuuming policies editing
> > wrt to
> > > > HOT?
> > >
> > > I think most people will be using autovacuum, and those who aren't will
> > > have to realize that HOT makes vacuuming less needed.
> >
> > Hmm, and how will they realize it, if they don't understand how HOT
> > comes into the equation?
> >
>
> How about just adding a "for more information look here link" pointing
> to http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/access/heap/README.HOT?rev=1.2
>
>
> That is the link Alvaro sent me in the first part of the thread.  It was a
> very good read explaining how HOT works along with the caveats.

Sure, we could do that, but it is very technical.  I would say 99% of
users don't need to know those details.

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://postgres.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

Re: Where to find information on the new HOT tables?

From
Alvaro Herrera
Date:
Bruce Momjian escribió:

> The release note item says we can now reclaim without vacuum:
>
>        "UPDATE"s and "DELETE"s leave dead tuples behind, as do failed
>        "INSERT"s. Previously only "VACUUM" could reclaim
>        space taken by dead tuples. With HOT dead tuple space can be
>        automatically reclaimed at the time of "INSERT" or ...
>
> so it seems logical that the need to vacuum would change.  We can add
> an explicit sentence about that to the release notes.

Ok, but the problem is not only realizing that you need to change it;
you also need to know what to.

--
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support