Thread: Postgresql Materialized views

Postgresql Materialized views

From
Jean-Michel Pouré
Date:
Dear Friends,

In my past development projects, I always used the concept of
Materialized VIEW to speed-up SELECTs over INSERTs.

You are well aware of Jonathan Gardner preliminary work:
http://www.varlena.com/varlena/GeneralBits/Tidbits/matviews.html

When do you plan to add MATERIALIZED VIEWS to PostgreSQL?
This would be major improvement ni the case of Web applications.

I run a 400.000+ message board using PhpBB 3.0. After optimization, some
queries still need 30 millisecond to run. With Materialized views, it
should be possible to drive these queries to 1 millisecond. This means
that in some situations a PostgreSQL backend could handle 10 times more
queries.

My database handles 10 to 20 queries every second. There are 100 selects
for 1 INSERT. But my database could well handle over 500 queries a
second using materialized views.

At my level, here are my plans:

1) Publish some long query LOGs from my database, longuer than 30
milliseconds.

2) Write some PL code to demonstrate the interest in Materialized Views.
Publish benckmarks showing time improvement, like 1 milisecond. 30x
faster.

3) Then wait for someone on Hackers mailing list to pick-up this
important issue and integrate Materialized views in PostgreSQL schema
and SQL language.

Any information and discussion about materialized views is welcome.

Please pick-up this important issue for developpers. There is no need to
concentrate on complex issues, when handling materialized views could
boost somme web apps. by a factor of 10 or more.

Kind regards and happy new year.
I hope that 2008 will be the year of materialized views.

Jean-Michel Pouré



Re: Postgresql Materialized views

From
Mark Mielke
Date:
Jean-Michel Pouré wrote:
> In my past development projects, I always used the concept of
> Materialized VIEW to speed-up SELECTs over INSERTs

Unless you are going to *pay* for it - you do realize that the best way 
to get it implemented, would be to open up the source code, and give it 
a try yourself?

If it was so easy, and such a clear win, I think one of the very 
competent people using PostgreSQL today would have already done it?

Cheers,
mark

-- 
Mark Mielke <mark@mielke.cc>


Re: Postgresql Materialized views

From
"Dave Page"
Date:
On 12/01/2008, Mark Mielke <mark@mark.mielke.cc> wrote:
> Jean-Michel Pouré wrote:
> > In my past development projects, I always used the concept of
> > Materialized VIEW to speed-up SELECTs over INSERTs
>
> Unless you are going to *pay* for it - you do realize that the best way
> to get it implemented, would be to open up the source code, and give it
> a try yourself?

In fairness to Jean-Michel, he has spent hundreds of hours in the past
doing just that and far more for the pgAdmin users in the community -
I'm sure we can excuse him for asking for what many do think would be
a useful feature in the hopes that someone listening might just decide
to pick it up.

In the meantime though - have you tried rolling your own materialised
views with some triggers Jean-Michel? I have good results doing that
in the past.

Regards, Dave


Re: Postgresql Materialized views

From
Mark Mielke
Date:
Dave Page wrote: <blockquote cite="mid:937d27e10801121029n10fd242gc5a7089e8905b0dc@mail.gmail.com" type="cite"><pre
wrap="">On12/01/2008, Mark Mielke <a class="moz-txt-link-rfc2396E"
href="mailto:mark@mark.mielke.cc"><mark@mark.mielke.cc></a>wrote: </pre><blockquote type="cite"><pre
wrap="">Jean-MichelPouré wrote:   </pre><blockquote type="cite"><pre wrap="">In my past development projects, I always
usedthe concept of
 
Materialized VIEW to speed-up SELECTs over INSERTs     </pre></blockquote><pre wrap="">Unless you are going to *pay*
forit - you do realize that the best way
 
to get it implemented, would be to open up the source code, and give it
a try yourself?   </pre></blockquote><pre wrap="">
In fairness to Jean-Michel, he has spent hundreds of hours in the past
doing just that and far more for the pgAdmin users in the community -
I'm sure we can excuse him for asking for what many do think would be
a useful feature in the hopes that someone listening might just decide
to pick it up.

In the meantime though - have you tried rolling your own materialised
views with some triggers Jean-Michel? I have good results doing that
in the past</pre></blockquote> I'm not good with names - I suppose Jean-Michel should be asking his benefactors to
returnthe favour then? :-)<br /><br /> In my own case - I use a combination of triggers and application to maintain
materializedviews - but the subject does seem complex to me.<br /><br /> The last two uses of materialized views I
used:<br/><br /> Counts, because as we all know, PostgreSQL count(*) is slow, and in any case, my count(*) is not on
thewhole table, but on a subset. Doing this in a general way seems complex to me as it would need to be able to
evaluatewhether a given INSERT or UPDATE or one of the dependent tables would impact the WHERE clause for the
materializedview, and it still wouldn't know which rows to add/update/remove without detailed analysis, so it would
eitherbe throwing out the entire materialized view and recreating it on INSERT or UPDATE (or deferring until the next
query?)in which case it may be very slow, or it may be very complex.<br /><br /> Another one that I use is a complex
joinof several tables, and merging 1:N tables including aggregate queries into a 1:1 materialized view. I see this as
thesame problem where it needs to do dependency analysis, and it still doesn't know how to INSERT/UPDATE/DELETE
materializedrows without complex analysis forcing a re-build. In my case, it is 1 ms to query my materialized view and
1500ms to rebuild the materialized view. I do NOT want to rebuild this view after every update.<br /><br /> In summary,
Idon't think materialized views is an easy thing to do. Perhaps the very simplest of cases - but the simplest of cases
canbe easily managed with triggers or application logic.<br /><br /> Cheers,<br /> mark<br /><br /><pre
class="moz-signature"cols="72">-- 
 
Mark Mielke <a class="moz-txt-link-rfc2396E" href="mailto:mark@mielke.cc"><mark@mielke.cc></a>
</pre>

Re: Postgresql Materialized views

From
"Joshua D. Drake"
Date:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Sat, 12 Jan 2008 10:26:49 -0500
Mark Mielke <mark@mark.mielke.cc> wrote:

> Jean-Michel Pouré wrote:
> > In my past development projects, I always used the concept of
> > Materialized VIEW to speed-up SELECTs over INSERTs
> 
> Unless you are going to *pay* for it - you do realize that the best
> way to get it implemented, would be to open up the source code, and
> give it a try yourself?
> 
> If it was so easy, and such a clear win, I think one of the very 
> competent people using PostgreSQL today would have already done it?
> 

No actually, and your reply is offensive. There are a lot of things
PostgreSQL is missing that are "easy" and a clear win, yet people still
don't do them. A simple one is the ridiculous usage of pg_dump and
pg_dumpall. Or that we can't use pg_restore to use the plain text
backup.

I think his email was very well written and a simple request of
discussion of alternatives as well as future plans.

Sincerely,

Joshua D. Drake


> Cheers,
> mark
> 


- -- 
The PostgreSQL Company: Since 1997, http://www.commandprompt.com/ 
Sales/Support: +1.503.667.4564   24x7/Emergency: +1.800.492.2240
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
SELECT 'Training', 'Consulting' FROM vendor WHERE name = 'CMD'


-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFHiQ4LATb/zqfZUUQRAvEpAKCmLzjPg1+95ddWHBqogK4Ea981AgCfa4FG
2AVhx/5BNwYbCta086Iz1oo=
=ng7C
-----END PGP SIGNATURE-----

Re: Postgresql Materialized views

From
Mark Mielke
Date:
Mark Mielke wrote:
> Counts, because as we all know, PostgreSQL count(*) is slow, and in 
> any case, my count(*) is not on the whole table, but on a subset. 
> Doing this in a general way seems complex to me as it would need to be 
> able to evaluate whether a given INSERT or UPDATE or one of the 
> dependent tables would impact the WHERE clause for the materialized 
> view, and it still wouldn't know which rows to add/update/remove 
> without detailed analysis, so it would either be throwing out the 
> entire materialized view and recreating it on INSERT or UPDATE (or 
> deferring until the next query?) in which case it may be very slow, or 
> it may be very complex.

Bah. I forgot to add: The feature I've been wondering about (and not 
necessarily looking for somebody else to do, although I don't think I 
know the code well enough to do it at this point):

Web applications often make the same queries over and over. While 
memcache can be used to cache results, the memcache interface is 
different from the web application interfere requiring complex code, and 
as well, one loses the transaction guarantees as the memcache results 
are not guaranteed to be up-to-date with the database. I see the 
greatest overall performance gain for web applications to be for 
PostgreSQL to hang on to the results of the previous X queries along 
with transactions numbers of each of the dependent tables as of the 
snapshot of the table that is used, and if one of them matches, return 
the results immediately. I believe MySQL does this (although not sure 
how reliable their implementation is). I believe I have seen this 
subject talked about on this list in the past. For web applications, I 
believe this gives most of the benefits that materialized views would 
provide, with less of the costs?

Cheers,
mark

-- 
Mark Mielke <mark@mielke.cc>


Re: Postgresql Materialized views

From
Simon Riggs
Date:
On Sat, 2008-01-12 at 13:27 +0100, Jean-Michel Pouré wrote:

> Please pick-up this important issue for developpers. There is no need to
> concentrate on complex issues, when handling materialized views could
> boost somme web apps. by a factor of 10 or more.

It's more complex than you think, but the main reason was that HOT was a
prerequisite for making summary tables work efficiently, which is only
now just about to go live into 8.3

