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

Groups > comp.databases.postgresql > #157

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

Show all headers | View raw

On Mon, 27 Jun 2011 11:17:17 +0200, Laurenz Albe wrote:


> 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.

Accepted. As for my insults, I will have to refer you back to this:
http://tinyurl.com/68gu822
That, in my humble opinion, is a work of an idiot, period. That isn't an 
insult, that is just a statement of fact.

> 
> 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.

They would have saved the day. I needed the way to make certain that the 
existing indexes are used. In other words, I believe that I am smarter 
than the optimizer and that I should be able to override its decisions, 
at my own peril, of course.

> 
> 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.

Well, my knowledge of PostgreSQL was, in my opinion, quite satisfactory. 
I did read the literature, I did read several articles, I did read Greg 
Smith's tuning book and I did engage in the learning process for several 
months. Given my experience with other databases (Oracle, MySQL), I'd say 
that my knowledge was far above the knowledge of an average Postgres user.


> 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.

True. The original system was tuned by the people who know 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.

Actually, that is precisely the punch line of the Postgres promotors. 
EnterpriseDB even sells a product that is supposed to make it easier 
still. They are saying that similarities are such that it is fairly easy 
to do just that, change few things, recompile and, voila. I am not going 
to name names, but I have attended several of the NY PUG meetings and 
that's precisely the claim that has been made. I am glad that we agree on 
that. You can trust me, however, that hints would have saved the day.

> 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.

I did. The size of the histograms is 1024. That ought to be enough.

> 
> [...]
>> Hints are a solution of the last resort.
> [...]
> 
> I think we agree on that one. Good.

But they have to be there, as a solution of the last resort. The main 
problem with Postgres is that there is no tuning methodology, there is 
not much one can do to help himself. Postgres doesn't allow its users to 
control the query plan. It reflects a deep mistrust toward its own users.

> 
> 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

It's not just tools, it's the architecture. Plans of the running 
statement are not visible because they are not shared. You cannot see 
them. There is no way to tell whether the executing process has just 
executed "set enable_seqscan=off". You don't see the running plan and you 
don't see the execution environment for that process. The only way you 
could see that would be to have a shared memory area to read it from. In 
Oracle terminology, that would mean a shared pool.


> 
>> 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?

I don't. I can only see that PostgreSQL is much less popular than MySQL 
and I can see that it isn't getting better. I can see that the meeting in 
NYC was a flop, despite people like me desperately seeking for an 
alternative to Oracle. Sorry, PostgreSQL is not it. As I've stated above, 
the main problem is a complete lack of tuning methodology. There is not 
much user can do, short of posting the output of explain analyze, 
preferably formatted using www.depesz.com. User or a DBA doesn't have a 
methodology that could lead to a better performing plan. And yes, the 
fact that Postgres designers do not trust their users enough to provide 
them with means to override the optimizer decisions, should all else 
fail, is also deeply disturbing. Of course, now I can pull my final 
objection: PostgreSQL is the ONLY major database system which does so. 
Even Firebird designers have accepted the inevitable and will provide 
basic hints in the version 3.0, despite the similar objections as are the 
one made by PostgreSQL developers.


> 
> Writing to the mailing list to solve a performance problem might work
> better than you think. 

It is not a tuning methodology. Sorry. This is simply unacceptable. I 
will not do so, under any circumstances.

> 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. 

Unfortunately, if I have to spend money for the pilot project to succeed, 
it will not be on PostgreSQL. If we are talking about spending money, 
there are other databases which are commercial from scratch. That is 
probably the reason for such a low popularity of PostgreSQL.


> 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 :^)

The trouble is that I need to have hints as an insurance policy. Should 
all else fail, I want to be able to force my decision upon the optimizer.
Humans are still much smarter than computer programs and know the data 
better.

> 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 didn't just get rejected, I got steamrolled. The argument that hints 
are unnecessary is indefensible, unless you can prove that the optimizer 
is smarter than humans. On the other hand, such a claim would be not only 
preposterous, it would be downright rude.
I've seen many bad optimizer decisions by the Postgres optimizer which 
I've influenced through the set of "set" commands. Unfortunately, when 
application is written in Hibernate, it's easy to rewrite a SQL command 
or two, but to enter countless "set" commands is daunting at best. Of 
course, those "set" commands will stay in effect even after the 
problematic command has been executed, so you'll have to do it all over 
again, for the next command, plus keeping in mind the switches for the 
previous executions.


> 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? 

