Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.postgresql > #156

Re: Is PostgreSQL good? (was: migrating oracle to postgres)

Show all headers | View raw

Mladen Gogala wrote:
>> I have no problem to discuss hints and other perceived or real
>> shortcomings of PostgreSQL, only before I do this I wanted to ascertain
>> that you are still able to post a civil reply. I wanted to bring the
>> heat level down.
>
> You wanted to bring the level down by telling me that I sound like a
> broken record? I do find that interesting.

I apologize for the quip -- I tried to stay calm in the face of all your
insults against PostgreSQL and its developers, but I couldn't hold this
one back.

I try to sum up your arguments why hints are necessary:
- You missed them in a time constrained porting project where they might
  have saved the day.

I think that this is a valid concern (remember, I'm not dead set against hints).
However, a porting project from a database system you know well to one
that you don't know well -- with project managers tapping fingers and
breathing down your neck -- is something that in my experience has a very
good chance of failing, no matter what.

There probably went some tuning effort (and some hinting) into getting the
original system to perform as it did, and that was done by somebody who
knows Oracle.
That's not your fault -- whoever planed that migration project should have
known better than to assume that you just have to rewrite a couple of triggers
and the system will perform well.

I trust that you made sure that it were really bad query plans that brought
the performance down, that you had current statistics and default_statistics_target
set high enough.

[...]
> Hints are a solution of the last resort.
[...]

I think we agree on that one. Good.

Another thing where I agree with you is that Oracle has more
options for the end user to analyze what a long running query is currently
doing and why. In PostgreSQL you'll typically have to analyze these things
later, and you'll need tools that are not part of core PostgreSQL.

> If they are not there, the database will not get used.

That's a bold statement. How do you explain that there are people who
successfully use PostgreSQL for big performance-critical applications?

Writing to the mailing list to solve a performance problem might work
better than you think. But you are not limited to that. You can either
hire people who can give you the answers, or you can build up skills to
answer the question yourself. Writing to the list is only the low-cost,
low-end option.

Free open source software works differently in many ways. Don't consider it
as something to save money primarily. If you want to do it right, you will
have to invest money, either into building skills or into hiring them.
You can read the code and modify the optimizer, you can even build a hint
system into it. That's what I call "providing the necessary tools for
the users to have chance to help themselves".
But by then you probably understand PostgreSQL well enough to get away
without hints :^)

Another misunderstanding on your side is to separate "the community" and
"the users". In free open source, the users are an integral part of the
community. I guess you sense a split here because you "got rejected" on
a mailing list.

I did not witness the exchange, but could it be that you entered there with
the deprecatory language and the absolute certainty that you are right
that you exhibited here? I normally get good reasons if my ideas get rejected.
Your hapless Oracle Analyst will have to remain polite even if you call
Oracle's optimizer a crock of shit, but there is no such constraint on
people who talk to you on PostgreSQL mailing lists.

Anyway, I digressed from a technical discussion. Sorry.

But before I return, let me complain about some unfounded accusations that you make:

"Nobody uses PgSQL for benchmarks (see www.tpc.org) because of the
performance problems."

Can you justify this statement?

"... the much better performing commercial databases like Oracle or DB2."

Can you justify that?

"Oracle still defeats Postgres in performance, and by a wide margin."

You use this to swipe any concern that Oracle's implementation may be
suboptimal in some respects. How can we make reasonable comparisons in the
face of arguments like this?

As you as an engineer must know, performance is not a magic number that
drops out of a benchmark program and has a linear order.
That's what management would like, and commercial databases go a long way
to "tune" for commercial benchmarks just for that reason.
I would never claim that PostgreSQL performs better than Oracle nor vice versa.
You need a lot of additional constraints to make a meaningful statement,
and that statement could not easily be used outside its context.

I think we have pretty much exhausted what we have to say concerning hints.
It seems that we don't disagree that much, except that I don't think that
PostgreSQL's lack of query hints renders it useless in real-life "enterprise"
scenarios.

About ROWID:

Upon further meditation, I think that Oracle did not originally intend
to provide ROWID as a goodie for the users to quickly locate rows.
The reason to keep it stable must be to make the implementation of indexes
easier. I don't think that "rows as moving targets" are the real problem,
that is easily solved with unique keys. The problem is that a changing
ROWID will force the index to change whenever the row is updated.
This is a price that PostgreSQL has to pay (extra work on update,
indexes must get vacuumed), but Oracle does not get this for free.
In order to keep ROWIDs stable, it must leave a trail of pointers from
the original location of a row to where the row is actually stored
("row chaining" is the technical term).
This will lead to extra disk accesses and a performance penalty in the
critical path of statement execution, and if things get too bad one might
have to reorganize the table (thereby changing the ROWIDs of the affected rows).