--  Simon Riggs 2ndQuadrant  http://www.2ndQuadrant.com



Re: Postgresql Materialized views

From
Mark Mielke
Date:
Joshua D. Drake wrote:<br /><blockquote cite="mid:20080112105920.561e97d4@commandprompt.com" type="cite"><blockquote
type="cite"><prewrap="">Unless you are going to *pay* for it - you do realize that the best
 
way to get it implemented, would be to open up the source code, and
give it a try yourself?

If it was so easy, and such a clear win, I think one of the very 
competent people using PostgreSQL today would have already done it?   </pre></blockquote><pre wrap="">No actually, and
yourreply is offensive. There are a lot of things
 
PostgreSQL is missing that are "easy" and a clear win, yet people still
don't do them. A simple one is the ridiculous usage of pg_dump and
pg_dumpall. Or that we can't use pg_restore to use the plain text
backup.

I think his email was very well written and a simple request of
discussion of alternatives as well as future plans. </pre></blockquote> Offensive is relative. I find it offensive when
peopledemand things on one of the many mailing lists I read without providing anything to the community.<br /><br /> I
didn'trealize the original poster did not fit this class of person. For this, I apologize. As for tone - I don't see
anythingtechnically wrong with my response. The best way to get something done *is* to pay for it, or do it yourself.
It'sa tried and true practice in the open source community. Also, I do not think it is as easy as you say - but feel
freeto continue the discussion and prove how idiotic I am for calling the problem "not easy". :-)<br /><br />
Cheers,<br/> mark<br /><br /><pre class="moz-signature" cols="72">-- 
 
Mark Mielke <a class="moz-txt-link-rfc2396E" href="mailto:mark@mielke.cc"><mark@mielke.cc></a>
</pre>

Re: Postgresql Materialized views

From
"Jonah H. Harris"
Date:
On Jan 12, 2008 5:31 PM, Mark Mielke <mark@mark.mielke.cc> wrote:
> Joshua D. Drake wrote:
>> I think his email was very well written and a simple request of
>> discussion of alternatives as well as future plans.

Agreed, JD.

> Offensive is relative. I find it offensive when people demand things on one
> of the many mailing lists I read without providing anything to the
> community.

I have to agree with JD.  Your response was quite negative.
Similarly, your statement, "If it was so easy, and such a clear win, I
think one of the very competent people using PostgreSQL today would
have already done it?" is misplaced.  Jean-Michel didn't say it was
easy at all, he just explained some of the benefits.

Having used materialized views for years, I can tell you they are an
important feature Postgres currently lacks (in native form).  Also, as
I have personally looked into implementing materialized views in
Postgres, I can tell you it's not too difficult.  However, as Simon
stated, it's an iterative process.

You don't need to jump on someone for a well-written request with a
specific use-case.

-- 
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: Postgresql Materialized views

From
Greg Smith
Date:
On Sat, 12 Jan 2008, Jean-Michel Pour� wrote:

> You are well aware of Jonathan Gardner preliminary work:
> http://www.varlena.com/varlena/GeneralBits/Tidbits/matviews.html

There's also PostgreSQL::Snapshots ; intro at
http://cunha17.cristianoduarte.pro.br/postgresql/snapshots.en_us.php and
main project page at http://pgfoundry.org/projects/snapshot/

> I run a 400.000+ message board using PhpBB 3.0...My database handles 10
> to 20 queries every second. There are 100 selects for 1 INSERT. But my
> database could well handle over 500 queries a second using materialized
> views.

I hope you don't take this the wrong way, but if you can't hit 500
queries/second on that volume of messages I would guess that something is
wrong with either the design scalability of the PhpBB software running
against a PostgreSQL database or some detail of how you've got it setup.
A quick read suggests it's not unusual for people to drop PhpBB and use
something vBulletin instead exactly because of PhpBB's issues handling
larger communities.  It's probably out of date but I found the discussion
of query optimization for larger message boards at
http://www.phpbb.com/community/viewtopic.php?t=135383 to be informative on
this topic.

> At my level, here are my plans:
> 1) Publish some long query LOGs from my database, longuer than 30
> milliseconds.

Rather than chasing after core product features that are some distance
off, I think what might be more productive for you in the short term is to
collect this information--including EXPLAIN ANALYZE plans--and include it
along with information about your server and how the postgresql.conf is
setup in a message to the performance list.  That might get you immediate
suggestions.  In addition to "query is badly written/indexed for
PostgreSQL" (which is another potential side to the query issues discussed
in the phpbb forum topic I referenced), problems you might not have caught
that could be nailing you include things like not allocating enough memory
for use by the database and tables not being analyzed frequently enough.

I would love to have materialized views in the core database.  But it's a
ways off no matter what, is moderately hard to accomplish, and you can
emulate some of the benefits using things like Gardner's trigger-based
approach.  All of that makes it harder to kick off such a project.  I
don't think you need to convince anyone that it's important--the
occasional person has been screaming about needing this feature for years
now.  The real question is who cares enough about the feature that it's
worth their trouble to fund development, and I'm not sure whether your
personal attempts to rouse demand will impact that.

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD

Re: Postgresql Materialized views

From
Jean-Michel Pouré
Date:
> In fairness to Jean-Michel, he has spent hundreds of hours in the past
> doing just that and far more for the pgAdmin users in the community -
> I'm sure we can excuse him for asking for what many do think would be
> a useful feature in the hopes that someone listening might just decide
> to pick it up.
> 
> In the meantime though - have you tried rolling your own materialised
> views with some triggers Jean-Michel? I have good results doing that
> in the past.

Dear friends,

For of all, thanks for picking up my message and replying. 

I agree with Dave Page and others that it is hard to find contributors
for a Free project. When writing 2% of pgAdmin2 code, I noticed that you
could count 10.000 end-users for ONE developper. 

This is why I usually offer a bounty for any kind of feature. 

Two months ago, my last bounty for a Kdenlive feature (100€), but no-one
aggreed to receive the money after developing the feature, probably
because there were dozens of contibutors (people writing code on top of
someone else code).

But the power of PostgreSQL is to be a real community, like Xorg or
Apache. This makes all the difference. This was the heart of my message.

This being said, our behind the scene needs are:
* We are a non-profit organisation based in France, providing
real-estate listing services.
* We are going to spend 2000€ buying two servers (Phantom 4-die + 8GB 2U
servers), running on Debian: one for PostgreSQL, the other for Apache2.
* We plan to launch the first virtual real-estate agency managed by a
free community.
* We are going to use a mixture of PhpBB 3.0, Drupal and a custom
applications. 
* We plan to handle thousands of simultaneous users.

So ...

Based on my current logs, I know that I may have to buy and run a web
farm. Which I do not want, because it implies extra hosting costs. 

AND 

I know that ONE PostgreSQL database can handle 1000 simultaneous
queries, when using server-side code. 

I wrote a small PostgreSQL query optimization HOWTO : 
http://area51.phpbb.com/phpBB/viewtopic.php?f=3&t=29292
Pardon me if some information is not accurate. 

I tried my best to educate PhpBB developers choosing the right technology. 
PostgreSQL... Now, I am going to write the requested server-side code for PhpBB 3.0
Then I will benchmark queries on our large server.

In the end I hope that someone will be willing to pick-up this
MATERIALIZED VIEW issue, so that any applications running 
on PostgreSQL can benefit from "lightning speed".

If someone is willing to receive a bounty, please contact me.

Being a non-profit organisation, I can ask money to contributors on our web site.
This will not be any kind of large sum of money, something between 100€ and 500€.

Kind regards and happy new year.
Jean-Michel Pouré



Re: Postgresql Materialized views

From
James Mansion
Date:
Mark Mielke wrote:
> Joshua D. Drake wrote:
>>> Unless you are going to *pay* for it - you do realize that the best
>>> way to get it implemented, would be to open up the source code, and
>>> give it a try yourself?
>>>       
Because users possibly want to do that - use it?  Some of us have better 
things to do than go
through the learning curve of how the internals of a non-trivial system 
work.  Does that really
mean its unreasonable to voice an opinion of what would make the system 
more useful?
>>
> Offensive is relative. I find it offensive when people demand things 
> on one of the many mailing lists I read without providing anything to 
> the community.
>
If your view of the community is that it should be insular and closed to 
those who can't or won't be developers, then fine.  But taking that 
attitude will pretty much guarantee that your system will never amount 
to more than a hill of beans.

One of the major problems with open source as a whole is that you get 
this 'fix it yourself or pay for it' business which provides no way to 
spread the cost over many users who would all have something to gain - 
but none of whom can justify footing the bill for the entire 
development.  Most of us are in that position as users, even if we do 
have skills that would enable us to help - we have our own issues to 
deal with.  Buying support isn't the answer - its not support that's 
needed, after all, so much as an ability to buy a share of influence 
over a roadmap..

Do you want ensure that only the very rich in cash or time can have any 
influence?  You're going the right way about it with your attitude, 
which appears deliberately user-hostile.

What do you want?

James



Re: Postgresql Materialized views

From
Mark Mielke
Date:
FYI: I don't like being attacked for stating the truth, nor distracting 
the mailing list with these emotional discussions. However, there are 
things that need to be clarified. Feel free to kill the thread in your 
mail browser.

James Mansion wrote:
> Mark Mielke wrote:
>> Joshua D. Drake wrote:
>>>> Unless you are going to *pay* for it - you do realize that the best
>>>> way to get it implemented, would be to open up the source code, and
>>>> give it a try yourself?
> Because users possibly want to do that - use it?  Some of us have 
> better things to do than go
> through the learning curve of how the internals of a non-trivial 
> system work.  Does that really
> mean its unreasonable to voice an opinion of what would make the 
> system more useful?