Because nobody has proven me wrong. The pearls of wisdom, like the URL I 
quoted at the beginning of this post, just reinforce me in my argument. 
In essence, the argument boils down to the following points:
- Hints are needed. I know that for a fact from my personal experience.
  That is beyond debate. I do trust my own eyes.
- Humans are smarter than computer programs. To claim otherwise is rude,
  to say the least.
- Refusal to provide hints reflects deep mistrust toward the users.
- PostgreSQL has no usable tuning methodology that user could follow to
  speed up the SQL execution. None whatsoever. 



> 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.

On the other hand, many people who have tried talking sense into 
PostgreSQL people and have failed, might have stopped using PostgreSQL 
altogether. Maybe that's why PGEast in NYC was such a flop? There is a 
reason for so much larger degree of popularity of MySQL, even after its 
purchase by Oracle. Has anyone ever asked what is PostgreSQL doing wrong?

> 
> 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?

Yes, of course I can. I was unable to find even a single TPC benchmark on 
the TPC site, done by using Postgres. TPC (stands for "Transaction 
Processing Council") is the place where people place TPC benchmarks. 
Databases used for benchmarks are the usual suspects: Oracle, MS SQL, 
DB2, Sybase and some more exotic versions EXASQL. I didn't find any 
Postgres benchmark there, feel free to correct me if I'm wrong.

> "... the much better performing commercial databases like Oracle or
> DB2."
> 
> Can you justify that?

Yes, I can. The reasons why both Oracle and DB2 are, generally speaking, 
much faster than Postgres are 
- proper partitioning
- the ability to parallelize queries
Both of those abilities are missing from Postgres. In particular, Oracle 
can do many things that Postgres cannot. Here I have in mind things like 
the star schema, bitmap indexes are still somewhat painful topic, index 
organized tables, index clusters, hash clusters, full index scan, fast 
index scan or a skip scan. And I didn't even mention RAC, "AS OF" queries 
or flashback.


> "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?

The "reasonable comparison" must start with the fact that Oracle is the 
2nd largest software company in the world and that its resources are many 
times larger than the resources at the disposal to the Postgres 
developers. If Postgres wants to compete with Oracle, it can do so only 
based on price, definitely not on the features or performance. Even so, 
if you want people to consider Postgres being alternative at all, which 
wast majority of companies is not doing, you have to ask corporate users 
what do they want. The topic of hints have popped up again and again. And 
will not go away. As a matter of fact, I once stated that I will write a 
hint system myself. The project that I am currently working on and is 
consuming the most of my spare time will be done by August the 1st. I 
will then renew my C programming, I used to be pretty good at that, and 
write it, even if it takes me a full year to do so. 

> 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.

What allows me to make such a broad comparison is the fact that PostgreSQL 
has no tuning methodology. If, for instance, the two of us are given the 
same SQL statement to make faster, you on Postgres and me on Oracle, I 
will have a method to follow and will, most likely, end up with a 
solution, much, much before you. If all else fails, I will be able to 
just force the execution plan that I like. You will still be waiting for 
an answer from the mailing list.


> 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.

Well, it's like cruising on a passenger ship which doesn't have life 
boats. Most likely, you will not need them but the passengers on the 
Titanic could have used few more of those. 




> 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.

Well, I will refrain from judgment here, but I am still waiting for an 
implementation. I've mentioned Sphinx, which requires globally unique 
bigint as the key. Maybe, there is a way to do it without. Let's wait and 
see. 

> 
> 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?

It seems that we do operate in different environments. In my world, DBA 
acts as goalkeeper who evaluates and filters out acceptable solutions. 
I've been working for several CIO's and have been advising on a vide 
range of software, from monitoring software, backup software to 
alternative DB software. I cannot recollect an instance of my advice not 
being followed. The decision, of course, is made by the CIO, but the 
evaluation is my responsibility. That is, after all, how I ended up with 
Postgres. Now, I have a problem: CIO has made a decision, based on my 
recommendation, and I have to tell him that I was wrong. I was able to 
kill one porting project, but that doesn't mean that I am off the hook.




Finally, let me explain what am I doing here. What I've set out to do is 
to warn anybody who wants to replace Oracle by Postgres that it will not 
be as easy as it looks and that there are terminal deficiencies in the 
mentality of PostgreSQL developer which will never allow it to gain much 
popularity or become a real alternative to Oracle. Had I been properly 
warned, I wouldn't have ended with such an egg on my face. I will only 
react if someone mentions conversion from Oracle. People ought to be 
warned.


-- 
http://mgogala.byethost5.com

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