So I'd say that ROWID is at least a mixed blessing.

I still don't see how "rows as a moving target" should prevent global
indexes on inheritance hierarchies. Normal indexes have to deal with the
problem as well. What should make it harder for a global index?
In fact, there has been at least one attempt to implement such a solution:
http://archives.postgresql.org/pgsql-hackers/2009-03/msg01046.php
So I don't think that it is impossible by design.

Finally:

> Unfortunately for Josh, it is
> people like me who decide which database will get to live in the
> corporate server room.

That is interesting. You seem to operate in a different kind of business
environment than most of us.
Usually management (where purchase decisions of that magnitude are made)
will resort to glossy brochures and hired consultants rather than asking their
DBA. To some extent, that is because they know that the DBA will always advise
them to use the system he or she knows best. Right?

Yours,
Laurenz Albe 

Back to comp.databases.postgresql | Previous | Next — Previous in thread | Next in thread | Find similar

Thread

migrating oracle to postgres Username <user@domain.tld> - 2011-06-20 19:28 +0200
  Re: migrating oracle to postgres Mladen Gogala <no@email.here.invalid> - 2011-06-20 20:08 +0000
    Re: migrating oracle to postgres Username <user@domain.tld> - 2011-06-20 23:45 +0200
      Re: migrating oracle to postgres Mladen Gogala <no@email.here.invalid> - 2011-06-20 22:37 +0000
      Re: migrating oracle to postgres Mladen Gogala <gogala.mladen@gmail.com> - 2011-06-21 02:01 +0000
  Re: migrating oracle to postgres "Laurenz Albe" <invite@spam.to.invalid> - 2011-06-21 09:18 +0200
    Re: migrating oracle to postgres Mladen Gogala <gogala.mladen@gmail.com> - 2011-06-21 13:38 +0000
      Is PostgreSQL good? (was: migrating oracle to postgres) "Laurenz Albe" <invite@spam.to.invalid> - 2011-06-22 12:30 +0200
        Re: Is PostgreSQL good? Harry Tuttle <OTPXDAJCSJVU@spammotel.com> - 2011-06-22 13:15 +0200
        Re: Is PostgreSQL good? (was: migrating oracle to postgres) Mladen Gogala <gogala.mladen@gmail.com> - 2011-06-22 12:02 +0000
          Re: Is PostgreSQL good? "M. Strobel" <sorry_no_mail_here@nowhere.dee> - 2011-06-22 21:52 +0200
            Re: Is PostgreSQL good? Mladen Gogala <no@email.here.invalid> - 2011-06-22 22:43 +0000
          Re: Is PostgreSQL good? (was: migrating oracle to postgres) "Laurenz Albe" <invite@spam.to.invalid> - 2011-06-24 12:29 +0200
            Re: Is PostgreSQL good? Hans Castorp <REWYRLXHEGHO@spammotel.com> - 2011-06-24 12:57 +0200
              Re: Is PostgreSQL good? "Laurenz Albe" <invite@spam.to.invalid> - 2011-06-24 14:53 +0200
              Re: Is PostgreSQL good? Mladen Gogala <no@email.here.invalid> - 2011-06-24 16:32 +0000
            Re: Is PostgreSQL good? (was: migrating oracle to postgres) Mladen Gogala <gogala.mladen@gmail.com> - 2011-06-24 13:53 +0000
              Re: Is PostgreSQL good? (was: migrating oracle to postgres) "Laurenz Albe" <invite@spam.to.invalid> - 2011-06-27 11:17 +0200
                Re: Is PostgreSQL good? (was: migrating oracle to postgres) Mladen Gogala <gogala.mladen@gmail.com> - 2011-06-28 06:11 +0000
                Re: Is PostgreSQL good? "Laurenz Albe" <invite@spam.to.invalid> - 2011-06-28 10:34 +0200
                Re: Is PostgreSQL good? Mladen Gogala <gogala.mladen@gmail.com> - 2011-06-28 13:30 +0000
                Re: Is PostgreSQL good? "Laurenz Albe" <invite@spam.to.invalid> - 2011-06-29 09:50 +0200
                Re: Is PostgreSQL good? Mladen Gogala <no@email.here.invalid> - 2011-06-29 21:38 +0000
                Re: Is PostgreSQL good? "Laurenz Albe" <invite@spam.to.invalid> - 2011-06-30 09:14 +0200
                Re: Is PostgreSQL good? Mladen Gogala <mgogala@no.address.invalid> - 2011-06-30 13:22 +0000
            Re: Is PostgreSQL good? Don Y <nowhere@here.com> - 2011-06-30 07:05 -0700
              Re: Is PostgreSQL good? Mladen Gogala <no@email.here.invalid> - 2011-06-30 15:41 +0000
                Re: Is PostgreSQL good? Richard Kettlewell <rjk@greenend.org.uk> - 2011-06-30 16:49 +0100
                Re: Is PostgreSQL good? Mladen Gogala <no@email.here.invalid> - 2011-06-30 19:24 +0000
                Re: Is PostgreSQL good? Don Y <nowhere@here.com> - 2011-06-30 13:21 -0700
                Re: Is PostgreSQL good? Mladen Gogala <no@email.here.invalid> - 2011-06-30 22:42 +0000
                Re: Is PostgreSQL good? Don Y <nowhere@here.com> - 2011-07-01 11:57 -0700
                Re: Is PostgreSQL good? Mladen Gogala <gogala.mladen@gmail.com> - 2011-07-01 22:58 +0000
                Re: Is PostgreSQL good? Richard Kettlewell <rjk@greenend.org.uk> - 2011-07-01 14:07 +0100
                Re: Is PostgreSQL good? Mladen Gogala <gogala.mladen@gmail.com> - 2011-07-01 13:29 +0000
                Re: Is PostgreSQL good? Don Y <nowhere@here.com> - 2011-06-30 13:17 -0700
                Re: Is PostgreSQL good? Mladen Gogala <gogala.mladen@gmail.com> - 2011-06-30 23:53 +0000
              Re: Is PostgreSQL good? "Laurenz Albe" <invite@spam.to.invalid> - 2011-07-01 10:13 +0200
                Re: Is PostgreSQL good? Mladen Gogala <gogala.mladen@gmail.com> - 2011-07-01 13:38 +0000
                Re: Is PostgreSQL good? "Laurenz Albe" <invite@spam.to.invalid> - 2011-07-01 16:58 +0200
                Re: Is PostgreSQL good? Mladen Gogala <no@email.here.invalid> - 2011-07-01 15:33 +0000
                Re: Is PostgreSQL good? Don Y <nowhere@here.com> - 2011-07-01 11:39 -0700
                Re: Is PostgreSQL good? "Laurenz Albe" <invite@spam.to.invalid> - 2011-07-04 16:39 +0200
                Re: Is PostgreSQL good? Don Y <nowhere@here.com> - 2011-07-04 12:11 -0700
                Re: Is PostgreSQL good? "Laurenz Albe" <invite@spam.to.invalid> - 2011-07-05 08:58 +0200
                Re: Is PostgreSQL good? Don Y <nowhere@here.com> - 2011-07-05 11:29 -0700
                Re: Is PostgreSQL good? "Laurenz Albe" <invite@spam.to.invalid> - 2011-07-06 09:00 +0200
                Re: Is PostgreSQL good? Don Y <nowhere@here.com> - 2011-07-06 01:00 -0700
                Re: Is PostgreSQL good? "Laurenz Albe" <invite@spam.to.invalid> - 2011-07-07 09:05 +0200
                Re: Is PostgreSQL good? Don Y <nowhere@here.com> - 2011-07-07 00:34 -0700
                Re: Is PostgreSQL good? Matthew Woodcraft <mattheww@chiark.greenend.org.uk> - 2011-07-04 20:12 +0100
                Re: Is PostgreSQL good? Don Y <nowhere@here.com> - 2011-07-04 12:24 -0700
      Re: migrating oracle to postgres Robert Klemme <shortcutter@googlemail.com> - 2011-06-23 17:48 +0200
        Re: migrating oracle to postgres Mladen Gogala <no@email.here.invalid> - 2011-06-23 16:42 +0000
          Re: migrating oracle to postgres Mladen Gogala <no@email.here.invalid> - 2011-06-23 18:57 +0000
          Re: migrating oracle to postgres Robert Klemme <shortcutter@googlemail.com> - 2011-06-26 17:35 +0200
            Re: migrating oracle to postgres Mladen Gogala <gogala.mladen@gmail.com> - 2011-06-26 19:08 +0000
          Re: migrating oracle to postgres Robert Klemme <shortcutter@googlemail.com> - 2011-06-27 00:32 -0700
            Re: migrating oracle to postgres Mladen Gogala <no@email.here.invalid> - 2011-06-28 16:00 +0000
              Re: migrating oracle to postgres Robert Klemme <shortcutter@googlemail.com> - 2011-06-28 22:28 +0200

csiph-web