It's unreasonable to demand or set a time schedule like "drop everything 
and work on materialized views in 2008 because you are wasting your time 
on other things - web developers can get 10X improvement today if you do 
this one thing". It's not unreasonable to ask for support for an idea. 
As it was, I believe I misinterpreted Jean-Michel's message as a demand 
(the words seemed demanding), whereas it may have been a plea. I was far 
more sympathetic to his second post.

>> Offensive is relative. I find it offensive when people demand things 
>> on one of the many mailing lists I read without providing anything to 
>> the community.
>>
> If your view of the community is that it should be insular and closed 
> to those who can't or won't be developers, then fine.  But taking that 
> attitude will pretty much guarantee that your system will never amount 
> to more than a hill of beans.

First point: I don't speak for the developers and it is not my system. 
Second point: This has nothing to do with insular vs non-insular 
community. Everything I stated was true - you may not like what I 
stated, but it was true. It's possible I could have adjusted a word or 
two, or added a smiley to get an affect you would find more pleasing, 
but there was nothing incorrect about what I stated. The best way to get 
a feature in is to pay for somebody to do it, or do it yourself. If you 
need reference points look to the last 20 large features that went into 
PostgreSQL. See how it progresses. Also, people have been working on 
materialized views on and off for years with differing degrees of 
success. It is *NOT* the simple problem that people make it out to be. 
(Although it seems that some people may have a good grasp of the 
problem, and may be verging on a solution)

> One of the major problems with open source as a whole is that you get 
> this 'fix it yourself or pay for it' business which provides no way to 
> spread the cost over many users who would all have something to gain - 
> but none of whom can justify footing the bill for the entire 
> development.  Most of us are in that position as users, even if we do 
> have skills that would enable us to help - we have our own issues to 
> deal with.  Buying support isn't the answer - its not support that's 
> needed, after all, so much as an ability to buy a share of influence 
> over a roadmap..

Open source is a double-edged sword for sure. This does not give any 
right to demand anything. If you want to work on something *with* other 
people, the message is "I am looking at doing tihs, but don't know how 
to start - does anybody with similar interest want to help?" This is 
*not* the message that was presented.

> Do you want ensure that only the very rich in cash or time can have 
> any influence?  You're going the right way about it with your 
> attitude, which appears deliberately user-hostile.
> What do you want?
I want people to respect the few developers we have, because I value 
them and respect them.

As for my "attitude" - words in email are hardly capable of showing 
emotion, especially something as short as I wrote. You will think what 
you wish of my words, and your decision was already made the moment you 
ignored the fact that I spoke truth and focused only on the tone.

I have an apology for Jean-Michel. I don't have an apology for people 
such as you who took this opportunity to attack me. Pot, kettle, black.

Cheers,
mark

-- 
Mark Mielke <mark@mielke.cc>


Re: Postgresql Materialized views

From
Alvaro Herrera
Date:
James Mansion wrote:

> If your view of the community is that it should be insular and closed to 
> those who can't or won't be developers, then fine.  But taking that 
> attitude will pretty much guarantee that your system will never amount to 
> more than a hill of beans.

Keep in mind that Mark Mielke is not a PostgreSQL developer.

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


Re: Postgresql Materialized views

From
"Merlin Moncure"
Date:
On Jan 12, 2008 4:19 PM, Simon Riggs <simon@2ndquadrant.com> wrote:
> On Sat, 2008-01-12 at 13:27 +0100, Jean-Michel Pouré wrote:
>
> > Please pick-up this important issue for developpers. There is no need to
> > concentrate on complex issues, when handling materialized views could
> > boost somme web apps. by a factor of 10 or more.
>
> It's more complex than you think, but the main reason was that HOT was a
> prerequisite for making summary tables work efficiently, which is only
> now just about to go live into 8.3

+1

If you know how to write triggers, materialization techniques aren't
all that difficult.  The real technical limitation was not lack of
materialization techniques (write triggers), but was dealing with the
mvcc penalty.  Previously to HOT, for summary tables I would redirect
the trigger to insert to a 'roll up' table and move the data to the
summary on cron or from an application event.

Materialized views are syntax sugar (but still very sweet).

merlin


Re: Postgresql Materialized views

From
"Sean Utt"
Date:
<sarcasm>Good to see</sarcasm> things haven't changed, and requests for 
features and improvements on the pgsql-hackers list can still degenerate 
rapidly into a discussion about how to request features and improvements.

As Joshua Drake has pointed out before, most of the core people working on 
PostgreSQL don't actually use it for anything themselves. I will expand a 
little on that and say that this means that while they are extremely good at 
what they do, they really don't have a clue what might be useful to someone 
"in the wild". Sort of like automotive engineers who in the 1970's made the 
Cadillac's engine so large that you couldn't change the spark plugs without 
taking the motor mounts loose and lifting the engine.

The basic question this brings up in the context of this latest flurry of 
hurt feelings is whether user demand is officially a driving force in 
PostgreSQL development.
If the answer to that question is yes, then the next question is how is that 
structured?

I'm not sure that the collective answer to the first question is actually 
yes.  If it actually is yes, then the next question has barely been touched, 
as witnessed by these flurries of electrons on the list, unless the 
structure is Anarchy. Which is oxymoronic of course.

There doesn't appear to be an easy way to officially "take the temperature" 
of either the developer community, or the user community,  and there 
certainly is no official way to clearly and easily communicate between the 
two in order to effect change. Unfortunately for all of us, the 
communications, social, organizational, and people skills/talents necessary 
to envision and create the type of social structure that benefits the entire 
community are outside the range of experience of everyone on this list. How 
do I know that? Because if even one person had those talents/skills it would 
have happened already. That is what those type of people do, they can't help 
themselves.* I'm not sure that anyone reading this would even be able to 
recognize such a person if they met them. Perhaps we should go fishing for 
some help from one of those "University Places"? From people outside the 
Computer Science department? Maybe even some people in Industrial 
Psychology? Somebody probably needs a Master's project....

--
Sean Utt

* Actually I'm being optimistic, while organizers are compulsive, they know 
a hopeless cause when they see it, and quietly disappear. I'm operating 
under the assumption that the PostgreSQL community is not a hopeless cause 
organizationally.

[entire original message deleted for lack of usefulness] 



Re: Postgresql Materialized views

From
"Joshua D. Drake"
Date:
Sean Utt wrote:
> <sarcasm>Good to see</sarcasm> things haven't changed, and requests for 
> features and improvements on the pgsql-hackers list can still degenerate 
> rapidly into a discussion about how to request features and improvements.
> 
> As Joshua Drake has pointed out before, most of the core people working 
> on PostgreSQL don't actually use it for anything themselves. 

<snip>

> Unfortunately for all of us, 
> the communications, social, organizational, and people skills/talents 
> necessary to envision and create the type of social structure that 
> benefits the entire community are outside the range of experience of 
> everyone on this list. How do I know that? Because if even one person 
> had those talents/skills it would have happened already. That is what 
> those type of people do, they can't help themselves.* I'm not sure that 
> anyone reading this would even be able to recognize such a person if 
> they met them. 

I would argue that they know and recognize them but... they don't care. 
They are -hackers. They care about code. Some of them in a tertiary 
manner care about the entire paragraph above, others not at all.

I think if you look around what you will see is disparate group of 
people all organizing the community in their own particular way for the 
benefit of the community as a whole.

Consider the following, in the last 2 years we have had a number of 
significant community events happen that allow for exactly what you are 
stating above.  Formation of the Fundraising group and affiliation with 
Software in the Public Interest. This allowed us to execute the 
Anniversary, PostgreSQL Conference West, as well as the upcoming EAST 
conference. It also allowed to to provide help to PgDay.IT for last 
years Italian PostgreSQL day. Since that time they have also formed 
ITPUG and Italian non-profit for PostgreSQL. Lastly this affiliation has 
allowed allowed the community as a whole to sponsor travel for 
PostgreSQL speakers to various conferences and support major events such 
as OSCON and LinuxWorld.


> Perhaps we should go fishing for some help from one of 
> those "University Places"? From people outside the Computer Science 
> department? Maybe even some people in Industrial Psychology? Somebody 
> probably needs a Master's project....

Actually you just need to not be on -hackers :). This belongs over in 
-advocacy.

Sincerely,

Joshua D. Drake




Re: Postgresql Materialized views

From
"Webb Sprague"
Date:
Just my two cents on this (rapidly degenerating) thread.

On 1/13/08, Sean Utt <sean@strateja.com> wrote:
> <sarcasm>Good to see</sarcasm> things haven't changed, and requests for
> features and improvements on the pgsql-hackers list can still degenerate
> rapidly into a discussion about how to request features and improvements.
>
> As Joshua Drake has pointed out before, most of the core people working on
> PostgreSQL don't actually use it for anything themselves. I will expand a
> little on that and say that this means that while they are extremely good at
> what they do, they really don't have a clue what might be useful to someone
> "in the wild".   Sort of like automotive engineers who in the 1970's made the
> Cadillac's engine so large that you couldn't change the spark plugs without
> taking the motor mounts loose and lifting the engine.

As a very satisfied Postgres "customer", I take exception to the comparison.

<SNIP>

