Re: Anyone working on better transaction locking?

"Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote in message
news:8236(dot)1049906884(at)sss(dot)pgh(dot)pa(dot)us(dot)(dot)(dot)
> "Ron Peacetree" <rjpeace(at)earthlink(dot)net> writes:
> > "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote in message
> > news:4096(dot)1049860699(at)sss(dot)pgh(dot)pa(dot)us(dot)(dot)(dot)
> >> Ron, the tests that I've seen offer no support for that thesis.
>
> > What tests? I've seen no tests doing head-to-head,
> > feature-for-feature comparisons (particularly for low level
features
> > like locking) of PostgreSQL vs the "biggies": DB2, Oracle, and SQL
> > Server. What data I have been able to find is application level,
and
> > certainly not head-to-head.
>
> Who said anything about feature-for-feature comparisons? You made
an
> (unsupported) assertion about performance, which has little to do
with
> feature checklists.
>
That's not quite fair. My assertion was about the performance of an
exact feature in comparison to that same feature in another DB
product, not about overall application level performance... As I
said, I'll get back to you and the list on this.

> The reason I don't believe there's any fundamental MVCC problem is
that
> no such problem showed up in the head-to-head performance tests that
> Great Bridge did about two years ago. GB is now defunct, and I have
> not heard of anyone else willing to stick their neck out far enough
to
> publish comparative benchmarks against Oracle. But I still trust
the
> results they got.
>
Last year eWeek did a shoot out that PostgreSQL was notable in its
absence from:
http://www.eweek.com/print_article/0,3668,a=23115,00.asp
Taking those results and adding PostgreSQL to them should be eminently
feasible since the entire environment used for the test is documented
and the actual scripts and data used for the test are also available.
Of course, MySQL has been evolving at such a ferocious rate that even
one year old results, let alone two year old ones, run the risk of not
being accurate for it.

> I have helped various people privately with Oracle-to-PG migration
> performance problems, and so far the issues have never been MVCC or
> transaction issues at all. What I've seen is mostly planner
> shortcomings, such as failure to optimize "foo IN (sub-SELECT)"
> decently. Some of these things are already addressed in development
> sources for 7.4.
>
It's probably worth noting that since SQL support was added to
Postgres rather than being part of the product from Day One, certain
"hard" SQL constructs may still be having teething problems. NOT IN,
for instance, was a problem for both Oracle and SQL Server at some
point in their history (fuzzy memory: pre Oracle 6, not sure about SQL
Server version...)

> >> Postgres certainly has plenty of performance issues, but I have
no
> >> reason to believe that the fundamental MVCC mechanism is one of
> >> them.
>
> > Where in your opinion are they then? How bad are they in
comparison
> > to MySQL or any of the "Big Three"?
>
> See the TODO list for some of the known problems. As for "how bad
are
> they", that depends completely on the particular application and
queries
> you are looking at ...
>
Fair enough.