> There doesn't appear to be an easy way to officially "take the temperature"
> of either the developer community, or the user community,  and there
> certainly is no official way to clearly and easily communicate between the
> two in order to effect change.

Huh?  A politely worded feature request generally gets discussed and
then put on the TODO list if there is some consensus about its
usefulness.  If there is no consensus, then the requester usually has
to do more work, which might involve prototyping some code etc.
Admittedly, some developers get grumpy sometimes, but, as the man
said, "Let him who is without sin throw the first stone..."  There was
an issue with the tone of the request for material views in the
beginning of this thread, but that seems to ironed out among those who
are actually interested in accomplishing something.

May I propose the following:  (1) can we put materialized views on the
TODO, perhaps as a library or as core, still subject to a lot of
design work and with no particular deadline?  (2)  Can we discontinue
this particular flame war about the responsive of the developers (who
in my estimation do a huge amount of work that benefits me immensely
with nary a "thank you")?

Sorry for the meta rant, I just couldn't take it anymore.


Re: Postgresql Materialized views

From
Tom Lane
Date:
"Webb Sprague" <webb.sprague@gmail.com> writes:
> May I propose the following:  (1) can we put materialized views on the
> TODO, perhaps as a library or as core, still subject to a lot of
> design work and with no particular deadline?

Actually, I had thought they *were* on the TODO list, because certainly
it's been suggested before.  I can't find anything about 'em in the
list though, so +1 for adding the entry.  There are plenty of other TODO
items that we have no idea how to do, so the lack of a clear design is
hardly an objection ;-)
        regards, tom lane


Re: Postgresql Materialized views

From
Andrew Dunstan
Date:

Sean Utt wrote:
>
> As Joshua Drake has pointed out before, most of the core people 
> working on PostgreSQL don't actually use it for anything themselves. I 
> will expand a little on that and say that this means that while they 
> are extremely good at what they do, they really don't have a clue what 
> might be useful to someone "in the wild". Sort of like automotive 
> engineers who in the 1970's made the Cadillac's engine so large that 
> you couldn't change the spark plugs without taking the motor mounts 
> loose and lifting the engine.
>
>

This is both gratuitously offensive and based on a demonstrably false 
premise. The definition of "core people working on PostgreSQL" is 
somewhat vague. But if you were to take it as, say, the group of active 
committers, then I would say that the majority of us earn our living in 
whole or in part using PostgreSQL. Certainly I do (there's a reason I 
use an elephant logo for my business).

Many of the things I have contributed to PostgreSQL have been in 
response to user requests, and often things I personally have no 
immediate need for. The same is true of many contributors. And almost 
all of mine have been aimed at increasing usability (e.g. dollar 
quoting, CSV import/export). So I rather take offense at the quite 
inappropriate spark plug analogy.

As for feature requests, everyone has a right to air an opinion. 
Personally, I will pay more attention to people who contribute to the 
community than to those who don't. That doesn't just mean coders, though 
- contribution comes in many forms. In the case of the present request, 
Jean-Michel is a contributor, and any suggestion to the contrary is 
right out of line.

The real question for me is whether we want to support Materialized 
Views at the grammar level. If we do then it should be worked on. If not 
then it can probably be dealt with via a bolt-on module. Personally I'm 
inclined to say we should support it via the grammar.

cheers

andrew



Re: Postgresql Materialized views

From
"Joshua D. Drake"
Date:
Andrew Dunstan wrote:
> 
> 
> Sean Utt wrote:
>>
>> As Joshua Drake has pointed out before, most of the core people 
>> working on PostgreSQL don't actually use it for anything themselves. I 
>> will expand a little on that and say that this means that while they 
>> are extremely good at what they do, they really don't have a clue what 
>> might be useful to someone "in the wild". Sort of like automotive 
>> engineers who in the 1970's made the Cadillac's engine so large that 
>> you couldn't change the spark plugs without taking the motor mounts 
>> loose and lifting the engine.
>>
>>
> 
> This is both gratuitously offensive and based on a demonstrably false 
> premise. The definition of "core people working on PostgreSQL" is 
> somewhat vague. But if you were to take it as, say, the group of active 
> committers, then I would say that the majority of us earn our living in 
> whole or in part using PostgreSQL. Certainly I do (there's a reason I 
> use an elephant logo for my business).

The consideration of my comment (which I believe was made some time ago) 
was not about -hacking which as I understand it is what you and most 
everyone else on -hackers does. My comment was a consideration to the 
amount of "core" that are managing postgresql in a production 
environment. E.g; being DBAs. I would argue that very few committers 
actually qualify as that either. Feel free to prove me wrong :)

Sincerely,

Joshua D. Drake



Re: Postgresql Materialized views

From
Andrew Dunstan
Date:

Joshua D. Drake wrote:
> Andrew Dunstan wrote:
>>
>>
>> Sean Utt wrote:
>>>
>>> As Joshua Drake has pointed out before, most of the core people 
>>> working on PostgreSQL don't actually use it for anything themselves. 
>>> I will expand a little on that and say that this means that while 
>>> they are extremely good at what they do, they really don't have a 
>>> clue what might be useful to someone "in the wild". Sort of like 
>>> automotive engineers who in the 1970's made the Cadillac's engine so 
>>> large that you couldn't change the spark plugs without taking the 
>>> motor mounts loose and lifting the engine.
>>>
>>>
>>
>> This is both gratuitously offensive and based on a demonstrably false 
>> premise. The definition of "core people working on PostgreSQL" is 
>> somewhat vague. But if you were to take it as, say, the group of 
>> active committers, then I would say that the majority of us earn our 
>> living in whole or in part using PostgreSQL. Certainly I do (there's 
>> a reason I use an elephant logo for my business).
>
> The consideration of my comment (which I believe was made some time 
> ago) was not about -hacking which as I understand it is what you and 
> most everyone else on -hackers does. My comment was a consideration to 
> the amount of "core" that are managing postgresql in a production 
> environment. E.g; being DBAs. I would argue that very few committers 
> actually qualify as that either. Feel free to prove me wrong :)
>
>

I'm not going to  bother trying, because you just moved the goalposts 
(managing in a production environment vs using). And why should the 
number of DBAs matter one whit? Why should they matter more than, say 
application developers, when it comes to language level features?

cheers

andrew


Re: Postgresql Materialized views

From
"Joshua D. Drake"
Date:
Andrew Dunstan wrote:

>>
> 
> I'm not going to  bother trying, because you just moved the goalposts 
> (managing in a production environment vs using). And why should the 
> number of DBAs matter one whit? Why should they matter more than, say 
> application developers, when it comes to language level features?


Andrew don't get your knickers in a bunch. This was not an attack, I 
wasn't declaring that one type of user was better than another, nor was 
I moving goal posts. I was simply explaining the intent of my statement 
because Sean quoted me without context.

Oh and as a note... the fact that you wonder about the worth of the 
number of DBAs proves my point infinitely.

Joshua D. Drake



Re: Postgresql Materialized views

From
Andrew Dunstan
Date:

Joshua D. Drake wrote:
> Andrew Dunstan wrote:
>
>>
>> I'm not going to  bother trying, because you just moved the goalposts 
>> (managing in a production environment vs using). And why should the 
>> number of DBAs matter one whit? Why should they matter more than, say 
>> application developers, when it comes to language level features?
>
>
> Andrew don't get your knickers in a bunch. This was not an attack, I 
> wasn't declaring that one type of user was better than another, nor 
> was I moving goal posts. I was simply explaining the intent of my 
> statement because Sean quoted me without context.
>
> Oh and as a note... the fact that you wonder about the worth of the 
> number of DBAs proves my point infinitely.
>
>

What is your point? You are implying something, but I'm damned if I know 
what. I did not wonder about the worth of DBAs - that would be silly. 
Please do not put words in my mouth that I did not speak. What I 
wondered was why DBAs should be uniquely important, and why we should 
think that there is any significance to the number of DBAs as opposed to 
other classes of user, among the ranks of either the core team, or the 
active committers, or hackers generally. I don't think they should - 
there are many classes of user whose needs we should cater for, of which 
DBAs are just one.

cheers

andrew


Re: Postgresql Materialized views

From
"Sean Utt"
Date:
My point is simply this: The lack of a clear formal process for feature 
requests leads to this degradation in the conversation. Without a formalized 
structure, the conversation devolves rapidly into an argument over semantics 
and word choice. It is not my contention that the "core" developers need to 
be different in any way. It is also not my contention that the users need to 
be different in any way. It is my contention that the "process" currently 
generates more ill will than it needs to, and needs to be replaced. The 
problem is a systemic one. There needs to be a more formal structure put in 
place than just the -hackers mailing list. There needs to be a way to 
evaluate the demand for a specific feature as well as the benefits and the 
effort it will require. It needs to be done in as neutral a way as possible. 
In order to be effective, it will have to be driven into being by the 
developers, because they will be the ones who can hamstring it -- not the 
users.

Or we can just keep bickering among ourselves over semantics and word 
choice. That seems to be fun for everyone so far.

Sean



Re: Postgresql Materialized views

From
Andrew Dunstan
Date:

Sean Utt wrote:
> My point is simply this: The lack of a clear formal process for 
> feature requests leads to this degradation in the conversation. 
> Without a formalized structure, the conversation devolves rapidly into 
> an argument over semantics and word choice. It is not my contention 
> that the "core" developers need to be different in any way. It is also 
> not my contention that the users need to be different in any way. It 
> is my contention that the "process" currently generates more ill will 
> than it needs to, and needs to be replaced. The problem is a systemic 
> one. There needs to be a more formal structure put in place than just 
> the -hackers mailing list. There needs to be a way to evaluate the 
> demand for a specific feature as well as the benefits and the effort 
> it will require. It needs to be done in as neutral a way as possible. 
> In order to be effective, it will have to be driven into being by the 
> developers, because they will be the ones who can hamstring it -- not 
> the users.
>
> Or we can just keep bickering among ourselves over semantics and word 
> choice. That seems to be fun for everyone so far.
>
>

Very lofty sentiments. Perhaps next time you have a suggestion to make 
you could rise to that level in the first rather than the second 
instance, and avoid the unnecessary insults.

cheers

andrew


Re: Postgresql Materialized views

From
Tom Lane
Date:
"Sean Utt" <sean@strateja.com> writes:
> My point is simply this: The lack of a clear formal process for feature 
> requests leads to this degradation in the conversation.

Two comments:

1) The existing informal process has served us very well for more than
ten years now.  I'm disinclined to consider replacing it, because that
would risk altering the community's dynamics for the worse.

2) In the end, this is an open source *community*; no amount of formal
feature requesting will have any material impact on what actually gets
implemented, because there isn't any central control.  What gets
implemented is whatever individual contributors choose to work on,
either because they find it interesting or (in some cases) because
someone pays them to do something specific.  Certainly, some
contributors pay attention to what's being requested, but I see no
reason to think that increasing the level of formality will help them.

In short: we haven't got a "road map" and we like it that way.
        regards, tom lane


Re: Postgresql Materialized views

From
Mark Mielke
Date:
Sean Utt wrote:
> My point is simply this: The lack of a clear formal process for 
> feature requests leads to this degradation in the conversation. 
> Without a formalized structure, the conversation devolves rapidly into 
> an argument over semantics and word choice. It is not my contention 
> that the "core" developers need to be different in any way. It is also 
> not my contention that the users need to be different in any way. It 
> is my contention that the "process" currently generates more ill will 
> than it needs to, and needs to be replaced. The problem is a systemic 
> one. There needs to be a more formal structure put in place than just 
> the -hackers mailing list. There needs to be a way to evaluate the 
> demand for a specific feature as well as the benefits and the effort 
> it will require. It needs to be done in as neutral a way as possible. 
> In order to be effective, it will have to be driven into being by the 
> developers, because they will be the ones who can hamstring it -- not 
> the users.
What sort of structure are you envisioning? Features do make it into 
PostgreSQL - PostgreSQL has had, and arguable still has a more complete 
feature set than well funded alternatives such as MySQL. Their is a TODO 
list that both grows and shrinks with each release. I have myself seen 
major changes in 8.0, 8.1, 8.2, and 8.3 that have personally benefited 
me. What problem needs to be solved?

I agree with your sentiment. For somebody who wants to request a 
feature, expect it to be taken up by a champion (other than them), and 
monitor it's regular process, no clear infrastructure seems to exist. 
However, I question the relevance or value of such a system. My own 
initial contribution was a reaction to the notion that somebody should 
be able to demand other people to volunteer their time to work on 
something. It may have been unfair and cold to the original poster, and 
for this, I apologize. I don't believe the user community should 
necessary be able to demand or prioritize a feature unless they are 
willing to put up resources to support the effort. Resources usually 
means either people or money. If people truly have a strong business 
case for a feature, there are several qualified companies willing to 
take their money and turn it into something real. If people are not able 
to produce such a business case or justify the expenditure of funds, 
attracting volunteers to do the same work requires a very different 
approach. It requires zeal, compelling argument, and a reasonable amount 
of ego stroking or challenge. Putting in place an official process can 
have the opposite effect. People such as myself are very willing to 
volunteer efforts in an informal manner without formal deadlines or 
processes, because we enjoy it. Forcing the regular amount of red tape 
many of us need to cut just to get our jobs done at work is not very 
motivational for people such as myself. Now, while I have contributed to 
other open source projects, I have not personally contributed much to 
PostgreSQL. The core PostgreSQL contributors would have to make their 
voice heard. I think, though, that telling them that they must work on a 
certain feature, because that's what the users are asking for, is the 
wrong approach. Not to say that is exactly what you are requesting, but 
I suggest that is where you are leading.

Cheers,
mark

-- 
Mark Mielke <mark@mielke.cc>


Re: Postgresql Materialized views

From
Andrew Chernow
Date:
> I think, though, that telling them that they must work on a 
> certain feature, because that's what the users are asking for, is the 
> wrong approach. Not to say that is exactly what you are requesting, but 
> I suggest that is where you are leading.
> 
> Cheers,
> mark
> 

The more communication between users and developers the better. 
Companies would pay a fortune for the user complaints, moans and groans, 
suggestions, bug reports and demands that postgresql sees in a single day.

IMHO developers need reality checks ... well so do users but for 
different reasons.  There is no quicker way to kill a product or project 
than to ignore the needs/wants of the user/customer base (open-source or 
not).

andrew


Re: Postgresql Materialized views

From
"Webb Sprague"
Date:
I realize that some very important navel-gazing (^H^H^H "group
process") is happening, but let us remember where bona-fide feature
requests should go:

http://www.postgresql.org/docs/faqs.TODO.html

So far, I don't see any mention of materialized views on this page,
and I did refresh ... :)


Re: Postgresql Materialized views

From
"Joshua D. Drake"
Date:
Webb Sprague wrote:
> I realize that some very important navel-gazing (^H^H^H "group
> process") is happening, but let us remember where bona-fide feature
> requests should go:
> 
> http://www.postgresql.org/docs/faqs.TODO.html
> 
> So far, I don't see any mention of materialized views on this page,
> and I did refresh ... :)

But did you clear your cache? :P

Joshua D. Drake

> 
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
>        subscribe-nomail command to majordomo@postgresql.org so that your
>        message can get through to the mailing list cleanly
> 



Re: Postgresql Materialized views

From
Mark Mielke
Date:
Andrew Chernow wrote:
>> I think, though, that telling them that they must work on a certain 
>> feature, because that's what the users are asking for, is the wrong 
>> approach. Not to say that is exactly what you are requesting, but I 
>> suggest that is where you are leading.
> The more communication between users and developers the better. 
> Companies would pay a fortune for the user complaints, moans and 
> groans, suggestions, bug reports and demands that postgresql sees in a 
> single day.
>
> IMHO developers need reality checks ... well so do users but for 
> different reasons.  There is no quicker way to kill a product or 
> project than to ignore the needs/wants of the user/customer base 
> (open-source or not).

Nobody is ignoring users or needs or wants. It is a question of 
priority. My priorities may not match yours may not match Tom's or one 
of the other core contributors. Valuable features are being added to 
PostgreSQL with each release as community efforts (or business efforts). 
With the exception of some arguably naive requests such as "give me a 
super fast count(*) - why is that so hard?" users are certainly being 
listened to - because we are the users. I have personally requested 
items, found myself to be not alone, and seen the group request 
implemented ahead of my expectations.

So again, my question is - what is broken? What needs to be fixed?

I, for one, think the core developers are doing a great job, and the 
extended team (community) is working very well together. I am a 
PostgreSQL advocate *because* of the model, not in spite of the model. 
If you want a corporate model, I believe Oracle is also a great product 
and it may be more compatible with your expectations? (Ironically, I 
would expect to see new features that *I* ask for implemented in 
PostgreSQL *before* Oracle would listen to me)

Cheers,
mark

-- 
Mark Mielke <mark@mielke.cc>


Re: Postgresql Materialized views

From
"Joshua D. Drake"
Date:
Mark Mielke wrote:

> Nobody is ignoring users or needs or wants. It is a question of 
> priority. My priorities may not match yours may not match Tom's or one 
> of the other core contributors. Valuable features are being added to 

FYI, the terminology core contributors is confusing. There is not really 
any such thing in PostgreSQL land. There is "core" which isn't really 
relevant to development (except in some specific circumstances such as 
addressing security concerns). There are "contributors" which are people 
who are constantly contributing to the community. An incomplete list of 
them is here:

http://www.postgresql.org/community/contributors

Sincerely,

Joshua D. Drake



Re: Postgresql Materialized views

From
"Webb Sprague"
Date:
> But did you clear your cache? :P

Freud might say it takes a lifetime to clear one's cache ....
Luckily, in therapy you don't have to wait for those darn Postgres
developers ;)

>
> Joshua D. Drake
>
>
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 1: if posting/reading through Usenet, please send an appropriate
> >        subscribe-nomail command to majordomo@postgresql.org so that your
> >        message can get through to the mailing list cleanly
> >
>
>


Re: Postgresql Materialized views

From
Simon Riggs
Date:
On Sun, 2008-01-13 at 17:44 -0800, Sean Utt wrote:

> It is not my contention that the "core" developers need to 
> be different in any way. It is also not my contention that the users need to 
> be different in any way.

First, this is an open forum, so thank you for expressing your views
openly in the manner you've felt them.

General comment:

I think one perspective I have on the above statement is the feeling
that there is a distinction between two groups of people and that one
group is put here to serve the other group better.

Many new users of Postgres are so used to the closed source situation of
Developers being the only people who can see the code that they often
perpetuate the concept of tiering or groups, when it doesn't exist.

Almost all of the people on the list are users of Postgres. There's just
a complete range of people from new users to experienced hackers.
Postgres is well documented, well commented and completely open source,
so there is no barrier to anyone who wishes to change, and if you choose
to define that change positively, improve.

So I support Mark Mielke's views on writing code. Anybody who wants to
code, can. There's probably a project of a size and complexity that's
right for your first project. Apparently the guy that invented the new
scheduling algorithms for Linux wasn't even a coder, but he sat down and
worked it out. 

This is Hackers: Write some code today, everybody. You *can*.

--  Simon Riggs 2ndQuadrant  http://www.2ndQuadrant.com



Re: Postgresql Materialized views

From
Jean-Michel Pouré
Date:
Dear Friends,

I hope that this flame war can stop, as it is useless.

The logic of free software is that developers pick-up issues, based on
their skills and interest. The power of the cummunity is to gather very
talented developers from all over the planet. Freedom is the logic and
there is no need to drive the community. In the end, PostgreSQL relies
on the knownledge of talented developers.

When posting this thread, I hope that a talented developer would some
day pick-up the materialized view issue and work on it, during a process
of discussion. 

In a few days, I will post some precise statistics on how much
MATERIALIZED VIEW could boost a 400.000 posts PhpBB 3.0 forum. I will
write the required PL trigger, test them and install them on my server.

Then each individual is able to decide whether materiazed views are
important or not. This is my definition of freedom. Freedom of choice.

I don't ask for more.
Now, I hope that the list can return to a more peaceful state.

Kind regards,
Jean-Michel Pouré



Re: Postgresql Materialized views

From
Csaba Nagy
Date:
On Mon, 2008-01-14 at 09:22 +0000, Simon Riggs wrote:
> So I support Mark Mielke's views on writing code. Anybody who wants to
> code, can. There's probably a project of a size and complexity that's
> right for your first project. 

The main problem is that usually that initial thing is not what you
desperately need today... so the motivation will be pretty low unless
you just have loads of time to start off playing with the code.

> Apparently the guy that invented the new
> scheduling algorithms for Linux wasn't even a coder, but he sat down and
> worked it out.

> This is Hackers: Write some code today, everybody. You *can*.

Certainly everybody  can write code, but the barrier to accept it is
pretty high in the postgres community. So you better be a damn good
coder if you expect your code to be accepted... and even then with
considerable fight for justifying the use case for your feature ;-)

This is all good for a stable product, but it really makes the barrier
between simple users and hackers pretty high.

Cheers,
Csaba.




Re: Postgresql Materialized views

From
Mark Mielke
Date:
Jean-Michel Pouré wrote:
> When posting this thread, I hope that a talented developer would some
> day pick-up the materialized view issue and work on it, during a process
> of discussion. 
>   

In case it was lost in the noise - there are several capable people that 
have been personally on this issue for years. You are not the first to 
suggest it, and your opinion that the feature is valuable is shared by a 
larger group. There is still a difference between talking about it and 
doing it. A few of the doers told me off privately stating that it is 
not that difficult. My suspicion is that it *is* difficult and they are 
not doing themselves credit, or their solution is incomplete, but 
whatever - the result is the same. When one or more of these people are 
ready, you will likely see it released. It may even be complete before 
2008 is complete.

> In a few days, I will post some precise statistics on how much
> MATERIALIZED VIEW could boost a 400.000 posts PhpBB 3.0 forum. I will
> write the required PL trigger, test them and install them on my server.
>   

The two factors of note here: 1) If you can write a PL trigger for it, 
the value of the feature is limited. In my own case, I found it fairly 
easy to write triggers, or update the summary table from the application 
(poor man's view). 2) In my experience, a custom PL trigger can make 
assumptions about the application that allow greater optimization that a 
general 'syntactical sugar' solution could. In my own case, performance 
of queries leapt from 1500 ms to 1 ms. Even if materialized views were 
implemented to a level that most people would consider "full", I do not 
expect to see the same speed improvement, because a generalized 
implementation would not be able to make the assumptions that I can. 
FYI, my triggers are perhaps 10 lines each, and I believe I have three 
triggers in the 1500 ms -> 1 ms example. I have a view and a summary 
table. I update the summary table from the view. In my opinion, this 
solution is very manageable given the 1500:1 performance improvement it 
grants me.

> Then each individual is able to decide whether materiazed views are
> important or not. This is my definition of freedom. Freedom of choice.
>
> I don't ask for more.
> Now, I hope that the list can return to a more peaceful state

You are doing fine. I am sorry for assuming you intended more and giving 
you a cold-ish shoulder.

Cheers,
mark

-- 
Mark Mielke <mark@mielke.cc>


Re: Postgresql Materialized views

From
Alvaro Herrera
Date:
Mark Mielke wrote:

> FYI, my triggers are perhaps 10 lines each, and I believe I have three
> triggers in the 1500 ms -> 1 ms example. I have a view and a summary
> table. I update the summary table from the view. In my opinion, this
> solution is very manageable given the 1500:1 performance improvement
> it grants me.

But you had to modify your queries.  I would think that a materialized
views implementation worth its salt would put the view to work on the
original, unmodified queries.

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


Re: Postgresql Materialized views

From
"Roberts, Jon"
Date:

> -----Original Message-----
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> Sent: Sunday, January 13, 2008 8:18 PM
> To: Sean Utt
> Cc: Andrew Dunstan; Joshua D. Drake; pgsql-hackers@postgresql.org
> Subject: Re: [HACKERS] Postgresql Materialized views
> 
> "Sean Utt" <sean@strateja.com> writes:
> > My point is simply this: The lack of a clear formal process for feature
> > requests leads to this degradation in the conversation.
> 
> Two comments:
> 
> 1) The existing informal process has served us very well for more than
> ten years now.  I'm disinclined to consider replacing it, because that
> would risk altering the community's dynamics for the worse.
> 
> 2) In the end, this is an open source *community*; no amount of formal
> feature requesting will have any material impact on what actually gets
> implemented, because there isn't any central control.  

Wow.  Being new to Open Source, this amazes me.

> What gets
> implemented is whatever individual contributors choose to work on,
> either because they find it interesting or (in some cases) because
> someone pays them to do something specific.  Certainly, some
> contributors pay attention to what's being requested, but I see no
> reason to think that increasing the level of formality will help them.

What happens when a person adds a feature or changes the architecture of the
database that is perceived by some as incorrect or going in the wrong
direction?  



Jon


Re: Postgresql Materialized views

From
Mark Mielke
Date:
Alvaro Herrera wrote: <blockquote cite="mid:20080114132644.GD4584@alvh.no-ip.org" type="cite"><pre wrap="">Mark Mielke
wrote:</pre><blockquote type="cite"><pre wrap="">FYI, my triggers are perhaps 10 lines each, and I believe I have
three
triggers in the 1500 ms -> 1 ms example. I have a view and a summary
table. I update the summary table from the view. In my opinion, this
solution is very manageable given the 1500:1 performance improvement
it grants me.   </pre></blockquote><pre wrap="">
But you had to modify your queries.  I would think that a materialized
views implementation worth its salt would put the view to work on the
original, unmodified queries. </pre></blockquote><br /> I might be slow today (everyday? :-) ) - but what do you mean
bythis? The only difference between *_table and *_view is that *_table is the summary table and *_view is the view. The
triggerson the tables the view is derived from select from *_view and update *_table. The queries remain unchanged
exceptfor deciding whether to use *_table or *_view. Yes, syntactical sugar would make it prettier and more manageable
-but I am confused as to how a syntactical sugar solution would improve performance, and I highly suspect it would not
achievethe same performance benefit. As I said - I can make assumptions about how the base tables are updated. A
generalizedsolution would not be able to make these assumptions?<br /><br /> For some further background - the base
tablesare a mirror of accpac tables (augh!) from mssql. The view and summary table gathers information from 5 or so of
thesetables including aggregates, conditionals, sub-selects (different queries to the same base tables) and deep joins.
Perhapsmy imagination is too limited - but I don't see how it would be easy to make syntactical sugar for this and
stillmaintain the performance I describe above. For about 30 lines of pl/pgsql and some application-side updates (again
fromthe view to the summary table) in the synchronization script it seems acceptable.<br /><br /> Cheers,<br /> mark<br
/><br/><pre class="moz-signature" cols="72">-- 
 
Mark Mielke <a class="moz-txt-link-rfc2396E" href="mailto:mark@mielke.cc"><mark@mielke.cc></a>
</pre>

Re: Postgresql Materialized views

From
tomas@tuxteam.de
Date:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Mon, Jan 14, 2008 at 07:28:52AM -0600, Roberts, Jon wrote:

[...]

> What happens when a person adds a feature or changes the architecture of the
> database that is perceived by some as incorrect or going in the wrong
> direction?  

(S)he gets to hold onto her/his own version. If there is enough power
behind both variants, a fork might happen.

Forks are not bad per se -- on the contrary, the variants can explore
a wider area of design space. If they stay sufficiently open, they later
steal ideas (and even code) from each other. Think genetic algorithms on
a global scale ;-)

But now it's definitely off-topic, so I'll shut up already.

Regards
- -- tomás
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (GNU/Linux)

iD8DBQFHi20GBcgs9XrR2kYRAppzAJ0VSKzWb8jjEQfMCwW1ycpCAyEZUQCdFmtz
tDpywf0ywvk+3j0qI1GUHkI=
=3TME
-----END PGP SIGNATURE-----



Re: Postgresql Materialized views

From
Alvaro Herrera
Date:
Mark Mielke wrote:
> Alvaro Herrera wrote:

>> But you had to modify your queries.  I would think that a materialized
>> views implementation worth its salt would put the view to work on the
>> original, unmodified queries.
>
> I might be slow today (everyday? :-) ) - but what do you mean by this? The 
> only difference between *_table and *_view is that *_table is the summary 
> table and *_view is the view.

My point is that you should be able to query _table and the system
should automatically use the view, without you saying so (except by
initially creating them).

At least if you had eagerly-updated materialized views.  If you had lazy
ones, I think those should be used explicitely only.

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


Re: Postgresql Materialized views

From
"Roberts, Jon"
Date:
> -----Original Message-----
> From: pgsql-hackers-owner@postgresql.org [mailto:pgsql-hackers-
> owner@postgresql.org] On Behalf Of Alvaro Herrera
> Sent: Monday, January 14, 2008 8:20 AM
> To: Mark Mielke
> Cc: Jean-Michel Pouré; pgsql-hackers@postgresql.org
> Subject: Re: [HACKERS] Postgresql Materialized views
>
> Mark Mielke wrote:
> > Alvaro Herrera wrote:
>
> >> But you had to modify your queries.  I would think that a materialized
> >> views implementation worth its salt would put the view to work on the
> >> original, unmodified queries.
> >
> > I might be slow today (everyday? :-) ) - but what do you mean by this?
> The
> > only difference between *_table and *_view is that *_table is the
> summary
> > table and *_view is the view.
>
> My point is that you should be able to query _table and the system
> should automatically use the view, without you saying so (except by
> initially creating them).
>

I agree!  From a BI perspective, a materialized view is worthless if you
have to re-write your query.  There isn't a tool on the market that is smart
enough to rewrite a query to a view because all other databases handle the
rewriting internally.


Jon


Re: Postgresql Materialized views

From
Andrew Dunstan
Date:

Roberts, Jon wrote:
>> What gets
>> implemented is whatever individual contributors choose to work on,
>> either because they find it interesting or (in some cases) because
>> someone pays them to do something specific.  Certainly, some
>> contributors pay attention to what's being requested, but I see no
>> reason to think that increasing the level of formality will help them.
>>     
>
> What happens when a person adds a feature or changes the architecture of the
> database that is perceived by some as incorrect or going in the wrong
> direction?  
>
>
>   

If that's the general perception it doesn't get added to our source 
tree. It's very rare that it gets to anything like as formal as a vote. 
To avoid the possibility of people spending lots of time doing work 
which is ultimately not adopted, we strongly discourage ivory tower 
development. For major features especially, developers are encouraged to 
discuss early and often.

But that's a different issue from which items people work on, which is 
very much a matter of individual choice, or at least something the 
community has little control over.

cheers

andrew




Re: Postgresql Materialized views

From
Martijn van Oosterhout
Date:
On Mon, Jan 14, 2008 at 08:30:53AM -0600, Roberts, Jon wrote:
> > My point is that you should be able to query _table and the system
> > should automatically use the view, without you saying so (except by
> > initially creating them).
> >
> I agree!  From a BI perspective, a materialized view is worthless if you
> have to re-write your query.  There isn't a tool on the market that is smart
> enough to rewrite a query to a view because all other databases handle the
> rewriting internally.

I don't know about worthless, given that people are doing materialised
views on postgres already. It is however a completely orthoginal
problem. Someone needs to write the code to maintain such a view before
you can even think about working on the planner.

However, step 1 would be to get them onto the TODO list.

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Those who make peaceful revolution impossible will make violent revolution inevitable.
>  -- John F Kennedy

Re: Postgresql Materialized views

From
"Zeugswetter Andreas ADI SD"
Date:
> > But you had to modify your queries.  I would think that a materialized
> > views implementation worth its salt would put the view to work on the
> > original, unmodified queries.
> >
>
> I might be slow today (everyday? :-) ) - but what do you mean by this?
> The only difference between *_table and *_view is that *_table is the
> summary table and *_view is the view. The triggers on the tables the
> view is derived from select from *_view and update *_table. The queries
> remain unchanged except for deciding whether to use *_table or *_view.

Traditionally materialized views exist, so that you do not need to code differently.
Your queries still run on the detail table, but are silently answered
by a suitable MV. The MV might have count + other aggregated columns
grouped by some columns, and thus be able e.g. shortcircuit a
"select count(*) from atab". The MV should be MVCC aware (have different
values for different snapshots) and not substantially reduce possible
concurrency of updates to the base table.
> For some further background - the base tables are a mirror of accpac
> tables (augh!) from mssql. The view and summary table gathers
> information from 5 or so of these tables including aggregates,
> conditionals, sub-selects (different queries to the same base tables)
> and deep joins. Perhaps my imagination is too limited - but I
> don't see
> how it would be easy to make syntactical sugar for this and still
> maintain the performance I describe above. For about 30 lines of
> pl/pgsql and some application-side updates (again from the
> view to the
> summary table) in the synchronization script it seems acceptable.

As long as you can formulate a normal view on the above statement,
you should be able to tell the db to materialize that.

A good MV feature would be able to use that MV regardless of whether
you select from the view, or use a statement that the view is a generalization of.

I think MV's where originally invented to boost benchmark results
and thus had to operate on given sql to base tables.

Andreas


Re: Postgresql Materialized views

From
Tom Lane
Date:
"Zeugswetter Andreas ADI SD" <Andreas.Zeugswetter@s-itsolutions.at> writes:
> Traditionally materialized views exist, so that you do not need to code differently.
> Your queries still run on the detail table, but are silently answered
> by a suitable MV. The MV might have count + other aggregated columns
> grouped by some columns, and thus be able e.g. shortcircuit a 
> "select count(*) from atab". The MV should be MVCC aware (have different
> values for different snapshots) and not substantially reduce possible 
> concurrency of updates to the base table.

Note that you just raised the minimum bar for implementation of the
feature by a couple orders of magnitude.  We cannot automatically
substitute an MV into queries unless this is guaranteed not to change
the results.  No lazy updates, MVCC transparency required, etc.
        regards, tom lane


Re: Postgresql Materialized views

From
"Greg Sabino Mullane"
Date:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160


> My point is simply this: The lack of a clear formal process for feature 
> requests leads to this degradation in the conversation. Without a formalized 
> structure, the conversation devolves rapidly into an argument over semantics 
> and word choice.
...
> There needs to be a way to evaluate the demand for a specific feature as 
> well as the benefits and the effort it will require.

You could always start a page on the developer's wiki:

http://developer.postgresql.org/

That would seem to be a good place to at least describe the problem in detail, 
show how you would like a feature to behave, and have people add the pros and 
cons of certain approaches. Certainly would be better to have a page to point 
to rather than trying to trawl through mailing archives (heck, the page could 
even mostly be a collection of such links).

- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200801141104
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8

-----BEGIN PGP SIGNATURE-----

iD8DBQFHi4gvvJuQZxSWSsgRA66dAKCGCPBPDfTFDoizE0WDwXBzDK/W3ACg8dwZ
99OvuSU9PPmG6XDPPK2iQzA=
=Xseg
-----END PGP SIGNATURE-----




Re: Postgresql Materialized views

From
"Zeugswetter Andreas ADI SD"
Date:
> > Traditionally materialized views exist, so that you do not need to code differently.
> > Your queries still run on the detail table, but are silently answered
> > by a suitable MV. The MV might have count + other aggregated columns
> > grouped by some columns, and thus be able e.g. shortcircuit a
> > "select count(*) from atab". The MV should be MVCC aware (have different
> > values for different snapshots) and not substantially reduce possible
> > concurrency of updates to the base table.
>
> Note that you just raised the minimum bar for implementation of the
> feature by a couple orders of magnitude.  We cannot automatically
> substitute an MV into queries unless this is guaranteed not to change
> the results.  No lazy updates, MVCC transparency required, etc.

Yes, unfortunately. But don't you also think that this is what makes it
a worthwhile feature ?

I mean, we do have the doityourself triggered summary table approach,
which is not overly difficult to set up. It needs some thought and possibly design
by the user to solve the most obvious concurrency issues, but it is doable.

Imho MV could be separated in 2 parts:
1: materialized and MVCC aware views (only used explicitly)
2: add the smarts to rewrite sql

Part 1 is already useful by itself since it provides a generic and easy
solution to concurrency for the user. (probably nice and mindboggling, how to
best implement that, though :-)

The "lazy update" and non MVCC approach imho sounds too much like your
"you can make it arbitrarily fast if it does not need to be correct" :-)

Andreas


Re: Postgresql Materialized views

From
"Joshua D. Drake"
Date:
Roberts, Jon wrote:

>> 2) In the end, this is an open source *community*; no amount of formal
>> feature requesting will have any material impact on what actually gets
>> implemented, because there isn't any central control.  
> 
> Wow.  Being new to Open Source, this amazes me.

Well it depends on what you mean by formal feature requesting. I will 
agree with Tom that we are a bunch of cats, you can not herd us. However 
feature requests do get done and do have material impact.

However it usually takes more work than it should to actually get the 
feature accepted and or committed.


>> someone pays them to do something specific.  Certainly, some
>> contributors pay attention to what's being requested, but I see no
>> reason to think that increasing the level of formality will help them.
> 
> What happens when a person adds a feature or changes the architecture of the
> database that is perceived by some as incorrect or going in the wrong
> direction?  

They can't add a feature. They can submit a feature for inclusion but if 
we don't like it we don't take it. It is the hope that people who are 
trying to submit follow this:

http://www.postgresql.org/docs/faqs.FAQ_DEV.html

Sincerely,

Joshua D. Drake


> 




> 
> 
> Jon
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
> 



Re: Postgresql Materialized views

From
Ron Mayer
Date:
Mark Mielke wrote:
> Mark Mielke wrote:
>> Counts, because as we all know, PostgreSQL count(*) is slow, and in
>> any case, my count(*) is not on the whole table, but on a subset.
>> Doing this in a general way seems complex to me as it would need to be
>> able to evaluate whether a given INSERT or UPDATE or one of the
>> dependent tables would impact the WHERE clause for the materialized
>> view, and it still wouldn't know which rows to add/update/remove
>> without detailed analysis, so it would either be throwing out the
>> entire materialized view and recreating it on INSERT or UPDATE (or
>> deferring until the next query?) in which case it may be very slow, or
>> it may be very complex.
> 
> Bah. I forgot to add: The feature I've been wondering about (and not
> necessarily looking for somebody else to do, although I don't think I
> know the code well enough to do it at this point):
> 
> Web applications often make the same queries over and over. While
> memcache can be used to cache results, the memcache interface is
> different from the web application interfere requiring complex code, and
> as well, one loses the transaction guarantees as the memcache results
> are not guaranteed to be up-to-date with the database. 

Regarding up-to-dateness note that there is a pgfoundry project that
helps there.   http://pgfoundry.org/projects/pgmemcache/   The other
advantages of doing the caching outside the database is that (a) the
memory for the cached results don't have to sit in the database machine,
and (b) you can cache post-processed (rendered into HTML or gifs)
fragments rather than raw data.


Re: Postgresql Materialized views

From
Bruce Momjian
Date:
Added to TODO:
* Add the ability to automatically create materialized views  Right now materialized views require the user to create
triggerson the  main table to keep the summary table current.  SQL syntax should be able  to manager the triggers and
summarytable automatically.  A more  sophisticated implementation would automatically retrieve from the  summary table
whenthe main table is referenced, if possible.
 

I also thought this was on the TODO list.

---------------------------------------------------------------------------

Zeugswetter Andreas ADI SD wrote:
> 
> > > Traditionally materialized views exist, so that you do not need to code differently.
> > > Your queries still run on the detail table, but are silently answered
> > > by a suitable MV. The MV might have count + other aggregated columns
> > > grouped by some columns, and thus be able e.g. shortcircuit a 
> > > "select count(*) from atab". The MV should be MVCC aware (have different
> > > values for different snapshots) and not substantially reduce possible 
> > > concurrency of updates to the base table.
> > 
> > Note that you just raised the minimum bar for implementation of the
> > feature by a couple orders of magnitude.  We cannot automatically
> > substitute an MV into queries unless this is guaranteed not to change
> > the results.  No lazy updates, MVCC transparency required, etc.
> 
> Yes, unfortunately. But don't you also think that this is what makes it 
> a worthwhile feature ?
> 
> I mean, we do have the doityourself triggered summary table approach,
> which is not overly difficult to set up. It needs some thought and possibly design
> by the user to solve the most obvious concurrency issues, but it is doable.
> 
> Imho MV could be separated in 2 parts:
> 1: materialized and MVCC aware views (only used explicitly)
> 2: add the smarts to rewrite sql
> 
> Part 1 is already useful by itself since it provides a generic and easy
> solution to concurrency for the user. (probably nice and mindboggling, how to 
> best implement that, though :-)
> 
> The "lazy update" and non MVCC approach imho sounds too much like your
> "you can make it arbitrarily fast if it does not need to be correct" :-) 
> 
> Andreas
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
> 
>                http://www.postgresql.org/docs/faq

--  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: Postgresql Materialized views

From
Tom Lane
Date:
"Zeugswetter Andreas ADI SD" <Andreas.Zeugswetter@s-itsolutions.at> writes:
>> Note that you just raised the minimum bar for implementation of the
>> feature by a couple orders of magnitude.

> Yes, unfortunately. But don't you also think that this is what makes it 
> a worthwhile feature ?

Well, my point is that taking automatic rewriting as a required feature
has at least two negative impacts:

* it rules out any form of lazy update, even though for many applications
an out-of-date summary view would be acceptable for some purposes;

* requiring MVCC consistency will probably hugely reduce the variety of
views that we can figure out how to materialize, and cost performance
even for the ones we can do at all.

It's not zero-cost, even if you consider implementation effort and
complexity as free (which I don't).
        regards, tom lane


Re: Postgresql Materialized views

From
"Heikki Linnakangas"
Date:
Merlin Moncure wrote:
> On Jan 12, 2008 4:19 PM, Simon Riggs <simon@2ndquadrant.com> wrote:
>> On Sat, 2008-01-12 at 13:27 +0100, Jean-Michel Pouré wrote:
>>
>>> Please pick-up this important issue for developpers. There is no need to
>>> concentrate on complex issues, when handling materialized views could
>>> boost somme web apps. by a factor of 10 or more.
>> It's more complex than you think, but the main reason was that HOT was a
>> prerequisite for making summary tables work efficiently, which is only
>> now just about to go live into 8.3
> 
> +1

I don't quite agree with that. HOT certainly speeds up UPDATEs on small 
tables, like you a summary table, but there's a lot of use cases like 
data warehousing, where the summary tables are not updated that often 
for the updates to become a bottleneck.

> If you know how to write triggers, materialization techniques aren't
> all that difficult.  The real technical limitation was not lack of
> materialization techniques (write triggers), but was dealing with the
> mvcc penalty.  Previously to HOT, for summary tables I would redirect
> the trigger to insert to a 'roll up' table and move the data to the
> summary on cron or from an application event.
> 
> Materialized views are syntax sugar (but still very sweet).

There's two things involved in materialized views:

1. Automatically updating the materialized view, when the tables change. 
This can be done with triggers, right now, but requires quite a bit of 
manual work to set up, especially with more complex views.

2. Using the materialized views to speed up existing queries. For 
example, if you have a materialized view on "SELECT COUNT(*) FROM foo", 
and someone issues the query "SELECT COUNT(*) FROM foo", the planner 
should automatically use the view to satisfy that.

1 is syntactic sugar, but 2 isn't.

These are orthogonal features. Implementing just 1 without 2 would still 
be very useful, and in fact that seems to be what most people mean by 
materialized views.

--   Heikki Linnakangas  EnterpriseDB   http://www.enterprisedb.com


Re: Postgresql Materialized views

From
Simon Riggs
Date:
On Wed, 2008-01-16 at 11:16 +0000, Heikki Linnakangas wrote:
> Merlin Moncure wrote:
> > On Jan 12, 2008 4:19 PM, Simon Riggs <simon@2ndquadrant.com> wrote:
> >> On Sat, 2008-01-12 at 13:27 +0100, Jean-Michel Pouré wrote:
> >>
> >>> Please pick-up this important issue for developpers. There is no need to
> >>> concentrate on complex issues, when handling materialized views could
> >>> boost somme web apps. by a factor of 10 or more.
> >> It's more complex than you think, but the main reason was that HOT was a
> >> prerequisite for making summary tables work efficiently, which is only
> >> now just about to go live into 8.3
> > 
> > +1
> 
> I don't quite agree with that. HOT certainly speeds up UPDATEs on small 
> tables, like you a summary table, but there's a lot of use cases like 
> data warehousing, where the summary tables are not updated that often 
> for the updates to become a bottleneck.

I should have said that was *my* reason for not doing it sooner.

My thinking was if you load a 1000 rows and they all have the same key
in your summary table then you'll be doing 1000 updates on a single row.
With HOT that will cause the block to fill up and then we wipe out the
previously updated rows, since they are inserted and deleted in same
transaction. So all 1000 updates can occur without going beyond that
single block. Much faster, no mess. 

Before HOT, large loads were worse, since there was no chance of
VACUUMing the table between updates. 

--  Simon Riggs 2ndQuadrant  http://www.2ndQuadrant.com



Re: Postgresql Materialized views

From
Andreas Pflug
Date:
Simon Riggs wrote:
>
> My thinking was if you load a 1000 rows and they all have the same key
> in your summary table then you'll be doing 1000 updates on a single row.
>   
This is true because the statement level triggers are still rudimentary, 
with no OLD and NEW support. A single AFTER statement trigger execution 
could maintain the summary table with much less effort.

Regards,
Andreas



Re: Postgresql Materialized views

From
"Florian G. Pflug"
Date:
Tom Lane wrote:
> Well, my point is that taking automatic rewriting as a required feature
> has at least two negative impacts:
> 
> * it rules out any form of lazy update, even though for many applications
> an out-of-date summary view would be acceptable for some purposes;
> 
> * requiring MVCC consistency will probably hugely reduce the variety of
> views that we can figure out how to materialize, and cost performance
> even for the ones we can do at all.
> 
> It's not zero-cost, even if you consider implementation effort and
> complexity as free (which I don't).

There is one big additional advantage of automatic rewriting though, I 
believe. If we had the infrastructure to recognize that possibility of 
using a predefined (materialized) view for executing a query, we can 
also use that infrastructure to get implement a kind of optimizer hints.

How? We'd need "statistics-materialized views", which don't materialize 
the full result of the view, but instead compute it's statistical 
properties (the same which ANALYZE computes for a table). When planning 
a query we can then substitute the guessed values for rowcount and 
friends of a subplan by the values computed for the corresponding 
statistics-materialized view.

However, until someone figures out *how* to *actually* recognize that 
possibility of using a MV for executing a query, this is just 
hand-wavering of course...

regards, Florian Pflug